SokrMan Posted August 25, 2010 Share Posted August 25, 2010 So I have a job table with the following columns: id, jobtext, jobdate, and id. This is how it looks right now: http://prahan.com/jobs/display.html.php I have another table called jobcategory. And another table called category. The jobcategory table includes the following columns: jobid, categoryid. The category table includes the following columns: id, name. Then jobcategory table matches together category.id and job.id. Going back to the table i need a way to display the category name with the rest of the jobs information. Maybe using LEFT JOIN? This is the code of my table page: <html><head><title>Available Jobs</title></head><body> <?php $db_host = 'localhost'; $db_user = 'root'; $db_pwd = 'pass'; $database = 'db'; $table = 'job'; if (!mysql_connect($db_host, $db_user, $db_pwd)) die("Can't connect to database"); if (!mysql_select_db($database)) die("Can't select database"); // sending query $result = mysql_query("SELECT job.id, jobtext, jobdate, name FROM job LEFT JOIN author ON author.id=job.authorid "); if (!$result) { die("Query to show fields from table failed"); } $fields_num = mysql_num_fields($result); echo "<h1>Jobs:</h1>"; echo "<table border='1'><tr>"; // printing table headers for($i=0; $i<$fields_num; $i++) { $field = mysql_fetch_field($result); echo "<td>{$field->name}</td>"; } echo "</tr>\n"; // printing table rows while($row = mysql_fetch_row($result)) { echo "<tr>"; // $row is array... foreach( .. ) puts every element // of $row to $cell variable foreach($row as $cell) echo "<td>$cell</td>"; echo "</tr>\n"; } mysql_free_result($result); ?> </body></html> Thanks in advance! Quote Link to comment Share on other sites More sharing options...
samshel Posted August 25, 2010 Share Posted August 25, 2010 U guessed it ! Something like this.. SELECT job.id, jobtext, jobdate, name, category.name FROM job LEFT JOIN author ON author.id=job.authorid LEFT JOIN jobcategory ON(jobcategory.jobid = job.id) LEFT JOIN category ON (jobcategory.category_id = category.id) Quote Link to comment Share on other sites More sharing options...
SokrMan Posted August 25, 2010 Author Share Posted August 25, 2010 Im getting this error #1052 - Column 'name' in field list is ambiguous Quote Link to comment Share on other sites More sharing options...
samshel Posted August 26, 2010 Share Posted August 26, 2010 SELECT job.id, jobtext, jobdate, job.name, category.name FROM job LEFT JOIN author ON author.id=job.authorid LEFT JOIN jobcategory ON(jobcategory.jobid = job.id) LEFT JOIN category ON (jobcategory.category_id = category.id) Quote Link to comment Share on other sites More sharing options...
SokrMan Posted August 26, 2010 Author Share Posted August 26, 2010 Hmm... SELECT job.id, jobtext, jobdate, category.name FROM job LEFT JOIN author ON author.id = job.authorid LEFT JOIN jobcategory ON ( jobcategory.jobid = job.id ) LEFT JOIN category ON ( jobcategory.categoryid = category.id ) If I use that code, the columns i see are id, jobtext, jobdate, and name (which shows the category). But I need to still see the author's name. so i should have id, jobtext, jobdate, name (which shows the author's name), and name (which shows the category) Quote Link to comment Share on other sites More sharing options...
dgoosens Posted August 26, 2010 Share Posted August 26, 2010 something like this ? SELECT job.id, jobtext, jobdate, category.name as catName, author.name as authorName FROM job LEFT JOIN author ON author.id = job.authorid LEFT JOIN jobcategory ON ( jobcategory.jobid = job.id ) LEFT JOIN category ON ( jobcategory.categoryid = category.id ) pay attention when joining different tables that have columns with same 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.