UPDATE the database

[phppup]

I want to UPDATE reords in my DB using a form.  What is the correct UPDATE syntax.

 

All the tutorials seem to offer lessons on updating full columns or ages, but not a single record row by ID.

 

 

[Psycho]

All the tutorials seem to offer lessons on updating full columns or ages, but not a single record row by ID.

 

Really? I did a simple google search for "MySQL Update tutorial" and the very first result provided a pretty simple example. And, how is this a PHP question? (moving to MySQL forum).

 

But, to answer your question, you simply put a WHERE clause on the update statement. That WHERE clause can be broad (WHERE gender = 'female') or very specific (WHERE id = '1').

[phppup]

do I need to list every field by name individually, or is there a way to UPDATE the entire row by ID?

 

[Zane]

do I need to list every field by name individually, or is there a way to UPDATE the entire row by ID?

You only need to list the fields you want to change/UPDATE

 

The WHERE clause takes care of which row it is..

[phppup]

SO if I have 50 fields in a single row, I need to list them in the same manner as when I input them from the form?

There is now way to simply state that I want the entire ID row to be overwritten (since MySQL will automatically NOT overwrite items that were not changed)?

[Zane]

SO if I have 50 fields in a single row, I need to list them in the same manner as when I input them from the form?

There is now way to simply state that I want the entire ID row to be overwritten (since MySQL will automatically NOT overwrite items that were not changed)?

They DO NOT need to be in the same order as they were inputted from the form.

They fields simply need to exist.  An easy way to grab all your inputs and update is through the use of a foreach loop

 

Provide some code if you need quality help.  Otherwise, you'll just get a random snippet of what it may look like.

[phppup]

Don't have the code yet.  I have multiple fields form a form that were input from dropdown menus.

 

So I would need a loop for each item in row??

[Zane]

Don't have the code yet.

You say this yet...

I have multiple fields form a form that were input from dropdown menus.

I'd consider that code.  Even if it is HTML, it has every bit to do with how you craft your update

 

So I would need a loop for each item in row??

This, I don't understand.  You only need one loop.. and that is to loop through your input fields... your $_POST array.

[phppup]

Here is the code that was at the top of my INSERT.php code

 

$roastturkey = $_POST['roastturkey'];

$broccoli = $_POST['broccoli'];

$brisket = $_POST['brisket'];

$carrots = $_POST['carrots'];

 

and then it repeats as the VALUES to be posted.

 

I'm still a bit confused about my UPDATE statement.

 

UPDATe mytable SET (all the post names) WHERE id="id" ...... so the SET is the list of fields, but there's a way to loop through them to avoid listing each one seperately??

 

Please help.  As I've discovered I could have saved LOTS of time had I understood and implemented other PHP shortcuts earlier in this process.

 

Thanks.

 

 

[Zane]

Yes, there's a way to loop through them all without listing them separately.

 

First of all though, avoid manually creating variables that only hold the value of another.

$roastturkey = $_POST['roastturkey'];

Has no benefit whatsoever other than aesthetics.  Gee, now I only have to type $broccoli instead of $_POST['broccoli']... how l337!!

That is a pointless concept unless you have calculations involved.  You wouldn't pour your coffee from your coffee cup into another coffee cup just to add cream to it would you.... and then again for sugar?

 

To loop through your inputs, you need to know two things.

1 - Do you want everything in $_POST?

2 - What don't you need from $_POST?

... Yes I realize those are the same, but a little repetition never hurt anyone.

 

Using a foreach loop, you can grab EVERYTHING from $_POST and append it to a variable.  For example, assuming you wanted EVERYTHING inside $_POST

$myUpdateList = array();
foreach($_POST as $indexName => $value) {
     $myUpdateList[] = "$indexName = \"$value\"";
}

This will yeild an array containing all of your fields and their new values..Everything in that array will look something like this

broccoli = "burnt"

 

Now, the task is to take that array and implode it into a comma separated list..

$fields = implode(",", $myUpdateList);

 

All that's left is the actual UPDATE statement now... and you have everything you need for it inside $fields

UPDATE yourTable SET $fields WHERE id = $id

 

Hope this helps

 

[phppup]

I'm just doing what the internet tell me (which is probably why I'm having so much trouble, huh).

 

So instead of listing them twice, what??

[phppup]

And why is my BROCCOLI now BURNT.... i suppose this is more than just you being funny.

And will I ever actually see (or need to use) my BURNT items?

[Zane]

i suppose this is more than just you being funny.

No, it's nothing more than me being funny.  I figured it would help a little to use some humor in my explanation, but apparently not.

 

So instead of listing them twice, what??

I gave you more than enough information to get started.

 

 

broccoli is only going to equal "burnt" if you set it that way in your form.  I have a feeling you're using a bunch of checkboxes, so broccoli would most likely equal 1 or 0.  But again, I have no idea what your HTML looks like.. so I have to resort to my own imagination.

[phppup]

Using 85% dropdowns, and then some standard name,address input fields.

 

Ummmm, easiest way to provide the $id for the script?

 

[phppup]

I appreciate the humor.

It's just that as a newbie, ya never know if htere's some variable at work that you haven't encountered yet.

I was thinking they might have a similar numeric value or name concontination or some sort.

PS: the broccoli is STEAMED.... so at worst it gets soggy.  LOL

[Zane]

Using 85% dropdowns, and then some standard name,address input fields.

 

Ummmm, easiest way to provide the $id for the script?

 

"85% dropdowns" is not HTML code..

as for the $id, I have no idea where it even comes from because you haven't supplied any code for it.

[phppup]

Yes, the dropdowns were ALL written manually with HTMN (i've since learned there is a php method that's less exhausting).

 

The id is being input into a field calling it from the DB as record_id.

$record_id = (isset($_POST['record_id'])) ? $_POST['record_id'] : '';

[phppup]

Code not working

Getting T-string error:

 

 

$myUpdateList = array();foreach($_POST as $indexName => $value) {    $myUpdateList[] = "$indexName = \"$value\"";}

$fields = implode(",", $myUpdateList);

$sql=UPDATE pass SET $fields WHERE id = 3;

 

Am I missing parenthesis or semi colons, or quotes?

[fenway]

This really isn't the place for php syntax errors.

[phppup]

Having trouble using the code mentioned.  Is it a syntax error?  Should MyUpdateList be my database name?

I'm sure it's a minor glitch.  Or maybe I'm better of listing each item, as the shortcut is requiring much more work.. LOL