Using Elseif to change SQL result

[Bifter]

Hi,

 

I have created the following code, however only the last elseif (email) functions correctly and returns an sql query, if I swap the elseif around, say firstname and email - then firstname will function, so its only every the last one in the chain that works.

 

<?php
$firstname = $_GET['sfirstname'];
$lastname = $_GET['ssurname'];
$company = $_GET['scompany'];
$email = $_GET['semail'];
if(isset($_GET['sfirstname'])) {
$result = mysql_query("SELECT * FROM users WHERE firstname = '$firstname'");
} elseif (isset($_GET['ssurname'])) {
$result = mysql_query("SELECT * FROM users WHERE surname = '$lastname'");
} elseif (isset($_GET['scompany'])) {
$result = mysql_query("SELECT * FROM users WHERE company = '$company'");
} elseif (isset($_GET['semail'])) {
$result = mysql_query("SELECT * FROM users WHERE email = '$email'");
}else {
$result = mysql_query("SELECT * FROM users WHERE id = 0");
}
while ($row = mysql_fetch_assoc($result)) {
    echo "<tr>";
  echo "<td>" . "<div align=\"center\">". $row['firstname'] . "</b>" . "</div></td>";
  echo "<td>" . "<div align=\"center\">". $row['surname'] . "</b>" . "</div></td>";
  echo "<td>" . "<div align=\"center\">". $row['company'] . "</b>" . "</div></td>";
  echo "<td>" . "<div align=\"center\">". $row['email'] . "</b>" . "</div></td>";
  echo "</tr>";
}
?>
mysql_free_result($result);

 

Would be very grateful if somebody can assist me on this.

[DarkSuperHero]

it has to do with the logic your using i believe....every if and elseif condition in your code will be true at one time...all the queries are run(one at time) and yous store the query in the variable $result, which gets overwritten by every query...so by the time you return your result the last elseif has overwritten everything stores in the variable before...

 

what are you trying to test? if the information is in the database ? trying to return everythign in the users table ?

 

but in anycase you need to change your query to fit what you want, and you probably only need one query.... :-)

..................

 

after thinking of this a bit...i think this might work for you....although im not entirely sure...

<?php
$firstname = $_GET['sfirstname'];
$lastname = $_GET['ssurname'];
$company = $_GET['scompany'];
$email = $_GET['semail'];
if(isset($_GET['sfirstname'])) {
$result[] = mysql_query("SELECT * FROM users WHERE firstname = '$firstname'");
} elseif (isset($_GET['ssurname'])) {
$result[] = mysql_query("SELECT * FROM users WHERE surname = '$lastname'");
} elseif (isset($_GET['scompany'])) {
$result[] = mysql_query("SELECT * FROM users WHERE company = '$company'");
} elseif (isset($_GET['semail'])) {
$result[] = mysql_query("SELECT * FROM users WHERE email = '$email'");
}else {
$result[] = mysql_query("SELECT * FROM users WHERE id = 0");
}

for($i=0;$i<count($result);$i++){
while ($row = mysql_fetch_assoc($result[$i])) {
    echo "<tr>";
  echo "<td>" . "<div align=\"center\">". $row['firstname'] . "</b>" . "</div></td>";
  echo "<td>" . "<div align=\"center\">". $row['surname'] . "</b>" . "</div></td>";
  echo "<td>" . "<div align=\"center\">". $row['company'] . "</b>" . "</div></td>";
  echo "<td>" . "<div align=\"center\">". $row['email'] . "</b>" . "</div></td>";
  echo "</tr>";
}
}
?>
mysql_free_result($result);

 

 

 

[Bifter]

Thanks for the reply.

 

Basicly there are 4 form text fields; firstname, lastname, company and email...when somebody enters for instance John into the firstname text field I would like it to query the table users and return every entry where the firstname is John...

[DarkSuperHero]

edit to the last post....

 

 

[kenrbnsn]

How many of the $_GET values can be entered at once? One or more?

 

Using a switch statement might work better:

