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! Link to comment https://forums.phpfreaks.com/topic/211735-left-join-help-displaying-tables-from-mysql-database/ 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) Link to comment https://forums.phpfreaks.com/topic/211735-left-join-help-displaying-tables-from-mysql-database/#findComment-1103724 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 Link to comment https://forums.phpfreaks.com/topic/211735-left-join-help-displaying-tables-from-mysql-database/#findComment-1103737 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) Link to comment https://forums.phpfreaks.com/topic/211735-left-join-help-displaying-tables-from-mysql-database/#findComment-1103758 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) Link to comment https://forums.phpfreaks.com/topic/211735-left-join-help-displaying-tables-from-mysql-database/#findComment-1103823 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... Link to comment https://forums.phpfreaks.com/topic/211735-left-join-help-displaying-tables-from-mysql-database/#findComment-1103826 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.