PHP and MySQL limit not working right.

Question

I have a working Filter on MySQL statements but I don't want all the thousands of records coming up on the page all the time. I would like to have maybe 30 at most. is there an easy way of doing this? Or am I barking up the wrong tree?!?

if possible can someone give me some assistance?

I put in a Limit statement at the beginning of the Query and it fails when I filter items down. Am I missing anything?

error_reporting(0);
include("config.php");

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"         "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>GT Entertainment Song Database</title>
<script type="text/javascript"     src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.0/jquery.min.js"></script>
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.8.16/jquery-ui.min.js"></script>
<link href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/themes/base/jquery-    ui.css" rel="stylesheet" type="text/css"/>
<style>
BODY, TD {
        font-family:Arial, Helvetica, sans-serif;
        font-size:12px;
}
</style>
</head>


<body>

<form id="form1" name="form1" method="post" action="search.php">
<label for="Track">CAVS ID</label>
<input name="Track" type="text" id="from" size="10" value="<?php echo     $_REQUEST["Track"]; ?>" />
<label>Title or Artist:</label>
<input type="text" name="string" id="string" value="<?php echo     stripcslashes($_REQUEST["string"]); ?>" />
<label>Disk</label>
<select name="Disk">
<option value="">--</option>
<?php
        $sql = "SELECT * FROM ".$SETTINGS["data_table"]." GROUP BY Disk ORDER BY Disk     LIMIT 1,30";
        $sql_result = mysql_query ($sql, $connection ) or die ('request "Could not     execute SQL query" '.$sql);
        while ($row = mysql_fetch_assoc($sql_result)) {
                echo "<option value='".$row["Disk"]."'".    ($row["Disk"]==$_REQUEST["Disk"] ? " selected" : "").">".$row["Disk"]."</option>";
        }
?>
</select>
<label>Comment</label>
<select name="Comment">
<option value="">--</option>
<?php
        $sql = "SELECT * FROM ".$SETTINGS["data_table"]." GROUP BY Comment ORDER BY     Comment LIMIT 1,30";
        $sql_result = mysql_query ($sql, $connection ) or die ('request "Could not     execute SQL query" '.$sql);
        while ($row = mysql_fetch_assoc($sql_result)) {
                echo "<option value='".$row["Comment"]."'".    ($row["Comment"]==$_REQUEST["Comment"] ? " selected" : "").">".$row["Comment"]."</option>";
        } 
?>
</select>
<input type="submit" name="button" id="button" value="Filter" />
  </label>
  <a href="search.php">
  reset</a>
</form>
<br /><br />
<table width="700" border="1" cellspacing="0" cellpadding="4">
  <tr>
    <td width="90" bgcolor="#CCCCCC"><strong>CAVS ID</strong></td>
    <td width="95" bgcolor="#CCCCCC"><strong>Track</strong></td>
    <td width="159" bgcolor="#CCCCCC"><strong>Artist</strong></td>
    <td width="191" bgcolor="#CCCCCC"><strong>Disk</strong></td>
    <td width="113" bgcolor="#CCCCCC"><strong>Comment</strong></td>
  </tr>
<?php

// Start query
$sql = "SELECT * FROM ".$SETTINGS["data_table"]." WHERE 1=1 ";

if ($_REQUEST["string"]!='') {
        $sql .= " AND (Title LIKE '%".mysql_real_escape_string($_REQUEST["string"])."%'     OR Artist LIKE '%".mysql_real_escape_string($_REQUEST["string"])."%')";
}
if ($_REQUEST["Disk"]!='') {
        $sql .= " AND Disk='".mysql_real_escape_string($_REQUEST["Disk"])."'"; 
}

if ($_REQUEST["Comment"]!='') {
        $sql .= " AND Comment='".mysql_real_escape_string($_REQUEST["Comment"])."'";
}
if ($_REQUEST["Track"]!='') {
        $sql .= " AND Track='".mysql_real_escape_string($_REQUEST["Track"])."'";
}

$sql_result = mysql_query ($sql, $connection ) or die ('request "Could not execute SQL     query" '.$sql);
if (mysql_num_rows($sql_result)>0) {
        while ($row = mysql_fetch_assoc($sql_result)) {
?>
  <tr>
    <td><?php echo $row["Track"]; ?></td>
    <td><?php echo $row["Title"]; ?></td>
    <td><?php echo $row["Artist"]; ?></td>
    <td><?php echo $row["Disk"]; ?></td>
    <td><?php echo $row["Comment"]; ?></td>
  </tr>
<?php
        }
} else {
?>
<tr><td colspan="5">No results found.</td>
<?php  
}
?>
</table>

</body>
<a href="http://www.gtentertainment.ca/index.php">Go Back to Home</a>
</html>

add `LIMIT 30` to the end – SchautDollar Jun 27 '13 at 04:43 — SchautDollar, Jun 27 '13 at 04:43
add LIMIT 0,30 in your second sql query. – skparwal Jun 27 '13 at 04:46 — skparwal, Jun 27 '13 at 04:46
see http://en.wikipedia.org/wiki/Separation_of_concerns – Sam Dufel Jun 27 '13 at 04:47 — Sam Dufel, Jun 27 '13 at 04:47

score 2 · Accepted Answer · edited May 25 '19 at 14:21

2

You need to put LIMIT clause at very end pf your query,

.............
if ($_REQUEST["Track"]!='') {
        $sql .= " AND Track='".mysql_real_escape_string($_REQUEST["Track"])."'";
}
$sql .= " LIMIT 30";

Note: Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO, or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

edited May 25 '19 at 14:21

Zoe

27,060
21
118
148

answered Jun 27 '13 at 04:45

Rikesh

26,156
14
79
87

@user2474701 - Dude, you missing the semi-colon at end of the statement. `Line:86` – Rikesh Jun 27 '13 at 05:06

score 1 · Answer 2 · answered Jun 27 '13 at 04:43

1

LIMIT 30 at the end of the query

SELECT column_name(s)
FROM table_name
LIMIT number;

From http://www.w3schools.com/sql/sql_top.asp

answered Jun 27 '13 at 04:43

Jonnny

4,939
11
63
93

It failed, I put it on the end of the Query just below the While Query. – user2474701 Jun 27 '13 at 04:48
$sql_result = mysql_query ($sql, $connection ) or die ('request "Could not execute SQL query" '.$sql); if (mysql_num_rows($sql_result)>0) { while ($row = mysql_fetch_assoc($sql_result)) { LIMIT 30;Parse error: syntax error, unexpected T_LNUMBER in /home/www/gtentertainment.ca/php_site/search.php on line 117 – user2474701 Jun 27 '13 at 04:53
@user2474701 - You're placing at wrong place. Put above your while loop & just after your last if condition, check my answer. – Rikesh Jun 27 '13 at 04:56

score 1 · Answer 3 · answered Jun 27 '13 at 04:52

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:

SELECT * FROM tbl LIMIT 95,18446744073709551615;

With one argument, the value specifies the number of rows to return from the beginning of the result set:

SELECT * FROM tbl LIMIT 5;     # Retrieve first 5 rows

PHP and MySQL limit not working right.

3 Answers3