mysql_fetch_array() returns only 500 records. How to increase?

[JsusSalv]

Hello Everyone:

 

Does anyone know how to increase the number of results returned by mysql_fetch_array() within in a while loop? I only need to display about 600 records but my script is only returning 500 records max.  Is this a PHP limit or a MySQL issue?  I tried using LIMIT 600 but that didn't work.  Any suggestions?

[ShiloVir]

LIMIT 0, 600 Perhaps? Im not sure. Running more than 500 results on a page would take forever to load. Perhaps pagitation would help u out?

[JsusSalv]

If only! I tried that but it didn't work.  I only have five columns with very little info in each.  My friend doesn't want pagination.  I'd rather do pages but it's his website.  Any other suggestions?

[JsusSalv]

If this is a limit that PHP or MySQL has then please tell me so I can just stop here and push for pagination.

[PFMaBiSmAd]

What error or symptom do you get when there are more than 500 rows?

 

There is no built in limit on the number of rows. There is a data packet limit on how much can be retrieved in a single query, but there is an error associated with this that would tell you it is happening and none of the rows would be returned.

 

It is likely that something else in your code is either causing this or your code has no error checking, error reporting, or error recovery logic to tell you what is actually occurring. Posting your code would be the quickest way for anyone to help you with what it might or might not be doing.

[JsusSalv]

Here's what I have so far and it works fine...but shows only 500 records:

 

<?php
// Build SQL query.
$query = "SELECT * FROM memberlist INNER JOIN users ON memberlist.ID = users.ID ORDER BY LastName ASC LIMIT 0,800";

// Process SQL query.
$numresults = mysql_query($query);

// Total number of members
$numrows = mysql_num_rows($numresults);
?>
<div id="contentSection">
<div id="memberCount">Current Member Count: <?php echo $numrows; ?></div>
<table cellpadding="0" cellspacing="0" border="0" class="directoryTable">
        <thead>
            <tr>
        <th class="tableHeader">FIRST NAME</th>
        <th class="tableHeader">LAST NAME</th>
        <th class="tableHeader">JOB TITLE</th>
        <th class="tableHeader">ORGANIZATION</th>
        <th class="tableHeader">INDUSTRY SECTOR</th>
    </tr>
	</thead>
	<tbody>
	   	<?php
	        while ($row = mysql_fetch_array($numresults)) {
	    ?>
	   	<tr class="d1">
	      	<td class="tableData"><?php echo $row['FirstName']; ?></td>
	        <td class="tableData"><?php echo $row['LastName']; ?></td>
	        <td class="tableData"><?php echo $row['JobTitle']; ?></td>
	        <td class="tableData"><?php echo $row['Organization']; ?></td>
	        <td class="tableData"><?php echo $row['Industry']; ?></td>
	    </tr>
	    <?php
	        }
	    ?>
	</tbody>
</table>
</div>	<!-- end.contentSection -->

 

 

While grabbing my code I realized that the second table that is JOINed may be limiting the total number of records being retrieved.  Is this possible?

[PFMaBiSmAd]

An INNER JOIN with an ON condition will only return rows that exist in both tables.

[JsusSalv]

Ok, that makes sense.  However, one table has 596 records, the other table has 495.  But I'm only getting 500 records exactly.  Should I change from an INNER JOIN to something else?

[PFMaBiSmAd]

I'm going to guess that there is some mix of five duplicate ID's in one of the tables so that instead of getting 495 matches, you get 500. And since there are not an equal number of rows in both tables, it would appear that there are some missing rows in one of the tables or duplicates in the other but without a matching id in the other table.

 

To answer what type of query, you would need to tell us what you want the results to be? Which table to you want all the rows from and what do you want to happen when there is no matching row in the other table?

 

Edit: Also, if you have duplicate id's in either table, do you want duplicates? Removing duplicates would be your first step in getting the output that you want.

[JsusSalv]

The ID's are all unique. The other info may be duplicates but not the ID's. I did think about that as well but I don't believe that could be it.

 

However, I just ran a test and you are right. The uneven number of rows is causing the issue somewhere.  I removed the INNER JOIN and only used the first table...lo' and behold, all 596 rows showed up.  Hmmmm....back to the database drawing board for me on this one.

 

To answer the second part, I am trying to upload a csv of members to one table. The second table is supposed to mimic (have a copy) of all the records in the second table. The first table is updated every two weeks, the second table should never be changed. However, if there are more records in the newly uploaded member list in the first table then the difference in rows should also be added to the second table.  Thereby making both tables equal in the number of records each has.  Confusing, I know.  Any guidance?

[jordanwb]

Just throwing this out there but what if MySQL ran out of memory to complete the query? He's using mysql_query so it has to get each row. This would be unlikely if each row took up little space though. Can you post the table structure?

[.josh]

All else fails you could always break it down into several queries with offset limits...but I think the problem is more along the lines of what PFM is saying.

[JsusSalv]

How are "several queries with offset limits" performed?  Can you provide an example or links to where I can see some examples? 

[kenrbnsn]

Is it possible the script is running too long?

 

Try using ini_set with max_execution_time

<?php
ini_set('max_execution_time',0);
?>

 

Ken

[JsusSalv]

I think the code isn't well written for the purpose it was intended for.  I believe PFM was right about that two tables being limited by the one with less records.  Thank you all for helping me with this!

[jordanwb]

Is it possible the script is running too long?

 

Try using ini_set with max_execution_time

<?php
ini_set('max_execution_time',0);
?>

 

Ken

 

I don't think so. If it ran for too long you'd get an error message.

[PFMaBiSmAd]

To help more with the problem, we would need to know a lot more about your tables and the data.

 

Start by posting the two table definitions. That would let someone see if there is a problem with the data type or size of the id fields.

 

Also, what method are you using to get the number of rows that you posted before for each table?

[jordanwb]

Also, what method are you using to get the number of rows that you posted before for each table?

 

He posted his code and he used mysql_num_rows. I was thinking, if he had four TEXT fields, each row would use ~256KB of space. With over 500 rows that would be 125MB of RAM used

[PFMaBiSmAd]

The code that was posted is what is telling him there are 500 rows in the joined result set. I was asking how he determined -

 

one table has 596 records, the other table has 495.

[jordanwb]

Perhaps he was using phpMyAdmin? But I think I see what you're getting at.

[JsusSalv]

I got the info from phpMyAdmin

[jordanwb]

Try running the query in phpMyAdmin and see what you get.