Dear all,
I am making a search algo that will search the database upon provided inputs
There are 5 inputs
1.keywork :- which is textfield and name is keyword
2.course : which is a drop down and name is course
3.level : which is a drop down and name is level
4.country : which is a drop down and name is country
5.university : which is a drop down and name is university
when user inters anything in the keyword field then it shd search in the rest 2-5 field correspinding in the dabase base and if user inputed any string in the keyword field and chose anything from rest search parameters then is should refine the search accordingly.
what is happeneing with my code is when user inters anything in keyword field it is searing correct but when user inters anything in keyword and rest 4 field then it is not redefining search.
i am giving the code below
$selectors = array(
"keyword" => "tbluniv_degree like '%$_POST[keyword]%' or tbluniv_branch_name like '%$_POST[keyword]%' or tbluniv_country like '%$_POST[keyword]%' or tbluniv_level like '%$_POST[keyword]%' or tbluniv_uni_name like '%$_POST[keyword]%'",
"course" => "tbluniv_branch_name = '$_POST[course]'",
"country" => "tbluniv_country = '$_POST[country]'",
"level" => "tbluniv_level = '$_POST[level]'",
"university" => "tbluniv_uni_name like'%$_POST[university]%'"
);
$cwant = array();
foreach (array_keys($selectors) as $cdi) {
if ($_POST[$cdi]) {
array_push($cwant, $selectors[$cdi]);
}
}
if ($cwant) {
$restrict = implode (" and ",$cwant);
$choose = 1;
} else {
$choose = 0;
}
if($choose)
{
echo $sql="select distinct(university_username),tbluniv_id,tbluniv_uni_name ,tbluniv_degree,tbluniv_med_inst, tbluniv_beg_prog,tbluniv_prog_dur ,tbluniv_app_deadl from tbluniv_courses_detail WHERE $restrict";
$res=mysql_query($sql);
}
else
{
echo $sql="select distinct(university_username),tbluniv_id,tbluniv_uni_name ,tbluniv_degree,tbluniv_med_inst, tbluniv_beg_prog,tbluniv_prog_dur ,tbluniv_app_deadl from tbluniv_courses_detail WHERE 1=2";
$res=mysql_query($sql);
}
any help wll be apreciated
thanks and regards
jawed
search query needed
Moderator: General Moderators
-
jawedshamshedi
- Forum Commoner
- Posts: 35
- Joined: Fri May 16, 2008 1:17 am
- Location: India
- Contact:
Re: search query needed
My advice would be to echo the query you're trying to preform and see what it's really putting together before it executes.
-
jawedshamshedi
- Forum Commoner
- Posts: 35
- Joined: Fri May 16, 2008 1:17 am
- Location: India
- Contact:
Re: search query needed
hi jade
thanks for the reply, i already did it,
the query is working fine as the keyword part is searching in 'or' condition for 3 fields, and when user selects any option from 2-5 and anything inputted from keyword then the search shd redefine itself accordingl, but it is not doing soo, by the way thanks for the response...
thanks for the reply, i already did it,
the query is working fine as the keyword part is searching in 'or' condition for 3 fields, and when user selects any option from 2-5 and anything inputted from keyword then the search shd redefine itself accordingl, but it is not doing soo, by the way thanks for the response...
Re: search query needed
I know what the problem is that you're having. Your logic is flawed with your OR statement.
When you use an or statement in SQL you have to include all the previous and statements first. For instance:
Every time you use an OR statement you need to include any other and statements before it. If you didn't put the previous statements before it then it would be like trying to do something like this in php:
Here you can easily see that the logic is flawed. Once you use the or statement, you're no longer taking into account that the $name is john and the id 10. To fix this you'd have to do something like:
I suggest you sit down and write out what you really want your query to do. Here's a refined search script I wrote myself:
When you use an or statement in SQL you have to include all the previous and statements first. For instance:
Code: Select all
SELECT name FROM members WHERE name like '%john%' and id='$id' OR name like '%john%' and dob='1998'
Code: Select all
if ($name = "john" and $id='10' or $dob = '1998')
{
//do something
}
Code: Select all
<?php
if (($name = "john" and $id='10') or ($name="john" and $dob = "1998"))
{
//do something
}
Code: Select all
/*****
*Purpose: search for shop information
*Precondition: none
*Postcondition: returns all valid shop information
****/
function search($shop, $car, $reqmaintance, $zip, $page)
{
echo "<br><center><img src=\"images/shop.JPG\" align=\"middle\" /><b>Search Results</b></center><br/><br/>";
$flag = 0;
//JOIN EVERYTHING TOGETHER FIRST
$query = "SELECT DISTINCT name, address, zip, shop.id FROM shop ";
if ($car || $reqmaintance)
$query .= "JOIN shopvisit ON shopid = shop.id JOIN maintancedone ON shopvisitid = shopvisit.id JOIN vehicle ON vehicleid=vehicle.id JOIN vehicletype ON vehicletypeid = vehicletype.id ";
if (!$page) //make sure there's a default page
$page = 1;
if ($shop)
{
$query .= "WHERE name LIKE '%$shop%' ";
$flag = 1;
}
if ($zip && !$flag)
{
$subzip = substr($zip, 0, 3);
$query .= "WHERE zip LIKE '$subzip%' ";
$flag = 1;
}
else if ($zip)
{
$subzip = substr($zip, 0, 3);
$query .= "AND zip LIKE '$subzip%' ";
}
//figure out which car this is
$cardata = new vehicletypeobj($car);
//find the make and model of the car
$newmake = $cardata->make;
$newmodel = $cardata->model;
if ($car && !$flag)
{
$query .= "WHERE make='$newmake' AND model='$newmodel' ";
$flag = 1;
}
else if ($car)
$query .= "AND make='$newmake' AND model='$newmodel' ";
if ($reqmaintance && !$flag)
{
$query .= "WHERE reqmaintanceid='$reqmaintance' ";
$flag = 1;
}
else if ($reqmaintance)
$query .= "AND reqmaintanceid='$reqmaintance' ";
$shownext = $page * 25;
$show = $shownext - 25;
//calculate the total results for the entire query
$total = mysql_query($query) or die ('error: ' . mysql_error());
$totalresults = mysql_num_rows($total);
//show only the current 25 results
$query .= " ORDER BY name ASC LIMIT $show, $shownext";
$loop = mysql_query($query);
while($row = mysql_fetch_array($loop))
{
echo "<a href=shop.php?id=" . $row['id'] . "><b>" . stripslashes($row['name']) . "</b></a> " . $row['address'] . ", " .
$row['zip'] . "<br /><br />";
}
echo "<center>" . $totalresults . " results found </center>";
$prevpage = $page - 1;
$nextpage = $page + 1;
if ($totalresults > 25)
if ($page != 1) //there is a previous page
echo "<p align=right><a href=?shop=$shop&car=$car&zip=$zip&reqmaintance=$reqmaintance&&page=$prevpage&get=1 class=link>
« Prev</a> |
<a href=?shop=$shop&car=$car&zip=$zip&reqmaintance=$reqmaintance&&page=$nextpage&get=1>Next »</a></p>";
else
echo "<p align=right><a href=\"?shop=$shop&car=$car&zip=$zip&reqmaintance=$reqmaintance&page=$nextpage&get=1>Next »</a></p>";
}
?>
-
jawedshamshedi
- Forum Commoner
- Posts: 35
- Joined: Fri May 16, 2008 1:17 am
- Location: India
- Contact:
Re: search query needed
Thanks Jade,
I got the logic what ur trying to say,
Thanks a alot.
I got the logic what ur trying to say,
Thanks a alot.