Trouble Retrieving Data From Multiple Tables
Posted: Sat Jan 01, 2011 7:07 pm
I have the following code which is supposed to fetch data from two separate tables and print them out. One of them(blogs) has the primary key (id) of the other table (members) as one of its columns for normalization purposes. I tried to use the join statement to facilitate the data retrieval.
Here is the error message I get:
"Could not retrieve the data becasue: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE members.id = blogs.id' at line 1.
The query being run was: SELECT blogs.title, blogs.entry, members.firstname FROM blogs, members ORDER BY title ASC WHERE members.id = blogs.id"
So I figure the join statement (WHERE members.id =blogs.id is probably deprecated. So I remove that section from the code and I do get this:
{row['title']} john {row['title']} cars smith {row['title']} sue {row['title']} smith {row['title']} cars john {row['title']} cars sue
john, smith and sue are actual database values from the firstname column of members table and cars is an entry made by john alone. Clearly, the query results don't make any sense. Any ideas about how to fix this code to achieve the intended results (that is print all blog titles, entires and the firstname of contributing member)? Any help is appreciated.
Code: Select all
<?php
//address error handling
ini_set ('display_errors', 1);
error_reporting (E_ALL & ~E_NOTICE);
//include the config file
require_once("config.php");
//Define the query. Select all rows from firstname column in members table, title column in blogs table,and entry column in blogs table, sorting in ascneding order by the title entry, knowing that the id column in mebers table is the same as the id column in blogs table.
$query = 'SELECT members.firstname, blogs.title, blogs.entry FROM members, blogs ORDER BY title ASC
WHERE members.id = blogs.id';
if ($r = mysql_query ($query)) { //Run the query.
//Retrieve and print records.
while ($row = mysql_fetch_array($r)) {
print "<p><h3>{row['title']}</h3>
{$row['entry']}<br />
{$row['firstname']}
</p>;
}
} else { //Query didn't run.
die ('<p>Could not retreive the data becasue: <b>' .mysql_error().'</b>.</p><p>The query being run was: '.$query.'</p>');
} //End of IF query.
mysql_close(); //Close the database connection.
?>Here is the error message I get:
"Could not retrieve the data becasue: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE members.id = blogs.id' at line 1.
The query being run was: SELECT blogs.title, blogs.entry, members.firstname FROM blogs, members ORDER BY title ASC WHERE members.id = blogs.id"
So I figure the join statement (WHERE members.id =blogs.id is probably deprecated. So I remove that section from the code and I do get this:
{row['title']} john {row['title']} cars smith {row['title']} sue {row['title']} smith {row['title']} cars john {row['title']} cars sue
john, smith and sue are actual database values from the firstname column of members table and cars is an entry made by john alone. Clearly, the query results don't make any sense. Any ideas about how to fix this code to achieve the intended results (that is print all blog titles, entires and the firstname of contributing member)? Any help is appreciated.