Solved

can't figure out how to implement the LIMIT clause

Posted on 2011-03-09
10
812 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
[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
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 

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

Are You Using the Best Web Development Editor?

The worlds of web hosting and web development are constantly evolving. Every year we see design trends change, coding standards adapt and new frameworks/CMS created. With such a quick pace of change it’s easy to get lost trying to keep up.

See if your editor made the list.

Question has a verified solution.

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

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
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…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

615 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