pablo1988

Muddy_Funster would you do me a massive favor and add the code into the below please? I don't want to mess it up. It would be hugely appreciated! <?php if(isset($_POST['submit'])){ $fname = $_POST['fname']; $lname = $_POST['lname']; $skill = $_POST['skill']; //connect to the database $db=mysql_connect ("127.0.0.1", "root", "") or die ('I cannot connect to the database because: ' . mysql_error()); //select the database to use $mydb=mysql_select_db("resource matrix"); //query the database table $sql="SELECT DISTINCT First_Name, Last_Name, l.Resource_ID FROM ((resource l inner join resource_skill ln on l.Resource_ID = ln.Resource_ID) inner join skill n on ln.Skill_ID = n.Skill_ID) WHERE First_Name LIKE '{$fname}' OR Last_Name LIKE '{$lname}' OR Skill_Name LIKE '%{$skill}%'";

Muddy_Funster I am very new to php and don't know how I would make use of what you have written. Could you show me please?

I assume it needs to have some code to say if blank ignore, rather than bring back all perhaps?

Thanks CPD, I have applied % to both sides of skill only, however when I search by name and leave skill blank it brings back all records. I assume because I have used % on both sides it takes anything?? $sql="SELECT DISTINCT First_Name, Last_Name, l.Resource_ID FROM ((resource l inner join resource_skill ln on l.Resource_ID = ln.Resource_ID) inner join skill n on ln.Skill_ID = n.Skill_ID) WHERE First_Name LIKE '{$fname}' OR Last_Name LIKE '{$lname}' OR Skill_Name LIKE '%{$skill}%'"; See above code. Please can you advise what I should do to fix this? Thanks.

Hi, My code below only allows me to search exact terms and therefore does not allow for spelling mistakes of names etc. Please can somebody advise how I can alter the code below to allow a user to search part of the search criteria and still retrieve results e.g. a search for "business" would return "business analysis", "business architecture" and so on. Any help would be greatly appreciated. $sql="SELECT DISTINCT First_Name, Last_Name, l.Resource_ID FROM ((resource l inner join resource_skill ln on l.Resource_ID = ln.Resource_ID) inner join skill n on ln.Skill_ID = n.Skill_ID) WHERE First_Name LIKE '$fname' OR Last_Name LIKE '$lname' OR Skill_Name LIKE '$skill'"; Thanks a lot! Paul

Can anybody see how to fix the table below. My results are being stacked horizontally rather than vertically. I have attached a picture for you to see what I mean. Thanks //SKILL - query the database table $sql="SELECT Skill_Name, Rating FROM resource_skill ln inner join skill n on ln.Skill_ID = n.Skill_ID WHERE ln.Resource_ID=" . $contactid; //SKILL - run the query against the mysql query function $result=mysql_query($sql) or die(mysql_error() . '<br />' . $sql); echo "<table id=skill>"; echo "<tr>"; echo "<th>Skill</th>"; echo "<th>Rating</th>"; echo "</tr>"; //create while loop and loop through result set while($row=mysql_fetch_array($result)){ $Skill_Name=$row['Skill_Name']; $Rating=$row['Rating']; //display the result of the array echo "<ul>\n"; echo "<td>" . "" .$Skill_Name . " <td>" . $Rating . "</td></td>\n"; echo "</ul>"; } } echo "</tr>"; echo "</table>"; ?>

Does anybody know how to show the below php results in a table format? <?php if(isset($_POST['submit'])){ if(isset($_GET['go'])){ $fname = $_POST['fname']; $lname = $_POST['lname']; $skill = $_POST['skill']; //connect to the database $db=mysql_connect ("127.0.0.1", "root", "") or die ('I cannot connect to the database because: ' . mysql_error()); //select the database to use $mydb=mysql_select_db("resource matrix"); //query the database table $sql="SELECT DISTINCT First_Name, Last_Name, l.Resource_ID FROM ((resource l inner join resource_skill ln on l.Resource_ID = ln.Resource_ID) inner join skill n on ln.Skill_ID = n.Skill_ID) WHERE First_Name LIKE '$fname' OR Last_Name LIKE '$lname' OR Skill_Name LIKE '$skill'"; //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)){ $First_Name =$row['First_Name']; $Last_Name=$row['Last_Name']; $Resource_ID=$row['Resource_ID']; //display the result of the array echo "<ul>\n"; echo "<li>" . "<a href=\"a.php?id=$Resource_ID\">" .$First_Name . " " . $Last_Name . "</a></li>\n"; echo "</ul>"; } } else{ echo "<p>Please enter a search query</p>"; } }

I am trying to add two search boxes to the below code. I need First Name, Last Name and Skill. I have added the boxes and connected fname (first_name) but am not sure how to link the other two correctly. Can anybody please help? I think it's something to do with the bold red text. Thanks <h3>Search Contacts Details</h3> <p>You may search either by first or last name</p> <form method="post" action="a.php?go" id="searchform"> <input type="text" name="fname"> <input type="text" name="lname"> <input type="text" name="skill"> <input type="submit" name="submit" value="Search"> </form> <?php if(isset($_POST['submit'])){ if(isset($_GET['go'])){ if(preg_match("/^[ a-zA-Z]+/", $_POST['fname'])){ $fname=$_POST['fname']; //connect to the database $db=mysql_connect ("127.0.0.1", "root", "") or die ('I cannot connect to the database because: ' . mysql_error()); //select the database to use $mydb=mysql_select_db("resource matrix"); //query the database table $sql="SELECT DISTINCT First_Name, Last_Name, l.Resource_ID FROM ((resource l inner join resource_skill ln on l.Resource_ID = ln.Resource_ID) inner join skill n on ln.Skill_ID = n.Skill_ID) WHERE First_Name LIKE '%" . $fname . "%' OR Last_Name LIKE '%" . $lname ."%' OR Skill_Name LIKE '%" . $skill ."%'";

Thanks but I got errors with that. Is there not a way of adding the DISTINCT function into the code somewhere, or GROUP BY? DISTINCT worked when I was using SELECT DISTINCT First_Name etc but not with SELECT DISTINCT* Would hugely appreciate a solution to this! Thanks.

Yes, that has no effect for some reason.

How can I stop duplication in the below code? Where do I implement the DISTINCT function? $sql="SELECT * FROM ((resource l inner join resource_skill ln on l.Resource_ID = ln.Resource_ID) inner join skill n on ln.Skill_ID = n.Skill_ID) WHERE First_Name LIKE '%" . $name . "%' OR Last_Name LIKE '%" . $name ."%' OR Skill_Name 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)){ $First_Name =$row['First_Name']; $Last_Name=$row['Last_Name']; $Resource_ID=$row['Resource_ID']; //-display the result of the array echo "<ul>\n"; echo "<li>" . "<a href=\"a.php?id=$Resource_ID\">" .$First_Name . " " . $Last_Name . "</a></li>\n"; echo "</ul>"; } }

