Ajax AutoComplete Support Product Page

Where clause ... Search database .. but with WHERE clause...

Shared 02 Dec 2010 16:28:29
1
likes this idea
02 Dec 2010 16:28:29 reinhardt ellis posted:
It would be great to have a conditional option..

so if I search for something in a database it should bring up only the value in the "where" = input value..

Say i have 30 countries.. it can search all the counties as it does at the moment.. but if i have another field "active" then i would like to have the option of a where clause in the extention.. so it search the counties fields but with a conditional region..

i hope this makes sense...

Replies

Replied 09 Dec 2010 20:53:58
09 Dec 2010 20:53:58 C Brown replied:
You can add this into the SQL statment on the autopage-[your page name].asp page

From:
Sql = Sql & sPrimary & sOptional & " FROM " & sTable & " WHERE " & sLike
To:
Sql = Sql & sPrimary & sOptional & " FROM " & sTable & " WHERE Active = 1 and " & sLike

Replied 20 Dec 2010 18:53:51
20 Dec 2010 18:53:51 reinhardt ellis replied:
Hi thanks for that..



<?php
error_reporting(0);
set_error_handler("userErrorHandler");

// user defined error handling function
function userErrorHandler($errno, $errmsg, $filename, $linenum, $vars)
{
	header("HTTP/1.0 500 Internal Error", true, 500);
	echo "<br />$errmsg in <b>$filename</b> on line <b>$linenum</b><br />";
}

require_once('Connections/cpha2010.php');
mysql_select_db($database_cpha2010);

//--jszone (DO NOT REMOVE THIS)

$table='members';
$fields = array('memberCoName:primary','memberPProvince:optional');
//--jszone (DO NOT REMOVE THIS)

function convert( $val )
{
	list($field,) = explode(':',$val);
	return $field;
}



$fields = implode(',',array_map('convert',$fields));

//--
$sql = 'SELECT '.$fields.' FROM `'.$table.'`'; 

$mc = isset($_GET['mc'])?true:false;
$sa = isset($_GET['sa'])?true:false;

if (!empty($_GET['q'])) 
{
	$f = explode(",", $fields);
	
	$sql .= ' WHERE memberActive = Active AND '.$f[0].' LIKE "';
//--	$sql .= ' WHERE '.$f[0].' LIKE "';
	
	if (true === $mc) $sql .= "%";
	
	$sql .= mysql_real_escape_string($_GET['q']).'%"';
	
	if (true === $sa)
	{
		$fields = explode(',',$fields);
		
		$opt = array_slice($fields,1,count($fields));
		
		foreach ($opt as $key => $value)
		{
			if ($mc === true)
			{
				$sql .= ' OR "'.$value.'" LIKE "%'.mysql_real_escape_string($_GET['q']).'%"';
			}
			else 
			{
				$sql .= ' OR "'.$value.'" LIKE "'.mysql_real_escape_string($_GET['q']).'%"';
			}
		}	
	}
}

if (isset($_GET['limit']) && is_int($_GET['limit']))
{
	$sql .= ' LIMIT '.$_GET['limit'];
}

$res = mysql_query($sql);

header('Content-type: text/plain');
while ($row = mysql_fetch_array($res, MYSQL_NUM))
{
	echo join("|", $row)."\n";
}
?>




i have replaced the code


//--	$sql .= ' WHERE '.$f[0].' LIKE "';


with

$sql .= ' WHERE memberActive = Active AND '.$f[0].' LIKE "';


but then i get an error

autocomplete-search_members-php-1.php on line 79





Replied 20 Dec 2010 19:03:22
20 Dec 2010 19:03:22 reinhardt ellis replied:
I have even tried this

at line 32.. to select "memberActive" field but with no luck


$sql = 'SELECT '.$fields.',memberActive FROM `'.$table.'`'; 

Replied 22 Dec 2010 10:53:17
22 Dec 2010 10:53:17 Miroslav Zografski replied:
Hello Reinhard,

try setting the memberActive in `` quotes.
Or pass here the error.

Regards,
Replied 23 Dec 2010 08:51:58
23 Dec 2010 08:51:58 reinhardt ellis replied:
Hi... Miro

This is what i have now...


$sql = 'SELECT '.$fields.', `memberActive` FROM `'.$table.'`'; 

$mc = isset($_GET['mc'])?true:false;
$sa = isset($_GET['sa'])?true:false;

if (!empty($_GET['q'])) 
{
	$f = explode(",", $fields);
	
	$sql .= ' WHERE `memberActive` = Active AND '.$f[0].' LIKE "';



and then i get the error

mysql_fetch_array(): supplied argument is not a valid MySQL result resource in E:\wamp\www\cpha\autocomplete-search_members-php-1.php on line 76


Then the error comes up here somewhere...

$res = mysql_query($sql);

header('Content-type: text/plain');
while ($row = mysql_fetch_array($res, MYSQL_NUM))
{
	echo join("|", $row)."\n";
}



but it must be because of the top sql not being correct.??

Replied 13 Jan 2011 15:06:05
13 Jan 2011 15:06:05 Miroslav Zografski replied:
Hello all,

A follow up from a chat with Reinhardt:

the line:
 $sql .= ' WHERE `memberActive` = '.$active.' AND '.$f[0].' LIKE "';

does not provide a correct escape for the value that comes from $active variable. Thus some errors are thrown.
Instead use :

$sql .= ' WHERE `memberActive` = "'.$active.'" AND '.$f[0].' LIKE "';


Regards,
Replied 13 Apr 2011 22:17:37
13 Apr 2011 22:17:37 P R replied:
QuoteYou can add this into the SQL statment on the autopage-[your page name].asp page

From:
Sql = Sql & sPrimary & sOptional & " FROM " & sTable & " WHERE " & sLike
To:
Sql = Sql & sPrimary & sOptional & " FROM " & sTable & " WHERE Active = 1 and " & sLike



is it possible to search for a session variable instead of the static "1" ?

is there a workarround?

Reply to this topic