UPDATE refuses to update in php, but does in phpadmin

[salewit]

This is driving me crazy. This is a routine that counts the number of items each vendor has, and stores it in another table.

Here's part of the code

$result = mysql_query("SELECT count(*) from inventory WHERE mfg like '".$row['vendorid']."';");
$count = mysql_fetch_row($result);
echo $count[0]; # Properly returns 11 

$j = "UPDATE catcount SET count = " . $count[0] . " WHERE name LIKE '" . $row['vendorid'] . "' LIMIT 1;";
echo $j; # Properly returns UPDATE catcount SET count = 11 WHERE name LIKE "ri" LIMIT 1;
$result = mysql_query($j);

1) When I run this and look in catcount, "ri" count becomes 0 every time.
2) If I copy and paste that update statement directly into phpmyadmin, "ri" then becomes 11 as it should have in my script.
3) If I run my script again, it knocks out the 11, and it becomes 0 again. This happens on only 3 o4 fields out of about 100 vendors which really makes it confusing as to why it's not working on just a few random ones.

Any advice?

[Demaestro]

I wonder if because you are using like but haven't included any wildcard chars as a reason.

Some rows may = ri some may need the a wildcard char to match which MAY explain why it works sometimes.

$j = "UPDATE catcount SET count = " . $count[0] . " WHERE name LIKE '%" . $row['vendorid'] . "%' LIMIT 1;";

Without seeing the data I am not sure if this is the problem though

[salewit]

Yeah, changed LIKE to = and still the same thing. I also even took off the LIMIT 1 and had the same effect. In phpmyadmin I ran: "SELECT * FROM catcount where name = "ri"; and only got that one, so there are no dupes.

I should mention that I just added another check of mysql_affected_rows after every update, and since most of the items haven't changed, I get a 0 next to them, but these 3-4 problem updates ALWAYS have an affected row of 1 even though they're staying at 0. It's almost like it's getting updated to something then back to 0 again, but there's just nothing there to show why that would happen.

Here's the entire code from this section as it actually is. I'm kind of an amateur at this, so I was reluctant to show it.

echo "Checking vendor quantities....<br>";

$selstate = "SELECT vendorid,vendorname FROM vendors";
$resultID = mysql_query($selstate,$linkID);
 for ($y=0;$y < mysql_num_rows($resultID);$y++) {
 $row = mysql_fetch_assoc($resultID);
 echo $row['vendorname'] . "...";
 $resultID2 = mysql_query("SELECT count(*) from inventory WHERE status = 1 AND mfg like '".$row[vendorid]."';");
 $count = mysql_fetch_row($resultID2);
 $j = "UPDATE catcount SET count = " . $count[0] . " WHERE name = '" . $row['vendorid'] . "' LIMIT 1;";
 $resultID3 = mysql_query($j);
 $jj = mysql_affected_rows();
 echo "$count[0] $j $jj <br>";
}

That last troubleshooting echo displays "11 UPDATE catcount SET count = 11 WHERE name = 'ri' LIMIT 1; 1
on the one test problem line. But the quantity in "count" is always 0.

[Demaestro]

Too be honest I don't see anything wrong with your code.

I wonder if the issue is funny/bad data.

Try this in PHPmyAdmin

**This will return vendors with no inventory record with a status of 1

select vendorid,vendorname from vendors where vendorid not in (select mfg from inventory WHERE status = 1)

Does it return anything?

[rocknbil]

