Updating Multi Items in table

cafrow

I thought this would be very simple to do, but for the last hour I have been trying all sorts of different syntacs to try to get my edit script to work. Basicly this will allow users to edit something that was posted.

<?php
include'mconnect.php';

$id=$_REQUEST['editid'];
$title=$_REQUEST['title'];
$message=nl2br($_REQUEST['message']);

if (!mysql_query("UPDATE News SET title = '$title' AND message = '$message' WHERE id = '$id' LIMIT 1")){
print "News did not update". mysql_error();
}
else{
print "News was updated ";
echo $title;
echo $message;
}
mysql_close($mconnect);

?>

Now I got this from trying to copy what PHPMyAdmin does when it updates, I also tried putting a comma after the first value is added between $title and AND. WHen I run the script as is above it does not error HOWEVER it does not update title correctly, it makes title Zero. The echo on the variable come back the way they should so I have no clue as to why this is not work. If someone could please tell me the error in my SQL that would be great, I have been over at Mysql.com trying to figure out what they say to do but their manuals are hard to read and don't always make sence.

Thanks

LordShryku

What kind of field is title? You may be trying somethign beyond the limitations of the field. Try splitting your query up into a string and echoing that to make sure it looks right

$sql = "UPDATE News SET title = '$title' AND message = '$message' WHERE id = '$id' LIMIT 1";
echo $sql."<br />";
if (!mysql_query($sql)){
   print "News did not update". mysql_error();
}
else{
   print "News was updated ";
   echo $title;
   echo $message;
}

Sxooter

Print the sql statement to your screen (i.e. print $query) and cut and paste that into your mysql monitor.

Note that MySQL coerces things like 'doublenutfudge' into the number 0 if you try to insert it into an int column, so it's quite likely that you have the wrong thing to be inserted or that title is an int column.

Just guessing. Next time, try posting your DDL (i.e. the statements used to create the table) when asking for help, as it gives us a lot more info to work with.

cafrow

UPDATE News SET title = 'Halo' AND message = 'Testing Message' 
WHERE id = '5' LIMIT 1

Okay, this is what echo $sql give back.

Okay, so I check my structure and here is what the field are

title= varchar(50)
message=blob

now the weird thing is that the Message field does NOT update at all and the title is still 0.

when I run my add-news script it still works and is able to add new, here is what I use to add news.

if(!mysql_query("INSERT INTO News (user, message, title, date) 
VALUES ('$username', '$message', '$title', '$datetime')")){
print 'Data could not be updated'. mysql_error();
}
else print 'News has been updated';

Please let me know what you think. I am not getting ANY errors and this is what i get when I hit my Edit-news page.

The code I execute:

<?php
include'mconnect.php';

$id=$_REQUEST['editid'];
$title=$_REQUEST['title'];
$message=nl2br($_REQUEST['message']);

$sql = "UPDATE News SET title = '$title' AND message = '$message' WHERE id = '$id'"; 
echo $sql."<br />"; 
if (!mysql_query($sql)){ 
   print "News did not update". mysql_error(); 
} 
else{ 
   print "News was updated "; 
   echo $title; 
   echo $message; 
}
mysql_close($mconnect);

?>

The response when I hit the code page.

UPDATE News SET title = 'halo' AND message = 'Testing Message.' 
WHERE id = '6' LIMIT 1
News was updated haloTesting Message.

Now just in case it helps, here is what my forum looks like

<form name="form1" method="post" action="editnews.php">
<input name="editid" type="hidden" value="<?php echo $postid ?>">
<input type="text" name="title" value="<?php echo $post['title'] ?>"><br>
<textarea name="message" cols="80" rows="15"><?php echo $post['message']?></textarea>
<br>
<input type="submit" name="Submit" value="Submit">
</form>

EDIT-- Edit the MySQL querry that updates, I removed Limit 1 as a Suggestion from sxooter.

Sxooter

OK, a couple of ideas, not sure what the exact problem is still.

One, using a limit clause on an update is a bit non-standard. Well, limit clauses themselves are pretty non-standard (i.e. not defined by the SQL 92 spec) but using on in an update can cause lots of unintended consequences.

Are there more than one row with id=5? Is id a unique keyed row, so there can be only one row with a given id? If so, the limit is a dunsel, and can be removed.

That limit 1 is bugging me though... Just seems kinda weird.

cafrow

Removed Limit 1 from Mysql Query, tried it again and no result.

I tried it is different records and they are all doing the same thing, changing title to 0 and not doing ANYTHING to Message, just leaving it as was.

ID is a unique auto-increment field.

Edit- If someone has a different Syntacs suggestion let me know, I do not care how i do it, just want it to work. 🙂

Sxooter

Have you tried

Update News ...

i.e. quoting the table name?

cafrow

Mysql error with Update 'News'

Anything else?

Sxooter

What does

select * from News where id=5

show?

Can you show me your exact create table statement you used for this table?

cafrow

I used PHPMyAdmin to create the table. I can do Insert into the table without a problem, can read from it, but when I try to UPDATE it is when I am having problems. When I read from the table this is what I get

title= 0
message = Testing message
user = Cafrow
id = 5

If I go into PHP My Admin I can edit the table and enter the same data that I am trying to edit and it works fine. but as soon as I run my script is when title becomes 0 and does not change anything else.

EDIT --

THis is what PHP My Admin does when I update a record from there.

UPDATE `News` SET `title` = 'Horizons' WHERE `ID` = '5' AND 
`user` = 'CaFrow2' AND `date` = '2004-01-02 14:15:49' AND 
`message` LIKE 0x4f6b61.......  AND `title` = '0' LIMIT 1 ;

The message field is REALLY long so I delete most of it and just added the ..... to make it easier to read.

Sxooter

So, have you tried ` quoting the Title and Message, since they are both upper case?

update News set Title='abc', Message='123' where id=5

???

cafrow

Sorry, title and message are lower case, my bad.

Sxooter

Well then, I don't have a clue. Sorry.

Sxooter

OK, I copied the update you listed from phpmyadmin:

UPDATE News SET title = 'Horizons' WHERE ID = '5' AND
user = 'CaFrow2' AND date = '2004-01-02 14:15:49' AND
message LIKE 0x4f6b61....... AND title = '0' LIMIT 1 ;

Note that your ID is uppercase? It needs to be quoted with back ticks I think. So would News.

I'd suggest recreating the table with all lower case names to simplify your future, if upper/lower casing is the problem. I'm not sure it is though, I'm not familiar with MySQL's behaviour here, as I only use lower case and don't ever quote field and table names...

cafrow

that did not work, I have another script at home that I used to update my users Passwords that I will have to look at and see what I can do.

not sure what is going on. I will post here again later if I get a chance to make it work. Thanks for all your help.

Sxooter

Did you change your ANDs to commas?

cafrow

that was it, the comma did it. Thanks so much, that helped me SO MUCH, thanks, I love you sxooter.