MySQL: Why is it ignoring the text in the form field?

Question

I am trying to teach myself MySQL/PHP from the very beginning. The following code was lifted from various tutorial sites. I'm using phpMyAdmin provided by my webhost.

I made a table with an auto-incrementing field called "ID" and another field called "first" (varchar, limit 30, not null). Then I made a simple form with one text field named "first" and a Submit button. I type my name into the box and click Submit. This does create a row in the database with an ID, but the "first" field is always blank.

I tried replacing '$_POST[first]' with some straight-up words, and that worked - the words appeared in the table with an ID number just fine. That's how I know it is indeed managing to talk to the database, it's just not picking up the text field

After the INSERT statement runs, I have it display all the records in the table. It shows all of the ID numbers and then blank where "first" should be.

I also have it echo the INSERT statement. This is what the echo displays: INSERT INTO tblHurray(ID, first) VALUES ('','')

When I substitute words for '$_POST[first]', the echo looks like this: INSERT INTO tblHurray(ID, first) VALUES ('','words')

This is my first question so please let me know if I've left out any pertinent information! And thanks in advance for your help.

This is the form:

<form action="run_input.php" method="post">
Name: <input type="text" name="first">
<input type="Submit">
</form>

This is what runs when "Submit" is clicked:

<?
include("run_connect.php"); // this connects to the database, this works

$step1 = "INSERT INTO tblHurray(ID, first) VALUES ('','$_POST[first]')";
mysql_query($step1);
echo "$step1";

echo "<b><center>Database Output</center></b><br><br>";

$step2=mysql_query("SELECT * FROM tblHurray");

$num=mysql_numrows($step2);

$i=0;
while ($i < $num) {

$firstname=mysql_result($step2,$i,"first");
$ID=mysql_result($step2,$i,"ID");
echo "$ID: $firstname<br />";

$i++;
}
?>

Answer 1

This line

$step1 = "INSERT INTO tblHurray(ID, first) VALUES ('','$_POST[first]')";

needs to look like this:

$step1 = "INSERT INTO tblHurray(ID, first) VALUES ('','" . mysql_escape_string($_POST['first']) . "')";

It's a good habit to always quote your array keys (['first'] rather than [first]).

Plus it's good to get used to always escaping user input before inserting into the database to prevent SQL injection.

Also double-check to make sure the name of the input on your HTML form matches your PHP code.

$_POST['first']

is not the same as

$_POST['First']

Answer 2

Try

$step1 = "INSERT INTO tblHurray(ID, first) VALUES ('','".$_POST['first']."')";

Although, if you're really going learn it, then start with PHP PDO. It will save you ALOT of trouble in the long run, especially with parametrized queries.

Answer 3

Mark Biek nailed it, but as a fractional tweak, the mysql_real_escape_string function is better than mysql_escape_string as it factors in the character set associated with the connection.