php - 通过php将html表单数据发送到sql数据库

Question

我正在尝试将表单上收集的数据插入到 mysql 数据库中。我能够成功连接，但未插入数据。我读过很多类似的问题，但到目前为止还没有成功。

sqldatabase.php

<?php
$servername = "localhost";
$username = "USER";
$password = "PASS";
$dbname = "DATABASE";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

$first_name = $_POST['first_name'];
$last_name = $_POST['last_name'];
$first_spouse = $_POST['first_spouse'];
$last_spouse = $_POST['last_spouse'];
$address = $_POST['address'];
$city = $_POST['city'];
$state = $_POST['state'];
$zip = $_POST['zip'];
$phonehome = $_POST['phonehome'];
$phonecell = $_POST['phonecell'];
$email = $_POST['email'];
$dob = $_POST['dob'];
$occupation = $_POST['occupation'];
$shirt_size = $_POST['shirt_size'];
$cap_size = $_POST['cap_size'];
$shirtnum1 = $_POST['shirtnum1'];
$shirtnum2 = $_POST['shirtnum2'];
$desc = $_POST['desc'];
$bylaws_rules = $_POST['bylaws_rules'];
$umpires = $_POST['umpires'];
$alcohol = $_POST['alcohol'];
$waiver = $_POST['waiver'];


$sql="INSERT INTO 'softball_reg_2016' (first_name, last_name, first_spouse, last_spouse,
 address, city, state, zip, phonehome, phonecell, email, dob, occupation, shirt_size,
  cap_size, shirtnum1, shirtnum2, desc, bylaws_rules, umpires, alcohol, waiver)
  VALUES ('$_POST[first_name]', '$_POST[last_name]', '$_POST[first_spouse]', '$_POST[last_spouse]',
 '$_POST[address]', '$_POST[city]', '$_POST[city]', '$_POST[state]', '$_POST[zip]',
 '$_POST[phonehome]', '$_POST[phonecell]', '$_POST[email]', '$_POST[dob]', '$_POST[occupation]',
 '$_POST[shirt_size]', '$_POST[cap_size]', '$_POST[shirtnum1]', '$_POST[shirtnum2]',
 '$_POST[desc]', '$_POST[bylaws_rules]', '$_POST[umpires]', '$_POST[alcohol]', '$_POST[waiver]')";

echo "Connected successfully";

mysqli_close($conn);
?> 

我的html

