Need help understanding insert

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
sully6119
Forum Newbie
Posts: 4
Joined: Sun Nov 09, 2008 10:20 am

Need help understanding insert

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Need help understanding insert

Post 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.
Mark Baker
Forum Regular
Posts: 710
Joined: Thu Oct 30, 2008 6:24 pm

Re: Need help understanding insert

Post 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"
sully6119
Forum Newbie
Posts: 4
Joined: Sun Nov 09, 2008 10:20 am

Re: Need help understanding insert

Post 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?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Need help understanding insert

Post 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')";
...
sully6119
Forum Newbie
Posts: 4
Joined: Sun Nov 09, 2008 10:20 am

Re: Need help understanding insert

Post by sully6119 »

Haha! What a silly user error. Thanks for the help. It works now :)
Mark Baker
Forum Regular
Posts: 710
Joined: Thu Oct 30, 2008 6:24 pm

Re: Need help understanding insert

Post 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.
sully6119
Forum Newbie
Posts: 4
Joined: Sun Nov 09, 2008 10:20 am

Re: Need help understanding insert

Post 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 :)
Post Reply