Solved

can't figure out how to implement the LIMIT clause

Posted on 2011-03-09
10
802 Views
Last Modified: 2012-06-27
I have a grid that works perfect, almost...
I would like to use the LIMIT clause to only display the top 5.  I have it ordering properly, but it shows all the records, not the top 5.  Can someone please help me get the LIMIT 0,5 to work properly?

<?php
session_start();

if(!isset($_SESSION['id'])){
	header("location:login.php");
      }else{
      $id = $_SESSION['id']; 
}
//echo "$id";

require 'connections/class.eyemysqladap.inc.php';
require 'connections/class.eyedatagridPORTFOLIO.inc.php';

$db = new EyeMySQLAdap('host', 'user', 'password', 'name');
$x = new EyeDataGrid($db);
$where = "id=$id";

// Set the query
$x->setQuery("Symbol,Full_Name,Industry,Affiliation,Current_Value,Movement,id", "portfolio", "", "$where");

// Hide ID Column
$x->hideColumn('id');

// Allows filters
$x->allowFilters();

// Change column type
$x->setColumnType('Symbol', EyeDataGrid::TYPE_HREF);
$x->setColumnType('Industry', EyeDataGrid::TYPE_IMAGE);

?>

Open in new window



class.eyedatagridPORTFOLIO.inc.php
<?php

class EyeDataGrid
{
	private $results_per_page = 9;
	private $query; // SQL query
	private $filter = false; // Current filter
	private $limit = true; // Current limit
	private $_db, $result; // Database related
	private $select_fields = ''; // Field used to select
	private $select_where = ''; // Where clause
	private $select_table = ''; // Table to read

* Outputs the datagrid to the screen
	*
	*/
	public function printTable()
	{
		// Set the limit
		$this->setLimit(($this->page - 1) * $this->results_per_page, $this->results_per_page);

		// FILTER
		$filter_query = '';
		if ($this->select_where)
			$filter_query .= "(" . $this->select_where . ")";

		if ($this->allow_filters and $this->filter)
		{
			if (!strstr($this->filter['Value'], '%'))
				$filter_value = '%' . $this->filter['Value'] . '%';
			else
				$filter_value = $this->filter['Value'];

			if ($this->select_where)
				$filter_query = $filter_query . " AND ";

			$filter_query .= "(`" . $this->filter['Column'] . "` LIKE '" . $filter_value . "')";
		}
		
		if ($filter_query)
			$filter = 'WHERE ' . $filter_query;

		// ORDER
		if ($this->order)
			$order = "ORDER BY `" . $this->order['Column'] . "` " . $this->order['Order'];
		else
			$order = '';

		// LIMIT
		if ($this->limit)
			$limit = "LIMIT " . $this->limit['Low'] . ", " . $this->limit['High'];
		else
			$limit = '';

Open in new window

0
Comment
Question by:MsKrissy
  • 4
  • 3
  • 3
10 Comments
 
LVL 1

Expert Comment

by:kavik379
ID: 35086574
Try add this, maybe after the setQuery method:

$x-> setResultsPerPage(5);
0
 

Author Comment

by:MsKrissy
ID: 35086636
okay, that actually returns 5 records per page, but I only want 5 records returned total.
0
 
LVL 1

Expert Comment

by:kavik379
ID: 35086829
Ok. Using the code as is, I don't think it is possible to do what you want. If you notice in the first line of code in the method printTable, the lower limit is set according to the page number, so it will always change, thus changing the limit.
0
 

Author Comment

by:MsKrissy
ID: 35086897
I feel like the mother on Good Times when she first heard her husband James had died in a train accident.

DAMN!, DAMN!, DAMN!
0
 
LVL 1

Accepted Solution

by:
kavik379 earned 500 total points
ID: 35086929
Sorry. Maybe you could ask the author for help on it.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Closing Comment

by:MsKrissy
ID: 35086969
Thank you for the help.
0
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 35087132
How abot adding this:

$this->limit['Low'] = 0;

before this:
            // LIMIT
            if ($this->limit)
                  $limit = "LIMIT " . $this->limit['Low'] . ", " . $this->limit['High'];
            else
                  $limit = '';
0
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 35087279
At least try it.
0
 

Author Comment

by:MsKrissy
ID: 35087541
Thanks Roads Roads,
I tried that, it still gives me all of the records, it just shows whatever value I give.
$this->limit['Low'] = 0;

The problem is there are over 1000 records and I only want to show the top 10, I fear this might slow down the site over time.  As an alternative, I am just hiding the footer which allows you to toggle through pages.

it sucks I know.
0
 
LVL 27

Expert Comment

by:Lukasz Chmielewski
ID: 35087567
Well, I'm just a curious type of guy. But I suspect there IS a solution to that :P
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

930 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now