ifis Posted February 27, 2008 Share Posted February 27, 2008 I am trying to update a table with a result from another database based off a users selection. I am having a problem updating the table though. The scipt does not produce an error message, but I can't figure out why it does not work. // Connect to server and select databse. mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select DB"); //Convert male/female to he/she if ($sex == 'Male'){ $preposition= he; } If ($sex == 'Female'){ $preposition= she; } $sql = "SELECT * FROM Endorsements WHERE lic='$lic'"; $result=mysql_query($sql); //get the first entry from the result $row = mysql_fetch_array($result); $find = array("\$student","\$make","\$preposition","\$firstName","\$lastName","\$CFI","expire","limitations","departure","destination","\$route","\$airport","landingair","\$airspace","seldate","pilotcertificate","pilotnumber"); $replace = array($student,$make,$preposition,$firstName,$lastName,$CFI,$expireip,$limitations,$departure,$destination,$route,$airport,$landingair,$airspace,$seldate,$pilotcertificate,$pilotnumber); $dorse =str_replace($find,$replace,$row['end']); $dorse1 =str_replace($find,$replace,$row['end1']); $dorse2 =str_replace($find,$replace,$row['end2']); echo "$dorse</br>"; echo "$dorse1</br>"; echo "$dorse2</br>"; unset($host); unset($username); unset($password); unset($db_name); include("endorsementlog.inc"); // Connect to server and select databse. mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select DB"); $today=date("Y/m/d"); mysql_query("UPDATE Endorsementlog SET end='$dorse' AND end1='$dorse1' AND end2='$dorse2' WHERE Student='$student' AND Endorsement='$lic' AND Date='$today'") or die("Cannot inset into Endorsementlog table. MySQL Returned: ".mysql_error()); mysql_close(); It's probably something stupid, but I'm stuck. Thanks Quote Link to comment Share on other sites More sharing options...
Jessica Posted February 27, 2008 Share Posted February 27, 2008 Why do you unset the usersnamd and password before using them? Also, you don't need quotes around your variables in the connect function and others like that. Secondly, "Doesn't Work" is too vague. Where is $student defined? Quote Link to comment Share on other sites More sharing options...
revraz Posted February 27, 2008 Share Posted February 27, 2008 I would think you would want this $preposition= he; to be $preposition= "he"; and the same with she. Quote Link to comment Share on other sites More sharing options...
Jessica Posted February 27, 2008 Share Posted February 27, 2008 That too Good catch. Quote Link to comment Share on other sites More sharing options...
ifis Posted February 27, 2008 Author Share Posted February 27, 2008 I unset the variables because I am connecting to a different database and did not want the original variables that I used to connect to the first database to interfere with the connection with my second database. Basically, I want to get a string that is saved in one database, update the strings variables with inputs from a form and then save the resulted in another database. The script runs without an error message, but does not write anything to the MySQL database. Here is the first part of it //get data $make=$_POST['make']; $lic = $_POST['lic']; $student=$_POST['student']; $sex = $_POST['sex']; $CFI=$_POST['CFI']; $expireip=$_POST['expireip']; $firstName=$_POST['firstName']; $lastName=$_POST['lastName']; $limitations=$_POST['limitations']; $departure=$_POST['departure']; $destination=$_POST['destination']; $route=$_POST['route']; $airport=$_POST['airport']; $airspace=$_POST['airspace']; $landingair=$_POST['landingair']; $seldate=$_POST['seldate']; $pilotcertificate=$_POST['pilotcertificate']; $pilotnumber=$_POST['pilotnumber']; include("endorsement.inc"); Quote Link to comment Share on other sites More sharing options...
revraz Posted February 27, 2008 Share Posted February 27, 2008 In that case you want to do two things. One, echo the query and see how it looks. Two, use mysql_error() after the query to see if there are errors. Quote Link to comment Share on other sites More sharing options...
ifis Posted February 27, 2008 Author Share Posted February 27, 2008 the he/she has been working, but thanks for the suggestion Quote Link to comment Share on other sites More sharing options...
Jessica Posted February 27, 2008 Share Posted February 27, 2008 You should still change it, it's bad practice. Add this to the top of your code: ini_set('display_errors', 1); error_reporting(E_ALL); Quote Link to comment Share on other sites More sharing options...
ifis Posted February 27, 2008 Author Share Posted February 27, 2008 I have a mysql_error() and it does not show anything. Maybe there is a better way to do it. Here is the code I have: mysql_query("UPDATE Endorsementlog SET end='$dorse' AND end1='$dorse1' AND end2='$dorse2' WHERE Student='$student' AND Endorsement='$lic' AND Date='$today'") or die("Cannot inset into Endorsementlog table. MySQL Returned: ".mysql_error()) as far as echo the queary, how would I do that just echo "mysql_query('UPDATE Endorsementlog SET end='$dorse' AND end1='$dorse1' AND end2='$dorse2' WHERE Student='$student' AND Endorsement='$lic' AND Date='$today'') or die('Cannot inset into Endorsementlog table. MySQL Returned: '.mysql_error())"; ? Quote Link to comment Share on other sites More sharing options...
ifis Posted February 27, 2008 Author Share Posted February 27, 2008 ok, doing it now Add this to the top of your code: ini_set('display_errors', 1); error_reporting(E_ALL); Quote Link to comment Share on other sites More sharing options...
Jessica Posted February 27, 2008 Share Posted February 27, 2008 Put the query in a string as a variable and then print it. <?php $sql = "UPDATE Endorsementlog SET end='$dorse' AND end1='$dorse1' AND end2='$dorse2' WHERE Student='$student' AND Endorsement='$lic' AND Date='$today''; print $sql; mysql_query($sql) or die('Cannot inset into Endorsementlog table. MySQL Returned: '.mysql_error())"; ?> Quote Link to comment Share on other sites More sharing options...
ifis Posted February 27, 2008 Author Share Posted February 27, 2008 Ok, I put the code up and it displayed a lot of stuff (new to me, thanks), but it has to do with "undefined index" because I don't set all variables. I'll try the next. Quote Link to comment Share on other sites More sharing options...
Jessica Posted February 27, 2008 Share Posted February 27, 2008 Yep, those errors are really handy. The error you saw was probably about your post vars. Maybe you misspelled one form elements name? That could cause an error that would be hard to catch without all that error reporting. Quote Link to comment Share on other sites More sharing options...
revraz Posted February 27, 2008 Share Posted February 27, 2008 You dont use AND with UPDATE SET, use commas. Quote Link to comment Share on other sites More sharing options...
Jessica Posted February 27, 2008 Share Posted February 27, 2008 lol revraz you are much more observant than me today I am starting to feel pretty dumb. Quote Link to comment Share on other sites More sharing options...
ifis Posted February 27, 2008 Author Share Posted February 27, 2008 Ok, I echo'd the search and it came out correct, the variables are what they should be. I'll set rid of the AND's and see if that helps. Quote Link to comment Share on other sites More sharing options...
revraz Posted February 27, 2008 Share Posted February 27, 2008 Using AND really should have threw up a MySQL error. If it still doesn't work, post the new code. Quote Link to comment Share on other sites More sharing options...
ifis Posted February 27, 2008 Author Share Posted February 27, 2008 OK, now I got an error: Cannot inset into Endorsementlog table. MySQL Returned: 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 'Endorsement='Private Pilot Practical Test (Single-Engine)',Date='2008/02/27'' at line 1 here is the code I updated: mysql_query("UPDATE Endorsementlog SET end='$dorse',end1='$dorse1',end2='$dorse2' WHERE Student='$student',Endorsement='$lic',Date='$today'")or die("Cannot inset into Endorsementlog table. MySQL Returned: ".mysql_error()); Quote Link to comment Share on other sites More sharing options...
revraz Posted February 27, 2008 Share Posted February 27, 2008 I probably should have been a little more clear. You use commas for SET, but you use AND for WHERE. Quote Link to comment Share on other sites More sharing options...
ifis Posted February 27, 2008 Author Share Posted February 27, 2008 ok, I'm making the changes Quote Link to comment Share on other sites More sharing options...
ifis Posted February 27, 2008 Author Share Posted February 27, 2008 It WORKED!! I'm still not sure exactly why, I guess it is just using the commas vs. ANDs. Thanks for all the help! Quote Link to comment Share on other sites More sharing options...
revraz Posted February 27, 2008 Share Posted February 27, 2008 Isn't syntax a pain? Quote Link to comment Share on other sites More sharing options...
redarrow Posted February 27, 2008 Share Posted February 27, 2008 shouldnt brake lol <?php //database connection 1 $hosta=""; $usernamea=""; $passworda=""; $dbname_a=""; //database connection b. $hostb=""; $usernameb=""; $passwordb=""; $db_nameb=""; // Connect to server and select databse. mysql_connect("$hosta", "$usernamea", "$passworda")or die("cannot connect"); mysql_select_db("$db_namea")or die("cannot select DB"); //Convert male/female to he/she if ($sex == 'Male'){ $preposition='he'; } If ($sex == 'Female'){ $preposition='she'; } $sql = "SELECT * FROM Endorsements WHERE lic='$lic'"; $result=mysql_query($sql); //get the first entry from the result $row = mysql_fetch_array($result); $find = array("\$student","\$make","\$preposition","\$firstName","\$lastName", "\$CFI","expire","limitations","departure","destination","\$route","\$airport", "landingair","\$airspace","seldate","pilotcertificate","pilotnumber"); $replace = array($student,$make,$preposition,$firstName,$lastName,$CFI, $expireip,$limitations,$departure,$destination,$route,$airport,$landingair ,$airspace,$seldate,$pilotcertificate,$pilotnumber); $dorse =str_replace($find,$replace,$row['end']); $dorse1 =str_replace($find,$replace,$row['end1']); $dorse2 =str_replace($find,$replace,$row['end2']); echo "$dorse</br>"; echo "$dorse1</br>"; echo "$dorse2</br>"; include("endorsementlog.inc"); // Connect to server and select databse. mysql_connect("$hostb", "$usernameb", "$passwordb")or die("cannot connect"); mysql_select_db("$db_nameb")or die("cannot select DB"); mysql_query("UPDATE Endorsementlog SET end='$dorse' , end1='$dorse1' , end2='$dorse2' WHERE Student='$student' AND Endorsement='$lic' AND Date=NOW() ") or die("Cannot inset into Endorsementlog table. MySQL Returned: ".mysql_error()); ?> Quote Link to comment Share on other sites More sharing options...
revraz Posted February 27, 2008 Share Posted February 27, 2008 Are you trying to break it again? Quote Link to comment Share on other sites More sharing options...
redarrow Posted February 27, 2008 Share Posted February 27, 2008 add $pilotnumber=mysql_real_escape_string($_POST['pilotnumber']);to every varable mate... //get data $make=$_POST['make']; $lic = $_POST['lic']; $student=$_POST['student']; $sex = $_POST['sex']; $CFI=$_POST['CFI']; $expireip=$_POST['expireip']; $firstName=$_POST['firstName']; $lastName=$_POST['lastName']; $limitations=$_POST['limitations']; $departure=$_POST['departure']; $destination=$_POST['destination']; $route=$_POST['route']; $airport=$_POST['airport']; $airspace=$_POST['airspace']; $landingair=$_POST['landingair']; $seldate=$_POST['seldate']; $pilotcertificate=$_POST['pilotcertificate']; $pilotnumber=$_POST['pilotnumber']; Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.