Update command WHERE clause

[Ann_G]

I'm starting to feel a bit daft because I can't figure out what to put in the WHERE clause of my UPDATE command. I want to be able to modify a field in my database. When I use the line below it changes all of the titles in the database. Any clues would be greatly appreciated.

$query = "UPDATE mdbookclub SET title='$title' WHERE md_id=>=1";

[coopster]

Which row do you want to update? The one that has md_id value greater than or equal to 1?

[Ann_G]

First I have a form where people can select the title they like to update and that script looks like this.

$query = "SELECT * FROM mdbookclub
WHERE title ='$title'";
$result = mysql_query ($query);

So after they entered the title that title is reprinted on the screen for their verification.
Below it, on the same page, I want them to be able to modify that title and that's where I have a problem with the UPDAYE and WHERE clause. I assume I have to use the primary key of the returned selected record in my UPDATE statement but I can't figure out, or find info, on how to do that.

[hakre]

I assume I have to use the primary key of the returned selected record in my UPDATE statement but I can't figure out, or find info, on how to do that.

you need to pass the value of your primary key to the page in which you use the UPDATE query. this is important, because the primary key ensures that only the row in your database is updated which contains the title value to be changed. you can use a hidden input element (<input type="hidden" ... />) for that. just put it in the form where the user can change the title and give it a name (ie md_id) and a value (the value of the primary key whichs title is to be changed).

then put that value into your query:

$title = addslashes($_POST['title']); 
$md_id = intval($_POST['md_id']); 
$query = "UPDATE mdbookclub SET title='$title' WHERE md_id=$md_id";

that should do the trick. here an example for your form:

<form ...> 
<input type="text" name="title" value="<?=htmlspecialchars($row['title'])?>" /> 
<input type="hidden" name="md_id" value="<?=$row['md_id']?>" /> 
<input type="submit" value="Change Title" /> 
</form>

these 2 should work together.

[Ann_G]

Thank you for trying to help but it still doesn't work although I did exactly as you wrote. I do not get any error message, it just doesn't update. But if I leave the WHERE clause off, it changes all the titles. So I know where the problem lies but I just can't get it right. Well, I'll continue pondering and trying.
I appreciate your response nevertheless.

[frizhard]

Assuming you have retrieved the information of the book with this query

$query = "SELECT * FROM mdbookclub
WHERE title ='$title'";
$result = mysql_query ($query);
$row=mysql_fetch_assoc($result);

and that md_id is the primary key, you should update your row with this query (if the primary key is alphanumeric)

$up_query="UPDATE mdbookclub SET title='$title' WHERE md_id LIKE '{$row['md_id']}'"

or this one if the primary key is a simple integer

$up_query="UPDATE mdbookclub SET title='$title' WHERE md_id = {$row['md_id']}"

Hope this can help you

[Ann_G]

Thanks a lot for attempting to help. I tried both of your UPDATE commands still it doesn't work.

The SELECT retrieves the title without any problem.
I know the problem is with the primary key and the WHERE clause but nothing I tried has worked so far.

[hakre]

try mysql_error() [php.net] to obtain possibly error information. if there is no error at all, you have the wrong value for your primary key: the value you use for your primary key is not a value of any primary key of the whole table. otherwise at least one record is changed. echo out the query you send to the db. check the value you use in the where clause. query with a select statement and the exact same value for the primary key the table and check if the right entry is returned. write about your experiences in all these cases here again. and not only as: i did exactly as you told, try like i did that and post a "proof" that the program did what you intended it to do (ie. echo out the query, echo out the mysql return of the select query etc.).

[jamesa]

$query = "UPDATE mdbookclub SET title='$title' WHERE md_id=>=1";

Hmm, I've never seen '=>=' before. Did you mean this instead:

$query = "UPDATE mdbookclub SET title='$title' WHERE md_id >= 1";

[grandpa]

Ann_G

Could you give us the table structure for mdbookclub, and your current UPDATE statement.

There's no apparent reason for these UPDATE's to fail (except for the one jamesa just pointed out). As previously stated, determine if an error is being generated with the UPDATE. In my mind, there must be an error being generated since there is no update when you use the WHERE clause, yet there is an UPDATE without using the WHERE clause. And, updating every title would be the correct action without using the WHERE clause.

[Ann_G]

Sorry, I feel bad not responding earlier. Just been too busy. I haven't had a chance to try what you suggested yet but as soon as I do I'll let you know.

[Ann_G]

Thanks all of you for trying to help. I have been trying everything suggested but I'm still stuck.

Here is my MYSQL table with md_id as primary key
and UPDATE command:

CREATE TABLE `mdbookclub` (
`md_id` INT( 6 ) NOT NULL AUTO_INCREMENT ,
`title` VARCHAR( 100 ) NOT NULL ,
`author` VARCHAR( 30 ) NOT NULL ,
`f_author` VARCHAR( 30 ) NOT NULL ,
`pub_year` VARCHAR( 10 ) NOT NULL ,
`annotation` TEXT NOT NULL ,
`call_number` VARCHAR( 10 ) NOT NULL ,
PRIMARY KEY ( `md_id` ) ,
INDEX ( `title` , `author` , `call_number` )
);

With just this:
$query = "UPDATE mdbookclub SET title='$title'
it works but updates all titles which I don't want.
As does WHERE md_id >= 1";

I thought this would work but no such luck.
$query = "UPDATE mdbookclub SET title='$title' WHERE md_id='$md_id'";

[grandpa]