<?php
$wtmp = array();
foreach ($_GET as $k=>$v) {
   $v = trim(stripslashes($v));
   switch ($k) {
       case 'sfirstname':
           if (strlen($v) > 0) $wtmp[] = "firstname = '" . mysql_real_escape_string($v) . "'";
           break;
       case 'ssurname':
           if (strlen($v) > 0) $wtmp[] = "lastname = '" . mysql_real_escape_string($v) . "'";
           break;
       case 'scompany':
           if (strlen($v) > 0) $wtmp[] = "company = '" . mysql_real_escape_string($v) . "'";
           break;
       case 'semail':
           if (strlen($v) > 0) $wtmp[] = "email = '" . mysql_real_escape_string($v) . "'";
           break;
     }
}
if (!empty($wtmp)) {
    $q = "SELECT * FROM users WHERE " . implode(' AND ',$wtmp);
    $result = mysql_query($q) or die("Problem with the query: $q<br>" . mysql_error());
}
?>

 

Ken

[Bifter]

Hi Ken,

 

More than one, but if only one is entered then it still completes - would be good...how does this affect the code you posted?

 

Thanks,

Ben

[kenrbnsn]

My code should work whether one or more are entered.

 

Ken

[Bifter]

Hi,

 

Ken's code he posted, below:

<?php
$wtmp = array();
foreach ($_GET as $k=>$v) {
   $v = trim(stripslashes($v));
   switch ($k) {
       case 'sfirstname':
           if (strlen($v) > 0) $wtmp[] = "firstname = '" . mysql_real_escape_string($v) . "'";
           break;
       case 'ssurname':
           if (strlen($v) > 0) $wtmp[] = "lastname = '" . mysql_real_escape_string($v) . "'";
           break;
       case 'scompany':
           if (strlen($v) > 0) $wtmp[] = "company = '" . mysql_real_escape_string($v) . "'";
           break;
       case 'semail':
           if (strlen($v) > 0) $wtmp[] = "email = '" . mysql_real_escape_string($v) . "'";
           break;
     }
}
if (!empty($wtmp)) {
    $q = "SELECT * FROM users WHERE " . implode(' AND ',$wtmp);
    $result = mysql_query($q) or die("Problem with the query: $q<br>" . mysql_error());
}
?>

works fine because if I echo $q then the correct mysql_query is displayed, however I cant figure out how to display into a table, as if I use the normal code i use(below):

 

<?php
while ($row = mysql_fetch_assoc($result)) {
    echo "<tr>";
  echo "<td>" . "<div align=\"center\">". $row['firstname'] . "</b>" . "</div></td>";
  echo "<td>" . "<div align=\"center\">". $row['surname'] . "</b>" . "</div></td>";
  echo "<td>" . "<div align=\"center\">". $row['company'] . "</b>" . "</div></td>";
  echo "<td>" . "<div align=\"center\">". $row['email'] . "</b>" . "</div></td>";
  echo "</tr>";
}
?>

 

then I get the following errors:

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/ipendpoi/public_html/mac/index.php on line 294

 

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /home/ipendpoi/public_html/mac/index.php on line 301

 

If anybody can help I would be grateful.

 

Thanks.

[kenrbnsn]

Please post the code you're using now.

 

Ken

[Bifter]

Hi Ken,

 

Working on Sunday also  !!!

 

<?php
$wtmp = array();
foreach ($_GET as $k=>$v) {
   $v = trim(stripslashes($v));
   switch ($k) {
       case 'sfirstname':
           if (strlen($v) > 0) $wtmp[] = "firstname = '" . mysql_real_escape_string($v) . "'";
           break;
       case 'ssurname':
           if (strlen($v) > 0) $wtmp[] = "surname = '" . mysql_real_escape_string($v) . "'";
           break;
       case 'scompany':
           if (strlen($v) > 0) $wtmp[] = "company = '" . mysql_real_escape_string($v) . "'";
           break;
       case 'semail':
           if (strlen($v) > 0) $wtmp[] = "email = '" . mysql_real_escape_string($v) . "'";
           break;
     }
}
if (!empty($wtmp)) {
    $q = "SELECT * FROM users WHERE " . implode(' AND ',$wtmp);
    $result = mysql_query($q) or die("Problem with the query: $q<br>" . mysql_error());
}
while ($row = mysql_fetch_assoc($result)) {
    echo $row["firstname"];
    echo $row["surname"];
    echo $row["company"];
    echo $row["email"];
}
mysql_free_result($result);
?>

 

Thanks for looking at this for me again....

[Bifter]

sorry should have been:

 

