PDA

View Full Version : if/then mysql insert

utinaeniduin
12-08-2006, 01:49 AM
i want the values submitted in a form to be inserted in a different table depending on a single variable. this is what i am using, but its not inserting the values.


<?
if ($to=="cast") {

mysql_query("INSERT INTO cast (To,From,Email,Subject,Message,Date,Status)".
"VALUES ('$To', '$From', '$Email', '$Subject', '$Message', '$Date', '$Status')");

} elseif ($to=="crew") {

mysql_query("INSERT INTO crew (To,From,Email,Subject,Message,Date,Status)".
"VALUES ('$To', '$From', '$Email', '$Subject', '$Message', '$Date', '$Status')");

} elseif ($to=="pit") {

mysql_query("INSERT INTO pit (To,From,Email,Subject,Message,Date,Status)".
"VALUES ('$To', '$From', '$Email', '$Subject', '$Message', '$Date', '$Status')");

} else {echo "<body onLoad='window.location=\"contact.php\"'>";}
?>


the vars are being passed but they are not getting inserted. i don't understand. i thank you for any effort to help.
Tyree
12-08-2006, 02:47 AM
For one thing to make your code cleaner and more efficient:

$query = "INSERT INTO $to (To,From,Email,Subject,Message,Date,Status)".
"VALUES ('$to', '$from', '$email', '$subject', '$message', '$date', '$status')";

if (strlen($to)){
mysql_query($query);
} else {
echo "<body onLoad='window.location=\"contact.php\"'>";
}


If your $to values are the same as your table names, then why bother with all the if statements? :)
utinaeniduin
12-08-2006, 03:07 AM
oh that does look nicer doesn't it. thanks
Tyree
12-08-2006, 03:10 AM
Did that also fix your problem?
utinaeniduin
12-08-2006, 03:12 AM
i wish it had. i was hoping it was something simple like a mysql query syntax error, but unfortuneately no. still not working. and no error messages to work with either (there never were any for this problem).
Tyree
12-08-2006, 03:30 AM
Try printing $query to see if the MySQL syntax looks correct after the variables are parsed.Make sure all your table names and column names are exactly right including the case sensitivity.

Also, add this right after the "mysql_query($query); " line:

if (!mysql_query($query)) {
die('Invalid query: ' . mysql_error());
}


That'll give you a mysql error if there's something wrong with the syntax.
utinaeniduin
12-08-2006, 03:52 AM
thank you soo much. i got an error message. i have never seen one like it, so you think you could help?

Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'To,From,Email,Subject,Message,Date,Status)VALUES ('Director', 'Mark', '' at line 1

Director is the $To and Mark is the $From
Tyree
12-08-2006, 04:08 AM
So, do you have a variable $to and $To? Probably not a good idea....I'm not even sure that you can have both. I was under the impression that php ignored var cases...guess not!

Anyway...let's see...
You're sure that those are all your column names, case sensitive, right?

Just so we can see the whole query...let's change the if/error line I gave you previously to this:

if (!mysql_query($query)) {
$message = "The following error was encountered:" .
"<p>" . $query . "</p>"
"<p>" . mysql_errno() . ": " . mysql_error(). "<p>" ;

die($message);
}
guelphdad
12-08-2006, 02:34 PM
You shouldn't need to use three separate tables. Add another column to your only table that designates whether it is cast, crew or pit that it refers to and eliminate the unnecessary tables.
Tyree
12-08-2006, 02:40 PM
Good point! ;)
utinaeniduin
12-09-2006, 04:21 PM
i just wanted to say thank you for everyone's help. i was actually able to fiddle around and fix it myself. i used what the error message told me to work on it. it was actually as simple as using the " ` " around mysql table/column names. so thanks to tyree for giving me the code to view the error message.
guelphdad
12-09-2006, 06:56 PM
if you had to use the backtick then the error is you are using a reserved mysql word for your column or table name(s). you should rename whatever column/table name is on the reserved word list. note that the ` is proprietary to mysql and you won't be able to use it with another database should you port to one in the future.

FROM and TO are words on the reserved list!
utinaeniduin
12-10-2006, 11:40 PM
interesting...do you happen to know where i can find this list preferably with a brief description of when they are used.
guelphdad
12-11-2006, 12:03 AM
Yes, they would be part of the mysql manual here (http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html).