Hey all...

The sql query works fine- when I've tested it I get all the rows but ultimately somewhere after the initial query the 1st row gets skipped...

I've been smashing my head all morning and can't seem to see what's wrong here... perhaps I can't see the forest for the trees anymore...

Anyone mind taking a look:

		$result = mysql_query("SELECT pg_field30, pg_field31, pg_field39, pg_field40, pg_field34, pg_field35, pg_field36, pg_field37, pg_field38, pg_field51, pg_field134, pg_field42, pg_field138 
							  FROM zk_pagesetter_pubdata2 AS biss
							  WHERE pg_field51 >= '0'
							  AND pg_field30 =2009
							  AND pg_field134
							  IN (

						  SELECT MIN( pg_field134 )
						  FROM zk_pagesetter_pubdata2
						  WHERE zk_pagesetter_pubdata2.pg_field38 = biss.pg_field38
						  )"				  
						  ) or die(mysql_error());

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

		$result2 = mysql_query("SELECT count( * ) AS results
								FROM zk_pagesetter_pubdata8
								WHERE pg_field88 = '$row[8]' 
								AND pg_field89 = '$row[12]'
								AND pg_field106 = '$row[0]'"
								) or die ();

		$row2 = mysql_fetch_assoc($result2);
		if ($row2['results'] >= 1){

			$oldPoints = mysql_query("SELECT pg_field90 AS totalPoints
									  FROM zk_pagesetter_pubdata8
									  WHERE pg_field88 = '$row[8]' 
									  AND pg_field89 = '$row[12]'
									  AND pg_field106 = '$row[0]'"
									  ) or die ();

			$pointValue = mysql_fetch_assoc($oldPoints);
			$newPoints = ($pointValue['totalPoints'] + $row[9]);

			mysql_query("UPDATE
						 zk_pagesetter_pubdata8 
						 SET pg_field90 = '$newPoints' 
						 WHERE pg_field88 = '$row[8]' 
						 AND pg_field89 = '$row[12]'
						 AND pg_field106 = '$row[0]'"
						 ) or die (); 

		} else {

    A typical reason for such a problem is a spurious fetch before a loop, but I can find no such problem in your code snippet. I wonder if your many SQL statements could be simplified to one, e.g., (but grossly untested, not even for syntax errors)

    mysql_query("UPDATE zk_pagesetter_pubdata8 AS t8, zk_pagesetter_pubdata2 AS t2
    SET t8.pg_field90 = t8.pg_field90 + t2.pg_field51
    WHERE t2.pg_field38 = t8.pg_field88
        AND t2.pg_field138 = t8.pg_field89
        AND t2.pg_field30 = t8.pg_field106
        AND t2.pg_field51 >= 0
        AND t2.pg_field30 = 2009
        AND t2.pg_field134 IN (
            SELECT MIN( pg_field134 )
                FROM zk_pagesetter_pubdata2
                WHERE zk_pagesetter_pubdata2.pg_field38 = t2.pg_field38
            )");

      I don't see an ORDER BY in your query, so there's no guarantee what order you process the rows. You might be losing one somewhere else that happened to be the first returned when you ran the query outside of php.

        Write a Reply...