<?php
$wtmp = array();
foreach ($_GET as $k=>$v) {
   $v = trim(stripslashes($v));
   switch ($k) {
       case 'sfirstname':
           if (strlen($v) > 0) $wtmp[] = "firstname = '" . mysql_real_escape_string($v) . "'";
           break;
       case 'ssurname':
           if (strlen($v) > 0) $wtmp[] = "lastname = '" . mysql_real_escape_string($v) . "'";
           break;
       case 'scompany':
           if (strlen($v) > 0) $wtmp[] = "company = '" . mysql_real_escape_string($v) . "'";
           break;
       case 'semail':
           if (strlen($v) > 0) $wtmp[] = "email = '" . mysql_real_escape_string($v) . "'";
           break;
     }
}
if (!empty($wtmp)) {
    $q = "SELECT * FROM users WHERE " . implode(' AND ',$wtmp);
    $result = mysql_query($q) or die("Problem with the query: $q<br>" . mysql_error());
}
while ($row = mysql_fetch_assoc($result)) {
  echo "<tr>";
  echo "<td>" . "<div align=\"center\">". $row['firstname'] . "</div></td>";
  echo "<td>" . "<div align=\"center\">". $row['surname'] . "</div></td>";
  echo "<td>" . "<div align=\"center\">". $row['company'] . "</div></td>";
  echo "<td>" . "<div align=\"center\">". $row['email'] . "</div></td>";
  echo "</tr>";
}
mysql_free_result($result);
?>

[Bifter]

In case anybody is interested the following code works perfectly:

 

<?php
$wtmp = array();
foreach ($_GET as $k=>$v) {
   $v = trim(stripslashes($v));
   switch ($k) {
       case 'sfirstname':
           if (strlen($v) > 0) $wtmp[] = "firstname = '" . mysql_real_escape_string($v) . "'";
           break;
       case 'ssurname':
           if (strlen($v) > 0) $wtmp[] = "surname = '" . mysql_real_escape_string($v) . "'";
           break;
       case 'scompany':
           if (strlen($v) > 0) $wtmp[] = "company = '" . mysql_real_escape_string($v) . "'";
           break;
       case 'semail':
           if (strlen($v) > 0) $wtmp[] = "email = '" . mysql_real_escape_string($v) . "'";
           break;
     }
}
if (empty($wtmp)) {
    $q = "SELECT * FROM users WHERE id = 0";
    $result = mysql_query($q) or die("Problem with the query: $q<br>" . mysql_error());
}
else {
   $q = "SELECT * FROM users WHERE " . implode(' AND ',$wtmp);
}
while ($row = mysql_fetch_assoc($result)) {
  echo "<tr>";
  echo "<td>" . "<div align=\"center\">". $row['firstname'] . "</div></td>";
  echo "<td>" . "<div align=\"center\">". $row['surname'] . "</div></td>";
  echo "<td>" . "<div align=\"center\">". $row['company'] . "</div></td>";
  echo "<td>" . "<div align=\"center\">". $row['email'] . "</div></td>";
  echo "</tr>";
}
mysql_free_result($result);
?>

[kenrbnsn]

There is a problem with this:

<?php
if (empty($wtmp)) {
    $q = "SELECT * FROM users WHERE id = 0";
    $result = mysql_query($q) or die("Problem with the query: $q<br>" . mysql_error());
}
else {
   $q = "SELECT * FROM users WHERE " . implode(' AND ',$wtmp);
}
while ($row = mysql_fetch_assoc($result)) {
  echo "<tr>";
  echo "<td>" . "<div align=\"center\">". $row['firstname'] . "</div></td>";
  echo "<td>" . "<div align=\"center\">". $row['surname'] . "</div></td>";
  echo "<td>" . "<div align=\"center\">". $row['company'] . "</div></td>";
  echo "<td>" . "<div align=\"center\">". $row['email'] . "</div></td>";
  echo "</tr>";
}
mysql_free_result($result);
?>

You're never using the query that is created via the switch statement, so change it to:

<?php
if (empty($wtmp)) {
    $q = "SELECT * FROM users WHERE id = 0";
}
else {
   $q = "SELECT * FROM users WHERE " . implode(' AND ',$wtmp);
}
$result = mysql_query($q) or die("Problem with the query: $q<br>" . mysql_error());
while ($row = mysql_fetch_assoc($result)) {
  echo "<tr>";
  echo "<td>" . "<div align=\"center\">". $row['firstname'] . "</div></td>";
  echo "<td>" . "<div align=\"center\">". $row['surname'] . "</div></td>";
  echo "<td>" . "<div align=\"center\">". $row['company'] . "</div></td>";
  echo "<td>" . "<div align=\"center\">". $row['email'] . "</div></td>";
  echo "</tr>";
}
mysql_free_result($result);
?>

 

Ken