[PHP-DB] [SOLVED]Re: [PHP-DB] Search results page not working with table joins
--- Stuart Felenstein [EMAIL PROTECTED] wrote:
I have a search page and a results page. It's a
dynamic search. It works great with just the main
table, but as soon as I add in the table joins (to
translate the codes) it just dumps out all the
records
regardless of criteria.
Here is the script without the joins:
?php
$currentPage = $_SERVER[PHP_SELF];
if (isset($HTTP_GET_VARS['AdvSearch']) == false ||
$HTTP_GET_VARS['AdvSearch']==)
exit();
$hasSQLWhereClause=false;
$whereClause=;
$MM_whereConst= ;
$fieldValue = $HTTP_GET_VARS['PostStart'];
if(isset($HTTP_GET_VARS['PostStart'])
$HTTP_GET_VARS['PostStart'] != )
{
if($hasSQLWhereClause == false)
{
$hasSQLWhereClause=true;
$whereClause=$whereClause.
rtrim(ltrim(upper(PostStart))) =
rtrim(ltrim(upper($fieldValue)));
}
else
{
$whereClause=$whereClause. and
rtrim(ltrim(upper(PostStart))) =
rtrim(ltrim(upper($fieldValue)));
}
}
$fieldValue = $HTTP_GET_VARS['JobTitle'];
if(isset($HTTP_GET_VARS['JobTitle'])
$HTTP_GET_VARS['JobTitle']!= )
{
if($hasSQLWhereClause == false)
{
$hasSQLWhereClause=true;
$whereClause=$whereClause.
rtrim(ltrim(upper(JobTitle))) like
rtrim(ltrim(upper('%$fieldValue%')));
}
else
{
$whereClause=$whereClause. and
rtrim(ltrim(upper(JobTitle))) like
rtrim(ltrim(upper('%$fieldValue%')));
}
}
if(isset($HTTP_GET_VARS['Industry2']))
{
$colValue = $HTTP_GET_VARS['Industry2'];
$orCtr = 1;
if ($hasSQLWhereClause == false)
{
$hasSQLWhereClause=true;
$whereClause=$whereClause.(;
foreach($colValue as $fieldValue)
{
$whereClause=$whereClause.rtrim(ltrim(upper(Industry)))
= rtrim(ltrim(upper('$fieldValue')));
if($orCtr count($colValue))
{
$whereClause=$whereClause. or ;
}
$orCtr++;
}
$whereClause=$whereClause.);
}
else
{
$whereClause=$whereClause. and (;
foreach($colValue as $fieldValue)
{
$whereClause=$whereClause.rtrim(ltrim(upper(Industry)))
= rtrim(ltrim(upper('$fieldValue')));
if($orCtr count($colValue))
{
$whereClause=$whereClause. or ;
}
$orCtr++;
}
$whereClause=$whereClause.);
}
}
$fieldValue = $HTTP_GET_VARS['LocationCity'];
if(isset($HTTP_GET_VARS['LocationCity'])
$HTTP_GET_VARS['LocationCity']!= )
{
if($hasSQLWhereClause == false)
{
$hasSQLWhereClause=true;
$whereClause=$whereClause.
rtrim(ltrim(upper(LocationCity))) like
rtrim(ltrim(upper('%$fieldValue%')));
}
else
{
$whereClause=$whereClause. and
rtrim(ltrim(upper(LocationCity))) like
rtrim(ltrim(upper('%$fieldValue%')));
}
}
if(isset($HTTP_GET_VARS['LocationState2']))
{
$colValue = $HTTP_GET_VARS['LocationState2'];
$orCtr = 1;
if ($hasSQLWhereClause == false)
{
$hasSQLWhereClause=true;
$whereClause=$whereClause.(;
foreach($colValue as $fieldValue)
{
$whereClause=$whereClause.rtrim(ltrim(upper(LocationState)))
= rtrim(ltrim(upper('$fieldValue')));
if($orCtr count($colValue))
{
$whereClause=$whereClause. or ;
}
$orCtr++;
}
$whereClause=$whereClause.);
}
else
{
$whereClause=$whereClause. and (;
foreach($colValue as $fieldValue)
{
$whereClause=$whereClause.rtrim(ltrim(upper(LocationState)))
= rtrim(ltrim(upper('$fieldValue')));
if($orCtr count($colValue))
{
$whereClause=$whereClause. or ;
}
$orCtr++;
}
$whereClause=$whereClause.);
}
}
if(isset($HTTP_GET_VARS['TaxTerm2']))
{
$colValue = $HTTP_GET_VARS['TaxTerm2'];
$orCtr = 1;
if ($hasSQLWhereClause == false)
{
$hasSQLWhereClause=true;
$whereClause=$whereClause.(;
foreach($colValue as $fieldValue)
{
$whereClause=$whereClause.rtrim(ltrim(upper(TaxTerm)))
= rtrim(ltrim(upper('$fieldValue')));
if($orCtr count($colValue))
{
$whereClause=$whereClause. or ;
}
$orCtr++;
}
$whereClause=$whereClause.);
}
else
{
$whereClause=$whereClause. and (;
foreach($colValue as $fieldValue)
{
$whereClause=$whereClause.rtrim(ltrim(upper(TaxTerm)))
= rtrim(ltrim(upper('$fieldValue')));
if($orCtr count($colValue))
{
$whereClause=$whereClause. or ;
}
$orCtr++;
}
$whereClause=$whereClause.);
}
}
if($whereClause != )
$MM_whereConst=WHERE;
$maxRows_RecordSet1 = 10;
$pageNum_RecordSet1 = 0;
if (isset($HTTP_GET_VARS['pageNum_RecordSet1'])) {
$pageNum_RecordSet1 =
$HTTP_GET_VARS['pageNum_RecordSet1'];
}
$startRow_RecordSet1 = $pageNum_RecordSet1 *
$maxRows_RecordSet1;
mysql_select_db($, $link);
$query_RecordSet1 = SELECT * FROM VendorJobs
$MM_whereConst $whereClause order by PostStart
desc;
===
Re: [PHP-DB] Search results of a previous query?
You can concat to the 1º filter statement the 2º. ex: SELECT * FROM atable WHERE (fieldname = fieldvalue) AND (fieldname2 = fieldvalue2) For the user to search twice in the same field i think you have to use the LIKE operator instead of the = and use the % in the string as wildcard. ex: 1º Search: Name = %Carlos%// Will return all Carlos 2º Search: Name = %Triana%// Will return everybody that have Carlos and Triana in any part of their name if used a sql like the one above (with AND). This could be done how many times you want with only one field or several. Hope this Hepls. - Original Message - From: Lic. Carlos A. Triana Torres [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, February 18, 2004 1:36 PM Subject: [PHP-DB] Search results of a previous query? Hi all, I am trying to build a script to make nested searches in a table. I have a form compossed of a text field and a select dropdown menu with all the fields in the table. The user can write in the text box and then select the field in which to perform the search. This works fine so far...now, I want to show the results of the query, but give the user the possibility of searching again BUT only make the search with the results shown, not the entire tablecan anyone give me any ideas of how to do this last part? I tried passing the new queries in the url...but this won't give the user the possibility to search twice in the same field... Any ideas? Thanks before hand. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Search results
On Thursday 07 February 2002 02:16, Todd Williamsen wrote:
I have done a search page based on a specific column name, but in some
columns there is no results to be returned..
What I would like to do is display an error message stating that there is
no results .. something like Sorry your search returned Zero Rows
=
Here is my code:
?
$connection = @mysql_connect($databaseserver, $databaseuser, $databasepass)
or die(Can't connect to DB);
$db = @mysql_select_db($databasename, $connection) or die(could not select
DB);
$sql = SELECT id, FirstName, LastName FROM Canidate WHERE Industry =
\$Industry\;
$result = @mysql_query($sql, $connection) or die(could not execute
query);
$contact_list = ul;
while ($row = mysql_fetch_array($result)) {
$FOUND_SOMETHING = 1;
$id = $row[id];
$FirstName = $row[FirstName];
$LastName = $row[LastName];
$contact_list .=lia href=\show_can.php?id=$id\$LastName,
$FirstName/a;
}
$contact_list .=/ul;
$msg = font face=\arial\p align=\center\Sorry, Your Search Results
Returned Zero Records/p/font;
?
html
head
titleYour Search Results/title
meta http-equiv=Content-Type content=text/html; charset=iso-8859-1
/head
body
p align=centerfont face=Arial, Helvetica, sans-serifuYour Search
Results
for b quot;
? echo $Industry; ?
quot; /b/u/font/p
== this is the code block that is stumping me
?
if ($FOUND_SOMETHING) {
echo $contact_list; }
else {
echo $msg;
}
?
p align=centera href=admin.htmfont face=Arial, Helvetica,
sans-serifGo
Back to Main Menu/font/a/P
/body
==
hth
--
Jason Wong - Gremlins Associates - www.gremlins.com.hk
/*
Lonely is a man without love.
-- Englebert Humperdinck
*/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Search results
On Wed, 6 Feb 2002, Todd Williamsen wrote: that doesn't work Bit terse? What do you get? Error? Nothing? -- Jason Wong -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Search results
On Wed, 6 Feb 2002, Todd Williamsen wrote:
Please keep discussion on the list.
Doesn't execute the $msg variable. And it doesn't return any results:
Have you checked that your query does indeed return results?
I tried this:
?
if ($result == 0) {
echo $msg;
}
else {
echo $contact_list;
}
?
But that returns the $msg variable even if there is results
$result as defined in your code (see manual on mysql_query() for full
details) will be FALSE if your query does not get executed correctly. If
your query executes correctly $result will contain a link identifier.
However that doesn't necessarily mean that your query returned any
*results*.
Insert some echos in your while loop to see whether you have any results.
--
Jason Wong
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Search results
CHANGE:
?
if ($Industry != ) {
echo $msg;
} else {
echo $contact_list;
}
?
TO:
if( mysql_num_rows($result) 0 )
{
echo $contact_list;
}
else
{
echo $msg;
}
-Original Message-
From: Todd Williamsen [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, February 06, 2002 12:17 PM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] Search results
I have done a search page based on a specific column name, but in some
columns there is no results to be returned..
What I would like to do is display an error message stating that there is no
results .. something like Sorry your search returned Zero Rows
=
Here is my code:
?
$connection = @mysql_connect($databaseserver, $databaseuser, $databasepass)
or die(Can't connect to DB);
$db = @mysql_select_db($databasename, $connection) or die(could not select
DB);
$sql = SELECT id, FirstName, LastName FROM Canidate WHERE Industry =
\$Industry\;
$result = @mysql_query($sql, $connection) or die(could not execute query);
$contact_list = ul;
while ($row = mysql_fetch_array($result)) {
$id = $row[id];
$FirstName = $row[FirstName];
$LastName = $row[LastName];
$contact_list .=lia href=\show_can.php?id=$id\$LastName,
$FirstName/a;
}
$contact_list .=/ul;
$msg = font face=\arial\p align=\center\Sorry, Your Search Results
Returned Zero Records/p/font;
?
html
head
titleYour Search Results/title
meta http-equiv=Content-Type content=text/html; charset=iso-8859-1
/head
body
p align=centerfont face=Arial, Helvetica, sans-serifuYour Search
Results
for b quot;
? echo $Industry; ?
quot; /b/u/font/p
== this is the code block that is stumping me
?
if ($Industry != ) {
echo $msg;
} else {
echo $contact_list;
}
?
p align=centera href=admin.htmfont face=Arial, Helvetica,
sans-serifGo
Back to Main Menu/font/a/P
/body
==
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Search results
In article 001301c0bafa$34d15be0$[EMAIL PROTECTED],
[EMAIL PROTECTED] ("David Drummond") wrote:
Can anyone tell me what is the best way to implement displaying multiple
search results using next and previous buttons to cycle through all the
results. I see this method done on a lot of sites but don't know how to do
it in PHP.
"Limit" is how it's commonly done (though what's "best" really depends on
what you're doing). In mysql, it would be something like:
mysql_query("select foo from bar limit 5,10",$connect) //show ten records
from result set, starting with the sixth
I believe phpbuilder.com has a tutorial on this. If not them, then you
shouldn't have much trouble finding examples on one of the other PHP
tutorial sites.
--
CC
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP-DB] Search results
I've not seen the limit function, but it sounds quite uselfull. Whatever method,
you could
pass the first or last keys related to the current page and an indication as to
whether you
are going forwards or backwards - I suppose that in PHP it could all be done in
one file,
self-referential-like, as the "arrows" would somehow comprise a form.
Phil J.
CC Zona wrote:
In article 001301c0bafa$34d15be0$[EMAIL PROTECTED],
[EMAIL PROTECTED] ("David Drummond") wrote:
Can anyone tell me what is the best way to implement displaying multiple
search results using next and previous buttons to cycle through all the
results. I see this method done on a lot of sites but don't know how to do
it in PHP.
"Limit" is how it's commonly done (though what's "best" really depends on
what you're doing). In mysql, it would be something like:
mysql_query("select foo from bar limit 5,10",$connect) //show ten records
from result set, starting with the sixth
I believe phpbuilder.com has a tutorial on this. If not them, then you
shouldn't have much trouble finding examples on one of the other PHP
tutorial sites.
--
CC
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
