Solved

MySQL - Limit or Top Records

Posted on 2016-10-19
15
55 Views
Last Modified: 2016-10-21
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;";
0
Comment
Question by:bschwarting
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
15 Comments
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 41850524
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"; 

Open in new window

0
 
LVL 1

Author Comment

by:bschwarting
ID: 41850526
$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.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 41850531
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).
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 110

Expert Comment

by:Ray Paseur
ID: 41850534
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);
}

Open in new window

0
 
LVL 1

Author Comment

by:bschwarting
ID: 41850535
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;";
0
 
LVL 1

Author Comment

by:bschwarting
ID: 41850544
<!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">&nbsp;</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>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
    </font>
    </strong>
    </td>
    </tr>
	</table>
	</td>
    </tr>	
	</tbody>
</table>
</body>
</html>

Open in new window

0
 
LVL 1

Author Comment

by:bschwarting
ID: 41850545
This is the full page of code, do you see anything else that would mess up the limit?
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 41850571
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...
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 41850584
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

Open in new window

0
 
LVL 1

Author Comment

by:bschwarting
ID: 41850613
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" }

Open in new window

0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 41850628
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.
0
 
LVL 1

Author Comment

by:bschwarting
ID: 41850637
So why is it working here but not on the full page?
0
 
LVL 110

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 41850669
Probably some kind of logic error.  

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.
1
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 41850886
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.
2
 
LVL 1

Author Closing Comment

by:bschwarting
ID: 41853728
I removed $mem_connection = $condt_array[0]; temporarily and that got it working.  That put me in the direction I needed.  Thanks!
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to count occurrences of each item in an array.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question