jirble2 Posted January 8, 2011 Share Posted January 8, 2011 I'm very new to php, and so I haven't really mastered all the coding. But what I want to do is make a search form, which will search 15 different columns in a MySQL database. I've gone to a few different online walkthroughs, but I haven't managed to succeed as of yet. Can someone please show me the HTML and PHP coding required in order to make a search setup (the server is "localhost", the username is "root", the password is [blank], the database is "anzac war trophys" and the table is "anzac". And also assume that the collumns are called "a", "b", "c" and so on. Any help greatly appreciated! Quote Link to comment https://forums.phpfreaks.com/topic/223764-creating-a-search-form/ Share on other sites More sharing options...
Zurev Posted January 8, 2011 Share Posted January 8, 2011 No ones going to write the code for you man. Better to give us the code you get stuck on rather than ask for someone else to do it, how else will you learn? There are plenty of resources out there on this subject, but coming here and giving people your database information so they'll code it for you won't work. Some resources: http://www.webreference.com/programming/php/search/ http://www.pixel2life.com/publish/tutorials/707/create_your_own_php_mysql_search_engine/ http://www.devshed.com/c/a/MySQL/Building-a-Search-Engine-with-MySQL-and-PHP-5/ http://www.weberdev.com/ViewArticle/PHP:-A-simple-MySQL-search http://www.designplace.org/scripts.php?page=1&c_id=25 Quote Link to comment https://forums.phpfreaks.com/topic/223764-creating-a-search-form/#findComment-1156627 Share on other sites More sharing options...
GrooN Posted January 8, 2011 Share Posted January 8, 2011 For handling database connection you should look at these functions mysql_connect(); mysql_select_db(); mysql_query(); mysql_fetch_array(); To search the columns the function "strpos()" would come in handy, and if you want to make it a bit more advanced you should look at Regular Expressions Quote Link to comment https://forums.phpfreaks.com/topic/223764-creating-a-search-form/#findComment-1156628 Share on other sites More sharing options...
jirble2 Posted January 9, 2011 Author Share Posted January 9, 2011 Well thanks for the replies. I suppose that asking someone to do the coding for me is a pretty big ask. I should have added that if the coding was only small, then could someone show me, but those links tell me that the coding isn't exactly 'small'. But anyway, I'll have a go with those links Zurev gave me, and I'll post again if and when I run into trouble. Quote Link to comment https://forums.phpfreaks.com/topic/223764-creating-a-search-form/#findComment-1156884 Share on other sites More sharing options...
jirble2 Posted January 9, 2011 Author Share Posted January 9, 2011 Well I tried using the one on designplace, but when I try to do a search I get the message "Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\search.php on line 45". The coding on line 45 is $numrows=mysql_num_rows($numresults); . What am I doing wrong? My guess is that I've probably missed out on some little thing that I forgot to edit. Quote Link to comment https://forums.phpfreaks.com/topic/223764-creating-a-search-form/#findComment-1156892 Share on other sites More sharing options...
smproph Posted January 9, 2011 Share Posted January 9, 2011 When it gives you the error right there and points to the mysql query it is usually the SELECT query you are using that is the problem. At least in my cases it usually is. Could you copy and paste you SELECT statement you are using? Quote Link to comment https://forums.phpfreaks.com/topic/223764-creating-a-search-form/#findComment-1156905 Share on other sites More sharing options...
jirble2 Posted January 9, 2011 Author Share Posted January 9, 2011 When it gives you the error right there and points to the mysql query it is usually the SELECT query you are using that is the problem. At least in my cases it usually is. Could you copy and paste you SELECT statement you are using? $query = "select * from anzac where Serial No. like \"%$trimmed%\" order by Serial No."; Quote Link to comment https://forums.phpfreaks.com/topic/223764-creating-a-search-form/#findComment-1156914 Share on other sites More sharing options...
jirble2 Posted January 9, 2011 Author Share Posted January 9, 2011 Oh and also, in the tutorial, the msql query code looked like this (before I edited it obviously) $query = "select * from the_table where 1st_field like \"%$trimmed%\" order by 1st_field"; Quote Link to comment https://forums.phpfreaks.com/topic/223764-creating-a-search-form/#findComment-1156915 Share on other sites More sharing options...
QuickOldCar Posted January 9, 2011 Share Posted January 9, 2011 for this line $query = "select * from anzac where Serial No. like \"%$trimmed%\" order by Serial No."; You can't use a . or spaces like that, and it should be the exact that is the field in your database, something like serial_no You should look in your database and see the actual field values they are that are going to search into. Quote Link to comment https://forums.phpfreaks.com/topic/223764-creating-a-search-form/#findComment-1156949 Share on other sites More sharing options...
jirble2 Posted January 9, 2011 Author Share Posted January 9, 2011 Well, now when I try to load the page I get "Parse error: syntax error, unexpected T_STRING, expecting ',' or ';' in C:\xampp\htdocs\search.php on line 51" Line 51: echo "<li>" . "<a href=\"search.php?id=$id\>" .$serial_no . " " . $model . "</a></li>\n"; And if it helps, here is my entire code so far: <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <link rel="stylesheet" type="text/css" href="search.css"/> <title>ANZAC war Trophies - Search</title> </head> <body> <div id="banner"> <h1>ANZAC war Trophies</h1> </div> <h3>Search Contacts Details</h3> <p>You may search either by first or last name</p> <form method="post" action="search.php?go" id="searchform"> <input type="text" name="name"> <input type="submit" name="submit" value="Search"> </form> <?php if(isset($_POST['submit'])){ if(isset($_GET['go'])){ if(preg_match("/^[ a-zA-Z]+/", $_POST['name'])){ $name=$_POST['anzac war trophys']; //connect to the database $db=mysql_connect ("localhost", "root", "") or die ('I cannot connect to the database because: ' . mysql_error()); //-select the database to use $mydb=mysql_select_db("war_trophies"); //-query the database table $sql="SELECT id, serial_no, model, calibre, manufacturer, date_manufacture, captor, date_capture, place_capture, orig_alloc_place, orig_alloc_state, current_loc_place, current_loc_state, extant, condition FROM war_trophies WHERE serial_no LIKE '%" . $name . "%' OR model LIKE '%" . $name ."%'"; //-run the query against the mysql query function $result=mysql_query($sql); //-create while loop and loop through result set while($row=mysql_fetch_array($result)){ $id = $row["id"]; $serial_no = $row["serial_no"]; $model = $row["model"]; $calibre = $row["calibre"]; $manufacturer = $row["manufacturer"]; $date_manufacture = $row["date_manufacture"]; $captor = $row["captor"]; $date_capture = $row["date_capture"]; $place_capture = $row["place_capture"]; $orig_alloc_place = $row["orig_alloc_place"]; $orig_alloc_state = $row["orig_alloc_state"]; $current_loc_place = $row["current_loc_place"]; $current_loc_state = $row["current_loc_state"]; $extant = $row["extant"]; $condition = $row["condition"]; //-display the result of the array echo "<ul>\n"; echo "<li>" . "<a href=\"search.php?id=$id\>" .$serial_no . " " . $model . "</a></li>\n"; echo "</ul>"; } } else{ echo "<p>Please enter a search query</p>"; } } } ?> </body> </html> Quote Link to comment https://forums.phpfreaks.com/topic/223764-creating-a-search-form/#findComment-1156962 Share on other sites More sharing options...
QuickOldCar Posted January 9, 2011 Share Posted January 9, 2011 Change echo "<li>" . "<a href=\"search.php?id=$id\>" .$serial_no . " " . $model . "</a></li>\n"; to this echo "<li><a href='search.php?id=$id'>$serial_no $model</a></li>\n"; And should probably be using <br /> unless are converting the \n Quote Link to comment https://forums.phpfreaks.com/topic/223764-creating-a-search-form/#findComment-1156966 Share on other sites More sharing options...
jirble2 Posted January 9, 2011 Author Share Posted January 9, 2011 Change echo "<li>" . "<a href=\"search.php?id=$id\>" .$serial_no . " " . $model . "</a></li>\n"; to this echo "<li><a href='search.php?id=$id'>$serial_no $model</a></li>\n"; And should probably be using <br /> unless are converting the \n Well you've definitely helped, because now the page looks like it should, instead of just displaying an error. However, when I try to search now, i get the error: Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\search.php on line 32. Line 32: while($row=mysql_fetch_array($result)){ Quote Link to comment https://forums.phpfreaks.com/topic/223764-creating-a-search-form/#findComment-1156973 Share on other sites More sharing options...
QuickOldCar Posted January 9, 2011 Share Posted January 9, 2011 Most likely the query is failing due to quote's being wrong or not escaped LIKE '%" . $name . "%' OR model LIKE '%" . $name ."%'" Quote Link to comment https://forums.phpfreaks.com/topic/223764-creating-a-search-form/#findComment-1156978 Share on other sites More sharing options...
jirble2 Posted January 9, 2011 Author Share Posted January 9, 2011 Most likely the query is failing due to quote's being wrong or not escaped LIKE '%" . $name . "%' OR model LIKE '%" . $name ."%'" Sorry, but this is where my "very little knowledge" off php coding comes in. Could you tell me which part I should be looking at, and what I should change it to? I don't have a clue what you mean by "escaped", but I'll read up a bit more and try to learn. Quote Link to comment https://forums.phpfreaks.com/topic/223764-creating-a-search-form/#findComment-1156988 Share on other sites More sharing options...
QuickOldCar Posted January 9, 2011 Share Posted January 9, 2011 instead of $sql="SELECT id, serial_no, model, calibre, manufacturer, date_manufacture, captor, date_capture, place_capture, orig_alloc_place, orig_alloc_state, current_loc_place, current_loc_state, extant, condition FROM war_trophies WHERE serial_no LIKE '%" . $name . "%' OR model LIKE '%" . $name ."%'"; try $sql="SELECT * FROM war_trophies WHERE serial_no LIKE '%"."$name"."%' OR model LIKE '%"."$name"."%'"; Quote Link to comment https://forums.phpfreaks.com/topic/223764-creating-a-search-form/#findComment-1156989 Share on other sites More sharing options...
jirble2 Posted January 9, 2011 Author Share Posted January 9, 2011 tried that, but still gives the same error message Quote Link to comment https://forums.phpfreaks.com/topic/223764-creating-a-search-form/#findComment-1156990 Share on other sites More sharing options...
jirble2 Posted January 10, 2011 Author Share Posted January 10, 2011 Well in the end I found yet another tutorial, and for once, I managed to get it to work . My next step now is to format the appearance of the results. Is it possible to use CSS to format php coding? If so, how do you reference to individual areas within the php (ie. what's the php alternative of <div id="example>)? Quote Link to comment https://forums.phpfreaks.com/topic/223764-creating-a-search-form/#findComment-1157274 Share on other sites More sharing options...
QuickOldCar Posted January 10, 2011 Share Posted January 10, 2011 Yes you just label the divider and the css does the rest. you can break out of php and back in like this ?> <div id="example"> <?php or can echo it with single quotes within echo "<div id='example'>"; $somecode = "blah"; echo "</div>"; or can escape them echo "<div id=\"example\"><a href='index.php'>Home page</a></div>"; even something like this echo "<div id=\"example\">". $somecode."<a href='index.php'>Home page</a></div>"; Quote Link to comment https://forums.phpfreaks.com/topic/223764-creating-a-search-form/#findComment-1157412 Share on other sites More sharing options...
jirble2 Posted January 11, 2011 Author Share Posted January 11, 2011 Well what I really want to do is to have the search results in a table, where each column has a different part of each result like: |ID|Serial No.|Model|Condition| |---|------------|-------|------------| |4 |1234567 |M | Good | |---|------------|-------|------------| |7 |234568 |G | Bad | |---|------------|-------|------------| At the moment the results look like ID: 4 Serial No.: 1234567 Model: M Condition: Good ID: 7 Serial No.: 2345678 Model: G Condition: Bad Is this just a matter of using CSS or does it involve even more PHP coding to do? Quote Link to comment https://forums.phpfreaks.com/topic/223764-creating-a-search-form/#findComment-1157689 Share on other sites More sharing options...
jirble2 Posted January 11, 2011 Author Share Posted January 11, 2011 Lol I only just discovered AFTER doing that last reply that there is a table tool on this forum. Quote Link to comment https://forums.phpfreaks.com/topic/223764-creating-a-search-form/#findComment-1157691 Share on other sites More sharing options...
jirble2 Posted January 12, 2011 Author Share Posted January 12, 2011 Well never mind about the table, I worked out how to do that. But this now leaves me with 2 more things that I want to do to my table. 1: A count of results, where if there are no results a message saying something like "Please go back and try again" will display. And 2: At the moment, when I try to search by pressing spacebar or not entering anything, then I'm shown every single result there is. I want it so that when you enter spacebar or nothing at all nothing will happen and the page will stay at the search page. Anyone know how to do this? Quote Link to comment https://forums.phpfreaks.com/topic/223764-creating-a-search-form/#findComment-1158190 Share on other sites More sharing options...
QuickOldCar Posted January 12, 2011 Share Posted January 12, 2011 It's always best to post your current code so people know what it is. can swoop all your code and just press the php button in the editor. Quote Link to comment https://forums.phpfreaks.com/topic/223764-creating-a-search-form/#findComment-1158199 Share on other sites More sharing options...
jirble2 Posted January 12, 2011 Author Share Posted January 12, 2011 <html> <head> <title>Search results - ANZAC War Trophies</title> <link rel="stylesheet" type="text/css" href="index.css"/> </head> <body> <div id="nav"> <ul> <li><a href="index.html">Home</a></li> <li><a href="search.html">Search again</a></li> <li><a href="forum.html">Forum</a></li> <li><a href="contact.html">Contact us</a></li> <li><a href="login.html">Member login</a></li> </ul> </div><div id="main"> <h3>Your search returned ?? results</h3> </div><div id="results"> <table> <tr> <td>ID</td><td>Serial No.</td><td>Model</td><td>Caliber</td><td>Manufacturer</td><td>Date of Manufacture</td><td>Captor</td><td>Date of Capture</td><td>Place of Capture</td><td>Original Allocation - Town</td><td>Original Allocation - State</td><td>Current Location - Town</td><td>Current Location - State</td><td>Extant</td><td>Condition</td><td>Captured</td><td>Images</td> </tr> <?php mysql_connect ("localhost", "root","") or die (mysql_error()); mysql_select_db ("ANZAC War Trophies"); $term = $_POST['term']; $sql = mysql_query("select * from war_trophies where id like '%$term%' or serial_no like '%$term%' or model like '%$term%' or caliber like '%$term%' or manufacturer like '%$term%' or date_man like '%$term%' or captor like '%$term%' or date_cap like '%$term%' or place_cap like '%$term%' or orig_alloc_place like '%$term%' or orig_alloc_state like '%$term%' or current_loc_place like '%$term%' or current_loc_state like '%$term%' or extant like '%$term%' or captured like '%$term%' or cond like '%$term%' "); while ($row = mysql_fetch_array($sql)){ echo '<tr><td>'.$row['id']; echo '</td><td>'.$row['serial_no']; echo '</td><td>'.$row['model']; echo '</td><td>'.$row['caliber']; echo '</td><td>'.$row['manufacturer']; echo '</td><td>'.$row['date_man']; echo '</td><td>'.$row['captor']; echo '</td><td>'.$row['date_cap']; echo '</td><td>'.$row['place_cap']; echo '</td><td>'.$row['orig_alloc_place']; echo '</td><td>'.$row['orig_alloc_state']; echo '</td><td>'.$row['current_loc_place']; echo '</td><td>'.$row['current_loc_state']; echo '</td><td>'.$row['extant']; echo '</td><td>'.$row['cond']; echo '</td><td>'.$row['captured']; echo '</td><td>'.$row['images']; echo '</td>'; } ?> </div> </body> </html> Quote Link to comment https://forums.phpfreaks.com/topic/223764-creating-a-search-form/#findComment-1158206 Share on other sites More sharing options...
jirble2 Posted January 12, 2011 Author Share Posted January 12, 2011 That was search.php and there's also search.html: <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <link rel="stylesheet" type="text/css" href="index.css"/> <title>Search</title> </head> <body> <div id="banner"> <h1>Search</h1> </div> <div id="main"> <h3>Search</h3> <form action="search.php" method="post"> <input type="text" name="term" /><br /> <input type="submit" name="submit" value="Search" /> </form> </div> </body> </html> Quote Link to comment https://forums.phpfreaks.com/topic/223764-creating-a-search-form/#findComment-1158208 Share on other sites More sharing options...
jirble2 Posted January 12, 2011 Author Share Posted January 12, 2011 Oh and I should mention, even though it is probably pretty obvious, that where it says "Your search returned ?? results", I want the "??" to display the number of results returned. Quote Link to comment https://forums.phpfreaks.com/topic/223764-creating-a-search-form/#findComment-1158210 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.