[PHP-DB] If condition in query

2010-11-18 Thread Ethan Rosenberg 

Dear list -

Thank you for all your excellent help.

I wish to search a table.  In this case, I have five(5) columns: 
site, Record, BMI, Weight and Height.
I wish to be able to search on one or more of the columns.  If I use 
a query like:


$ste = $_POST['site'];
$req = $_POST['Record'];
$wgt = $_POST['Weight'];
$hgt = $_POST['Height'];
$bmi = $_POST['BMI'];

$sql1 =  select * from  intake2 where site = '$ste'   Weight = 
'$wgt'   Record = '$req'   '$hgt' = Height   '$bmi' = BMI ;

$result = mysqli_query($cxn, $sql1);

and do not use all the  variables, no data is returned.  I use to 
extract the data from the query.


while($row = mysqli_fetch_array($result[0]))

{
$site2 = $row[0];
$record2 = $row[1];
$bmi2 = $row[2];
$wgt2 = $row[3];
$hgt2 = $row[4];
printf(%s\t%d\t%3.1f\t%d\t%dbr /, $site2, $record2, 
$bmi2, $wgt2, $hgt2);

}


If I put an extra test in the query to exclude blank values;eg, 
(if(isset ($bmi)   '$bmi' = BMI ), $result defaults to a boolean 
and mysqli_fetch_array($result) fails.  I wish to be able to search 
on one or more, but not necessarily all, of the parameters and be 
able to output the results.


Advice and help please.

Thanks in advance.

Ethan



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] If condition in query

2010-11-18 Thread Niel Archer 
 Dear list -
 
 Thank you for all your excellent help.
 
 I wish to search a table.  In this case, I have five(5) columns: 
 site, Record, BMI, Weight and Height.
 I wish to be able to search on one or more of the columns.  If I use 
 a query like:
 
 $ste = $_POST['site'];
 $req = $_POST['Record'];
 $wgt = $_POST['Weight'];
 $hgt = $_POST['Height'];
 $bmi = $_POST['BMI'];
 
 $sql1 =  select * from  intake2 where site = '$ste'   Weight = 
 '$wgt'   Record = '$req'   '$hgt' = Height   '$bmi' = BMI ;
 $result = mysqli_query($cxn, $sql1);
 
 and do not use all the  variables, no data is returned.  I use to 
 extract the data from the query.
 
 while($row = mysqli_fetch_array($result[0]))
 
  {
  $site2 = $row[0];
  $record2 = $row[1];
  $bmi2 = $row[2];
  $wgt2 = $row[3];
  $hgt2 = $row[4];
  printf(%s\t%d\t%3.1f\t%d\t%dbr /, $site2, $record2, 
 $bmi2, $wgt2, $hgt2);
  }
 
 
 If I put an extra test in the query to exclude blank values;eg, 
 (if(isset ($bmi)   '$bmi' = BMI ), $result defaults to a boolean 
 and mysqli_fetch_array($result) fails.  I wish to be able to search 
 on one or more, but not necessarily all, of the parameters and be 
 able to output the results.
 
 Advice and help please.
 
 Thanks in advance.
 
 Ethan

First you need to protect your input from injection and other attacks.
http://en.wikipedia.org/wiki/SQL_injection

for the problem you ask, I'd suggest building the query in php rather
than SQL A simple example would be:

$where ' ';
if (isset($ste)) {
$where .=  site = '$ste';
}
if (isset($wgt)) {
$where .= , Weight =  '$wgt';
}

$sql .=  WHERE $where;


--
Niel Archer
niel.archer (at) blueyonder.co.uk


-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: [PHP-DB] If condition in query

2010-11-18 Thread Amit Tandon 
My question -
1.  why have u used mysqli_fetch_array($result[0]), in place of
mysqli_fetch_array($result)) i.e. without array brackets as $result is not
an array. What is returned by mysqli_fetch_array is an array.
2. Have u checked the your query (print $sq1) after u added  *extra test in
the query to exclude blank values;eg, (if(isset ($bmi)   '$bmi' = BMI )*,.
If your output works in mysql (mysql client), it would work here also


regds
amit

The difference between fiction and reality? Fiction has to make sense.


On Thu, Nov 18, 2010 at 10:22 PM, Niel Archer n...@chance.now wrote:

  Dear list -
 
  Thank you for all your excellent help.
 
  I wish to search a table.  In this case, I have five(5) columns:
  site, Record, BMI, Weight and Height.
  I wish to be able to search on one or more of the columns.  If I use
  a query like:
 
  $ste = $_POST['site'];
  $req = $_POST['Record'];
  $wgt = $_POST['Weight'];
  $hgt = $_POST['Height'];
  $bmi = $_POST['BMI'];
 
  $sql1 =  select * from  intake2 where site = '$ste'   Weight =
  '$wgt'   Record = '$req'   '$hgt' = Height   '$bmi' = BMI ;
  $result = mysqli_query($cxn, $sql1);
 
  and do not use all the  variables, no data is returned.  I use to
  extract the data from the query.
 
  while($row = mysqli_fetch_array($result[0]))
 
   {
   $site2 = $row[0];
   $record2 = $row[1];
   $bmi2 = $row[2];
   $wgt2 = $row[3];
   $hgt2 = $row[4];
   printf(%s\t%d\t%3.1f\t%d\t%dbr /, $site2, $record2,
  $bmi2, $wgt2, $hgt2);
   }
 
 
  If I put an extra test in the query to exclude blank values;eg,
  (if(isset ($bmi)   '$bmi' = BMI ), $result defaults to a boolean
  and mysqli_fetch_array($result) fails.  I wish to be able to search
  on one or more, but not necessarily all, of the parameters and be
  able to output the results.
 
  Advice and help please.
 
  Thanks in advance.
 
  Ethan

 First you need to protect your input from injection and other attacks.
 http://en.wikipedia.org/wiki/SQL_injection

 for the problem you ask, I'd suggest building the query in php rather
 than SQL A simple example would be:

 $where ' ';
 if (isset($ste)) {
$where .=  site = '$ste';
 }
 if (isset($wgt)) {
$where .= , Weight =  '$wgt';
 }

 $sql .=  WHERE $where;


 --
 Niel Archer
 niel.archer (at) blueyonder.co.uk


 --
 PHP Database Mailing List (http://www.php.net/)
 To unsubscribe, visit: http://www.php.net/unsub.php