PHP: Mysql limit range numbers

Question

I would like get number of records in a table then divide them by 4, after dividing them by 4 i want to create sql statements with limit ranges based on my result. For example I have a table with 8 records I divide by 4, I will create 2 sql statements with a limit range like limit 0,4 and limit 4,8

Final results will look like

Select * from prop where id=123 LIMIT 0,4

Select * from prop where id=123 LIMIT 4,8

My approach was to have for loop which will count the number of sql statements to be made. Then in the loop: first circle 0-4 and second will be 4-8

Am struggling on the limit 0-4 and limit 4-8

PHP script

include('connect.php');    
$query_1 = "Select COUNT(*) as Total from prop where ref = 'SB2004'";
$results_query_1 = mysql_query($query_1);
 while($row_query_1 = mysql_fetch_array($results_query_1))
 {
        $cnt = $row_query_1['Total'];
 }

 echo $cnt;
 echo "<br>";
 $num_grps = 0;
 if ($cnt % 4 == 0 )
 {
   echo $num_grps = $cnt / 4 ;

 }
$count_chk= $num_grps * 4;
 for ($i=1;$i<=$num_grps;$i++)
{
    //for loop for range
    for()
    {
        $range = '0,4';
        echo "SELECT prop_ref from prop limit".$range;
    }
}

[Please, stop using mysql_* functions](http://stackoverflow.com/q/12859942/1238019) in new code, they are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Instead of, have a look on [prepared statements](http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html), and use [Mysqli](http://php.net/manual/en/book.mysqli.php) or [PDO](http://php.net/manual/en/book.pdo.php). — zessx, Oct 30 '13 at 10:29
All i need is for the result to print echo 2 select statements with limit range for multiples of 4 cause its going to be used for paging plugin which relays on `divs`. unless there is a prepared statement that prints out sql statements — , Oct 30 '13 at 10:38
Why does that paging plugin not build those queries dynamically? And if you get to output them via echo, then what next – you’re gonna copy&paste them into the plugin code …? — CBroe, Oct 30 '13 at 10:53
The plugin doesn't allow.is there a way I can get the loop to print 0,4 and 4,8 dynamically — , Oct 30 '13 at 11:05

symcbean · Answer 1 · 2013-10-30T13:00:01.223

Either you've not understood the problem or haven't explained it very well.

The most immediate problem here is that you have misunderstood the syntax for the LIMIT clause. The first argument specifies the offset to start at and the second defines the number of rows to return, hence LIMIT 4,8 will return 8 rows (assuming there are 12 or more rows in the dataset).

The next issue is that you've not said if the results need to be reproducible - e.g. if you have rows with primary keys 1 and 2, should these always be returned in the same query. In the absence of an explicit ORDER BY clause, the rows will be returned based on the order in which they are found by the query.

The next issue is that you've not explained how you want to deal with the last case when the total number of rows is not an even multiple of 4.

The code you've provided counts the number of rows where ref = 'SB2004' but then creates queries which are not filtered - why?

The code you've provided does not change the limit in the queries - why?

The next issue is that there is never a good reason for running SELECT queries inside a loop like this. You didn't exlpain what you intend doing with the select queries. But based on the subsequent update....

 include('connect.php');    
 $query_1 = "Select COUNT(*) as Total from prop where ref = 'SB2004'";
 $cnt = mysql_fetch_assoc(mysql_query($query_1));

 $blocks=$cnt['Total']/4 + (0 == $cnt['Total'] % 4 ? 0 : 1);

 $qry2="SELECT * FROM prop where ref='SB2004' ORDER BY primary_key";
 $res=mysql_fetch_assoc($qry2);

 for ($x=0; $x<$blocks; $x++) {
    print "<div>\n$block<br />\n";
    for ($y=0; $y<4; $y++) {
       print implode(",", @mysql_fetch_assoc($res)). "\n";
    }
    print "</div>\n";
 }

It's trivial to refine this further to only issue a single query to the database.

If you really must generate individual SELECTs....

 include('connect.php');    
 $query_1 = "Select COUNT(*) as Total from prop where ref = 'SB2004'";
 $cnt = mysql_fetch_assoc(mysql_query($query_1));

 $blocks=$cnt['Total']/4 + (0 == $cnt['Total'] % 4 ? 0 : 1);

 for ($x=0; $x<$blocks; $x++) {
       $y=$x*4;
       print "SELECT * FROM prop where ref='SB2004' 
           ORDER BY primary_key LIMIT $y,4<br />\n"
 }

PHP: Mysql limit range numbers

1 Answers1