how to UPDATE correctly

[JeremyH]

I have a problem making my web page work.

My database has 2 fields: "catalog", which is an incremental number and corresponds to an image (ie "1.jpg","2.jpg",etc), and "rating", which has the item's score, which is what I want updated as users rate the item it corresponds to.

My page displays the items based on its score--highest "rating" value on top, and then decends as the "rating" value decreases. Each item has a <select><option> pull down menu where the user can give it a score. When the user hits SUBMIT, all the votes are stored in an array called "$rec".

The items in $rec are ordered based on how they were displayed on the voting page. I decided in the processing script to once again order things by rating, and then UPDATE each rating field by adding the value given it by the user. Here is my processing script:


$rec = $_POST['rec'];
$query = "SELECT * FROM `Items` ORDER BY `rating` DESC";
$result = mysql_query($query) or die(mysql_error());

$num = 0;

while($row = mysql_fetch_array($result)){

$newrank = "UPDATE `Items` SET `rating`=`rating` + $rec[$num]";
$resultTwo = mysql_query($newrank);

$num++;
}



This does not work. If I rate all the items with different scores, sometimes the database will not update at all. If I only score one item and leave the others blank (with a default of 0), then it assigns every item that one score. For instance, for the first item the user scores it "8" and doesn't vote on anything else. When SUBMIT is hit, I get $rec(8,0,0,0,0,0,0,etc...). That script will process, but each value in all rating fields has been increased by 8! Can anyone see what I am doing wrong? I am a MySQL rookie, and this is an exercise to get familiar with it, but I'm hung up on this step.

Thanks for any help.

Jeremy

[Fumigator]

You don't have a WHERE clause on your UPDATE query, so you are updating every row in the table.

[masterofollies]

I agree with Fumigator you need to define the item your rating like this.

$itemid - $_POST["id"];
$newrank = "UPDATE `Items` SET `rating`=`rating` + $rec[$num] WHERE id='$itemid'";

[guelphdad]

also while you are at it take a look at mysql_real_escape_string in the php manual. it is important to use.

[JeremyH]

Thanks for the answers...
After reading more MySQL documentation, I realized why I was getting the results I was getting-- "WHERE" is crucial.

Does '$itemid' need to be assigned a value, or can I insert the line you provided as is? In other words, does that say UPDATE rank for "this" row? Or does the line have to read, for instance, UPDATE rank WHERE id="3"? If $itemid needs an actual value, then I think I need to go back to the voting page and rewrite how it assigns values to my $rec array so it is pegged to the "category" value.

I bet I'm going about this exercise in the most backwards way... but I'm going to get this to work one way or another!

[masterofollies]

It has to be defined. Lets say you have a lemon which is ID #33

When you select that item, it should take you to a page all about the lemon, or however you have it setup. Something tells you, that you are viewing the lemon page, it should select the ID number.

Set the $itemid as whatever the item your viewing is. Then your good to go.

[JeremyH]

mysql_real_escape_string

I see that the PHP manual strongly recommends always including that, but wasn't mentioned at all in my PHP/MySQL books. Thanks for the tip on that.

[JeremyH]

Thanks masterofollies, I'll continue tinkering around with this. I think I have to rework my initial page where the user rates the items.

[masterofollies]

No prob, if you get stuck, post more of your coding, like the page that selects the item number, and we'll help you some more.

[JeremyH]

I got my project to work finally. Here is the result:

http://compujer.com/lps/rater.html

All the tips helped. "WHERE" in the query was crucial, but also importantly, I declared the $rec array at the beginning of the voting page. It was filling values in the array by the position they appeared on the webpage, not by value I wanted (the catalog number).

Thanks for all the help in getting this silly project to work.

Now I'm on to making a more complicated, multi-table database project--see if I learned anything.

Jeremy

[masterofollies]

Cool looks nice. I like Catalog #66 ;)

[Fumigator]

Those are men ya know :eek:

[masterofollies]

Those are men ya know :eek:

Impossible, they have female parts.