Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Location
    Walsall - UK
    Posts
    177

    PHP - combining 2 mySQL queries in one search + sort

    Hey again... I'm really stuck

    I have a search and a sort form.
    It searches bookings for cars... the booking table contains a foreign key "companyID" field that links the booking record with the record of the company - which contains the company name, and the contact name.

    I am searching and sorting as follows: http://www.eeeep.com/stuff/tt-serchproblem.jpg

    Problem is, when it searches the booking table .. it then needs to search the company details table to refine the users search.

    I am having a problem.. as my code gets and sorts details from the booking... but when I search in the company details, the search goes wrong and turns out like in the screenshot.

    My code is here:
    PHP Code:
    <?


    $sort 
    $_POST['sort'];




    $filter = array(); 

    if((
    $_POST['tyre_id'])) {
      
    $filter[] = "tyre_id='".$_POST['tyre_id']."'"; }

    if(
    count($filter) > 0) { 
      
    $filter_string "WHERE "implode(' AND '$filter); 
    } else { 
      
    $filter_string ""


    $company_contact_name=addslashes($company_contact_name);
    $company_name=addslashes($company_name);

    $query="SELECT * FROM tod_bookings $filter_string ORDER BY $sort ASC";

    $result=mysql_query($query);
    $num=mysql_num_rows($result);

    if (
    $num==0) {
    echo 
    "As of yet there are no bookings";
    } else {

    $i=0;
    while (
    $i $num) {

    $booking_id=mysql_result($result,$i,"booking_id");
    $company_id=mysql_result($result,$i,"company_id");
    $car_registration=mysql_result($result,$i,"car_registration");
    $date_in=mysql_result($result,$i,"date_in");


    /////// Check in company database
    $filter_string2='';

    $filter = array();

    if((
    $_POST['company_contact_name'])) {
      
    $filter2[] = "company_contact_name='".$_POST['company_contact_name']."'"; }

    if((
    $_POST['company_name'])) {
      
    $filter2[] = "company_name='".$_POST['company_name']."'"; }

    if(
    count($filter2) > 0) { 
      
    $filter_string2 "WHERE "implode(' AND '$filter2); 
    } else { 
      
    $filter_string2 ""



    $query2="SELECT * FROM tod_company_information $filter_string AND company_id='$company_id'";
    $result2=mysql_query($query2);
    $num2=mysql_num_rows($result2);


    $a=0;
    while (
    $a $num2) {

    $company_name=mysql_result($result2,$a,"company_name");
    $company_name=stripslashes($company_name);

    ++
    $a;
    }

    /////// Finish check



    ?>

        <tr>
        
        
                    <td><? echo "$date_in"?></td>
                    <td><b><? echo "$company_name"?></b></td>
                    <td><? echo "$car_registration"?></td>
                    <td><a href="page.php?page=booking-page-currentview&booking_id=<? echo "$booking_id"?>">Details</a> :: <a href="page.php?page=booking-page-done&booking_id=<? echo "$booking_id"?>">Done</a></td>
    </tr>
    <tr>
                    
                </tr>
                <tr>
                    
                </tr>
                    
    <?
    ++$i;
    }}

    mysql_close();
    ?>
    I would appreciate any help

    crE
    <<< Please see Forum Guidelines for signature setup. >>>

  2. #2
    Join Date
    Jun 2003
    Posts
    673
    Temporarily modify your code so it prints the queries before executing them; that way you will be able to see what is failing. Just glancing at your code, I can see that you have an SQL injection vulnerability from the tyre_id variable, and the $query2 variable is using $filter_string instead of $filter_string2. Furthermore, if $filter_string2 ends up being empty, $query2 will be missing its "WHERE" keyword.

  3. #3
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    34
    Do table join and you can search everything in one go. Here's roughly how to do it
    PHP Code:
    $query "SELECT * FROM tbl_booking b
        LEFT JOIN tbl_company c
        ON b.company_id = c.id
        
    $filter_string
        ORDER BY 
    $sort ASC"

  4. #4
    Join Date
    Aug 2003
    Location
    Walsall - UK
    Posts
    177
    Thanks dan... I didn't notice those

    Ok I think I'm almost there... But I have an IF syntax that's not behaving as it should - or at least I think it's not

    PHP Code:
    if(!$post_company_name||!$post_company_contact_name){ 
    Basically it's saying "If there arent any post variables in company_name or company_contact_name, then do this... else do this" .. pretty standard.

    What's happening is, even if there ARE something within those variables, it still insists on thinking there isn't, and just outputs whatever is underneath the IF clause. I've double checked there is something in the variables, as I've echoed them

    I've even tried:
    PHP Code:
    if($post_company_name=""||$post_company_contact_name=""){ 
    I really dont understand

    I would appreciate if you could shine any light on why this is happening, or slap me if im being stupid and not noticing something

    My full new code is here if you need it:

    PHP Code:
    <? 
    include('db.php'); 
    include(
    'cookie.php'); 

    ?>

    <span class="content"><b>&nbsp;Current Bookings<br><br></b></span>
        <form action="page.php?page=bookings-page-current" method="post" name="Sort">
            <table width="100%" border="0" cellspacing="2" cellpadding="2">
                <tr>
                    <td colspan="4" bgcolor="#868686"><font size="2" face="Arial,Helvetica,Geneva,Swiss,SunSans-Regular"><b>Search</b></font></td>
                </tr>
                <tr>
                    <td bgcolor="#a6a6a6"><font size="2" face="Arial,Helvetica,Geneva,Swiss,SunSans-Regular">Company:<br>
                            <input type="text" name="company_name" size="13" maxlength="100" border="0" class="textfield"></font></td>
                    <td bgcolor="#a6a6a6"><font size="2" face="Arial,Helvetica,Geneva,Swiss,SunSans-Regular">Contact Name:<br>
                            <input type="text" name="company_contact_name" size="13" maxlength="100" border="0" class="textfield"></font></td>
                    <td bgcolor="#a6a6a6"><font size="2" face="Arial,Helvetica,Geneva,Swiss,SunSans-Regular">Tyre:<br>
                        </font>
                        
    <select name="tyre_id" size="1" class="textbox" value="<? echo "$tyres_needed?>">
    <option value=""></option>
    <?

    // Get list of all tyres

    $query="SELECT * FROM tod_tyres";
    $result=mysql_query($query);
    $num=mysql_num_rows($result);

    if (
    $num==0) {
    echo 
    "As of yet there are no tyres in stock";
    } else {

    $i=0;
    while (
    $i $num) {

    $tyre_id=mysql_result($result,$i,"tyre_id");

    echo 
    "<option value=\"$tyre_id\">$tyre_id</option>";

                    
    ++
    $i;
    }}
    ?>

                                            </select>
                        
                        </td>
                    <td bgcolor="#a6a6a6"></td>
                </tr>
                <tr>
                    <td colspan="4" bgcolor="#868686"><font size="2" face="Arial,Helvetica,Geneva,Swiss,SunSans-Regular"><b>Sort</b></font></td>
                </tr>
                <tr>
                    <td bgcolor="#a6a6a6"><input type="radio" name="sort" value="date_in" checked border="0"><font size="2" face="Arial,Helvetica,Geneva,Swiss,SunSans-Regular">Date Fitted</font></td>
                    <td bgcolor="#a6a6a6"><input type="radio" name="sort" value="company_name" border="0"><font size="2" face="Arial,Helvetica,Geneva,Swiss,SunSans-Regular">Company</font></td>
                    <td bgcolor="#a6a6a6"><input type="radio" name="sort" value="car_registration" border="0"><font size="2" face="Arial,Helvetica,Geneva,Swiss,SunSans-Regular">Registration</font></td>
                    <td bgcolor="#a6a6a6"><input type="radio" name="sort" value="number_of_tyres_fitted" border="0"><font size="2" face="Arial,Helvetica,Geneva,Swiss,SunSans-Regular">Tyres Fitted</font></td>
                </tr>
            </table>    <input type="submit" name="submitButtonName" value="Search and Sort" border="0" class="submit" style="width:100%"></form>
            

            <table width="100%" border="0" cellspacing="2" cellpadding="0" class="content">
            

                <tr>
                    <td><b>Date In<br><br></b></td>
                    <td><b>Company<br><br></b></td>
                    <td><b>Registration<br><br></b></td>
                    <td><b>More...<br><br></b></td>
                </tr>

            
    <?



    /////// Check in company database

    $sort $_POST['sort'];

    $post_company_name=$_POST['company_name'];
    $post_company_contact_name=$_POST['company_contact_name'];

    echo 
    "comp:$post_company_name contact:$post_company_contact_name";

    if(
    $post_company_name=""||$post_company_contact_name=""){

    $filter = array(); 

    if((
    $_POST['tyre_id'])) {
      
    $filter[] = "tyre_id='".$_POST['tyre_id']."'"; }

    if(
    count($filter) > 0) { 
      
    $filter_string "WHERE "implode(' AND '$filter); 
    } else { 
      
    $filter_string ""


        echo 
    "$filter_string hey hey heySORT";

    $company_contact_name=addslashes($company_contact_name);
    $company_name=addslashes($company_name);

    $query="SELECT * FROM tod_bookings $filter_string ORDER BY $sort ASC";

    $result=mysql_query($query);
    $num=mysql_num_rows($result);

    if (
    $num==0) {
    echo 
    "As of yet there are no bookings";
    } else {

    $i=0;
    while (
    $i $num) {

    $booking_id=mysql_result($result,$i,"booking_id");
    $company_id=mysql_result($result,$i,"company_id");
    $car_registration=mysql_result($result,$i,"car_registration");
    $date_in=mysql_result($result,$i,"date_in");
        
    $query2="SELECT * FROM tod_company_information WHERE company_id='$company_id'";
    $result2=mysql_query($query2);
    $num2=mysql_num_rows($result2);

    $a=0;
    while (
    $a $num2) {

    $company_name=mysql_result($result2,$a,"company_name");
    $company_contact_name=mysql_result($result2,$a,"company_contact_name");


    ++
    $a;
    }


        
    /////// Finish check



    ?>

        <tr>
        
        
                    <td><? echo "$date_in"?></td>
                    <td><b><? echo "$company_name"?></b></td>
                    <td><? echo "$car_registration"?></td>
                    <td><a href="page.php?page=booking-page-currentview&booking_id=<? echo "$booking_id"?>">Details</a> :: <a href="page.php?page=booking-page-done&booking_id=<? echo "$booking_id"?>">Done</a></td>
    </tr>
    <tr>
                    
                </tr>
                <tr>
                    
                </tr>
                    
    <?
        
        
        
    ++$i;
    }
    }

    }else{


    $filter = array();

    if(
    $_POST['company_contact_name']) {
      
    $filter2[] = "company_contact_name='".$_POST['company_contact_name']."'"; }

    if(
    $_POST['company_name']) {
      
    $filter2[] = "company_name='".$_POST['company_name']."'"; }

    if(
    count($filter2) > 0) { 
      
    $filter_string2 "WHERE "implode(' AND '$filter2); 
    } else { 
      
    $filter_string2 ""


        echo 
    "$filter_string2 hey hey hey";

    $query2="SELECT * FROM tod_company_information $filter_string2";
    $result2=mysql_query($query2);
    $num2=mysql_num_rows($result2);


    $a=0;
    while (
    $a $num2) {

    $company_name=mysql_result($result2,$a,"company_name");

    $company_contact_name=mysql_result($result2,$a,"company_contact_name");

    ++
    $a;
    }

        
        
    /////// Finish check



    ?>

        <tr>
        
        
                    <td><? echo "$date_in"?></td>
                    <td><b><? echo "$company_name"?></b></td>
                    <td><? echo "$car_registration"?></td>
                    <td><a href="page.php?page=booking-page-currentview&booking_id=<? echo "$booking_id"?>">Details</a> :: <a href="page.php?page=booking-page-done&booking_id=<? echo "$booking_id"?>">Done</a></td>
    </tr>
    <tr>
                    
                </tr>
                <tr>
                    
                </tr>
                    
    <?
        
        
    }
        

    mysql_close();
    ?>
                        <tr>
                    
                </tr>
                <tr>
                    
                </tr></table>
    I really apprecite your help

    crE
    <<< Please see Forum Guidelines for signature setup. >>>

  5. #5
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    34
    First of all, you miss an '=' sign. It should be
    PHP Code:
    if($post_company_name=="" && $post_company_contact_name==""){ 
    Using one equal sign means you _assign_ empty string to the variables.

    Second, if I understand you correctly you should use && instead of ||. The code above reads "if there are neither company name nor contact name is given, then do this"

    Lastly, any reason why you don't use table join as I suggested above? If you first query returns several records, you will send a lot of queries ($query2) to the database and it takes resources. Unless I'm missing something here.

  6. #6
    Join Date
    Aug 2003
    Location
    Walsall - UK
    Posts
    177
    Ah, thanks oodie.... but it made no difference - the IF clause is still being an arse.

    I am using || because .. well, I just am - that bit is right

    I tried to avoid that as I really dont understand what it means or how to adapt it ... I hoped you would not notice I missed it lol

    crE
    <<< Please see Forum Guidelines for signature setup. >>>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •