search query needed

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jawedshamshedi
Forum Commoner
Posts: 35
Joined: Fri May 16, 2008 1:17 am
Location: India
Contact:

search query needed

Post by jawedshamshedi »

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
User avatar
Jade
Forum Regular
Posts: 908
Joined: Sun Dec 29, 2002 5:40 pm
Location: VA

Re: search query needed

Post by Jade »

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

Post by jawedshamshedi »

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...
User avatar
Jade
Forum Regular
Posts: 908
Joined: Sun Dec 29, 2002 5:40 pm
Location: VA

Re: search query needed

Post by Jade »

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:

Code: Select all

 
SELECT name FROM members WHERE name like '%john%' and id='$id' OR name like '%john%' and dob='1998'
 
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:

Code: Select all

 
if ($name = "john" and $id='10' or $dob = '1998')
{
//do something
}
 
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:

Code: Select all

 
<?php
 
if (($name = "john" and $id='10') or ($name="john" and $dob = "1998"))
{
//do something
}
 
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:

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>
                  &laquo; Prev</a> | 
                  <a href=?shop=$shop&car=$car&zip=$zip&reqmaintance=$reqmaintance&&page=$nextpage&get=1>Next &raquo;</a></p>";
        else
            echo "<p align=right><a href=\"?shop=$shop&car=$car&zip=$zip&reqmaintance=$reqmaintance&page=$nextpage&get=1>Next &raquo;</a></p>";
}
?>
 
jawedshamshedi
Forum Commoner
Posts: 35
Joined: Fri May 16, 2008 1:17 am
Location: India
Contact:

Re: search query needed

Post by jawedshamshedi »

Thanks Jade,
I got the logic what ur trying to say,
Thanks a alot.
Post Reply