If condition in query

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%d
", $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: If condition in query

> 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%d
", $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: If condition in query

--00248c176baea3c78a0495619564
Content-Type: text/plain; charset=UTF-8

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 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%d
", $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
>
>

--00248c176baea3c78a0495619564--