Dark Prince Posted January 6, 2012 Share Posted January 6, 2012 I had just realized now that in my thread i had started previous to this i asked how to update multiple rows in a table what i actually meant was columns in a row i need to use the UPDATE function in a mysqli query to update most of the columns in a row i used UPDATE column1, column2, etc... SET VALUES ('$value1', '$value2', etc...) i thought this was the correct syntax but it is not updating any columns at all. Link to comment Share on other sites More sharing options...
Larry Posted January 6, 2012 Share Posted January 6, 2012 If you check my book or the MySQL manual, you'll see that the proper syntax is UPDATE TABLE sometable SET column1=value, column2=value, etc. Link to comment Share on other sites More sharing options...
Dark Prince Posted January 7, 2012 Author Share Posted January 7, 2012 thank you larry i get confused where too look in the books i have your 2nd,3rd edition php and mysql and 2nd edition adv php Link to comment Share on other sites More sharing options...
Larry Posted January 7, 2012 Share Posted January 7, 2012 You're welcome. If you use the index, you'll find what you need. Link to comment Share on other sites More sharing options...
Antonio Conte Posted January 7, 2012 Share Posted January 7, 2012 Remember to use a WHERE clause in your queries. Else, every row will be updated this way. Link to comment Share on other sites More sharing options...
Dark Prince Posted January 12, 2012 Author Share Posted January 12, 2012 haha yeah gotta love the WHERE clause Link to comment Share on other sites More sharing options...
Dark Prince Posted January 21, 2012 Author Share Posted January 21, 2012 Ok I'm still having a problem my query isn't updating any information at all but it is not giving any errors other then my set error on a failed affected row check , in the start of my code it gets all the information from a SELECT query and then fills it into the html fields after all the php code, so there is a connection with the database, but if I change anything inside the fields and hit the submit which initiates all the field checks then passes into the UPDATE query then reloads the page but none of the fields change from the original information? Link to comment Share on other sites More sharing options...
Dark Prince Posted January 21, 2012 Author Share Posted January 21, 2012 $id = $_GET['id']; if ($value1 && $value2 && $value3 && $value4 && $value5 && $value6 && $value7 && $value8) { // These are needed fields $queryud = "UPDATE TABLE table SET column1=$value1, column2=$value2, column3=$value3, column4=$value4, column5=$value5, column6=$value6, column7=$value7, column8=$value8, column9=$value9, column10=$value10, column11=$value11, column12=$value12, column13=$value13, column14=$value14, column15=$value15, column16=$value16 WHERE id=$id"; $resultud = mysqli_query($dbc, $queryud); } else { echo '<p>You left a key field empty.</p>'; } if (mysqli_affected_rows ($dbc) == 1) { echo '<p>Your changes have been saved.</p>'; mysqli_close($dbc); } else { echo '<p>Your changes could not be saved.</p>'; } Link to comment Share on other sites More sharing options...
Larry Posted January 23, 2012 Share Posted January 23, 2012 What happens when you apply the standard PHP-MySQL debugging techniques (i.e., print the query out and run it using another interface)? Link to comment Share on other sites More sharing options...
Dark Prince Posted January 23, 2012 Author Share Posted January 23, 2012 I get 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 'TABLE tablename SET column1=value1, column2=value2, column3=value3, column4=value4, column5=value' at line 1 the error cuts out the last letter of the 5th value soi checked out everyrhing to do with values setting in post checking and the html form to the trimming and it is normal so I am really stumped here Link to comment Share on other sites More sharing options...
Larry Posted January 24, 2012 Share Posted January 24, 2012 What is an example of the exact query being executed? Link to comment Share on other sites More sharing options...
Dark Prince Posted January 24, 2012 Author Share Posted January 24, 2012 its running the exact one in my post up above. this is my table structure if this might have something to do with it. Field------------type------------------null-key------default-------extra----------- column1---int(10)unsigned---------no--pri---------null----auto_increment column2---varchar(60)--------------no--mul--------null------ column3---int(10)--------------------no--mul--------null------ column4---int(10)--------------------no---------------null------ column5---varchar(60)-------------no--mul---------null------ column6---varchar(5)---------------no---------------null------ column7---text-----------------------no----------------null------ column8---varchar(60)-------------no--mul---------null------ column9---varchar(60)-------------no--mul---------null------ column10--varchar(60)------------no--mul---------null------ column11--varchar(30)------------no----------------null------ column12--text----------------------no----------------null------ column13--text----------------------no----------------null------ column14--text----------------------no----------------null------ column15--text----------------------no----------------null------ column16--text----------------------no----------------null------ column17--text----------------------no----------------null------ column18--timestamp--------------no--------current_timestamp---on update current_timestamp column19--int(11)------------------no----------------null------ with this legend the query being run is this if (empty($errors)) { $queryud = "UPDATE TABLE table SET column2=$value2, column3=$value3, column4=$value4, column5=$value5, column6=$value6, column7=$value7, column8=$value8, column9=$value9, column10=$value10, column11=$value11, column12=$value12, column13=$value13, column14=$value14, column15=$value15, column16=$value16, column17=$value17 WHERE column1=$id"; $resultud = mysqli_query($dbc, $queryud); if (mysqli_affected_rows ($dbc) == 1) { header("Location: $cpage"); //changes have been saved auto-refresh. /* mysqli_close($dbc);*/ } else { echo '<p>Your changes could not be saved.</p>'; echo '<p>' . mysqli_error($dbc) . '<br/><br/>Query: ' . $queryud . '</p>'; } } else { // if $errors is not empty echo '<h1>ERROR!</h1> <p class="error">You forgot the following:<br/>'; foreach ($errors as $msg) { echo " - $msg<br/>\n"; } Link to comment Share on other sites More sharing options...
Larry Posted January 25, 2012 Share Posted January 25, 2012 First of all, that's a terrible database design. I can't imagine why you'd want to use columns named column1, column2, etc. Second, you have not yet posted an example of the ACTUAL QUERY BEING RUN. I'm not talking about the query as represented by a PHP variable. I'm talking about the actual query that the PHP script would run once it's put in the values of the variables. This is the query you should have run in phpMyAdmin to confirm the results, which is the standard first debugging approach. Link to comment Share on other sites More sharing options...
Dark Prince Posted January 26, 2012 Author Share Posted January 26, 2012 those aren't the actual name of the column's I edited them to keep it down to just the code itself without given names and variables. so here is an edited version of the query being run. 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 'TABLE tablename SET column2=random field, column3=1, column4=87517, column5=1, column6=sell, ' at line 1 Query: UPDATE TABLE tablename SET column2=random field, column3=1, column4=87517, column5=1, column6=sell, column7=big description field is boring to fill out., column8=key field who cares, column9=nother key field jeeze, column10=random unneeded field, column11=boring blah blah, column12=, column13=, column14=, column15=, column16=, column17= WHERE column1=14 Link to comment Share on other sites More sharing options...
Dark Prince Posted January 26, 2012 Author Share Posted January 26, 2012 the same query won't run in an isolated script either. Link to comment Share on other sites More sharing options...
Dark Prince Posted January 26, 2012 Author Share Posted January 26, 2012 phpmyadmin gives error 1064 I don't get it all the information being updated was just entered into an insert query with the other 2 columns not used in the update query the datetime and a counter field and it works just fine, but when its run in an update query it keeps stopping at the sql syntax error. Link to comment Share on other sites More sharing options...
Paul Swanson Posted January 26, 2012 Share Posted January 26, 2012 I've never used the key word TABLE in an update query. Try just: UPDATE tablename SET ... Link to comment Share on other sites More sharing options...
Dark Prince Posted January 26, 2012 Author Share Posted January 26, 2012 tried that too i even put UPDATE tablename WHERE column=value SET , instead of the WHERE at the end of the query I even tried adding the IGNORE as well. Link to comment Share on other sites More sharing options...
Larry Posted January 27, 2012 Share Posted January 27, 2012 You're missing the forest for the trees here. Look at your query: UPDATE TABLE tablename SET column2=random field, column3=1, column4=87517, column5=1, column6=sell, column7=big description field is boring to fill out., column8=key field who cares, column9=nother key field jeeze, column10=random unneeded field, column11=boring blah blah, column12=, column13=, column14=, column15=, column16=, column17= WHERE column1=14 None of your strings are quoted and several columns don't have values. Either one of those will make the query fail, let alone both. These are basics of queries. Link to comment Share on other sites More sharing options...
Dark Prince Posted January 28, 2012 Author Share Posted January 28, 2012 yeah it was the quotes but also the TABLE part after UPDATE would still stall the query, and the ones without values are NULL set by the php code if the field is empty. I thought it was missing quotes but I didn't give it thought because of your first example and thought something else was messing it up like maybe I got a column wrong and put an alpha numeric value in a numeric only field. but everything is working now thanks alot guys for your patience Link to comment Share on other sites More sharing options...
Recommended Posts