Jump to content


Photo

INSERT wierdness


  • Please log in to reply
6 replies to this topic

#1 idgcorp

idgcorp
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 13 April 2006 - 01:21 PM

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.




#2 wisewood

wisewood
  • Members
  • PipPipPip
  • Advanced Member
  • 226 posts
  • LocationRotherham, England

Posted 13 April 2006 - 01:35 PM

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.
wisewood: proven fact, I am both wise, and wooden.

#3 kenrbnsn

kenrbnsn
  • Staff Alumni
  • Advanced Member
  • 8,235 posts
  • LocationHillsborough, NJ, USA

Posted 13 April 2006 - 01:53 PM

Here's how I would code this segment:
<?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());
?>
Here's the explanation of the above code.
  • 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.
Ken

#4 wisewood

wisewood
  • Members
  • PipPipPip
  • Advanced Member
  • 226 posts
  • LocationRotherham, England

Posted 13 April 2006 - 02:04 PM

that is a REALLY clever way of generating the query. *copy & paste to my repisitory*
wisewood: proven fact, I am both wise, and wooden.

#5 kenrbnsn

kenrbnsn
  • Staff Alumni
  • Advanced Member
  • 8,235 posts
  • LocationHillsborough, NJ, USA

Posted 13 April 2006 - 02:09 PM

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

#6 wisewood

wisewood
  • Members
  • PipPipPip
  • Advanced Member
  • 226 posts
  • LocationRotherham, England

Posted 13 April 2006 - 02:16 PM

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.
wisewood: proven fact, I am both wise, and wooden.

#7 idgcorp

idgcorp
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 13 April 2006 - 03:44 PM

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!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users