adding a where variable inside sql

Question

if user select anything besides "all" it will lead to a where statement involving KodDaerah

$where = '';
$sql= "SELECT  * FROM   maklumatakaun 
        LEFT JOIN detailakaun ON maklumatakaun.id = detailakaun.id 
        LEFT JOIN maklumatbilakaun ON maklumatakaun.NoAkaun = maklumatbilakaun.NoAkaun 
        LEFT JOIN kodjenisakaun ON detailakaun.KodJenisAkaun = kodjenisakaun.KodJenisAkaun
        LEFT JOIN kodlokasi ON detailakaun.KodLokasi = kodlokasi.KodLokasi
        LEFT JOIN kodkategori ON maklumatakaun.KodKategori = kodkategori.KodKategori        
        LEFT JOIN koddaerah ON maklumatakaun.KodDaerah = koddaerah.KodDaerah
        WHERE maklumatakaun.KodKategori = '$KodKategori'        
        AND detailakaun.KodJenisAkaun = '$KodJenisAkaun'
        AND maklumatbilakaun.BulanBil = '$BulanBil' 
        AND maklumatbilakaun.TahunBil ='$TahunBil' 
        ".mysql_real_escape_string($where)."
        ORDER BY koddaerah.NamaDaerah ";

if($KodDaerah != "all"){
  $where = "AND maklumatakaun.KodDaerah = '$KodDaerah' "; //Add your where statement here
  //Whatever you want to do
}  
else {
  $where = "";
}

if user select all then the system will just use the current sql statement that i provided, for now im not sure what's wrong, so here is where user select the KodDaerah from drop down list box

<?php include('dbase.php');
$sql = "SELECT KodDaerah, NamaDaerah FROM koddaerah";
$result = mysql_query($sql);
echo "<select name='KodDaerah' id='KodDaerah' class='input_field' required />
<option>Pilih Daerah</option>
<option value='all'>Seluruh Pahang</option>";
while ($kod = mysql_fetch_array ($result)){
echo "<option value=".$kod['KodDaerah'].">" .$kod['NamaDaerah']."</option>
<option value='all'>Seluruh Pahang</option>";
}
echo "<?select>";
?>

The problem is now even when i select a certain KodDaerah, it will just run the provided sql query without using the WHERE statement in the $where. Can anybody help?

EDIT:

$sql = $sql . $where;
$result = mysql_query ($sql);
$BilAkaun = mysql_num_rows ($result);

The above code block setups up $sql with the initial value of $where (ie, "") prior to even reaching your if block to conditionally setup $where — Anthony Forloney
– Anthony Forloney, Commented Jan 14, 2015 at 0:55

alquist42 · Accepted Answer · 2015-01-14 01:06:35Z

try to move up the if statement:, remove mysql_real_escape_string (escape before)

$where = '';
if($KodDaerah != "all"){
  $where = "AND maklumatakaun.KodDaerah = '$KodDaerah' "; //Add your where statement here
  //Whatever you want to do
}  
else {
  $where = "";
}
$sql= "SELECT  * FROM   maklumatakaun 
        LEFT JOIN detailakaun ON maklumatakaun.id = detailakaun.id 
        LEFT JOIN maklumatbilakaun ON maklumatakaun.NoAkaun = maklumatbilakaun.NoAkaun 
        LEFT JOIN kodjenisakaun ON detailakaun.KodJenisAkaun = kodjenisakaun.KodJenisAkaun
        LEFT JOIN kodlokasi ON detailakaun.KodLokasi = kodlokasi.KodLokasi
        LEFT JOIN kodkategori ON maklumatakaun.KodKategori = kodkategori.KodKategori        
        LEFT JOIN koddaerah ON maklumatakaun.KodDaerah = koddaerah.KodDaerah
        WHERE maklumatakaun.KodKategori = '$KodKategori'        
        AND detailakaun.KodJenisAkaun = '$KodJenisAkaun'
        AND maklumatbilakaun.BulanBil = '$BulanBil' 
        AND maklumatbilakaun.TahunBil ='$TahunBil' 
        ".$where."
        ORDER BY koddaerah.NamaDaerah ";

and then do not append $where again

$result = mysql_query ($sql);
$BilAkaun = mysql_num_rows ($result);

Collectives™ on Stack Overflow

adding a where variable inside sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related