mysql_query UPDATE statement only affecting a subset of total records

[wmg007]

Hi guys,

 

I'm new to PHP and having some confusion over an issue. I've written a simple code snippet to process a db, look for certain records, and then update a field based upon those findings.  I've tried two sets, each with over 2000 records, but only 600ish get processed.  Can someone explain what I am doing wrong?

 

Code below:

 

<?php

 

//mysql_connect stuff

 

// mysql_select_db stuff

 

$query = "SELECT products.id\n"

. "  , scp.sub_category_id as scp_id\n"

. "  , topics.descr AS topic_descr\n"

. "  FROM products\n"

. "  JOIN sub_category_products scp ON products.id = scp.product_id\n"

. "  JOIN sub_categories sc ON scp.sub_category_id = sc.id\n"

. "  JOIN categories on sc.category_id = categories.id\n"

. "  JOIN topics ON categories.topic_id = topics.id\n"

. "  WHERE topics.id =7";

 

$result = mysql_query($query) or die(mysql_error());

 

while($result != "") {

 

$row = mysql_fetch_array($result) or die(mysql_error());

 

$query2 = "UPDATE products SET this_product = 1 WHERE id =" . $row['id'];

 

mysql_query($query2) or die(mysql_error());

 

 

}

 

?>

 

$query returns a count 4100 records

$query2 only processes 602

 

Thanks.

[Barand]

while($result != "")

 

I'm surprised it isn't still running.

 

Should be

 

<?php
while ($row = mysql_fetch_array($result)) {
...
}

 

[wmg007]

Thanks for your reply.  I'm not sure what's different from my while clause and yours other than you did in one line what I did in two, but I changed it in my code.  I now have 869/4123 records processed.  That's up from 602/4123.  Could there be an I/O issue or something?  I'm baffled.

 

Here is what I now have including the recommended change:

 

$query = "SELECT products.id\n"
. "  , scp.sub_category_id as scp_id\n"
. "  , topics.descr AS topic_descr\n"
. "  FROM products\n"
. "  JOIN sub_category_products scp ON products.id = scp.product_id\n"
. "  JOIN sub_categories sc ON scp.sub_category_id = sc.id\n"
. "  JOIN categories on sc.category_id = categories.id\n"
. "  JOIN topics ON categories.topic_id = topics.id\n"
. "  WHERE topics.id =7";


$result = mysql_query($query) or die(mysql_error());

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

$query2 = "UPDATE products SET some_product = 1 WHERE id =" . $row['id'];
echo $query2 . "<br />"; 


mysql_query($query2) or die(mysql_error());

}

 

Are there any conflicts with having two queries as such or does the structure seem valid?

[Barand]

with your

 

while ($result != '')

 

the value of $result never changes within the loop and should loop indefinitely.

 

With my version it reads each row in turn until there are no more, at which point it returns false and the loop terminates.

 

Anyway, you could just

 

<?php
$query = "UPDATE products
    JOIN sub_category_products scp ON products.id = scp.product_id
    JOIN sub_categories sc ON scp.sub_category_id = sc.id
    JOIN categories on sc.category_id = categories.id
    JOIN topics ON categories.topic_id = topics.id
SET products.this_product = 1     
WHERE topics.id =7";

?>

[wmg007]

True.  Didn't think of that...

 

It worked then...sorta.  I processed 3991 records.  Close enough!

 

Thanks.