Reputation: 13
PHP and MySQL limit not working right.
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>
Upvotes: 1
Views: 4357
Answers (3)
Reputation: 26421
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.
Upvotes: 2
Reputation:
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
Upvotes: 1
Reputation: 5039
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
Upvotes: 1