|
Tutorials / FAQs -
Database
|
|
Once you get the hang of executing MySQL queries from PHP you'll realize that it is something very powerful. The most common use of this is for performing searches based on user input. We all know that many searchable fields are optional. How do you deal with it when certain search criteria isn't available? I show you how here.
This is an example query from a PHP function that I built. It accepts user input, but not all of the fields are required. The isSet functions and the conditional logic at the bottom of the query show you how to selectively ignore or add certain conditions. Conditional Query Construction <?php // a query for notes function getNotes($tNoteID, $debugMode, $orderBy, $noteTypeID) { $tNoteID = ( isset($tNoteID) ? $tNoteID : 0 ); $noteTypeID = ( isset($noteTypeID) ? $noteTypeID : 0 ); $queryStr = " SELECT n.noteID , n.noteTitle , n.noteURL , n.noteText , n.createDate , DATE_FORMAT(n.createDate, '%d-%b-%Y') as fmtCreateDate , n.updateDate , DATE_FORMAT(n.updateDate, '%d-%b-%Y') as fmtupdateDate , n.noteTypeID , nt.noteType FROM notes n LEFT JOIN noteTypes nt ON n.noteTypeID = nt.noteTypeID WHERE 0=0"; if ( $noteTypeID > 0) { $queryStr = $queryStr." AND n.noteTypeID = ".$noteTypeID; } if ( $tNoteID > 0) { $queryStr = $queryStr." AND n.noteID = ".$tNoteID; } $orderBy = ( strlen($orderBy) > 0 ? $orderBy : "nt.noteType, n.noteTitle desc"); $queryStr = $queryStr." ORDER BY ".$orderBy; if ( $debugMode ) { echo "<br>qryDtl: ". $queryStr. "<br>"; } } ?>
|
