Searching Specific Fields (and)

[t5amec]

I know this is in no way the correct coding, but it gets my point across.

I am building a data base for bands to post their gigs so if someone feels like they wanna go see a gig they can just search for one in their home town, county etc.

They can search by:
Date
Venue
Band Name
Style
Country
County

No field would be "required" but of course the more fields you fill in, the better.

display all results of exact match to $gigDay,$gigMonth,$gigYear from gigFinder. if $gigDay,$gigMonth,$gigYear="" move on.
(and)
display all results of $gigVenue from gigFinder. if $gigVenue="" move on;
(and)
display all results of $gigBandName from gigFinder. if $gigBandName="" move on;
(and)
display all results of $gigPlay from gigFinder. if $gigPlay="" move on.
(and)
display all results of $gigCountry from gigFinder. if $gigCountry="" move on.
(and)
display all results of $gigCounty from gigFinder. if $gigCounty="" move on.

if all fields="" display all records

There must be a way of searching like this as it is used in this very forum, and many other websites, but I can't find the coding anywhere.

Please help....!!!

Make Sense? I hope so (-:

 

[jpadie]

couldn't you construct the 'where' clause by an analysis of the incoming form variables?
e.g. a single database query (rather than creating multiple queries on cascading temporary tables).

 

[t5amec]

Something like this? Have tried to find some Documentation on the WHERE clause on PHP.net, but can't find a thing...

SELECT * 
FROM gigFinder 
WHERE 'gigDay','gigMonth','gigYear' = '$gigDay','$gigMonth',$gigYear'
AND 'gigVenue' = '$gigVenue' 
AND 'gigBandName' = '$gigBandName'
AND 'gigPlay' = '$gigPlay'
AND 'gigCountry' = '$gigCountry'
AND 'gigCounty' = '$gigCounty'
ORDER BY 'gigDay','gigMonth','gigYear' ASCENDING;

Make Sense? I hope so (-:

 

[jpadie]

something like that indeed. i'm not sure that the first line of your where clause works. it's not the syntax that i use anyway (which is field = value and/or field=value etc).

 

[t5amec]

i have seperate fields for day, month and year and searching for an exact date is a must, so it will have to combine these three fields...

Make Sense? I hope so (-:

 

[jpadie]

then combine them with "And". Honestly it may be that your syntax works - i have never used it as i prefer the consistency approach.

'gigDay' = '$gigDay',
AND 'gigMonth' = '$gigMonth'
AND 'gigYear' = '$gigYear'

the above would still provide an exact date match.

 

[t5amec]

but if someone was to leave the field blank, what would happen?

Make Sense? I hope so (-:

 

[jpadie]

you would validate the incoming string before using it within the query.

you should validate anyway (as a matter of good practice) so that you minimise the impact of any sql injection attack.

another approach would be to restrict entries into this field by using a drop down box or a calendar class etc.

 

[Chacalinc]

Have tried to find some Documentation on the WHERE clause on PHP.net, but can't find a thing...

you will NOT find info about the WHERE cluse because that isn't PHP, it is MySQL.

the WHERE cluse must be create according to the field filled, so:

$where="";
if $_POST['$gigDay']!="" then $where="gigday=$_POST['$gigDay']"
if $_POST['$gigMonth']!="" then $where="AND gigmonth=$_POST['$gigMonth']"

...

$sql="Select somthing from table WHERE ".$where

hope this help.

 

[Chacalinc]

oops... the $where should add the strings:
if $_POST['$gigMonth']!="" then $where.="AND gigmonth=$_POST['$gigMonth']"

note the . in $where.="AND gigmonth=$_POST['$gigMonth']

 

[t5amec]

ok, so i will show you these two pages.

http://members.lycos.co.uk/themalloys/beheard/viewGigs.php

^ this page is just a general view of the example gigs i have in my database

http://members.lycos.co.uk/themalloys/beheard/searchGig.html

^ this page is where the search happens. at the moment, it only finds the record with exact matching fields to all fields. I want it to be able to search for fields only if they have been filled in.


Make Sense? I hope so (-:

 

[jpadie]

Hi

to test this code add a name of "Submit" to your submit button in the form. the code tests this value.

put this code in the page of the form's action.

<?
if (isset($_POST['Submit']))
{
	print_r($_POST);
	echo "<br><br>";
	buildsqlstring();
}

function buildsqlstring()
{
//arrived here because the form button has been pressed

//incoming wanted variables in the $_POST array are 
/*
gigBandName
gigPlay
gigDay
gigMonth
gigYear
gigVenue
gigCounty
gigCountry
*/

//check the POST array for empty strings and build the where code at the same timee
extract ($_POST);

if (!empty($gigBandName))
{
	$where[] = "`gigBandName`='". mysql_escape_string ($gigBandName) ."'";
}
if (!empty($gigPlay))
{
	$where[] = "`gigPlay`='" . mysql_escape_string($gigPlay) ."'";
}
if (!empty($gigVenue))
{
	$where[] = "`gigVenue`='".mysql_escape_string($gigVenue)."'";
}
if (!empty($gigCounty))
{
	$where[] = "`gigCounty`='".mysql_escape_string($gigCounty)."'";
}
if (!empty($gigCountry))
{
	$where[] = "`gigCountry`='".mysql_escape_string($gigCountry)."'";
}

//now check the dates
if (empty($gigDay) || empty($gigMonth) || empty($gigYear))
{
	//do nothing as there is a blank
}
else
{
	//check that the date is valid
	$gigDate = $gigYear."-".$gigMonth."-".$gigDay;
	if (strtotime($gigDate) === -1)
	{
		//do nothing because date is bogus - i.e. 30 February, 2003
	}
	else
	{
		//either
		$where[] = "`gigDay`='$gigDay' AND `gigMonth` = '$gigMonth' AND `gigYear`='$gigYear'";
	}
}

//now assemble the where code
$realwhere = " where ";
$cnt =count($where);
for ($i=0; $i<$cnt; $i++)
{
	if($i != ($cnt-1)) //this means it is not the last value
	{
		$realwhere .= $where[$i] . " AND ";
	}
	else
	{
		//this is the last value
		$realwhere .= $where[$i];
	}
}

echo $realwhere;
}
?>

sorry it's a bit long. there are shorter ways to get the same result but it would have been less easy to understand.

hth
Justin

 

[t5amec]

I have changed the coding to as above.

If I put in a country, for example, i get this quote:

Array ( [gigBandName] => [gigPlay] => [gigDay] => [gigMonth] => [gigYear] => [gigVenue] => [gigCounty] => [gigCountry] => UK [Submit] => Search )

where `gigCountry`='UK'

if this isn't making sense. try it for yourself..

http://members.lycos.co.uk/themalloys/beheard/searchGig.html

Make Sense? I hope so (-:

 

[jpadie]

that looks as though it works then!

the script was to evidence how to build a where string. clearly for you to use it would would not bother printing the variable nor echoing the where string.

instead
1. change the echo in the last line to a "return "
2. delete the print_r call and the echo lines at the beginning
3. add the function code to your current script on Viewgig.php and change the last line from echo $realwhere to return $realwhere.
4. replace the current line in your code for building the sql query with

$sql = "Select * from gigFinder ". buildsqlstring() . "  ORDER BY 'gigDay','gigMonth','gigYear' ASCENDING;

and all should work (I'm guessing at your viewgig.php code so more tweaks may be needed).

one thing to note: my code assumes there will always be some criterion passed in. to check for this add the following

if ($cnt === 0)
{$realwhere = "";}

just prior to the return call.

 

[t5amec]

The code is taking me to the correct searchGig.php page but there are no results within the table.

Here is the coding for the whole page..

<?
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database"); 
$query="Select * from gigFinder ". buildsqlstring() . "  ORDER BY 'gigDay','gigMonth','gigYear' ASCENDING";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

echo "";

?> 
<?
if (isset($_POST['Submit']))
{
    buildsqlstring();
}

function buildsqlstring()
{
//arrived here because the form button has been pressed

//incoming wanted variables in the $_POST array are 
/*
gigBandName
gigPlay
gigDay
gigMonth
gigYear
gigVenue
gigCounty
gigCountry
*/

//check the POST array for empty strings and build the where code at the same time
extract ($_POST);

if (!empty($gigBandName))
{
    $where[] = "`gigBandName`='". mysql_escape_string ($gigBandName) ."'";
}
if (!empty($gigPlay))
{
    $where[] = "`gigPlay`='" . mysql_escape_string($gigPlay) ."'";
}
if (!empty($gigVenue))
{
    $where[] = "`gigVenue`='".mysql_escape_string($gigVenue)."'";
}
if (!empty($gigCounty))
{
    $where[] = "`gigCounty`='".mysql_escape_string($gigCounty)."'";
}
if (!empty($gigCountry))
{
    $where[] = "`gigCountry`='".mysql_escape_string($gigCountry)."'";
}

//now check the dates
if (empty($gigDay) || empty($gigMonth) || empty($gigYear))
{
    //do nothing as there is a blank
}
else
{
    //check that the date is valid
    $gigDate = $gigYear."-".$gigMonth."-".$gigDay;
    if (strtotime($gigDate) === -1)
    {
        //do nothing because date is bogus - i.e. 30 February, 2003
    }
    else
    {
        //either
        $where[] = "`gigDay`='$gigDay' AND `gigMonth` = '$gigMonth' AND `gigYear`='$gigYear'";
    }
}

//now assemble the where code
$realwhere = " where ";
$cnt =count($where);
for ($i=0; $i<$cnt; $i++)
{
    if($i != ($cnt-1)) //this means it is not the last value
    {
        $realwhere .= $where[$i] . " AND ";
    }
    else
    {
        //this is the last value
        $realwhere .= $where[$i];
    }
}
if ($cnt === 0)
{$realwhere = "";}

return $realwhere;
}
?>
<html>
<head>
<title>Gig Finder Search Results</title>
</head>
<body>
<b><font face="Arial, Helvetica, sans-serif">Gig Finder Search Results</font></b><br><br>
<table border="0" cellspacing="2" cellpadding="2">
<tr> 
<th><font face="Arial, Helvetica, sans-serif">Name</font></th>
<th><font face="Arial, Helvetica, sans-serif">Phone</font></th>
<th><font face="Arial, Helvetica, sans-serif">Mobile</font></th>
<th><font face="Arial, Helvetica, sans-serif">Fax</font></th>
<th><font face="Arial, Helvetica, sans-serif">E-mail</font></th>
<th><font face="Arial, Helvetica, sans-serif">Website</font></th>
<th><font face="Arial, Helvetica, sans-serif">Edit</font></th>
<th><font face="Arial, Helvetica, sans-serif">Delete</font></th>
</tr>


<?
$i=0;
while ($i < $num) {
$gigDay=mysql_result($result,$i,"gigDay");
$gigMonth=mysql_result($result,$i,"gigMonth");
$gigYear=mysql_result($result,$i,"gigYear");
$gigBandName=mysql_result($result,$i,"gigBandName");
$gigVenue=mysql_result($result,$i,"gigVenue");
$gigCountry=mysql_result($result,$i,"gigCountry");
$gigCountry=mysql_result($result,$i,"gigCountry"); 
$gigTown=mysql_result($result,$i,"gigTown"); 
$gigPlay=mysql_result($result,$i,"gigPlay"); 
$gigAdmission=mysql_result($result,$i,"gigAdmission"); 
$gigStart=mysql_result($result,$i,"gigStart"); 
$gigRestriction=mysql_result($result,$i,"gigRestriction"); 
$gigComment=mysql_result($result,$i,"gigComment");
$gigContactEmail=mysql_result($result,$i,"gigContactEmail"); 
$gigContactWeb=mysql_result($result,$i,"gigContactWeb"); 
$gigId=mysql_result($result,$i,"gigId"); 
?>


<tr> 
<td><font face="Arial, Helvetica, sans-serif"><? echo "$gigBandName"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$gigStart"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$gigDay"; ?>/<? echo "$gigMonth"; ?>/<? echo "$gigYear"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$fax"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><a href="mailto:<? echo "$gigContactEmail"; ?>">E-mail</a></font></td>
<td><font face="Arial, Helvetica, sans-serif"><a href="<? echo "$web"; ?>">Website</a></font></td>
<td><font face="Arial, Helvetica, sans-serif"><a href="[URL unfurl="true"]http://members.lycos.co.uk/themalloys/members_area/example/update.php?id=<?[/URL] echo "$gigId"; ?>">Edit</a></font></td>
<td><font face="Arial, Helvetica, sans-serif"><a href="[URL unfurl="true"]http://members.lycos.co.uk/themalloys/members_area/example/delete.php?id=<?[/URL] echo "$id"; ?>">Delete</a></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo "$gigId"; ?></font></td>
</tr>
<?
++$i;
} 
echo "</table>";


?>

if you can spot an error somewhere, please let me know...


Make Sense? I hope so (-:

 

[t5amec]

I think I found the problem.

Twas in the $query line. All i had to do was change ASCENDING to ASC

Silly when you think about it, really.

Thanks for your help jpadie and Chacalinc

Make Sense? I hope so (-:

 

[jpadie]

glad it works. btw mysql_numrows is deprecated. unless strictly necessary it is better to use mysql_num_rows(). in this case you are using it to control the for loop, which is more traditionally done with a while loop instead (see the tip below).

=====
a tip to make parsing db output easier: instead of writing your variables using the mysql_result syntax, you can do something like

$results=mysql_query($sql);
while ($row=mysql_fetch_assoc($results))
{
 extract ($row);
 //use the variables
}

=====

cheers
Justin

 

[t5amec]

Cheers again...

I have the coding sorted for the return page now... woob woob!

I am trying to get it so that on the search page, users can only search for bands, dates, genre's that are on the database rather than trying their luck by typing a band in.

I have tried this:

<select name="gigBandName"> 
<option></option> 
<option><? echo "$gigBandName"; ?></option> 
<? 
++$i; 
} 
echo "</select>"; 
?>

but it is only putting the first example band in the drop down and the second example bands name as text next to the drop down...
(

http://members.lycos.co.uk/themallo.../search4Gig.php

)

any ideas what I am doing wrong?


Make Sense? I hope so (-:

 

[jpadie]

your syntax is wrong.

try creating a function something like the first code snip:

function getoptions($field)
{
include("dbinfo.inc.php");
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database"); 
$query="Select $field as rslt from gigFinder group by $field order by $field asc";
$result=mysql_query($query);
$optionstr = "";
while($row=mysql_fetch_assoc($result))
{
  extract ($row);
  $optionstr .= "<option value='$rslt'>$rslt</option>\r\n"
}
return $optionstr;
} //end of function

then in your html code try the following

<select name="whatever">
<?=getoptions('gigBandName')?>
</select>

in this way the code is reusable for each of the parameters that you want to populate a drop down with.

i haven't tested this code so i hope i've got it right. let me know. i can load up an editor to test it if needed.

hth
Justin

 

[t5amec]

its coming up with Parse Error line 14.. :-S

Make Sense? I hope so (-: