?
Solved

can't figure out how to implement the where clause

Posted on 2011-03-09
13
Medium Priority
?
1,253 Views
Last Modified: 2012-06-21
I have my grid working when I want to display all data, but I can't figure out how to implement a where clause so I can narrow my grid results.
Can someone please show me how/where to implement this clause: WHERE id='$id'


MY_Portfolio.php
<?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);

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

// 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


This is the class.eyedatagridPORTFOLIO.inc.php
<?php

 * EyeDataGrid
 * Provides datagrid control features
 * @link       http://www.eyesis.ca/projects/datagrid.html


class EyeDataGrid
{
	private $primary = ''; // Tables primary key column
	private $query; // SQL query
	private $hidden = array(); // Hidden columns
	private $type = array(); // Column types
	private $controls = array(); // Row controls, std or custom
	private $order = false; // Current order
	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

	/**
	 * Set the SELECT query
	 *
	 * @param string $fields Feilds to fetch from table. * for all columns
	 * @param string $table Table to select from
	 * @param string $primay Optional primary key column
	 * @param string $where Optional where condition
	 */
	public function setQuery($fields, $table, $primary = '', $where = '')
	{
		$this->primary = $primary;

		$this->select_fields = $fields;
		$this->select_table = $table;
		$this->select_where = $where;
	}

Open in new window

0
Comment
Question by:MsKrissy
  • 7
  • 6
13 Comments
 
LVL 1

Assisted Solution

by:kavik379
kavik379 earned 2000 total points
ID: 35085615
It looks like it should be the last parameter in the setQuery function:

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

Expert Comment

by:kavik379
ID: 35085850
Sorry, I think it should be this.

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

Author Comment

by:MsKrissy
ID: 35085890
I tried the following (separately) and all of them did not work:

$x->setQuery("Symbol,Full_Name,Industry,Affiliation,Current_Value,Movement,id", "portfolio",,$id);

$x->setQuery("Symbol,Full_Name,Industry,Affiliation,Current_Value,Movement,id", "portfolio",$id);

$x->setQuery("Symbol,Full_Name,Industry,Affiliation,Current_Value,Movement,id", "portfolio",'$id');

$x->setQuery("Symbol,Full_Name,Industry,Affiliation,Current_Value,Movement,id", "portfolio","$id");
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 

Author Comment

by:MsKrissy
ID: 35085938

I tried the following, and nothing changed:

$x->setQuery("Symbol,Full_Name,Industry,Affiliation,Current_Value,Movement,id", "portfolio",,"id=".$id);

$x->setQuery("Symbol,Full_Name,Industry,Affiliation,Current_Value,Movement,id", "portfolio","id=".$id);

$x->setQuery("Symbol,Full_Name,Industry,Affiliation,Current_Value,Movement,id", "portfolio",,"id=.$id");

$x->setQuery("Symbol,Full_Name,Industry,Affiliation,Current_Value,Movement,id", "portfolio",,"id=.$id.");

$x->setQuery("Symbol,Full_Name,Industry,Affiliation,Current_Value,Movement,id", "portfolio",,"id=$id");
0
 
LVL 1

Expert Comment

by:kavik379
ID: 35085940
After "portfolio" there needs to be two commas.
Also, instead of just $id, as the parameter, I think it should be "id=".$id
0
 
LVL 1

Expert Comment

by:kavik379
ID: 35085984
What about adding "id" as the parameter after "portfolio"

$x->setQuery("Symbol,Full_Name,Industry,Affiliation,Current_Value,Movement,id", "portfolio","id","id=".$id);
0
 

Author Comment

by:MsKrissy
ID: 35086025
"portfolio",,"id=".$id);

every time I put two commas in, I get the following error:

Parse error: syntax error, unexpected ',' in /home/content/html/mskrissy/xchange/scripts/my_portfolio.php on line 18
0
 

Author Comment

by:MsKrissy
ID: 35086065
No errors, but it doesn't return any records either.  But there are several records under this id.  I also did an echo out of the id to ensure the session_id is working and it's working.
0
 
LVL 1

Accepted Solution

by:
kavik379 earned 2000 total points
ID: 35086092
What about this:

$x->setQuery("Symbol,Full_Name,Industry,Affiliation,Current_Value,Movement,id", "portfolio","id","WHERE id=".$id);
0
 

Author Comment

by:MsKrissy
ID: 35086165
I get the following error when I tried that:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near WHERE id=3) ORDER BY `Current_Value` DESC LIMIT 0, 9 at line 1

it recognized the id, but it didn't like the SQL syntax we offered it in.
0
 

Author Comment

by:MsKrissy
ID: 35086310
We got it, thank you.  here is what we have:

I added this variable:
$where = "id=$id";

then I modified the code to this:
$x->setQuery("Symbol,Full_Name,Industry,Affiliation,Current_Value,Movement,id", "portfolio", "", "$where");
0
 
LVL 1

Expert Comment

by:kavik379
ID: 35086333
Nice. Glad it works now.
0
 

Author Closing Comment

by:MsKrissy
ID: 35086357
Thank you for the help, I will be submitting another question in like 5 minutes to figure out the LIMIT clause.  I wanted to separate the questions.

Thanks for everything.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article discusses how to create an extensible mechanism for linked drop downs.
This article discusses how to implement server side field validation and display customized error messages to the client.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

594 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