How do you apply the distinct function with SELECT * ? I know how to use it for SELECT DISTINCT First_Name etc. But as I am using SELECT * for both queries, I'm not sure I would apply it the code.

I see, that has done it! Thanks a lot! Now for the next challenge...getting rid of the duplicates. The highlighted RED text below duplicates names dependant on the number of skills they have assigned to them. -> I need to just show the First and Last Name once. The highlighted BLUE text below duplicates all of the persons details for each skill. -> I need to show the person details once other than the skill which I need to list all assigned to the person. I'm sure it's some use of DISTINCT but not sure where and how. Thanks again AyKay47 <!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"> <title>Search Contacts</title> <style type="text/css" media="screen"> ul li{ list-style-type:none; } </style> </head> <p><body> <h3>Search Contacts Details</h3> <p>You may search either by first or last name</p> <form method="post" action="a.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['name']; //connect to the database $db=mysql_connect ("127.0.0.1", "root", "") or die ('I cannot connect to the database because: ' . mysql_error()); //-select the database to use $mydb=mysql_select_db("resource matrix"); //-query the database table $sql="SELECT * FROM ((resource l inner join resource_skill ln on l.Resource_ID = ln.Resource_ID) inner join skill n on ln.Skill_ID = n.Skill_ID) WHERE First_Name LIKE '%" . $name . "%' OR Last_Name LIKE '%" . $name ."%' OR Skill_Name 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)){ $First_Name =$row['First_Name']; $Last_Name=$row['Last_Name']; $Resource_ID=$row['Resource_ID']; //-display the result of the array echo "<ul>\n"; echo "<li>" . "<a href=\"a.php?id=$Resource_ID\">" .$First_Name . " " . $Last_Name . "</a></li>\n"; echo "</ul>"; } } else{ echo "<p>Please enter a search query</p>"; } } } //end of our letter search script if(isset($_GET['id'])){ $contactid=$_GET['id']; //connect to the database $db=mysql_connect ("127.0.0.1", "root", "") or die ('I cannot connect to the database because: ' . mysql_error()); //-select the database to use $mydb=mysql_select_db("resource matrix"); $sql="SELECT * FROM resource l inner join resource_skill ln on l.Resource_ID = ln.Resource_ID inner join skill n on ln.Skill_ID = n.Skill_ID WHERE l.Resource_ID=" . $contactid; //-run the query against the mysql query function $result=mysql_query($sql) or die(mysql_error() . '<br />' . $sql); //-create while loop and loop through result set while($row=mysql_fetch_array($result)){ $First_Name =$row['First_Name']; $Last_Name=$row['Last_Name']; $Mobile_Number=$row['Mobile_Number']; $Email_Address=$row['Email_Address']; $Level=$row['Level']; $Security_Cleared=$row['Security_Cleared']; $Contract_Type=$row['Contract_Type']; $Contract_Expiry=$row['Contract_Expiry']; $Day_Rate=$row['Day_Rate']; $Post_Code=$row['Post_Code']; $Skill_Name=$row['Skill_Name']; //-display the result of the array echo "<ul>\n"; echo "<li>" . $First_Name . " " . $Last_Name . "</li>\n"; echo "<li>" . $Mobile_Number . "</li>\n"; echo "<li>" . "<a href=mailto:" . $Email_Address . ">" . $Email_Address . "</a></li>\n"; echo "<li>" . $Level . "</li>\n"; echo "<li>" . $Security_Cleared . "</li>\n"; echo "<li>" . $Contract_Type . "</li>\n"; echo "<li>" . $Contract_Expiry . "</li>\n"; echo "<li>" . $Day_Rate . "</li>\n"; echo "<li>" . $Post_Code . "</li>\n"; echo "<li>" . $Skill_Name . "</li>\n"; echo "</ul>"; } } ?> </body> </html>

Do you mean just change Resource_ID to resource.Resource_ID (resource being one of the tables Resource_ID is located)? I have added that but get the below error: Unknown column 'resource.Resource_ID' in 'where clause' SELECT * FROM resource l inner join resource_skill ln on l.Resource_ID = ln.Resource_ID inner join skill n on ln.Skill_ID = n.Skill_ID WHERE resource.Resource_ID=4 Is that what you mean by table identifier? Thanks for your help

Thanks AyKay47, see results below: Column 'Resource_ID' in where clause is ambiguous SELECT * FROM resource l inner join resource_skill ln on l.Resource_ID = ln.Resource_ID inner join skill n on ln.Skill_ID = n.Skill_ID WHERE Resource_ID=4