Page 1 of 1

Need help understanding insert

Posted: Sun Nov 09, 2008 10:31 am
by sully6119
Hi all. I'm a Python/Perl programmer trying to learn PHP/MySql and running into something I don't quite understand. I began with a simple contacts database that I can input stuff from a webpage. What I don't understand is why I have to enter anything for the primary id key when it is auto-incremented. Further confusing is, tutorials show a database like mine and the php code doesn't try to insert an int for the key. Let me show you what I have:

Code: Select all

 
mysql> desc contacts;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment | 
| f_name | varchar(20) | YES  |     | NULL    |                | 
| l_name | varchar(20) | YES  |     | NULL    |                | 
| age    | int(11)     | YES  |     | NULL    |                | 
| email  | varchar(60) | YES  |     | NULL    |                | 
| hphone | varchar(20) | YES  |     | NULL    |                | 
| cphone | varchar(20) | YES  |     | NULL    |                | 
+--------+-------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
 
This works:

Code: Select all

 
mysql> insert into contacts values (0,'s', 's', 32, 'email', 'none', 'nhone');
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from contacts;
+----+--------+--------+------+-------+--------+--------+
| id | f_name | l_name | age  | email | hphone | cphone |
+----+--------+--------+------+-------+--------+--------+
|  1 | s      | s |   32 | email | none   | nhone  |
+----+--------+--------+------+-------+--------+--------+
1 row in set (0.00 sec)
 
From my php:

Code: Select all

 
<?php
include 'config.php';
include 'opendb.php';
 
$f_name = $_POST['f_name'];
$l_name = $_POST['l_name'];
$email  = $_POST['email'];
$age    = $_POST['age'];
$hphone = $_POST['hphone'];
$cphone = $_POST['cphone'];
 
$query = "insert into contacts (
          f_name, l_name, email, age, hphone, cphone) values (
          $f_name, $l_name, $email, $age, $hphone, $cphone)";
 
mysql_query($query) or die(mysql_error());
 
include 'closedb.php';
 
echo "<h3>Input Successful!</h3>"
?>
 
That gives me this error: Unknown column 'test' in 'field list'

I've tried putting a 0 in the $query VALUES string, and I get tihs error: Column count doesn't match value count at row 1

Can someone help me understand how this should work and why? Thanks in advance!

-Sully

Re: Need help understanding insert

Posted: Sun Nov 09, 2008 11:47 am
by califdon
You should not supply a value for an auto-increment field, just as you thought, and indeed I believe that will generate an error. The fact that the error message is referring to a field 'test' that isn't even in your table suggests that you are not connecting to the database you think you are. Check your include files.

Re: Need help understanding insert

Posted: Sun Nov 09, 2008 11:49 am
by Mark Baker
As with all SQL statements, strings (VARCHAR, CHAR, etc ) should be wrapped in quote marks... I'm sure it's the same in Python and Perl

I suspect the reference to "test" is because one of the values you're trying to insert contains the word "test"

Re: Need help understanding insert

Posted: Sun Nov 09, 2008 12:21 pm
by sully6119
First, thanks for the replies. I just did this test to see if I was connecting properly and it looks good:

Code: Select all

 
$sql = "SELECT * FROM `contacts`";
 
$result = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_array($result)){
        echo $row['0'], $row['1'],  $row['2'], $row['3'];
}
 
I get "1ssuivan32" and the error: "Unknown column 'Sean' in 'field list'". At this point, Sean is being passed as $f_name, the first item in $_POST. Previously it was "test" as suggested above.

Any more suggestions? The quoting idea seems logical, but is it possible PHP doesn't quote this stuff on its own? And if not, how do I force it?

Re: Need help understanding insert

Posted: Sun Nov 09, 2008 12:40 pm
by califdon
Mark Baker is absolutely right. Sorry, I overlooked that. Yes, you must explicitly use quotes around strings. You can use single or double quotes, but php $variables will be interpreted only if they are within double quotes. So your query should look like this:

Code: Select all

...
$query = "INSERT INTO contacts (f_name, l_name, email, age, hphone, cphone) 
    VALUES ('$f_name', '$l_name', '$email', '$age', '$hphone', '$cphone')";
...

Re: Need help understanding insert

Posted: Sun Nov 09, 2008 12:47 pm
by sully6119
Haha! What a silly user error. Thanks for the help. It works now :)

Re: Need help understanding insert

Posted: Sun Nov 09, 2008 1:18 pm
by Mark Baker
sully6119 wrote:The quoting idea seems logical, but is it possible PHP doesn't quote this stuff on its own? And if not, how do I force it?
It's quite possible when you're building the query like this. PHP has no inate knowledge of the database definition, although.
You should also escape all your values to ensure that there's no corrupted (or corrupting) data being injected into your SQL statements

If you use an abstraction layer such as PDO, then you can use prepared statements.

Re: Need help understanding insert

Posted: Sun Nov 09, 2008 1:48 pm
by sully6119
Good info. Thanks again Mark.

I have very little SQL experience. Most of my development tasks have been building (fairly) large software test automation suites and easing *nix system administration tasks so a lot of this is new to me :)