surochek Posted June 17, 2007 Share Posted June 17, 2007 The first part of my form works like a charm: retrieves the info from the database and shows it in the form. But when it comes to updating the info: if (isset($_POST['submit'])) { $title=$_POST['title']; $genre=$_POST['genre']; $character_name=$_POST['character_name']; $role=$_POST['role']; $height=$_POST['height']; $hair=$_POST['hair']; $eyes=$_POST['eyes']; $weight=$_POST['weight']; $age=$_POST['age']; $clothing=$_POST['clothing']; $dwelling=$_POST['dwelling']; $growingup=$_POST['growingup']; $family=$_POST['family']; $relationships=$_POST['relationships']; $goals=$_POST['goals']; $motivations=$_POST['motivations']; $fears=$_POST['fears']; $loves=$_POST['loves']; $weaknesses=$_POST['weaknesses']; $positives=$_POST['positives']; $negatives=$_POST['negatives']; $update=mysql_query("UPDATE traits SET title='$title', genre='$genre', character_name='$character_name', role='$role', height='$height', hair='$hair', eyes='$eyes', weight='$weight', age='$age', clothing='$clothing', dwelling='$dwelling', growingup='$growingup', family='$family', relationships='$relationships', goals='$goals', motivations='$motivations', fears='$fears', loves='$loves', weaknesses='$weaknesses', positives='$positives', negatives='$negatives' WHERE username = '$_SESSION[$username]'"); if (mysql_query($update)) { echo "<center>Data saved succesfully! </center>";} else { echo "<br>Query failed: " . mysql_error(). $update;} } ?> This is the response I get: Query failed: 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 '1' at line 11 I can't make heads or tails of it. Help? Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted June 17, 2007 Share Posted June 17, 2007 I suspect the problem is that you're trying to query the query, if you see what i mean(take note of the comments): <?php if (isset($_POST['submit'])) { $title=$_POST['title']; $genre=$_POST['genre']; $character_name=$_POST['character_name']; $role=$_POST['role']; $height=$_POST['height']; $hair=$_POST['hair']; $eyes=$_POST['eyes']; $weight=$_POST['weight']; $age=$_POST['age']; $clothing=$_POST['clothing']; $dwelling=$_POST['dwelling']; $growingup=$_POST['growingup']; $family=$_POST['family']; $relationships=$_POST['relationships']; $goals=$_POST['goals']; $motivations=$_POST['motivations']; $fears=$_POST['fears']; $loves=$_POST['loves']; $weaknesses=$_POST['weaknesses']; $positives=$_POST['positives']; $negatives=$_POST['negatives']; $update="UPDATE traits SET title='$title', genre='$genre', character_name='$character_name', role='$role', height='$height', hair='$hair', eyes='$eyes', weight='$weight', age='$age', clothing='$clothing', dwelling='$dwelling', growingup='$growingup', family='$family', relationships='$relationships', goals='$goals', motivations='$motivations', fears='$fears', loves='$loves', weaknesses='$weaknesses', positives='$positives', negatives='$negatives' WHERE username = '$_SESSION[$username]'";//this should just be the contents of the query, not performing the query itself if (mysql_query($update)) {//because you do the query here echo "<center>Data saved succesfully! </center>";} else { echo "<br>Query failed: " . mysql_error(). $update;} } ?> Quote Link to comment Share on other sites More sharing options...
AndyB Posted June 17, 2007 Share Posted June 17, 2007 So what's line 11? It's usually much easier to debug code when you can actually see the query that failed. For that reason, I always separate my code as exemplified below: $query = "UPDATE traits SET title='$title', genre='$genre', character_name='$character_name', role='$role', height='$height', hair='$hair', eyes='$eyes', weight='$weight', age='$age', clothing='$clothing', dwelling='$dwelling', growingup='$growingup', family='$family', relationships='$relationships', goals='$goals', motivations='$motivations', fears='$fears', loves='$loves', weaknesses='$weaknesses', positives='$positives', negatives='$negatives' WHERE username = '$_SESSION[$username]'"); $update=mysql_query($query) or die("Error ". mysql_error(). " with query :. $query); // that helps Quote Link to comment Share on other sites More sharing options...
surochek Posted June 17, 2007 Author Share Posted June 17, 2007 AndyB: Thanks for the tip, but it only changed the error message, and I'm still confused. Error 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 '1' at line 1 with query : 1 I don't have a "1" in my query. All I can think of is the id field that's AUTO_INCREMENT, but I don't update that in the query. BTW, $update=mysql_query($query) or die("Error ". mysql_error(). " with query :. $query); // that helps is missing a ": $update=mysql_query($query) or die("Error ". mysql_error(). " with query : ". $query); it had me stumped for a moment, because I didn't expect a parse_error "unexpected $end". Quote Link to comment Share on other sites More sharing options...
AndyB Posted June 17, 2007 Share Posted June 17, 2007 Sorry about providing help with a typo in it Now that you've fixed my error, it's telling you that around line 11 you have a querystring that is "1" and nothing more. Can we see lines 1-11 please? I'd guess it's nothing to do with the query that you posted but some query that comes earlier in your code. Quote Link to comment Share on other sites More sharing options...
surochek Posted June 17, 2007 Author Share Posted June 17, 2007 Hey, finding a typo I can fix (almost) made my day! Means I'm learning!! Anyway, the supposed querystring "1" is what has me confused. I have no idea where it comes from. Here's the code that comes before the previous code. Is there more debugging I can add? There's only one other query in it: <?php session_start(); ?> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <title>Essential Character Traits</title> <link rel="stylesheet" type="text/css" href="builder2.css" /> </head> <body> <div id="wrapper"> <div class="header"> <span class="title">The Ultimate Character Builder</span><br /> <h3>Part 1<br /> Character Traits</h3> </div> <br /> <br /> <div class="left"> <?php include_once("left.inc"); include("connect.inc"); ?> </div> <div class="content"> <?php $user=$_SESSION['username']; $query = "SELECT * FROM traits WHERE username='$user'"; $result= mysql_query($query) or die (mysql_error()); $info=mysql_fetch_array($result, MYSQL_ASSOC) or die (mysql_error()); if ($result !==0) { $title=$info['title']; $genre=$info['genre']; $character_name=$info['character_name']; $genre=$info['genre']; $role=$info['role']; $height=$info['height']; $hair=$info['hair']; $eyes=$info['eyes']; $weight=$info['weight']; $age=$info['age']; $clothing=$info['clothing']; $dwelling=$info['dwelling']; $growingup=$info['growingup']; $family=$info['family']; $relationships=$info['relationships']; $goals=$info['goals']; $motivations=$info['motivations']; $fears=$info['fears']; $loves=$info['loves']; $weaknesses=$info['weaknesses']; $positives=$info['positives']; $negatives=$info['negatives']; ?> <form action="charactertraits2.php" method="post"> <table align="center"> <table border="0" cellpadding="3" cellspacing="3" summary="List of Essential Character Traits"> <tr><td align="right"><b>AUTHOR (Username):</b></td> <td><textarea rows="1" cols="40" name="user"><?php echo $user?></textarea></td></tr> <tr><td align="right"><b>TITLE:</b></td> <td><textarea rows="1" cols="40" name="title"><?php echo $title?></textarea></td></tr> <tr><td align="right"><b>CHARACTER:</b></td> <td><textarea rows="1" cols="40" name="character_name"><?php echo $character_name?></textarea></td></tr> <tr><td align="right">Genre:</td> <td><textarea rows="1" cols="40" name="genre"><?php echo $genre?></textarea></td></tr> <tr><td align="right">Role:</td><td><select name="role" size="1"> <option value="Protagonist" name="Protagonist">Protagonist</option> <option value="Contagonist" name="Contagonist">Contagonist</option> <option value="Antagonist" name="Antagonist">Antagonist (Villain)</option> <option value="Mentor" name="Mentor">Mentor</option> <option value="Logic" name="Logic">Logic (Reason)</option> <option value="Emotion" name="Emotion">Emotion (Heart)</option> <option value="Sidekick" name="Sidekick">Sidekick (Blind Supporter)</option> <option value="Skeptic" name="Skeptic">Skeptic (Naysayer)</option> </select></td></tr> <tr><td align="right">Height:</td> <td><textarea rows="1" cols="40" name="height"><?php echo $height?></textarea></td></tr> <tr><td align="right">Hair:</td> <td><textarea rows="1" cols="40" name="hair"><?php echo $hair?></textarea></td></tr> <tr><td align="right">Eyes:</td> <td><textarea rows="1" cols="40" name="eyes"><?php echo $eyes?></textarea></td></tr> <tr><td align="right">Body Type:</td> <td><textarea rows="1" cols="40" name="weight"><?php echo $weight?></textarea></td></tr> <tr><td align="right">Age during Story:</td> <td><textarea rows="1" cols="40" name="age"><?php echo $age?></textarea></td></tr> <tr><td align="right">Clothing:</td> <td><textarea rows="2" cols="40" name="clothing"><?php echo $clothing?> </textarea></td></tr> <tr><td align="right">Dwelling:</td> <td><textarea rows="2" cols="40" name="dwelling"><?php echo $dwelling?></textarea></td></tr> <tr><td align="right">Childhood & <br />Adolescence:</td> <td><textarea rows="2" cols="40" name="growingup"><?php echo $growingup?></textarea></td></tr> <tr><td align="right">Family:</td> <td><textarea rows="2" cols="40" name="family"><?php echo $family?></textarea></td></tr> <tr><td align="right">Adult Relationships:</td> <td><textarea rows="2" cols="40" name="relationships"><?php echo $relationships?></textarea></td></tr> <tr><td align="right">Ambitions/<br />Goals <br />(what does he/she want?):</td> <td><textarea rows="2" cols="40" name="goals"><?php echo $goals?></textarea></td></tr> <tr><td align="right">Motivations <br />(why does he/she want it?):</td> <td><textarea rows="2" cols="40" name="motivations"><?php echo $motivations?></textarea></td></tr> <tr><td align="right">Fears/<br />phobias:</td> <td><textarea rows="2" cols="40" name="fears"> <?php echo $fears?> </textarea></td></tr> <tr><td align="right">Loves:</td> <td><textarea rows="2" cols="40" name="loves"><?php echo $loves?></textarea></td></tr> <tr><td align="right">Weaknesses:</td> <td><textarea rows="2" cols="40" name="weaknesses"><?php echo $weaknesses?></textarea></td></tr> <tr><td align="right">Positive Traits/<br />Peculiarities:</td> <td><textarea rows="2" cols="40" name="positives"><?php echo $positives?></textarea></td></tr> <tr><td align="right">Negative Traits/<br />Bad habits:</td> <td><textarea rows="2" cols="40" name="negatives"><?php echo $negatives?></textarea></td></tr> <tfoot><td> </td><td colspan="3"><center> <?php echo "<input type='submit' name='submit'>"; echo "</form>"; } ...followed by the rest of the already-quoted stuff. Quote Link to comment Share on other sites More sharing options...
AndyB Posted June 17, 2007 Share Posted June 17, 2007 Ah. Now I'm going to suggest that the mysterious line 11 and the bogus query actually exists in one of your included files - most likely in connect.inc I assume that connect.inc contains your db password, etc. so when you post that please substitute xxxxx for any sensitive values. Also, as a security matter, .inc files can be accessed directly and their contents displayed (as a text file). Much better to name them as connect.php or connect.inc.php so they display nothing to nosey folk. Quote Link to comment Share on other sites More sharing options...
surochek Posted June 17, 2007 Author Share Posted June 17, 2007 So, this is the included file (dutifully changed to .php, both the file itself and the path, thank you for the tip). <?php $user="xxxx"; $host="xxxx"; $password="xxx"; $database="xxxxx"; mysql_connect($host,$user,$password) or die (mysql_error()); mysql_select_db($database) or die (mysql_error()); ?> Quote Link to comment Share on other sites More sharing options...
AndyB Posted June 17, 2007 Share Posted June 17, 2007 Well, there's clearly nothing wrong with that, so connect.php is good. Equally clearly there isn't going to be a line 11 in it. ... MySQL server version for the right syntax to use near '1' at line 11 Where is line 11? That's the stuff we need to track down and I can't see anything yet that looks like a line 11. So, basic question ... what's in the script before the bit if (isset($_POST['submit'])) {. I'm assuming there's something and that line 11 is part of that something and there's a database query execution there. Quote Link to comment Share on other sites More sharing options...
surochek Posted June 17, 2007 Author Share Posted June 17, 2007 I've been going 'round and 'round and 'round with this. The queries by themselves work. But... This is the whole script: <?php session_start(); ?> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <title>Essential Character Traits</title> <link rel="stylesheet" type="text/css" href="builder2.css" /> </head> <body> <div id="wrapper"> <div class="header"> <span class="title">The Ultimate Character Builder</span><br /> <h3>Part 1<br /> Character Traits</h3> </div> <br /> <br /> <div class="left"> <?php include_once("left.inc"); include("connect.php"); ?> </div> <div class="content"> <?php $user=$_SESSION['username']; $query = "SELECT * FROM traits WHERE username='$user'"; $result= mysql_query($query) or die (mysql_error()); $info=mysql_fetch_array($result, MYSQL_ASSOC) or die (mysql_error()); if ($result !==0) { $title=$info['title']; $genre=$info['genre']; $character_name=$info['character_name']; $genre=$info['genre']; $role=$info['role']; $height=$info['height']; $hair=$info['hair']; $eyes=$info['eyes']; $weight=$info['weight']; $age=$info['age']; $clothing=$info['clothing']; $dwelling=$info['dwelling']; $growingup=$info['growingup']; $family=$info['family']; $relationships=$info['relationships']; $goals=$info['goals']; $motivations=$info['motivations']; $fears=$info['fears']; $loves=$info['loves']; $weaknesses=$info['weaknesses']; $positives=$info['positives']; $negatives=$info['negatives']; ?> <form action="charactertraits2.php" method="post"> <table align="center"> <table border="0" cellpadding="3" cellspacing="3" summary="List of Essential Character Traits"> <tr><td align="right"><b>AUTHOR (Username):</b></td> <td><textarea rows="1" cols="40" name="user"><?php echo $user?></textarea></td></tr> <tr><td align="right"><b>TITLE:</b></td> <td><textarea rows="1" cols="40" name="title"><?php echo $title?></textarea></td></tr> <tr><td align="right"><b>CHARACTER:</b></td> <td><textarea rows="1" cols="40" name="character_name"><?php echo $character_name?></textarea></td></tr> <tr><td align="right">Genre:</td> <td><textarea rows="1" cols="40" name="genre"><?php echo $genre?></textarea></td></tr> <tr><td align="right">Role:</td><td><select name="role" size="1"> <option value="Protagonist" name="Protagonist">Protagonist</option> <option value="Contagonist" name="Contagonist">Contagonist</option> <option value="Antagonist" name="Antagonist">Antagonist (Villain)</option> <option value="Mentor" name="Mentor">Mentor</option> <option value="Logic" name="Logic">Logic (Reason)</option> <option value="Emotion" name="Emotion">Emotion (Heart)</option> <option value="Sidekick" name="Sidekick">Sidekick (Blind Supporter)</option> <option value="Skeptic" name="Skeptic">Skeptic (Naysayer)</option> </select></td></tr> <tr><td align="right">Height:</td> <td><textarea rows="1" cols="40" name="height"><?php echo $height?></textarea></td></tr> <tr><td align="right">Hair:</td> <td><textarea rows="1" cols="40" name="hair"><?php echo $hair?></textarea></td></tr> <tr><td align="right">Eyes:</td> <td><textarea rows="1" cols="40" name="eyes"><?php echo $eyes?></textarea></td></tr> <tr><td align="right">Body Type:</td> <td><textarea rows="1" cols="40" name="weight"><?php echo $weight?></textarea></td></tr> <tr><td align="right">Age during Story:</td> <td><textarea rows="1" cols="40" name="age"><?php echo $age?></textarea></td></tr> <tr><td align="right">Clothing:</td><td><textarea rows="2" cols="40" name="clothing"><?php echo $clothing?> </textarea></td></tr> <tr><td align="right">Dwelling:</td> <td><textarea rows="2" cols="40" name="dwelling"><?php echo $dwelling?></textarea></td></tr> <tr><td align="right">Childhood & <br />Adolescence:</td> <td><textarea rows="2" cols="40" name="growingup"><?php echo $growingup?></textarea></td></tr> <tr><td align="right">Family:</td> <td><textarea rows="2" cols="40" name="family"><?php echo $family?></textarea></td></tr> <tr><td align="right">Adult Relationships:</td> <td><textarea rows="2" cols="40" name="relationships"><?php echo $relationships?></textarea></td></tr> <tr><td align="right">Ambitions/<br />Goals <br />(what does he/she want?):</td> <td><textarea rows="2" cols="40" name="goals"><?php echo $goals?></textarea></td></tr> <tr><td align="right">Motivations <br />(why does he/she want it?):</td> <td><textarea rows="2" cols="40" name="motivations"><?php echo $motivations?></textarea></td></tr> <tr><td align="right">Fears/<br />phobias:</td> <td><textarea rows="2" cols="40" name="fears"> <?php echo $fears?> </textarea></td></tr> <tr><td align="right">Loves:</td> <td><textarea rows="2" cols="40" name="loves"><?php echo $loves?></textarea></td></tr> <tr><td align="right">Weaknesses:</td> <td><textarea rows="2" cols="40" name="weaknesses"><?php echo $weaknesses?></textarea></td></tr> <tr><td align="right">Positive Traits/<br />Peculiarities:</td> <td><textarea rows="2" cols="40" name="positives"><?php echo $positives?></textarea></td></tr> <tr><td align="right">Negative Traits/<br />Bad habits:</td> <td><textarea rows="2" cols="40" name="negatives"><?php echo $negatives?></textarea></td></tr> <tfoot><td> </td><td colspan="3"><center> <?php echo "<input type='submit' name='submit'>"; echo "</form>"; } if (isset($_POST['submit'])) { $title=$_POST['title']; $genre=$_POST['genre']; $character_name=$_POST['character_name']; $role=$_POST['role']; $height=$_POST['height']; $hair=$_POST['hair']; $eyes=$_POST['eyes']; $weight=$_POST['weight']; $age=$_POST['age']; $clothing=$_POST['clothing']; $dwelling=$_POST['dwelling']; $growingup=$_POST['growingup']; $family=$_POST['family']; $relationships=$_POST['relationships']; $goals=$_POST['goals']; $motivations=$_POST['motivations']; $fears=$_POST['fears']; $loves=$_POST['loves']; $weaknesses=$_POST['weaknesses']; $positives=$_POST['positives']; $negatives=$_POST['negatives']; $query=mysql_query("UPDATE traits SET title='$title', genre='$genre', character_name='$character_name', role='$role', height='$height', hair='$hair', eyes='$eyes', weight='$weight', age='$age', clothing='$clothing', dwelling='$dwelling', growingup='$growingup', family='$family', relationships='$relationships', goals='$goals', motivations='$motivations', fears='$fears', loves='$loves', weaknesses='$weaknesses', positives='$positives', negatives='$negatives' WHERE username = '$_SESSION[$username]'"); $update=mysql_query($query) or die("<br>Error: ". mysql_error(). " with query : ". $query); } ?> </table> </div> <br /> <br /> <br /> </div> </body> </html> The user comes to this page from a login page, but all it does is verify the password and username and set a session. The Session works. I am now getting the message: Error: 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 '1' at line 1 with query : 1 Quote Link to comment Share on other sites More sharing options...
AndyB Posted June 17, 2007 Share Posted June 17, 2007 Change: $query=mysql_query("UPDATE traits SET title='$title', genre='$genre', character_name='$character_name', role='$role', height='$height', hair='$hair', eyes='$eyes', weight='$weight', age='$age', clothing='$clothing', dwelling='$dwelling', growingup='$growingup', family='$family', relationships='$relationships', goals='$goals', motivations='$motivations', fears='$fears', loves='$loves', weaknesses='$weaknesses', positives='$positives', negatives='$negatives' WHERE username = '$_SESSION[$username]'"); $update=mysql_query($query) or die("<br>Error: ". mysql_error(). " with query : ". $query); to $query="UPDATE traits SET title='$title', genre='$genre', character_name='$character_name', role='$role', height='$height', hair='$hair', eyes='$eyes', weight='$weight', age='$age', clothing='$clothing', dwelling='$dwelling', growingup='$growingup', family='$family', relationships='$relationships', goals='$goals', motivations='$motivations', fears='$fears', loves='$loves', weaknesses='$weaknesses', positives='$positives', negatives='$negatives' WHERE username = '$_SESSION[$username]'"); $update=mysql_query($query) or die("<br>Error: ". mysql_error(). " with query : ". $query); In the upper chunk of code (yours as posted) the first database query succeeds so it's "result" is 1 - that's what spits out the nonsense query when you do a mysql_query on the resource. Quote Link to comment Share on other sites More sharing options...
surochek Posted June 17, 2007 Author Share Posted June 17, 2007 It doesn't throw any more errors at me, but it doesn't update anything (no new data in the database). Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted June 17, 2007 Share Posted June 17, 2007 Hmm, to debug that, try echoing the query after you try it: $query="UPDATE traits SET title='$title', genre='$genre', character_name='$character_name', role='$role', height='$height', hair='$hair', eyes='$eyes', weight='$weight', age='$age', clothing='$clothing', dwelling='$dwelling', growingup='$growingup', family='$family', relationships='$relationships', goals='$goals', motivations='$motivations', fears='$fears', loves='$loves', weaknesses='$weaknesses', positives='$positives', negatives='$negatives' WHERE username = '$_SESSION[$username]'"); $update=mysql_query($query) or die("<br>Error: ". mysql_error(). " with query : ". $query); echo '<br />Query: '.$query.'<br />'; This will check the correct values of the variables are being put into the query - for example, i would be suspicious of a problem with the session. Quote Link to comment Share on other sites More sharing options...
surochek Posted June 17, 2007 Author Share Posted June 17, 2007 That seems to be it. Here's the answer: Query: UPDATE traits SET title='Bitter Legacy', genre='Romantic Suspense', character_name='Alex', role='Protagonist', height='', hair='', eyes='', weight='', age='', clothing='jeans ', dwelling='', growingup='', family='Estranged', relationships='', goals='', motivations='', fears=' ', loves='', weaknesses='', positives='', negatives='' WHERE username = '' So I added a variable: if (isset($_POST['submit'])) { $user=$_SESSION['username']; //this one $title=$_POST['title']; $genre=$_POST['genre']; //etc. And changed the query to: //the preceding query WHERE username = '$user'"; And I get a nice browser response: Query: UPDATE traits SET title='Bitter Legacy', genre='Romantic Suspense', character_name='Alex', role='Protagonist', height='', hair='', eyes='', weight='', age='', clothing='jeans ', dwelling='', growingup='', family='Estranged', relationships='', goals='', motivations='', fears=' ', loves='', weaknesses='', positives='', negatives='' WHERE username = 'mashaholl' And when I check the table, the new info is in. But how do I make the form refill itself with the new data? Shouldn't that happen automatically when the page reloads? Maybe I'll get this to work some day. Quote Link to comment Share on other sites More sharing options...
GingerRobot Posted June 17, 2007 Share Posted June 17, 2007 Looks to me like the reason is just the order that the script runs. You fill in the info, hit submit: 1.) pulls the OLD data from the database. 2.) Display this in the form 3.) Update the data If you change it around a bit, you should get it working. Quote Link to comment Share on other sites More sharing options...
surochek Posted June 17, 2007 Author Share Posted June 17, 2007 Yes!! Thank you so much, all! Changed to: 1). if submit is pressed, update data 2). pull data from database 3). display it in the form In other words, put the if (isset($_POST['submit'])) { //some code } before the SELECT query and the form. Now on to the next step... 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.