Jump to content
Larry Ullman's Book Forums

Updating Multiple Columns In A Row


Recommended Posts

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

  • 2 weeks later...

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

$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

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

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

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

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

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

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

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

 Share

×
×
  • Create New...