Trying to Iterate thru a MySQL Table

[Guest]

Hi everyone:

 

I'm having a problem with trying to iterate thru MySQL using PHP.

 

 

I use MySQL select MAX(page_id) ... to get an integer for  $MAX (//biggest id record in the table) 

and MYSQL select MIN(page_id)...  to get an integer for  $MIN (//smallest id record in the table) 

 

Then I use this loop to iterate thru 100 records at a time (and avoid having too many MySQL records in memory at one time):

 

//iterate thru 100 Mysql rows at a time and add text to each of these records (using the addtext function)

 

for($i = $MIN; $i < $MAX; $i = $i + 100)

{

        $x = $i;

        $y = $i + 100;

        $q = mysql_query('SELECT page_title FROM testdb.test_page

WHERE (page_id >='.$x.' AND page_id < '.$y.'AND page_category = 5');

        while($row = mysql_fetch_array($q))

        {

         addtext($row['page_title'], "Additional text here");

        }

        echo "A batch of 100 was completed!";

}

 

I keep getting Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\testscript.php on line 47

 

This script keeps hanging on my Mysql statement  mysql_query('SELECT page_title FROM testdb.test_page

WHERE (page_id >='.$x.' AND page_id < '.$y.'AND page_category = 5');

 

Can someone provide advice on a better MYSQL query statement that gets 100 records at a time? i.e. select page_title from the table test_page and get 100 rows at a time and do something with these 100 records? 

 

P.S. Everything is working in my MySQL and PHP setup, so I know it's a problem with my sql query. Thanks.

Edited March 27, 2013 by telemachus18

[Psycho]

The error "Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given" means that your query is failing. But, you should NOT be trying to query 100 records at a time. It will put more load on the server by doing that than it would to just run one query. You should only do something like that when you are implementing pagination - i.e. only showing 100 (or some number) of records on a page at a time.

 

But, the problem look s pretty obvious to me - which you would have found if you did some simple debugging, such as checking for the error and/or echoing the query to the page.

 

Your query is this:

 

'SELECT page_title FROM testdb.test_page WHERE (page_id >='.$x.' AND page_id < '.$y.'AND page_category = 5'

 

If $x is 1 and $y is 100, the parsed query would be:

 

'SELECT page_title FROM testdb.test_page WHERE (page_id >=1 AND page_id < 100AND page_category = 5'

 

Do you notice that there is no space between "100" and the word "AND"? That is why I don't like to create strings with variables by concatenating the variables outside the quotes strings. I prefer to use double quoted strings and put the variables inside the quotes. makes it much easier to read, in my opinion. And, even more important - don't create the query inside the mysql_query() function. Create it as a string first. Then, if there is an error, you can echo the query to the page to inspect it.

 

 

$query = "SELECT page_title
          FROM testdb.test_page
          WHERE (page_id >= $x
            AND page_id < $y
            AND page_category = 5";
$result = mysql_query($query) or die("Query: $query<br>Error: " . mysql_error());

 

But, as I said, you are doing more harm than good by trying to break up the query into chunks. Just query all the records.