noise Posted May 26, 2009 Share Posted May 26, 2009 Hi First of all I'm very new with php and mysql queries and would appreciate all the help i can get. I have managed to take the following search script sample from the internet and changed a few fields to do what i want. http://www.webreference.com/programm...rch/index.html By changing the following script I achieved to query the the first name and lastname and display all the other added tables I added as well. Please see my changed code below. You may search either by first or last name <form method="post" action="search.php?go" id="searchform"> <p> <input type="text" name="name"> </p> <p> <input type="submit" name="submit" value="Search"> </p> </form> <?php if(isset($_POST['submit'])){ if(isset($_GET['go'])){ if(preg_match("/[A-Z | a-z]+/", $_POST['name'])){ $name=$_POST['name']; //connect to the database $db=mysql_connect ("localhost", "fresheye_staff", "q23652") or die ('I cannot connect to the database because: ' . mysql_error()); //-select the database to use $mydb=mysql_select_db("fresheye_staff"); //-query the database table $sql="SELECT ID, FirstName, LastName FROM staff WHERE FirstName LIKE '%" . $name . "%' OR LastName LIKE '%" . $name ."%'"; //-run the query against the mysql query function $result=mysql_query($sql); //-count results $numrows=mysql_num_rows($result); echo "<p>" .$numrows . " results found for " . stripslashes($name) . "</p>"; //-create while loop and loop through result set while($row=mysql_fetch_array($result)){ $FirstName =$row['FirstName']; $LastName=$row['LastName']; $ID=$row['ID']; //-display the result of the array echo "<ul>\n"; echo "<li>" . "<a href=\"search.php?id=$ID\">" .$FirstName . " " . $LastName . "</a></li>\n"; echo "</ul>"; } } else{ echo "<p>Please enter a search query</p>"; } } } if(isset($_GET['by'])){ $letter=$_GET['by']; //connect to the database $db=mysql_connect ("localhost", "fresheye_staff", "q23652") or die ('I cannot connect to the database because: ' . mysql_error()); //-select the database to use $mydb=mysql_select_db("fresheye_staff"); //-query the database table $sql="SELECT ID, FirstName, LastName FROM staff WHERE FirstName LIKE '%" . $letter . "%' OR LastName LIKE '%" . $letter ."%'"; //-run the query against the mysql query function $result=mysql_query($sql); //-count results $numrows=mysql_num_rows($result); echo "<p>" .$numrows . " results found for " . $letter . "</p>"; //-create while loop and loop through result set while($row=mysql_fetch_array($result)){ $FirstName =$row['FirstName']; $LastName=$row['LastName']; $ID=$row['ID']; //-display the result of the array echo "<ul>\n"; echo "<li>" . "<a href=\"search.php?id=$ID\">" .$FirstName . " " . $LastName . "</a></li>\n"; echo "</ul>"; } } if(isset($_GET['id'])){ $contactid=$_GET['id']; //connect to the database $db=mysql_connect ("localhost", "fresheye_staff", "q23652") or die ('I cannot connect to the database because: ' . mysql_error()); //-select the database to use $mydb=mysql_select_db("fresheye_staff"); //-query the database table $sql="SELECT * FROM staff WHERE ID=" . $contactid; //-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)){ $FirstName =$row['FirstName']; $LastName=$row['LastName']; $idnumber=$row['idnumber']; $position=$row['position']; $Recommended=$row['Recommended']; //-display the result of the array echo "<ul>\n"; echo "<li><h4>Full Name : " . $FirstName . " " . $LastName . "</h4></li>\n"; echo "<li><h4>ID Number : " . $idnumber . "</h4></li>\n"; echo "<li><h4>Position : " . $position . "</h4></li>\n"; echo "<li><h4>Recommended : " . $Recommended . "</h4></li>\n"; echo "</ul>"; } } ?> All I need to do now is change the code a little bit to quary(search) the idnumber table in the database and NOT the first name and lastname as it is currently doing. Can anybody please help me figure this out. I will greatly appreciate it. Link to comment https://forums.phpfreaks.com/topic/159658-need-help-with-a-search-query/ Share on other sites More sharing options...
Alt_F4 Posted May 28, 2009 Share Posted May 28, 2009 sorry can you clarify what it is that you want to do here? I'm not sure I fully understand. Link to comment https://forums.phpfreaks.com/topic/159658-need-help-with-a-search-query/#findComment-844582 Share on other sites More sharing options...
roopurt18 Posted May 28, 2009 Share Posted May 28, 2009 It would be a good idea if you removed your database credentials from your script before posting it on a public forum. Link to comment https://forums.phpfreaks.com/topic/159658-need-help-with-a-search-query/#findComment-844592 Share on other sites More sharing options...
fullyscintilla Posted May 28, 2009 Share Posted May 28, 2009 seem to me that you have alot of unneccesary code here.. If you want to have a form to search your database I can help you with that but i won't filter through that unneeded code. Link to comment https://forums.phpfreaks.com/topic/159658-need-help-with-a-search-query/#findComment-844593 Share on other sites More sharing options...
waynew Posted May 28, 2009 Share Posted May 28, 2009 You connect to the database on several occasions? Link to comment https://forums.phpfreaks.com/topic/159658-need-help-with-a-search-query/#findComment-844598 Share on other sites More sharing options...
kickstart Posted May 29, 2009 Share Posted May 29, 2009 Hi Had a look and nothing much:- You may search either by first or last name <form method="post" action="search.php?go" id="searchform"> <p> <input type="text" name="name"> </p> <p> <input type="submit" name="submit" value="Search"> </p> </form> <?php //connect to the database $db=mysql_connect ("localhost", "fresheye_staff", "q23652") or die ('I cannot connect to the database because: ' . mysql_error()); //-select the database to use $mydb=mysql_select_db("fresheye_staff"); if(isset($_POST['submit'])) { if(isset($_GET['go'])) { if(preg_match("/[A-Z | a-z]+/", $_POST['name'])) { $name=mysql_real_escape_string($_POST['name']); //-query the database table $sql="SELECT ID, FirstName, LastName FROM staff WHERE FirstName LIKE '%$name%' OR LastName LIKE '%$name%'"; //-run the query against the mysql query function $result=mysql_query($sql); //-count results $numrows=mysql_num_rows($result); echo "<p>$numrows results found for " . stripslashes($name) . "</p>"; //-create while loop and loop through result set echo "<ul>\n"; while($row=mysql_fetch_array($result)) { $FirstName =$row['FirstName']; $LastName=$row['LastName']; $ID=$row['ID']; //-display the result of the array echo "<li>" . "<a href=\"search.php?id=$ID\">$FirstName $LastName</a></li>\n"; } echo "</ul>"; } else { echo "<p>Please enter a search query</p>"; } } } else { if(isset($_GET['by'])) { $letter=mysql_real_escape_string($_GET['by']); //-query the database table $sql="SELECT ID, FirstName, LastName FROM staff WHERE FirstName LIKE '%$letter%' OR LastName LIKE '%$letter%'"; //-run the query against the mysql query function $result=mysql_query($sql); //-count results $numrows=mysql_num_rows($result); echo "<p>$numrows results found for $letter</p>"; //-create while loop and loop through result set echo "<ul>\n"; while($row=mysql_fetch_array($result)) { $FirstName =$row['FirstName']; $LastName=$row['LastName']; $ID=$row['ID']; //-display the result of the array echo "<li>" . "<a href=\"search.php?id=$ID\">$FirstName $LastName</a></li>\n"; } echo "</ul>"; } if(isset($_GET['id'])) { $contactid=((is_numeric($_GET['id'])) ? $_GET['id'] : 0); //-query the database table $sql="SELECT FirstName, LastName, idnumber, position, Recommended FROM staff WHERE ID=" . $contactid; //-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)) { $FirstName =$row['FirstName']; $LastName=$row['LastName']; $idnumber=$row['idnumber']; $position=$row['position']; $Recommended=$row['Recommended']; //-display the result of the array echo "<ul>\n"; echo "<li><h4>Full Name : $FirstName $LastName</h4></li>\n"; echo "<li><h4>ID Number : $idnumber</h4></li>\n"; echo "<li><h4>Position : $position</h4></li>\n"; echo "<li><h4>Recommended : $Recommended </h4></li>\n"; echo "</ul>"; } } } ?> All the best Keith Link to comment https://forums.phpfreaks.com/topic/159658-need-help-with-a-search-query/#findComment-844629 Share on other sites More sharing options...
fullyscintilla Posted May 29, 2009 Share Posted May 29, 2009 lets wait for the OP to give us some logic, we still need to know exactly what he wants to happen.. He wants to search for an ID in his database... but based on what?? First and last name? If the sky is purple?? we don't know yet.. Link to comment https://forums.phpfreaks.com/topic/159658-need-help-with-a-search-query/#findComment-844636 Share on other sites More sharing options...
roopurt18 Posted May 29, 2009 Share Posted May 29, 2009 He just wants to modify it to search on fields other than firstname and lastname. In addition to his search form, he'll need to modify his SQL statement: $sql="SELECT ID, FirstName, LastName FROM staff WHERE FirstName LIKE '%" . $name . "%' OR LastName LIKE '%" . $name ."%'"; Link to comment https://forums.phpfreaks.com/topic/159658-need-help-with-a-search-query/#findComment-844637 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.