0

I'm trying to figure out the appropriate way of writing a query that would display results based on whether one or two variables are present. I'm still quite new to PHP, though I am learning, albeit slowly.

My current code, which accepts only one variable for a search criteria is below. As you can see I have only one _POST variable I am receiving $reg

What I'd like to do is receive a second variable 

$toest=$_POST['toestelID'];

If only $reg contains a value {and $toest is Null/Empty) I would like it to query the WHERE statement as such

WHERE vg.luchtvaartmaatschappijID= '$reg'

If $reg and $toest both contain values then it would be WHERE vg.luchtvaartmaatschappijID= '$reg' AND vg.toestelID='$toest'

I know this can be done with a IF / Else type statement, I just don't know how to program this as such.

Original Code:

<?php
$reg=$_POST['luchtvaartmaatschappijID'];
if(isset($_POST['submit'])){
if(isset($_GET['go'])){

$reg=$_POST['luchtvaartmaatschappijID'];
$color1 = "#C2DFFF";  
$color2 = "#FFFFFF"; 
$row_count = 0;
//connect  to the database
$db=mysql_connect  ("localhost", "someusername",  "somepassword") or die ('I cannot connect to 
the database  because: ' . mysql_error());
//-select  the database to use
$mydb=mysql_select_db("somedatabase");
//-query  the database table
$sql="SELECT vg.*, lvm.luchtvaartmaatschappij AS lvmnaam, lvm.luchtvaartmaatschappijID as lvmid, 
t.toestel

FROM tbl_vliegtuiggegevens vg

INNER JOIN tbl_luchtvaartmaatschappij lvm
ON vg.lvmID = lvm.luchtvaartmaatschappijID

INNER JOIN tbl_toestel t
ON vg.toestelID = t.toestelID

WHERE vg.luchtvaartmaatschappijID= '$reg'
ORDER BY lvm.luchtvaartmaatschappij ASC, t.toestel ASC, vg.inschrijvingnmr ASC";
//-run  the query against the mysql query function

$result=mysql_query($sql);


echo "zoekresultaten:<br>
<table border='0' cellspacing='0'>
<tr>
<th width='100' valign='top'><div align='left'>cn</div></th>
 <th width='150' valign='top'><div align='left'>reg</div></th>
<th width='300' valign='top'><div align='left'>Luchtvaartmaatschappij</div></th>
<th width='330' valign='top'><div align='left'>Toestel</div></th>
</tr>
<tr>
<th height='1' colspan='4' valign='top' bgcolor='#333333'></th>
</tr>";
//-create  while loop and loop through result set
while($row=mysql_fetch_array($result)){
       $row_color = ($row_count % 2) ? $color1 : $color2;
      $reg=$row['inschrijvingnmr'];
      $toestel=$row['toestel'];
       $cnid=$row['cn'];
       $lvmid=$row['lvmid'];
       $lvmnaam=$row['lvmnaam'];
      $ID=$row['vliegtuiggegevenID'];
//-display the result of the array

echo "<tr>";
echo "<td bgcolor='$row_color'>".$cnid."</td>";
echo "<td bgcolor='$row_color'><a target='_blank' 
href=\"../vliegtuiggegevens/vliegtuiggegevens_form.php?id=$reg&cid=$cnid&lvid=$lvmid\">"   .$reg  
. "</a></td>";
echo "<td bgcolor='$row_color'>" . $lvmnaam . "</td>";
echo "<td bgcolor='$row_color'>" . $toestel . "</td>";
echo "</tr>";
// Add 1 to the row count 
$row_count++; 

}
echo "</table>";

}

if( mysql_num_rows($result) == 0 )
{
include('../vliegtuiggegevens/voegvliegtuiggegevenstoe_form.php');
} 

}
?>
Improve this question

2 Answers 2

Reset to default
1

first off I would strongly suggest using PDO, not only is it way more secure ( no sql injection). It can be way easier to use when you get the hang of it, and offers way more in terms of how the result data can be handled. Also the mysql functional library is deprecated. So you should use either PDO or mysqli.

To answer your question I like to build an array of where conditions for this kind of thing. So for example

 $PDO = new Pdo('mysql:host=localhost;database', 'user', 'password');
 //enable pdo error reporting, now you don't need all those die( 'bla' ) stuff.
 $PDO->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

 //create a stub of your query
 $sql = 'SELECT * FROM some_table WHERE';
 $where = array();
 $params = array();

 if( isset($_POST['something'] ) ){
     $where[] = 'something = :something'; // using pdo placeholder
     $params[':something'] = $_POST['something'];
 }

 if( isset($_POST['otherthing'] ) ){
     $where[] = 'otherthing = :otherthing'; // using pdo placeholder
     $params[':otherthing '] = $_POST['otherthing'];
 }

  //if you need a default value simply count your wheres
  // if not just use the part in the else chunk
  if( empty( $where ) ){
      $sql .= ' id = 0'; //no results on empty conditions etc... 
  }else{
      //now that you have some stuff
      $sql .= ' '.implode(' AND ', $where); //collapse it
  }

  // pdo can be trickier to see what your query actually is
  // so you can do something like this to check it out. but you'll have to do the quotes etc..
  //echo str_replace( array_keys( $params ), $params, $sql );

  //prepare the query 
  $stmt = $PDO->prepare( $sql );
  //execute it
  $stmt->execute( $params );
  //fetch the results
  $res = $stmt->fetchAll(PDO::FETCH_ASSOC);

That should give you something like what you need.

for reference

http://php.net/manual/en/book.pdo.php

(see example #2 in the following link )

http://php.net/manual/en/pdostatement.execute.php

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

thanks. when i post the code live I get an error on the page that says there is an unexpected { on line 43. In this case line 43 is this: if( isset($_POST['something'] ){
missing ")" sorry didn't test it. such as if( isset($_POST['something'] ) ){ ~ also depending what something is, and what you want "something" might be empty and you might not want the empty etc..
i saw that after i posted it. now i just get a fatal error. this might be too complicated for me right now.
@DaniëlCronk - not any harder then MySql what's the error say
i removed this: $stmt->execute( $params ); and then it worked. well, kind of. now i need to tweak it because i get mysql_fetch_array errors (expects parameter 1 to be resource)
|
0

Yes, you got it right - a few if and else statements are what you need. Here is a snippet that should help:

$toest=$_POST['toestelID'];
$reg=$_POST['reg'];
$where_stmt="";
if((isset($reg) && !empty($reg)) && (!isset($toest) || empty($toest)))
{
    $where_stmt="WHERE vg.luchtvaartmaatschappijID='".$reg."'";
}
elseif((isset($toest) && !empty($toest)) && (!isset($reg) || empty($reg)))
{
    $where_stmt="WHERE vg.toestelID='".$toest."'";
}
elseif(isset($reg) && !empty($reg) && isset($toest) && !empty($toest))
{
    $where_stmt="WHERE vg.luchtvaartmaatschappijID='".$reg."' and vg.toestelID='".$toest."'";
}
else
{
    //where statement should be excluded as no filters are available
}
$sql="SELECT vg.*, lvm.luchtvaartmaatschappij AS lvmnaam, lvm.luchtvaartmaatschappijID as lvmid, 
t.toestel FROM tbl_vliegtuiggegevens vg INNER JOIN tbl_luchtvaartmaatschappij lvm ON vg.lvmID = lvm.luchtvaartmaatschappijID INNER JOIN tbl_toestel t ON vg.toestelID = t.toestelID $where_stmt ORDER BY lvm.luchtvaartmaatschappij ASC, t.toestel ASC, vg.inschrijvingnmr ASC";
Improve this answer

1 Comment

yes, with some tweaking this was exactly what I was looking for. Thanks for your help, Kartik!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.