INSERT INTO HELP

[EricOnAdventure]

I am trying to use PHP to insert $variables into SQL with INSERT INTO.

I am pretty sure I am doing it wrong, after all, even this little sample I created is failing, any ideas?

 

 

DATABASE: (which worked without a hitch)

CREATE TABLE `inputtest` (
`id` int(4) NOT NULL auto_increment,
`GIVENNAME` varchar(50) NOT NULL default '', 
`FAMILYNAME` varchar(50) NOT NULL default '', 
`StudentTitle` varchar(50) NOT NULL default '', 
`EMAIL` varchar(50) NOT NULL default '', 
`PHONE` varchar(50) NOT NULL default '', 
PRIMARY KEY (`id`)
) 

The first page that is loaded is a default page which sets all $variables to '', that way I don't end up with 300 if statements.

$GIVENNAME = '';
$FAMILYNAME = '';
$StudentTitle = '';
$EMAIL = '';
$PHONE = '';

Then comes the page that takes the Session posts, if set, and turns them into $variables

Finally I input the $variables with the following:

mysql_connect("localhost", "root", "")or die("cannot connect");
mysql_select_db("ericgonzp")or die("cannot select DB");

	
$query = 	"INSERT INTO ericgonzp.inputtest(
GIVENNAME,FAMILYNAME,StudentTitle,EMAIL,PHONE,
)VALUES(
'$GIVENNAME','$FAMILYNAME','$StudentTitle','$EMAIL','$PHONE',
)"; 
                           

					   
$result=mysql_query($query);

echo $result;


if($result){
}

else {
echo "DATABASE ERROR";
}
?>

<?php
// close connection
mysql_close();
?>

The page displays DATABASE ERROR....

[requinix]

If you use mysql_error() to get the error message then it will hopefully clue you into those trailing commas.

 

Also, the mysql extension is really old and it's bad and you need to move to PDO or mysqli instead. Learn about them now and start using them before you write too much code and make it difficult to fix later.

You also need to learn about prepared statements because any naive hacker could break that code and make Bad Things happen to your site. PDO and mysqli both support prepared statements.

[Jacques1]

Your coding style in general is awkward.

• You've managed to use three(!) different naming styles in a single table: all_lowercase, ALL_UPPERCASE and PascalCase. C'mon. Pick one style and stick with it. I recommend all_lowercase for identifiers.
• If your scripts start with a long list of variable declarations, you're doing it wrong. Does it even make sense to blindly initialize all data with an empty string? This means you may end up with records that are entirely empty. Shouldn't there be a warning instead? Either way, there are arrays, and there are loops. There's never any need for cluttering your code with dozens of variables.
• For some strange reason, you've decided to tell your users that your database connection failed or that you couldn't select a database. What are they supposed to do with that info? This is your problem, not theirs, so the error belongs into an internal error log on the server (PDO can do that automatically).
• Your formatting looks entirely random. I recommend you grab a proper editor or IDE (integrated development envirnoment) and fix that. Formatting is very important for the readability of your code.

[gizmola]

Look, the error you have is this:

 

"INSERT INTO ericgonzp.inputtest(
GIVENNAME,FAMILYNAME,StudentTitle,EMAIL,PHONE,
)VALUES(

See the trailing comma after PHONE? (PHONE,)

 

That is invalid SQL syntax.

 

The same goes for:

 

'$GIVENNAME','$FAMILYNAME','$StudentTitle','$EMAIL','$PHONE',

Remove those commas, and it will probably work.

 

HOWEVER, as already stated, mysql_ extension has been removed from PHP. Your code only works because you are using an older version of PHP.

 

Please, do heed the advice from Requinix and Jacques. You should used Mysqli or PDO/Mysql (I prefer the PDO extension) and you really should clean up your code so it's readable.