SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict
    Join Date
    Dec 2011
    Posts
    221
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    WHERE and AND in condition

    Greetings!

    I have want to have a search query that will based on user preference.

    PHP Code:
    $avail $_POST['avail'];
    $sec $_POST['section'];

    $query "SELECT * ,b.b_Title as title, bac.id as bacid FROM tbl_book AS b
                          LEFT JOIN tbl_bdetail AS bd ON b.id = bd.b_Title
                          LEFT JOIN tbl_bacquisition AS bac ON bac.bd_Id = bd.id"
    ;

        if (
    count($avail)>0)
        {
    $query.="WHERE bac.ba_Status = '$avail'";}
            if (
    count($sec)>0)
        {
    $query.="WHERE b.section_Id = '$sec'";    } 
    What I am trying here is that when the user select the $sec option, I want to add the WHERE clause value in my $query, when the user select the $avail option I want to add the WHERE clause values.

    My problem is what if the user will select values among the two options? how can I make it happen. any help will do.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    start your WHERE clause with
    Code:
    WHERE 1=1
    then for each option selected by the user, append an AND condition

    if no options are selected, then 1=1 returns all rows

    if two options are selected, then the query looks like this --
    Code:
    WHERE 1=1
      AND bac.ba_Status = '$avail'
      AND b.section_Id = '$sec'
    please note, you should be using INNER JOINs not LEFT JOINs
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    Quote Originally Posted by claro View Post
    Greetings!

    I have want to have a search query that will based on user preference.

    PHP Code:
    $avail $_POST['avail'];
    $sec $_POST['section'];

    $query "SELECT * ,b.b_Title as title, bac.id as bacid FROM tbl_book AS b
                          LEFT JOIN tbl_bdetail AS bd ON b.id = bd.b_Title
                          LEFT JOIN tbl_bacquisition AS bac ON bac.bd_Id = bd.id"
    ;

        if (
    count($avail)>0)
        {
    $query.="WHERE bac.ba_Status = '$avail'";}
            if (
    count($sec)>0)
        {
    $query.="WHERE b.section_Id = '$sec'";    } 
    What I am trying here is that when the user select the $sec option, I want to add the WHERE clause value in my $query, when the user select the $avail option I want to add the WHERE clause values.

    My problem is what if the user will select values among the two options? how can I make it happen. any help will do.


    hey claro...!!

    its really simple, let me explain

    PHP Code:
    /// first check the two values to find which one is returning value??

    if($avail != '') {
    $cond "WHERE bac.ba_Status = '$avail' ";
    }
    elseif (
    $sec != '')
    {
     
    $cond "WHERE b.section_Id = '$sec'";
    }

    //if you want to find the result if both options were selected...???  then extend the if { } statement.

    //after checking the condition you can use it in mysql_query as,

    mysql_query("select * from table_name $cond"); 
    i think this would help you... i`m not so good in explaining...

    but thought of sharing myidea...

  4. #4
    SitePoint Addict
    Join Date
    Dec 2011
    Posts
    221
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Thank you for the post. did I put it right? seems like no effect.

    Code:
    <?php
    include'../connection/connect-db.php';
     
    $avail = $_POST['avail'];
    $sec = $_POST['section'];
     
     
    $strPage = $_REQUEST['Page'];
    if($_REQUEST['mode']=="Listing"){
    
    $query = "SELECT * ,b.b_Title as title, bac.id as bacid FROM tbl_book AS b
    					  LEFT JOIN tbl_bdetail AS bd ON b.id = bd.b_Title
    					  LEFT JOIN tbl_bacquisition AS bac ON bac.bd_Id = bd.id ";
    $result = mysql_query($query) or die(mysql_error());
     
    $Num_Rows = mysql_num_rows ($result);
     
    ########### pagins
    
    $Per_Page = 15;   // Records Per Page
     
    $Page = $strPage;
    if(!$strPage)
    {
    	$Page=1;
    }
     
    $Prev_Page = $Page-1;
    $Next_Page = $Page+1;
    
    
    $Page_Start = (($Per_Page*$Page)-$Per_Page);
    if($Num_Rows<=$Per_Page)
    {
    	$Num_Pages =1;
    }
    else if(($Num_Rows % $Per_Page)==0)
    {
    	$Num_Pages =($Num_Rows/$Per_Page) ;
    }
    else
    {
    	$Num_Pages =($Num_Rows/$Per_Page)+1;
    	$Num_Pages = (int)$Num_Pages;
    }
    
    if (count($avail)>0)
    	{
    	$query.="WHERE bac.ba_Status = '$avail' ORDER BY ba_Accnum ASC LIMIT $Page_Start , $Per_Page";
    	}
        
    	if (count($sec)>0)
    	{
    	$query.="WHERE b.section_Id = '$sec' ORDER BY ba_Accnum ASC LIMIT $Page_Start , $Per_Page";
    	}
    	else { $query.="ORDER BY ba_Accnum ASC LIMIT $Page_Start , $Per_Page ";} 
    
    $result = mysql_query($query) or die(mysql_error());

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    FML, kill me now
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict
    Join Date
    Dec 2011
    Posts
    221
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    This thing works for me. Thank you sir! I'm sorry I've included my code for pagination.

    Code:
    $avail = $_POST['avail']; 
    $sec = $_POST['section']; 
     
    
    $avail = $_POST['avail'];
    $sec = $_POST['section'];
    
    
    
    if( !empty($avail) AND !empty($sec)) {
    
     $cond = "WHERE b.section_Id = '$sec' AND bac.ba_Status = '$avail'";
    echo 'both are selected';
    
    }
    elseif ($sec != '')
    {
     $cond = "WHERE b.section_Id = '$sec'";
    echo 'section is selected';
    }
    elseif ($avail != '')
    {
    $cond = "WHERE bac.ba_Status = '$avail' ";
    echo 'availability is selected';
    }
    else
    {   echo 'nothing is selected';}
    
    $query = "SELECT * ,b.b_Title as title, bac.id as bacid FROM tbl_book AS b
                          LEFT JOIN tbl_bdetail AS bd ON b.id = bd.b_Title
                          LEFT JOIN tbl_bacquisition AS bac ON bac.bd_Id = bd.id $cond";
    					  
    $result = mysql_query($query) or die(mysql_error());

  7. #7
    SitePoint Member
    Join Date
    Jan 2012
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    great claro..!!

    FYI hae the habit of commenting in bettwing code its just for your reference, it will help if you require any help from you friends...

    Any way very much happy that you got the result...


Bookmarks

Posting Permissions

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