The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.
One of a set of tools we're offering as a way of saying thank you for being a part of the community.
MySQL - Limit or Top Records
How do I change this to limit it to the top 25 records?
$query_string = "SELECT * FROM data where con_start is NULL and con_end is NULL ORDER BY type, date_stamp;";
$query_string = "SELECT * FROM data where con_start is NULL and con_end is NULL ORDER BY type, date_stamp;";
Asked:
Who is Participating?
Add LIMIT 25 right after date_stamp. IIRC "TOP" is SQL server, and LIMIT is MySQL.
$query_string = "SELECT * FROM data where con_start is NULL and con_end is NULL ORDER BY type, date_stamp LIMIT 25";
$query_string = "SELECT * FROM data where con_start is NULL and con_end is NULL ORDER BY type, date_stamp LIMIT 25;";
I've tried this and it doesn't pull back any records.
I've tried this and it doesn't pull back any records.
What is the error you get from the SQL query? And does the query return a results set when you run it without the LIMIT clause? Have you tried putting the column names in backticks? Some of them might be MySQL reserved words, causing the query to fail (not sure about that, but it's easy to try).
This shows how to capture and visualize the MySQL errors, if any. The only error it reported for me was that the table did not exist. So I think the syntax is OK.
<?php // demo/temp_bschwarting.php
/**
* Demonstrate some of the basics of MySQLi
*
* References for PHP and MySQL(i):
*
* http://php.net/manual/en/mysqli.overview.php
* http://php.net/manual/en/class.mysqli.php
* http://php.net/manual/en/class.mysqli-stmt.php
* http://php.net/manual/en/class.mysqli-result.php
* http://php.net/manual/en/class.mysqli-warning.php
* http://php.net/manual/en/class.mysqli-sql-exception.php <-- DID NOT WORK PHP 5.3+, MySQL 5.1+
*
* http://php.net/manual/en/mysqli.construct.php
* http://php.net/manual/en/mysqli.real-escape-string.php
* http://php.net/manual/en/mysqli.query.php
* http://php.net/manual/en/mysqli.errno.php
* http://php.net/manual/en/mysqli.error.php
* http://php.net/manual/en/mysqli.insert-id.php
*
* http://php.net/manual/en/mysqli-result.num-rows.php
* http://php.net/manual/en/mysqli-result.fetch-array.php <-- DO NOT USE THIS
* http://php.net/manual/en/mysqli-result.fetch-object.php
*/
ini_set('display_errors', TRUE);
error_reporting(E_ALL);
echo '<pre>';
// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";
// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);
// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
$err
= "CONNECT FAIL: "
. $mysqli->connect_errno
. ' '
. $mysqli->connect_error
;
trigger_error($err, E_USER_ERROR);
}
// ACTIVATE THIS TO SHOW WHAT THE DB CONNECTION OBJECT LOOKS LIKE
// var_dump($mysqli);
// CREATING A QUERY
$sql
=
"
SELECT * FROM data WHERE con_start IS NULL and con_end IS NULL ORDER BY type, date_stamp LIMIT 25;
"
;
// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res = $mysqli->query($sql))
{
$err
= 'QUERY FAILURE:'
. ' ERRNO: '
. $mysqli->errno
. ' ERROR: '
. $mysqli->error
. ' QUERY: '
. $sql
;
trigger_error($err, E_USER_ERROR);
}
no errors
THIS WORKS AND BRINGS EVERYTHING (SLOW!!!)
$query_string = "SELECT * FROM data where con_start is NULL and con_end is NULL ORDER BY type, date_stamp;";
THIS RETURNS NOTHING
$query_string = "SELECT * FROM data where con_start is NULL and con_end is NULL ORDER BY type, date_stamp LIMIT 25;";
THIS WORKS AND BRINGS EVERYTHING (SLOW!!!)
$query_string = "SELECT * FROM data where con_start is NULL and con_end is NULL ORDER BY type, date_stamp;";
THIS RETURNS NOTHING
$query_string = "SELECT * FROM data where con_start is NULL and con_end is NULL ORDER BY type, date_stamp LIMIT 25;";
<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>True Natural Gas - Gas Rate Entry</title>
<link rel="STYLESHEET" type="text/css" href="images/template2.css">
<script>
<!--
function land(ref, target)
{
lowtarget=target.toLowerCase();
if (lowtarget=="_self") {window.location=loc;}
else {if (lowtarget=="_top") {top.location=loc;}
else {if (lowtarget=="_blank") {window.open(loc);}
else {if (lowtarget=="_parent") {parent.location=loc;}
else {parent.frames[target].location=loc;};
}}}
}
function jump(menu)
{
ref=menu.choice.options[menu.choice.selectedIndex].value;
splitc=ref.lastIndexOf("*");
target="";
if (splitc!=-1)
{loc=ref.substring(0,splitc);
target=ref.substring(splitc+1,1000);}
else {loc=ref; target="_self";};
if (ref != "") {land(loc,target);}
}
//-->
</script>
</head>
<body>
<?php
//========================================
//=========Grab Current Members===========
//========================================
// Make a MySQL Connection
include 'connect.php';
mysql_connect($server, $usr, $pwd) or die(mysql_error());
mysql_select_db($db) or die(mysql_error());
$counter = 0;
$cmonth = date('m');
$cyear = date('Y');
$query_string = "SELECT * FROM data where con_start is NULL and con_end is NULL ORDER BY type, date_stamp;";
$result = mysql_query($query_string);
while ($row = mysql_fetch_assoc($result)){
$data_mems[$counter] = $row[ 'memsep' ];
$data_term[$counter] = $row[ 'term' ];
$data_rate[$counter] = $row[ 'rate' ];
$data_type[$counter] = $row[ 'type' ];
$data_date[$counter] = $row[ 'date_stamp' ];
$counter = $counter + 1;
}
?>
<table height="100%" id="loginMainTable" width="100%" cellspacing="0" cellpadding="0" style="border:solid white 0px;">
<tbody>
<tr>
<td>
<table align="center" class="blueBorder" cellspacing="0" cellpadding="0">
<tbody><tr>
<td id="companyTD" width="100%" style="position:relative;">
<table width="100%" cellpadding="0" cellspacing="0">
<tbody><tr>
<td>
<img src="images/logo.png" align="absmiddle">
</td>
</tr>
</tbody></table>
</td>
</tr>
<tr>
<td background="images/feature1.back.png" align='left' valign="top" class="msgText">
<strong>
<font color="#FFFFFF">
<center><strong>Update Pending Contract Dates</strong></center>
</font>
</strong>
</td>
</tr>
<tr>
<td align="center" class="internalTD">
<table width="62%" height="100%" cellspacing="0" cellpadding="0">
<tbody>
<tr>
<td colspan="2" valign="top" class="msgText"> </td>
</tr>
<tr>
<td width="20%" valign="top" align="left" class="msgText"><strong>Account</strong></td>
<td width="10%" valign="top" align="left" class="msgText"><strong>Term</strong></td>
<td width="10%" valign="top" align="left" class="msgText"><strong>Rate</strong></td>
<td width="10%" valign="top" align="left" class="msgText"><strong>Type</strong></td>
</tr>
<?php
$counter2 = 0;
// Connecting to Oracle DB
$conn = oci_connect("");
if (!$conn) die("Error connecting to Oracle database: " . oci_error());
if (!$conn) {
$e = oci_error();
echo " ====> ERROR ====> ";
echo htmlentities($e['message']);
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
while ($counter2 <> $counter){
$membsep = $data_mems[$counter2];
// Build Query for Location Number
$stid = oci_parse($conn, 'SELECT MEMBERDETL.CONNDATE FROM MEMBERDETL WHERE MEMBERDETL.DISCDATE = 000000 AND MEMBERDETL.MBRSEP = '.$membsep);
if (!$stid) {
$e = oci_error($conn);
echo " ====> ERROR ====> ";
echo htmlentities($e['message']);
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
// Perform the logic of the query
$r = oci_execute($stid);
if (!$r) {
$e = oci_error($stid);
echo " ====> ERROR ====> ";
echo htmlentities($e['message']);
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
// Fetch the results of the query
while ($row = oci_fetch_array($stid, OCI_ASSOC+OCI_RETURN_NULLS)) {
foreach ($row as $item) {
$condt_array[] = $item;
}
}
$mem_connection = $condt_array[0];
if ($mem_connection !== null)
{
echo "<tr>";
echo "<td width='20%' valign='top' align='left' class='paramText'><a href='date_entry_account.php?memsep=$data_mems[$counter2]&dated=$data_date[$counter2]'>$data_mems[$counter2]</a></td>";
echo "<td width='10%' valign='top' align='left' class='paramText'>$data_term[$counter2]</td>";
echo "<td width='10%' valign='top' align='left' class='paramText'>$data_rate[$counter2]</td>";
echo "<td width='10%' valign='top' align='left' class='paramText'>$data_type[$counter2]</td>";
echo "</tr>";
}
unset($condt_array);
$counter2 = $counter2 + 1;
}
?>
</table>
<br>
<br>
</td>
</tr>
<tr>
<td background="images/feature1.back.png" align='center' valign="top" class="bottomText">
<strong>
<font size="+1" color="#FFFFFF">
<a href="admin.php" style="COLOR: #FFFFFF; text-decoration: none;">Admin Home</a>
</font>
</strong>
</td>
</tr>
</table>
</td>
</tr>
</tbody>
</table>
</body>
</html>
Well, it seems to be using the MySQL extension, which has been removed from PHP. That could be an issue, but it would probably show up as some kind of error. If you have a back-level PHP installation it may still be available.
Give me a moment to look at the code...
Give me a moment to look at the code...
Let's try just this much to deconstruct the problem into the SSCCE.
<?php
//========================================
//=========Grab Current Members===========
//========================================
// Make a MySQL Connection
include 'connect.php';
mysql_connect($server, $usr, $pwd) or die(mysql_error());
mysql_select_db($db) or die(mysql_error());
$counter = 0;
$cmonth = date('m');
$cyear = date('Y');
$query_string = "SELECT * FROM data WHERE con_start IS NULL and con_end IS NULL ORDER BY type, date_stamp LIMIT 25"; // EDITORIAL CHANGES
$result = mysql_query($query_string) or die(mysql_error()); // VISUALIZE ANY ERROR
while ($row = mysql_fetch_assoc($result)){
$data_mems[$counter] = $row[ 'memsep' ];
$data_term[$counter] = $row[ 'term' ];
$data_rate[$counter] = $row[ 'rate' ];
$data_type[$counter] = $row[ 'type' ];
$data_date[$counter] = $row[ 'date_stamp' ];
$counter = $counter + 1;
}
var_dump($data_date); // CHECK FOR OUTPUT
i changed it to 5 to clean it up some, but it looks like it's working with just that.
array(5) { [0]=> string(10) "2016-03-07" [1]=> string(10) "2016-03-23" [2]=> string(10) "2016-06-30" [3]=> string(10) "2016-07-06" [4]=> string(10) "2016-07-13" }
Yes, that makes sense for LIMIT 5. The only thing the LIMIT clause will do is stop the collection of rows, so if there are only three rows found by the query, you'll get all three in the results set. If there are more than LIMIT, you'll get just the LIMIT amount.
Minor point. I never use a ';' to terminate statements inside a string for SQL in PHP. The MySQL drivers do not expect or require it.
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
-
Course of the Month11 days, 17 hours left to enrollMonitoring and Operating a Private Cloud with System Center 2012 R2 273 lessons
Try searching the script for $condt_array. It is used on three lines. On one of the lines it says this:
$mem_connection = $condt_array[0];
You might want to use var_dump() to print out $mem_connection. It looks like the script will use a conditional statement about $mem_connection to decide whether to produce table rows.