WHERE and AND in condition

Databases
1

Greetings!

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


$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

start your WHERE clause with

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 –

WHERE 1=1
  AND bac.ba_Status = '$avail'
  AND b.section_Id = '$sec'

please note, you should be using INNER JOINs not LEFT JOINs

3

hey claro…!!

its really simple, let me explain

/// 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

Thank you for the post. did I put it right? seems like no effect.


<?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

FML, kill me now

6

This thing works for me. Thank you sir! I’m sorry I’ve included my code for pagination.


$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

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…