It might be that count is a mysql function (you're using it previously), and you are referencing the field count literally - it may conflict. Backtick your table object names:

$j = "UPDATE `catcount` SET `count` = " . $count[0] . " WHERE `name` = '" . $row['vendorid'] . "' LIMIT 1;";

Other ideas: what happens when you do this?

$result = mysql_query($j) or die("Cannot update record");
echo "Result $result";

It should echo "Result true";

What is the data type of the field "count"? Guessing it's numeric as you don't have it quoted (which should be fine . . . )

What's the possibility you have two records with the value ri?

The limit clause is not (should not be) needed, and like is less efficient than = if it's an exact match, but you said you already tried that.

[salewit]

Thanks for the help. I'm still stuck. This isn't making any sense.

Demaestro: I tried that select statement and it returned zero rows.

rocknbil: I had thought about maybe there being a problem using "count" as a field name and I changed it to to itemcnt. I also upticked everything. The new line is:

$j = "UPDATE `catcount` SET `itemcnt` = " . $count[0] . " WHERE `name` = '" . $row['vendorid'] . "';";
$resultID3 = mysql_query($j) or die("Cannot update record");
echo "Result $resultID3 <br>";

The returned result is 1 on all the lines, so I guess that's the same as true?

The data type is Smallint (never expecting more than approx 500 in any field)

The thing that is so maddening is that it's just a few fields. Here's the output from my rustic results. Note "ri" and "mp" are showing affected rows.

Checking manufacturers first....
Result 1
50 UPDATE `catcount` SET `itemcnt` = 50 WHERE `name` = 'moi'; 0
Result 1
11 UPDATE `catcount` SET `itemcnt` = 11 WHERE `name` = 'ri'; 1
Result 1
180 UPDATE `catcount` SET `itemcnt` = 180 WHERE `name` = 'hbp'; 0
Result 1
1 UPDATE `catcount` SET `itemcnt` = 1 WHERE `name` = 'scp'; 0
Result 1
24 UPDATE `catcount` SET `itemcnt` = 24 WHERE `name` = 'AKP'; 0
Result 1
47 UPDATE `catcount` SET `itemcnt` = 47 WHERE `name` = 'srp'; 0
Result 1
11 UPDATE `catcount` SET `itemcnt` = 11 WHERE `name` = 'catvp'; 0
Result 1
19 UPDATE `catcount` SET `itemcnt` = 19 WHERE `name` = 'mp'; 1
Result 1
21 UPDATE `catcount` SET `itemcnt` = 21 WHERE `name` = 'TSG'; 0

Now here's a brief browse from phpmyadmin after the script is run. Last field is an "date auto update" field:

moi 50 2011-03-02 21:17:15
ri 0 2011-04-20 13:42:20
hbp 180 2011-03-30 17:34:51
scp 1 0000-00-00 00:00:00
AKP 24 2011-01-25 00:23:21
srp 47 2011-02-17 01:25:31
catvp 11 2010-05-07 14:22:38
mp 0 2011-04-20 13:42:20
TSG 21 2010-12-05 20:21:54

Now here's a simple select (select * from `catcnt` where name = "ri" OR name = "mp";)

mp 0 2011-04-20 13:42:20
ri 0 2011-04-20 13:42:20

[salewit]

BTW, just tried changing the data type to INT, and it still did it, then changed it to VARCHAR(100) and changed the update to: UPDATE `catcount` SET `itemcount` = '" . $count[0] . "' WHERE `name` = '" . $row['vendorid'] . "'; and it still left a zero instead of the correct number. I also did a table check and repair, and both were ok. I'm stumped! If ALL the records weren't updating properly, that would be one thing. But just a seemingly arbitrary handful? It's bizarre.

[rocknbil]

Agreed . . it almost seems like php is passing the value as a string, and mysql is accepting it that way somehow. That would always give an 0. Maybe

$j = "UPDATE `catcount` SET `itemcnt` = " . (int)$count[0] . " WHERE `name` = '" . $row['vendorid'] . "';

or

$j = "UPDATE `catcount` SET `itemcnt` = " . intval($count[0]) . " WHERE `name` = '" . $row['vendorid'] . "';

It your server configured to display errors? If not, check your error log . . . or turn error reporting on, there may be an unseen PHP error ocurring.

[salewit]

Well the tip on turning on error reporting was the one that solved the mystery! Here's what happened if you're interested. Basically this script is going through several features of my website menu. It's not only building an index of how many items for each vendor (the code which I had here), but it's also building an index of items that are based in different countries, states, categories and a few other things. Then it builds a menu bar and stores that so that when a visitor visits a page, the menu is cached and doesn't get rebuilt every time with all this work. Well if you noticed, a couple of the vendor ID's that I had trouble with were "ri" and "mp". When I looked at the error reporting later in the script, I saw this: "Notice: Undefined index: RI on line 172".

Well what it turned out to be is the STATE code of Rhode Island is also RI and we have product that comes from Rhode Island thus the 0 was getting written AFTER the 11 from the vendor. The MP was the country code of "Northern Mariana Islands" which of course we had no product from.

So mystery solved! Thank you very much for your help on this. I don't think I would have figured it out if you hadn't mentioned turning the reporting on.

[rocknbil]

LOL . . . note to self: once again, when s**t goes sideways, do all the basic stuff FIRST. Error checking is #1. :-)