Change the query execution to report any errors, as shown in bold below. This will display the exact query statement and the mysql error. At this point the error message seems to be the only remaining place to find any clues as to why this won't work.

$query = "UPDATE mdbookclub SET title='$title' WHERE md_id='$md_id'";
mysql_query($query) or die("mdbookclub UPDATE failed<br>$query" . mysql_error());

[Ann_G]

I copied and pasted this:

$query = "UPDATE mdbookclub SET title='$title' WHERE md_id='$md_id'";
mysql_query($query) or die("mdbookclub UPDATE failed<br>$query" . mysql_error());

but I get no error message. It just doesn't update.

If I substitute this WHERE clause:
WHERE md_id = {$row['md_id']}"
I do get an error message.

And if I substitute this WHERE clause:
WHERE md_id >= 1";
it updates all titles (of course with the little brain power I have left I could actually figure out that would be the result).

I'm just as stomped as ever as to what the where clause should be.

Thanks anyhow everybody for trying to help. I so appreciate your willingness.

[jamesa]

What does this output:

echo $query = "UPDATE mdbookclub SET title='$title' WHERE md_id='$md_id'";

[Ann_G]

It doesn't do anything. I don't get an error message but it doesn't update either.

[grandpa]

There has got to be something wrong with '$md_id'.

Have you echoed the query so you can see what the values are?

[dreamcatcher]

Hi Ann_G,

When you store the value of md_id in your hidden form variables, look at your source code to see if there is actually any value there before you pass it to your update query.

Pretty much what grandpa said really. It might be that your variable has no value.

dc

[Ann_G]

When I echo md_id nothing is returned (and I'm sure I'm doing it right. But isn't the value of the md_id
the number I can clearly see for each record in the MYSQL database?

I tried this. I made the first couple of titles the same. Then using this UPDATE:

$query = "UPDATE mdbookclub SET title='$title' WHERE md_id = 2";

I was able to change one of the titles to the new title I gave it. So why doesn't it work if I just substitute $md_id for the number? I must be missing an important piece if information. I thought '$md_id'
stood for whatever value was there or I give it, just as, in this case, '$title'

[dmmh]

am I crazy or is nothing wrong here? I assume you are trying to update the title for md_id 1 and it updates all titles instead?

ofcourse this is gonna happen, you are still forcing it to update all rows where the value of the field 'md_id' is greater or equal to 1, if you edit the title of the first entry in the table, this would edit all titles, as you tell it to do so with:

$md_id == 1 in your scenario, so:
$query = "UPDATE mdbookclub SET title='$title' WHERE md_id >= '$md_id'";

is pretty much the same as saying:
$query = "UPDATE mdbookclub SET title='$title' WHERE md_id >= 1";

whats wrong with using:
$query = "UPDATE mdbookclub SET title='$title' WHERE md_id = '$md_id'";

ofcourse this will update only one row, but I dont think Im wrong when I say this is what you want to do? Or am I totally missing something here?

[dreamcatcher]

I thought '$md_id' stood for whatever value was there or I give it, just as, in this case, '$title'

$md_id will be the same as WHERE md_id = '2'", so long as $md_id has the value of 2. ie:

$md_id = '2';

If you have your data in a hidden field, you need to get that value to your UPDATE query. So when you process your form you do something like:

<input type="hidden" name="md_id" value="2" />

WHERE md_id = '".$_POST['md_id']."'";

Also, use the limit clause to ensure only one row is updated:

WHERE md_id = '".$_POST['md_id']."' LIMIT 1";

[Ann_G]

I'm at my wit's end. I tried so many approaches I'm about ready to quit.

How can I put the number after the value when I don't know what it is. I know only the name of the title that I wish to change. So this doesn't do it.
<input type="hidden" name="md_id" value="2" />

But if I substitute md_id (or $md_id) for the number, or
<input type="hidden" name="md_id" value="<?=$row['md_id']?>" /> It doesn't do anything.

I can't UPDATE because I can't seem to retrieve the primary key number. Yet I can retrieve data from all the other rows in MYSQL.

[dmmh]

paste some code from the script surrounding the query, so we can see if there are any errors there?

[dreamcatcher]

When you retrieve the data for editing, you need to get the id number from that query and place it in a hidden field to pass to the database for your update query.

Going back to your original query:

$query = "SELECT * FROM mdbookclub
WHERE title ='$title'";
$result = mysql_query ($query);

So, assuming you have done this query and you are using mysql_fetch_object to display your data, your id number would be found by doing:

$row = mysql_fetch_object($result);

echo $row->id;

Now in the form where you want to have people update this, you would add:

<input type="hidden" name="id" value="<?php echo $row->id;?>">

This is what you need to pass to the database to updated a specific row. Note that if you have two books with the same title, it might not be a good idea to pull them by book title as you could get the wrong book.

Might be best if you post us some more code, so we can help more.

dc

[Ann_G]

Thank you very much to all of you and particularly to DC who spelled out what I didn't know, i.e. to use
this function:

$row = mysql_fetch_object($result);

Now

echo $row->id;

prints out the primary key which I then enter into
a form field and that way the exact record is updated.

I'm not using the hidden field you suggested because it didn't do anyhting and I need a visible form field to enter the primary key number into. There probalby is a way of doing it where I don't manually have to enter the number, but this works.

Thank you again. You have all been very patient with me.

[dreamcatcher]

Ann_G,

Glad you got it working ok. We are all here to help each other, it makes coding all the more fun. :)

dc