INSERT wierdness

[idgcorp]

Hello all, i am fairly new to php/sql and I have come up with a weird problem that I am unable to solve.

I have this php code: (password, etc all have valid values at this point)




$link = mysql_connect($hostname,$username, $password);

if (! $link) DIE ("Unable to connect to database! Please try again later.");
mysql_select_db($dbname,$link);

$FirstName = $_POST['FirstName'];
$LastName = $_POST['LastName'];
$DJName = $_POST['DJName'];
$Affiliations = $_POST['Affiliations'];
$Address = $_POST['Address'];
$Telelphone = $_POST['Telelphone'];
$Email = $_POST['Email'];
$Passwd = $_POST['Passwd'];
$Format = $_POST['Format'];
$Styles = $_POST['Styles'];
$HowHear = $_POST['HowHear'];
$Comments = $_POST['Comments'];
$PhotoURL = $_POST['PhotoURL'];
$WebSite = $_POST['WebSite'];

mysql_query("INSERT INTO MailingList (FirstName, LastName, DJName, Affiliations, Address, Telelphone, Email, Passwd, Format, Styles, HowHear, Comments, PhotoURL, WebSite, Active, VIP, Invited, Notes) VALUES ('$FirstName', '$LastName','$DJName','$Affiliations','$Address','$Telelphone','$Email','$Passwd','$Format','$Styles','$HowHear','$Comments','$PhotoURL','$WebSite',0,0,0,NULL)");
mysql_close($link);




and this produces no result in the db, however when I go to the sql engine and just use the insert line with some strings it works fine. I am stumped, please help if you can, thanks.

[wisewood]

Are you sure you have all the field names spelled correctly, this is something that i still get wrong from time to time and it is always about the 40th thing i think to check.

[kenrbnsn]

Here's how I would code this segment:
[code]<?php
$link = mysql_connect($hostname,$username, $password) or die('Unable to connect to database! Please try again later.');

mysql_select_db($dbname,$link);

$qtmp = array();
foreach ($_POST as $field => $val)
     if ($field != 'submit') // I'm assuming that your submit button is named "submit"
            if (trim(stripslashes($val)) != '')
                   $qtmp[] = $field . "='" . mysql_real_escape_string(trim(stripslashes($val))) . "'";

$q = 'insert into MailingList set ' . implode(', ',$qtmp);
echo 'The generated query is: ' . $q . '<br>'; // debugging only -- this will show you the generated query
$rs = mysql_query($q) or die('There was a problem with the insert query: ' . $q . '<br>' . mysql_error());
?>[/code]
Here's the explanation of the above code.[list][*]I put the"or die" clause on the mysql_connect() line.[*]I'm using PHP to create parts of the query for me. The query is the alternative form using the "set" format. I only include the field in the query if it is not blank. I apply the function [a href=\"http://www.php.net/mysql_real_escape_string\" target=\"_blank\"]mysql_real_escape_string()[/a] to each value. This is probably overkill if any of your fields are all numeric.[*]I combine the parts to make the complete query using the implode() function.[*]Finally, I invoke the mysql_query() function with an "or die" clause to catch any errors.[/list]Ken

[wisewood]

that is a REALLY clever way of generating the query. *copy & paste to my repisitory*

[kenrbnsn]

Thanks. This method only works if the field names in the form are the same as the field names in the database table. It also works when doing updates.

Ken

[wisewood]

I was just about to try it with an update, saved me the trouble :-)

Its a much better way to go about it as you can just stick that bit of the code in an included file and forget about ever having to mess around with insert & update queries ever again. Much time-saving!

Ta.

[idgcorp]

YES! Thank you guys so much! I have to agree kenrbnsn's method will make everyones life alot easier. In addition, there was one field mispelled which was crapping the whole thing out and not giving me any errors. I think I need some more coffee.


Thanks again!