Hosting Solutions
mobile_logo Table of Contents
seal_frontseal_back

SQL LIKE Syntax UNION and AS Alias with Filters

Part 3 of MySQL PHP Search Programming Exercises for database driven web sites. Search pages that allow advanced targeting and filtering of information. Learn to build HTML Search Forms and Advanced Search Filters, Various MySQL Search Query Methods, Joined Table Queries Targeting Multiple Tables, Natural Language Full-Text Search Queries and discuss fine tuning your search programming and adjusting search parameters. <?php /********************************************************* MySQL PHP Search Exercises by Adam Khoury @ developphp.com MySQL Database Version Used In the Lessons: 5.1.58 PHP Version Used in the Lessons: 5.2.17 For Code Logic and Code Explanations Watch the Videos *********************************************************/ error_reporting(E_ALL); ini_set('display_errors', '1'); $search_output = ""; if(isset($_POST['searchquery']) && $_POST['searchquery'] != ""){ $searchquery = preg_replace('#[^a-z 0-9?!]#i', '', $_POST['searchquery']); if($_POST['filter1'] == "Whole Site"){ $sqlCommand = "(SELECT id, page_title AS title FROM pages WHERE page_title LIKE '%$searchquery%' OR page_body LIKE '%$searchquery%') UNION (SELECT id, blog_title AS title FROM blog WHERE blog_title LIKE '%$searchquery%' OR blog_body LIKE '%$searchquery%')"; } else if($_POST['filter1'] == "Pages"){ $sqlCommand = "SELECT id, page_title AS title FROM pages WHERE page_title LIKE '%$searchquery%' OR page_body LIKE '%$searchquery%'"; } else if($_POST['filter1'] == "Blog"){ $sqlCommand = "SELECT id, blog_title AS title FROM blog WHERE blog_title LIKE '%$searchquery%' OR blog_body LIKE '%$searchquery%'"; } include_once("connect_to_mysql.php"); $query = mysql_query($sqlCommand) or die(mysql_error()); $count = mysql_num_rows($query); if($count > 1){ $search_output .= "<hr />$count results for <strong>$searchquery</strong><hr />$sqlCommand<hr />"; while($row = mysql_fetch_array($query)){ $id = $row["id"]; $title = $row["title"]; $search_output .= "Item ID: $id - $title<br />"; } // close while loop } else { $search_output = "<hr />0 results for <strong>$searchquery</strong><hr />$sqlCommand"; } } ?> <html> <head> </head> <body> <h2>Search the Exercise Tables</h2> <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post"> Search For: <input name="searchquery" type="text" size="44" maxlength="88"> Within: <select name="filter1"> <option value="Whole Site">Whole Site</option> <option value="Pages">Pages</option> <option value="Blog">Blog</option> </select> <input name="myBtn" type="submit"> <br> </form> <div> <?php echo $search_output; ?> </div> </body> </html>
thumb

PHP Videos

Search Programming MySQL Database

Create and Populate Example Database TablesBuild the HTML Search Form with Filter Select ListSQL LIKE Syntax UNION and AS Alias with FiltersSQL MATCH AGAINST Fulltext Search UNION Tutorial