Jump to content

Archived

This topic is now archived and is closed to further replies.

idgcorp

INSERT wierdness

Recommended Posts

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.


Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
that is a REALLY clever way of generating the query. *copy & paste to my repisitory*

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.