netgeni Posted September 15, 2010 Share Posted September 15, 2010 Hello All, Iam completely new (3days old) to sql, php, html, so could do with a little help. Ok so I just found out about this wonderfull world and thought i would give it a try and I like it. But as I have no real idea of what Iam doing Iam finding it abit tricky to change some of the code I found at. http://www.phpfreaks.com/tutorial/simple-sql-search By the way thanks for this. So far I have setup a wamp server and got it all working fine and tweeked the above details in the url. This works great (well chuffed). But I could do with changing two things for now. 1). I could do with beeing able to search multiple things in the one text box for example. If I inputed "hello world" or "hel wor" it would search through the 19,000 lines in my database and find all instances of "hello" and "world" or "hel" "wor" in the same line's, it dosent matter what column they are in and output those lines. The code has the option to search all feilds "matchall" but not multiple words or parts of words.(hope that made sense I dont know all the terminology) It could be like using wild cards I suppose??. Is this possible or would I need multiple search boxes and if so how would I do that? 2). I would like the results to be returned in a table with headings for each column. If any one can help this would be great and thanks in advance for checking the long post out. Nick. Sorry if this is out there already but not to sure what I need to search for!??!?!? Ill include what Iam using so far. <?php $dbHost = 'localhost'; // localhost will be used in most cases // set these to your mysql database username and password. $dbUser = 'root'; $dbPass = ''; $dbDatabase = 'Stk_search'; // the database you put the table into. $con = mysql_connect($dbHost, $dbUser, $dbPass) or trigger_error("Failed to connect to MySQL Server. Error: " . mysql_error()); mysql_select_db($dbDatabase) or trigger_error("Failed to connect to database {$dbDatabase}. Error: " . mysql_error()); // Set up our error check and result check array $error = array(); $results = array(); // First check if a form was submitted. // Since this is a search we will use $_GET if (isset($_GET['search'])) { $searchTerms = trim($_GET['search']); $searchTerms = strip_tags($searchTerms); // remove any html/javascript. if (strlen($searchTerms) < 3) { $error[] = "Search terms must be longer than 3 characters."; }else { $searchTermDB = mysql_real_escape_string($searchTerms); // prevent sql injection. } // If there are no errors, lets get the search going. if (count($error) < 1) { $searchSQL = "SELECT id, Stock_Code, SV_Part_Number, SV_Description, SV_Search_Key FROM stkdb1 WHERE "; // grab the search types. $types = array(); $types[] = isset($_GET['Stock_Code'])?"`Stock_Code` LIKE '%{$searchTermDB}%'":''; $types[] = isset($_GET['SV_Part_Number'])?"`SV_Part_Number` LIKE '%{$searchTermDB}%'":''; $types[] = isset($_GET['SV_Description'])?"`SV_Description` LIKE '%{$searchTermDB}%'":''; $types[] = isset($_GET['SV_Search_Key'])?"`SV_Search_Key` LIKE '%{$searchTermDB}%'":''; $types = array_filter($types, "removeEmpty"); // removes any item that was empty (not checked) if (count($types) < 1) $types[] = "`Stock_Code` LIKE '%{$searchTermDB}%'"; // use the body as a default search if none are checked $andOr = isset($_GET['matchall'])?'AND':'OR'; $searchSQL .= implode(" {$andOr} ", $types) . " ORDER BY `Stock_Code`"; // order by title. $searchResult = mysql_query($searchSQL) or trigger_error("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$searchSQL}"); if (mysql_num_rows($searchResult) < 1) { $error[] = "The search term provided {$searchTerms} yielded no results."; }else { $results = array(); // the result array $i = 1; while ($row = mysql_fetch_assoc($searchResult)) { $results[] = "{$i}: {$row['Stock_Code']}<br />{$row['SV_Part_Number']}<br />{$row['SV_Description']}<br />{$row['SV_Search_Key']}<br /><br />"; $i++; } } } } function removeEmpty($var) { return (!empty($var)); } ?> <html> <title>My Simple Search Form</title> <style type="text/css"> #error { color: red; } </style> <body> <?php echo (count($error) > 0)?"The following had errors:<br /><span id=\"error\">" . implode("<br />", $error) . "</span><br /><br />":""; ?> <form method="GET" action="<?php echo $_SERVER['PHP_SELF'];?>" name="searchForm"> Search For: <input type="text" name="search" value="<?php echo isset($searchTerms)?htmlspecialchars($searchTerms):''; ?>" /><br /> Search In:<br /> Stock_Code: <input type="checkbox" name="Stock_Code" value="on" <?php echo isset($_GET['Stock_Code'])?"checked":''; ?> /> | SV_Part_Number: <input type="checkbox" name="SV_Part_Number" value="on" <?php echo isset($_GET['SV_Part_Number'])?"checked":''; ?> /> | SV_Description: <input type="checkbox" name="SV_Description" value="on" <?php echo isset($_GET['SV_Description'])?"checked":''; ?> /> | SV_Search_Key: <input type="checkbox" name="SV_Search_Key" value="on" <?php echo isset($_GET['SV_Search_Key'])?"checked":''; ?> /><br /> Match All Selected Fields? <input type="checkbox" name="matchall" value="on" <?php echo isset($_GET['matchall'])?"checked":''; ?><br /><br /> <input type="submit" name="submit" value="Search!" /> </form> <?php echo (count($results) > 0)?"Your search term: {$searchTerms} returned:<br /><br />" . implode("", $results):""; ?> </body> </html> Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.