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. Quote Link to comment 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. Quote Link to comment 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. Quote Link to comment 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. Quote Link to comment 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? Quote Link to comment 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 Quote Link to comment 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.. Quote Link to comment 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 ."%'"; 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.