Link to home
Start Free TrialLog in
Avatar of peps03
peps03

asked on

Need to fetch data from 2 tables, mysql

Hi, i have 2 tables, pages and keywords.

pages has a unique primary key (id)
keywords does not have a primary unique key. it stores keywords based on the pages.id

so an id can occur multiple times with different keywords.

i would like to output the pages id and some other columns with the keywords and date corresponding to the ids from the keywords table and add the keywords and date.

<table width="100%" border="0" cellspacing="2" cellpadding="2">
<?php
include 'connect.php';

$query = 	"SELECT pages.id, pages.aname, pages.bname, pages.taganame, pages.tagbname, 
				keywords.id keywords.keywords, keywords.date 
			FROM pages, keywords 
			WHERE pages.id = keywords.id";

		$result = mysql_query($query);
		while($output = mysql_fetch_array($result)){
			
			$id = 	 $output['id'];;
			$aname = $output['aname'];
			$bname = $output['bname'];
			$taganame = $output['taganame'];
			$tagbname = $output['tagbname'];
			$keywords = $output['keywords'];
			$date = $output['date'];
			
			echo "	
				  <tr>
					<td>$id</td>
					<td>$aname/$bname</td>
					<td>$taganame/$tagbname</td>
					<td>$keywords</td>
					<td>$date</td>
				  </tr>
			";
		}
?>
</table>

Open in new window


It outputs nothing, but also no errors.
What am i doing wrong?
Thanks!
Avatar of Chris Sandrini
Chris Sandrini
Flag of Switzerland image

"SELECT pages.id, pages.aname, pages.bname, pages.taganame, pages.tagbname, 
				keywords.keywords, keywords.date 
			FROM pages
                        LEFT JOIN keywords ON pages.id = keywords.id"

Open in new window


I guess the problem is that pages.id is unique. so there is only 1 entry. But keywords.id can have multiple entries. Try to join the thables using left join.
Avatar of innovasoft
innovasoft

Does your server suppress error messages? Maybe there are some, but the server doesn't show them.

What is the output of that:

<?php
include 'connect.php';

$query = 	"SELECT pages.id, pages.aname, pages.bname, pages.taganame, pages.tagbname, 
				keywords.id keywords.keywords, keywords.date 
			FROM pages, keywords 
			WHERE pages.id = keywords.id";

$result = mysql_query($query);

echo "Recordcount: " . mysql_num_rows($result);


?>

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of innovasoft
innovasoft

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of peps03

ASKER

Haha, thanks! That did the trick!

Checked it twice.. Well it works now!

Thanks