_DarkLink_ Posted July 31, 2009 Share Posted July 31, 2009 Good day, My first post at this forum and I'm asking away already. Anyway, I'm experiencing some problems a project of mine: I have made an SQL database for storing info and I am using PHP to extract what is in it and display the info on a page. After displaying everything, I have also included a search engine on the page, it's fairly simple of what I need it to do: - User enters a search term and selects a choice in one of the three radio buttons, - The processing code looks up the search term under the column that the user picked out with the radio buttons, - And finally it extracts the results and displays them on the page. Okay so, here is my SQL code: # # Data for table `coords` # DROP TABLE IF EXISTS coords; CREATE TABLE coords ( id int(10) NOT NULL auto_increment, tag varchar(5) NOT NULL, guild varchar(27) NOT NULL, name varchar(45) NOT NULL, base varchar(16) NOT NULL, econ int(6) NOT NULL, maxecon int(6) NOT NULL, location varchar(12) NOT NULL, comment varchar(100) NOT NULL, ipaddress varchar(45) NOT NULL, date int(12) NOT NULL, PRIMARY KEY (id), UNIQUE id (id), UNIQUE location (location), UNIQUE location_2 (location) ); After creating the database tables and columns, I have made the PHP and HTML page. database.php <? include ('db.php'); // for db details $self = $_SERVER['PHP_SELF']; //the $self variable equals this file $ipaddress = ("$_SERVER[REMOTE_ADDR]"); //the $ipaddress var equals users IP $connect = mysql_connect($host,$username,$password) or die('<p class="error">Unable to connect to the database server at this time.</p>'); mysql_select_db($database,$connect) or die('<p class="error">Unable to connect to the database at this time.</p>'); //delete any of same address $time = time(); $point = ($time - 604800); $query = "DELETE FROM coords WHERE date < ".$point; //run the query. if it fails, display error $result = @mysql_query("$query"); //End ... (head tags and html tags.) ... <body> <? include ('db.php'); // for db details $con = mysql_connect($host, $username, $password) or die("Failed to connect to MySQL Server. Error: " . mysql_error()); mysql_select_db($database, $con) or die("Failed to connect to database {$database}. Error: " . mysql_error()); if ($_GET['search']){ $search = stripslashes($_GET['search']); $types = "types of search"; switch ($_GET['name']) { case 'on': $types = "name"; break; default: $types = " "; break; } switch ($_GET['tag']) { case 'on': $types = "tag"; break; default: $types = " "; break; } switch ($_GET['guild']) { case 'on': $types = "guild"; break; default: $types = " "; break; } $connect = mysql_connect($host,$username,$password) or die('<p class="error">Unable to connect to the database server at this time.</p>'); mysql_select_db($database,$connect) or die('<p class="error">Unable to connect to the database at this time.</p>'); $querya = "SELECT * FROM coords WHERE `{$types}` LIKE '%{$search}%' ORDER BY `{$types}`;"; $result = @mysql_query("$querya") or die("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$searchSQL}"); ?> <center> <table> <table width="83%" border="1"> <tr> <td ><b>Tag</b></td> <td ><b>Guild</b></td> <td ><b>Player</b></td> <td ><b>Base</b></td> <td ><b>Location</b></td> <td ><b>Econ</b></td> <td ><b>Comments</b></td> </tr> <? while ($row = mysql_fetch_array($result)) { $tag = stripslashes($row['tag']); $guild = stripslashes($row['guild']); $name = stripslashes($row['name']); $base = stripslashes($row['base']); $location = stripslashes($row['location']); $comment = stripslashes($row['comment']); $id = stripslashes($row['id']); $econ = stripslashes($row['econ']); $maxecon = stripslashes($row['maxecon']); echo('<tr><center><td>['.$tag.']</td><td>'.$guild.'</td><td>'.$name.'</td><td>'.$base.'</td><td><a href="http://delta.astroempires.com/map.aspx?loc='.$location.'">'.$location.'</a></td><td>'.$econ.'/'.$maxecon.'</td><td>'.$comment.'</td></center></tr>'); } echo "<a href=\"database.php\">New Search</a>"; ?> </table> </table> <?} else{ ?> </center> <!--start inputbox--> <table width="83%"> <tr> <td style="background: url(http://www.teamdelta.byethost12.com/barbg.jpg) repeat-x top;"> <center><b><font color="#F3EC84">»Search«</font></b></center> </td> </tr> <tr> <td style="background: #222222;"><!-- at the bottom of the page, we display our comment form --> <form method="GET" action="<?php echo $_SERVER['PHP_SELF'];?>" name="searchForm"> <table border ="0" width="100%"> <tr> <td><center> Search For: <input type="text" name="search"> Player <input type="radio" name="name" value="on"> | Guild Tag <input type="radio" name="tag" value="on"> | Guild Name <input type="radio" name="guild" value="on"> <input type="submit" name="submit" value="Search"> </tr> </center> </td> </tr> </form> </td> </tr> </table> <!--end input box--> <center> <table> <table width="83%" border="1"> <tr> <td ><b>Tag</b></td> <td ><b>Guild</b></td> <td ><b>Player</b></td> <td ><b>Base</b></td> <td ><b>Location</b></td> <td ><b>Econ</b></td> <td ><b>Comments</b></td> </tr> <? $query = "SELECT * FROM coords ORDER BY `id` DESC;"; $result = @mysql_query("$query") or die('<p class="error">There was an unexpected error grabbing routes from the database.</p>'); // while we still have rows from the db, display them while ($row = mysql_fetch_array($result)) { $tag = stripslashes($row['tag']); $guild = stripslashes($row['guild']); $name = stripslashes($row['name']); $base = stripslashes($row['base']); $location = stripslashes($row['location']); $comment = stripslashes($row['comment']); $id = stripslashes($row['id']); $econ = stripslashes($row['econ']); $maxecon = stripslashes($row['maxecon']); echo('<tr><center><td>['.$tag.']</td><td>'.$guild.'</td><td>'.$name.'</td><td>'.$base.'</td><td><a href="http://delta.astroempires.com/map.aspx?loc='.$location.'">'.$location.'</a></td><td>'.$econ.'/'.$maxecon.'</td><td>'.$comment.'</td></center></tr>'); } ?> </table> </table> <? } ?> </center> </body> </html> ?> The problem now is that when I enter a search term, it seems to be searching a space string for the column. (' ') Any help is appreciated, Thank you in advance. Link to comment https://forums.phpfreaks.com/topic/168293-solved-php-html-and-sql-search-form/ Share on other sites More sharing options...
haku Posted July 31, 2009 Share Posted July 31, 2009 Can you scale that code down to the search form and the search query? It's a lot of code to dig through for those two things. Link to comment https://forums.phpfreaks.com/topic/168293-solved-php-html-and-sql-search-form/#findComment-887662 Share on other sites More sharing options...
_DarkLink_ Posted July 31, 2009 Author Share Posted July 31, 2009 Good day, My first post at this forum and I'm asking away already. Anyway, I'm experiencing some problems a project of mine: I have made an SQL database for storing info and I am using PHP to extract what is in it and display the info on a page. After displaying everything, I have also included a search engine on the page, it's fairly simple of what I need it to do: - User enters a search term and selects a choice in one of the three radio buttons, - The processing code looks up the search term under the column that the user picked out with the radio buttons, - And finally it extracts the results and displays them on the page. Okay so, here is my SQL code: # # Data for table `coords` # DROP TABLE IF EXISTS coords; CREATE TABLE coords ( id int(10) NOT NULL auto_increment, tag varchar(5) NOT NULL, guild varchar(27) NOT NULL, name varchar(45) NOT NULL, base varchar(16) NOT NULL, econ int(6) NOT NULL, maxecon int(6) NOT NULL, location varchar(12) NOT NULL, comment varchar(100) NOT NULL, ipaddress varchar(45) NOT NULL, date int(12) NOT NULL, PRIMARY KEY (id), UNIQUE id (id), UNIQUE location (location), UNIQUE location_2 (location) ); After creating the database tables and columns, I have made the PHP page with HTML included in it. database.php A self updating system. <? include ('db.php'); // for db details $self = $_SERVER['PHP_SELF']; //the $self variable equals this file $ipaddress = ("$_SERVER[REMOTE_ADDR]"); //the $ipaddress var equals users IP $connect = mysql_connect($host,$username,$password) or die('<p class="error">Unable to connect to the database server at this time.</p>'); mysql_select_db($database,$connect) or die('<p class="error">Unable to connect to the database at this time.</p>'); //delete any of same address $time = time(); $point = ($time - 604800); $query = "DELETE FROM coords WHERE date < ".$point; //run the query. if it fails, display error $result = @mysql_query("$query"); //End ... (HTML tags and head tags.) ... The default display of the page when you enter it, the search form and the data is being displayed (inside the body tags). <!--start inputbox--> <table width="83%"> <tr> <td style="background: url(http://www.teamdelta.byethost12.com/barbg.jpg) repeat-x top;"> <center><b><font color="#F3EC84">»Search«</font></b></center> </td> </tr> <tr> <td style="background: #222222;"><!-- at the bottom of the page, we display our comment form --> <form method="GET" action="<?php echo $_SERVER['PHP_SELF'];?>" name="searchForm"> <table border ="0" width="100%"> <tr> <td><center> Search For: <input type="text" name="search"> Player <input type="radio" name="name" value="on"> | Guild Tag <input type="radio" name="tag" value="on"> | Guild Name <input type="radio" name="guild" value="on"> <input type="submit" name="submit" value="Search"> </tr> </center> </td> </tr> </form> </td> </tr> </table> <!--end input box--> <center> <table> <table width="83%" border="1"> <tr> <td ><b>Tag</b></td> <td ><b>Guild</b></td> <td ><b>Player</b></td> <td ><b>Base</b></td> <td ><b>Location</b></td> <td ><b>Econ</b></td> <td ><b>Comments</b></td> </tr> <? $query = "SELECT * FROM coords ORDER BY `id` DESC;"; $result = @mysql_query("$query") or die('<p class="error">There was an unexpected error grabbing routes from the database.</p>'); // while we still have rows from the db, display them while ($row = mysql_fetch_array($result)) { $tag = stripslashes($row['tag']); $guild = stripslashes($row['guild']); $name = stripslashes($row['name']); $base = stripslashes($row['base']); $location = stripslashes($row['location']); $comment = stripslashes($row['comment']); $id = stripslashes($row['id']); $econ = stripslashes($row['econ']); $maxecon = stripslashes($row['maxecon']); echo('<tr><center><td>['.$tag.']</td><td>'.$guild.'</td><td>'.$name.'</td><td>'.$base.'</td><td><a href="http://delta.astroempires.com/map.aspx?loc='.$location.'">'.$location.'</a></td><td>'.$econ.'/'.$maxecon.'</td><td>'.$comment.'</td></center></tr>'); } ?> </table> </table> <? } ?> </center> </body> </html> The search processing code when the search form has been submitted. <? include ('db.php'); // for db details $con = mysql_connect($host, $username, $password) or die("Failed to connect to MySQL Server. Error: " . mysql_error()); mysql_select_db($database, $con) or die("Failed to connect to database {$database}. Error: " . mysql_error()); if ($_GET['search']){ $search = stripslashes($_GET['search']); $types = "types of search"; switch ($_GET['name']) { case 'on': $types = "name"; break; default: $types = " "; break; } switch ($_GET['tag']) { case 'on': $types = "tag"; break; default: $types = " "; break; } switch ($_GET['guild']) { case 'on': $types = "guild"; break; default: $types = " "; break; } $connect = mysql_connect($host,$username,$password) or die('<p class="error">Unable to connect to the database server at this time.</p>'); mysql_select_db($database,$connect) or die('<p class="error">Unable to connect to the database at this time.</p>'); $querya = "SELECT * FROM coords WHERE `{$types}` LIKE '%{$search}%' ORDER BY `{$types}`;"; $result = @mysql_query("$querya") or die("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$searchSQL}"); ?> After processing, display the results on the page. <center> <table> <table width="83%" border="1"> <tr> <td ><b>Tag</b></td> <td ><b>Guild</b></td> <td ><b>Player</b></td> <td ><b>Base</b></td> <td ><b>Location</b></td> <td ><b>Econ</b></td> <td ><b>Comments</b></td> </tr> <? while ($row = mysql_fetch_array($result)) { $tag = stripslashes($row['tag']); $guild = stripslashes($row['guild']); $name = stripslashes($row['name']); $base = stripslashes($row['base']); $location = stripslashes($row['location']); $comment = stripslashes($row['comment']); $id = stripslashes($row['id']); $econ = stripslashes($row['econ']); $maxecon = stripslashes($row['maxecon']); echo('<tr><center><td>['.$tag.']</td><td>'.$guild.'</td><td>'.$name.'</td><td>'.$base.'</td><td><a href="http://delta.astroempires.com/map.aspx?loc='.$location.'">'.$location.'</a></td><td>'.$econ.'/'.$maxecon.'</td><td>'.$comment.'</td></center></tr>'); } echo "<a href=\"database.php\">New Search</a>"; ?> </table> </table> <?} else{ ?> </center> The whole thing put together would be. <? include ('db.php'); // for db details $self = $_SERVER['PHP_SELF']; //the $self variable equals this file $ipaddress = ("$_SERVER[REMOTE_ADDR]"); //the $ipaddress var equals users IP $connect = mysql_connect($host,$username,$password) or die('<p class="error">Unable to connect to the database server at this time.</p>'); mysql_select_db($database,$connect) or die('<p class="error">Unable to connect to the database at this time.</p>'); //delete any of same address $time = time(); $point = ($time - 604800); $query = "DELETE FROM coords WHERE date < ".$point; //run the query. if it fails, display error $result = @mysql_query("$query"); //End ?> ... (HTML tags and head tags.) ... <body> <? include ('db.php'); // for db details $con = mysql_connect($host, $username, $password) or die("Failed to connect to MySQL Server. Error: " . mysql_error()); mysql_select_db($database, $con) or die("Failed to connect to database {$database}. Error: " . mysql_error()); if ($_GET['search']){ $search = stripslashes($_GET['search']); $types = "types of search"; switch ($_GET['name']) { case 'on': $types = "name"; break; default: $types = " "; break; } switch ($_GET['tag']) { case 'on': $types = "tag"; break; default: $types = " "; break; } switch ($_GET['guild']) { case 'on': $types = "guild"; break; default: $types = " "; break; } $connect = mysql_connect($host,$username,$password) or die('<p class="error">Unable to connect to the database server at this time.</p>'); mysql_select_db($database,$connect) or die('<p class="error">Unable to connect to the database at this time.</p>'); $querya = "SELECT * FROM coords WHERE `{$types}` LIKE '%{$search}%' ORDER BY `{$types}`;"; $result = @mysql_query("$querya") or die("There was an error.<br/>" . mysql_error() . "<br />SQL Was: {$searchSQL}"); ?> <center> <table> <table width="83%" border="1"> <tr> <td ><b>Tag</b></td> <td ><b>Guild</b></td> <td ><b>Player</b></td> <td ><b>Base</b></td> <td ><b>Location</b></td> <td ><b>Econ</b></td> <td ><b>Comments</b></td> </tr> <? while ($row = mysql_fetch_array($result)) { $tag = stripslashes($row['tag']); $guild = stripslashes($row['guild']); $name = stripslashes($row['name']); $base = stripslashes($row['base']); $location = stripslashes($row['location']); $comment = stripslashes($row['comment']); $id = stripslashes($row['id']); $econ = stripslashes($row['econ']); $maxecon = stripslashes($row['maxecon']); echo('<tr><center><td>['.$tag.']</td><td>'.$guild.'</td><td>'.$name.'</td><td>'.$base.'</td><td><a href="http://delta.astroempires.com/map.aspx?loc='.$location.'">'.$location.'</a></td><td>'.$econ.'/'.$maxecon.'</td><td>'.$comment.'</td></center></tr>'); } echo "<a href=\"database.php\">New Search</a>"; ?> </table> </table> <?} else{ ?> </center> <!--start inputbox--> <table width="83%"> <tr> <td style="background: url(http://www.teamdelta.byethost12.com/barbg.jpg) repeat-x top;"> <center><b><font color="#F3EC84">»Search«</font></b></center> </td> </tr> <tr> <td style="background: #222222;"><!-- at the bottom of the page, we display our comment form --> <form method="GET" action="<?php echo $_SERVER['PHP_SELF'];?>" name="searchForm"> <table border ="0" width="100%"> <tr> <td><center> Search For: <input type="text" name="search"> Player <input type="radio" name="name" value="on"> | Guild Tag <input type="radio" name="tag" value="on"> | Guild Name <input type="radio" name="guild" value="on"> <input type="submit" name="submit" value="Search"> </tr> </center> </td> </tr> </form> </td> </tr> </table> <!--end input box--> <center> <table> <table width="83%" border="1"> <tr> <td ><b>Tag</b></td> <td ><b>Guild</b></td> <td ><b>Player</b></td> <td ><b>Base</b></td> <td ><b>Location</b></td> <td ><b>Econ</b></td> <td ><b>Comments</b></td> </tr> <? $query = "SELECT * FROM coords ORDER BY `id` DESC;"; $result = @mysql_query("$query") or die('<p class="error">There was an unexpected error grabbing routes from the database.</p>'); // while we still have rows from the db, display them while ($row = mysql_fetch_array($result)) { $tag = stripslashes($row['tag']); $guild = stripslashes($row['guild']); $name = stripslashes($row['name']); $base = stripslashes($row['base']); $location = stripslashes($row['location']); $comment = stripslashes($row['comment']); $id = stripslashes($row['id']); $econ = stripslashes($row['econ']); $maxecon = stripslashes($row['maxecon']); echo('<tr><center><td>['.$tag.']</td><td>'.$guild.'</td><td>'.$name.'</td><td>'.$base.'</td><td><a href="http://delta.astroempires.com/map.aspx?loc='.$location.'">'.$location.'</a></td><td>'.$econ.'/'.$maxecon.'</td><td>'.$comment.'</td></center></tr>'); } ?> </table> </table> <? } ?> </center> </body> </html> The problem is that it is searching for a space string in the columns of the table. (' ') [EDIT] I have sorted out the code a little bit, hope it helps.[EDIT] Any help is appreciated, Thank you in advance. Link to comment https://forums.phpfreaks.com/topic/168293-solved-php-html-and-sql-search-form/#findComment-887673 Share on other sites More sharing options...
_DarkLink_ Posted July 31, 2009 Author Share Posted July 31, 2009 Well it seems that I have successfully found the problem in my search engine. Here is the solution: I should have replaced this: if ($_GET['search']){ $search = stripslashes($_GET['search']); $types = "types of search"; switch ($_GET['name']) { case 'on': $types = "name"; break; default: $types = " "; break; } switch ($_GET['tag']) { case 'on': $types = "tag"; break; default: $types = " "; break; } switch ($_GET['guild']) { case 'on': $types = "guild"; break; default: $types = " "; break; } ... Search For: <input type="text" name="search"> Player <input type="radio" name="name" value="on"> | Guild Tag <input type="radio" name="tag" value="on"> | Guild Name <input type="radio" name="guild" value="on"> <input type="submit" name="submit" value="Search"> With: if ($_GET['search'] == "do"){ $search = stripslashes($_GET['searchterm']); $types = "types of search"; if ($_GET['name'] == "on"){ $types = "name"; } else if ($_GET['tag'] == "on"){ $types = "tag"; } else if ($_GET['guild'] == "on"){ $types = "guild"; } else { $types = ' '; } ... Search For: <input type="text" name="searchterm"> Player <input type="radio" name="name" value="on"> | Guild Tag <input type="radio" name="tag" value="on"> | Guild Name <input type="radio" name="guild" value="on"> <input type="hidden" name="search" value="do"> <input type="submit" value="Search"> But anyway, this topic is solved, thank you for those that have read this, took interest in this and tried to help me. Link to comment https://forums.phpfreaks.com/topic/168293-solved-php-html-and-sql-search-form/#findComment-887850 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.