pablo1988 Posted April 10, 2012 Share Posted April 10, 2012 I am getting the below error message: Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\Program Files (x86)\EasyPHP-5.3.9\www\a.php on line 78 The two issues are: 1. The red text I need to somehow use the DISTINCT function as it is duplicating a person for every skill they have. 2. The blue text is causing the error above, if I remove the join it works but assigns every possible skill to the person (because skill table and resource table are not joined). I therefore need the join there but without the error. My code is below: <!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"); //-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 Resource_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)){ $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> Quote Link to comment Share on other sites More sharing options...
yami007 Posted April 10, 2012 Share Posted April 10, 2012 probably a query error...you might use mysql_error Quote Link to comment Share on other sites More sharing options...
pablo1988 Posted April 10, 2012 Author Share Posted April 10, 2012 thanks yami007, how would I do that? I don't have much knowledge with mysql. Quote Link to comment Share on other sites More sharing options...
AyKay47 Posted April 10, 2012 Share Posted April 10, 2012 my money is on an ambiguity issue with Resource_ID, but add this to the query: $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 Resource_ID=" . $contactid; //-run the query against the mysql query function $result=mysql_query($sql) or die(mysql_error() . '<br />' . $sql); post the results. Quote Link to comment Share on other sites More sharing options...
pablo1988 Posted April 10, 2012 Author Share Posted April 10, 2012 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 Quote Link to comment Share on other sites More sharing options...
AyKay47 Posted April 10, 2012 Share Posted April 10, 2012 yah that's what I was thinking. In the WHERE clause you specify Resource_ID without a table identifier attached. A table identifier is needed from the table it's coming from. Quote Link to comment Share on other sites More sharing options...
pablo1988 Posted April 10, 2012 Author Share Posted April 10, 2012 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 Quote Link to comment Share on other sites More sharing options...
AyKay47 Posted April 10, 2012 Share Posted April 10, 2012 you have specified an alias for the tbl resource ( l ), use that. l.Resource_ID Quote Link to comment Share on other sites More sharing options...
pablo1988 Posted April 10, 2012 Author Share Posted April 10, 2012 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> Quote Link to comment Share on other sites More sharing options...
AyKay47 Posted April 10, 2012 Share Posted April 10, 2012 http://dev.mysql.com/doc/refman/5.0/en/distinct-optimization.html Quote Link to comment Share on other sites More sharing options...
pablo1988 Posted April 10, 2012 Author Share Posted April 10, 2012 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. 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.