<form action="/php/sqldatabase.php" method="POST" id="registration">

       <h2>Registration for 2016 Summer Season (April-September)</h2>  
    <p>
        <label for="name">Name:</label>
        <input type="text" id="first_name" name="first_name" placeholder="First Name" autofocus="" />
        <input type="text" id="last_name" name="last_name" placeholder="Last Name" />
    </p>

    <p>
        <label for="spouse">Name of Spouse<i>(Optional)</i>:</label>
        <input type="text" id="first_spouse" name="first_spouse" placeholder="First Name" />
        <input type="text" id="last_spouse" name="last_spouse" placeholder="Last Name" />
    </p>

    <p>
        <label for="address1">Address:</label>
        <input type="text" id="address" name="address" placeholder="Street Address" />
        <input type="text" id="city" name="city" placeholder="City" />
    </p>

    <p>
        <label for="address2"></label>
        <input type="text" id="state" name="state" placeholder="State" />
        <input type="number" id="zip" name="zip" placeholder="Zip Code" />
    </p>

    <p>
        <label for="phone">Phone:</label>
        <input type="tel" id="phonehome" name="phone" placeholder="Home Phone" />
        <input type="tel" id="phonecell" name="phone" placeholder="Work/Cell Phone" />
    </p>

    <p>
        <label for="phone">Email:</label>
        <input type="email" id="email" name="email" />
    </p>

    <p>
        <label for="dob">Date of Birth:</label>
        <input type="date" id="dob" name="dob" />
    </p>

    <p>
        <label for="occupation">Occupation (Former, if retired):</label>
        <input type="text" id="occupation" name="occupation" />
    </p>

    <div id="shirt">
    <p>
        <label for="size">Uniform:</label>
        <select name="shirt_size" id="shirt_size">
            <option value="">Shirt Size</option>
            <option value="medium">Medium</option>
            <option value="large">Large</option>
            <option value="XL">XL</option>
            <option value="2XL">2XL</option>
            <option value="3XL">3XL</option>
        </select>
        <select name"cap_size" id="cap_size">
            <option value="">Cap Size</option>
            <option value="XS/S">XS/S</option>
            <option value="S/M">S/M</option>
            <option value="M/L">M/L</option>
            <option value="L/XL">L/XL</option>
        </select>
    </p>

    <p>
       <label for="shirtnum">Shirt Number:</label>
       <input type="number" id="shirtnum1" name="shirtnum1" placeholder="1st Choice" min="0" max="99" />
       <input type="number" id="shirtnum2" name="shirtnum2" placeholder="2nd Choice" min="0" max="99" />
    </p>
    </div>
    <div id="describe">

    <p>
      <span class="describe1">
        <b>Describe any information you deem important regarding your ability and/or availability or any other information you deem important to the season.</b>
        <textarea name="description" id="desc" cols="30" rows="10"></textarea>
      </span>
    </p>
    </div>

    <div id="ethics">
        <h2>Code of Ethics</h2>
        <p>
            <span class="ethics1">
            <input type="checkbox" id="bylaws_rules" name="bylaws_rules" /> 
                I agree to abide by the Bylaws and decisions of the Club and Club Officials.
            </span>
        </p>

        <p>
            <span class="ethics1">
            <input type="checkbox" id="umpires" name="umpires" />
                I agree to accept the decisions of the Umpires and Team Managers.
            </span>
        </p>

        <p>
            <span class="ethics1">
            <input type="checkbox" id="alcohol" name="alcohol" />
                I agree to abstain from alcoholic beverages prior to a game.
            </span>
        </p>
        </div>

        <div id="waiver">
            <h2>Release of Liability</h2>

                <p>
                <b>I agree to hold harmless the club.</b>
                </p>
                <input type="checkbox" id="waiver" name="waiver" />
        </div>

        <ol class="requires">

            <li>Dues are $95 and should be received by April 6, 2016</li>
            <li>If you decline to play after being drafted, your registration fee will not be refunded.</li>
            <li>All members must be at least 50 years old by December 31, 2016</li>
            <li>The deadline for receipt of registrations is April 6, 2016. Registrations received after this date will 
                not be processed for the player drat. Assignments to teams will then be made according to League guidelines
                regarding late registering players.</li>
            <li>Registrations received without the correct fee will not be considered as received and will not be valid until the correct fee is received.</li>
        </ol>

        <p>&nbsp;</p>
        <p>&nbsp;</p>
        <p>&nbsp;</p>
        <p>
            <button type="submit" id="register">Register!</button>
        </p>

    </form>

感谢您的帮助!

Answer 1

作为社区 wiki 发布。

这里有一些问题。

• 首先，您从未执行过查询。
• 你从不检查 empty()字段，这可能会在表中插入空行。

查阅手册:

• > http://php.net/manual/en/mysqli.query.php

面向对象的风格

mixed mysqli::query ( string $query [, int $resultmode = MYSQLI_STORE_RESULT ] )

程序风格

mixed mysqli_query ( mysqli $link , string $query [, int $resultmode = MYSQLI_STORE_RESULT ] )

那么您对表使用了错误的标识符限定符:

INSERT INTO 'softball_reg_2016'
            ^                 ^

是常规单引号，应该是勾号或根本没有引号:

INSERT INTO `softball_reg_2016`

仅此一点就会引发语法错误。

阅读标识符限定符:

• > http://dev.mysql.com/doc/en/identifier-qualifiers.html

然后您使用 desc 作为列名，它是 MySQL 保留字。这也会给你带来一个错误。因此，要么将其重命名为其他名称，要么将其包装在刻度中。

`desc`

引用:

• > https://dev.mysql.com/doc/refman/5.5/en/keywords.html

同时检查错误:

• > http://php.net/manual/en/mysqli.error.php
• > http://php.net/manual/en/function.error-reporting.php

另外，您当前的代码对SQL injection开放。使用mysqli_* with prepared statements ，或PDO与 prepared statements .

此外，既然您已经向 POST 数组声明了变量，为什么还要将 POST 数组放入查询中？只是使用了变量。您使用更多代码实际上毫无意义。

正如评论中所述:

"You also have two input fields with same name: phone. Is that intended or just the copy/paste usual problem? – FirstOne"