Trouble Retrieving Data from Multiple Tables

MySQL database
#1

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.

<?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 "

{row['title']}

{$row['entry']}
{$row['firstname']} ; } } else { //Query didn't run. die ('

Could not retreive the data becasue: ' .mysql_error().'.

The query being run was: '.$query.'

'); } //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.

#2

Hi there,

Try the following:

SELECT
    m.`firstname`
    , b.`title`
    , b.`entry`
FROM
    `members` AS m
INNER JOIN
    `blogs` AS b
ON
    m.`id` = b.`id`
ORDER BY
    b.`title` ASC
#3

Ok, I ran the query suggested by Smokey and it seems to work. But the desired results didn’t get displayed. All I got was a blank screen. I suspect something is wrong with the while clause that’s supposed to print out the rows and columns. Any suggestions?

#4

Try this:

[php]
$sql = " SELECT
m.firstname
, b.title
, b.entry
FROM
members AS m
INNER JOIN
blogs AS b
ON
m.id = b.id
ORDER BY
b.title ASC";
$query = mysql_query($sql);
if($query !== false && mysql_num_rows($query) > 0)
{
while(($row = mysql_fetch_assoc($query)) !== false)
{
echo “

”.$row[‘title’]."

";
echo “

”.$row[‘entry’]."
".$row[‘firstname’]."

";
}
}
else if($query == false)
{
echo “

Query was not successful because:”.mysql_error()."

";
echo “

The query being run was “”.$sql.”"

";
}
else if($query !== false && mysql_num_rows($query) == 0)
{
echo “

The query returned 0 results.

”;
}
[/php]
#5

Alright smokey I tried your suggestion and all I got was this:

Query was not successful because:Query was empty

The query being run was “”

Just as a recap, here is the script I ended up with, based on ur suggestions, which provided the above message.

<?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 m.`firstname` , b.`title` , b.`entry` FROM `members` AS m INNER JOIN `blogs` AS b ON m.`id` = b.`id` ORDER BY b.`title` ASC"; $query = mysql_query($sql); if($query !== false && mysql_num_rows($query) > 0) { while(($row = mysql_fetch_assoc($query)) !== false) { echo "

".$row['title']."

"; echo "

".$row['entry']."
".$row['firstname']."

"; } } else if($query == false) { echo "

Query was not successful because:".mysql_error()."

"; echo "

The query being run was \"".$sql."\"

"; } else if($query !== false && mysql_num_rows($query) == 0) { echo "

The query returned 0 results.

"; } mysql_close(); //Close the database connection. ?>
#6

I am a definite beinner and I am having similar problems. I have managed to construct a query to read one table and output the results as an XML file (this even tentatively validates!) (Listing one) Please excuse all the commented out code which remains from the learning process.

Listing 1.
[php]<?php

// info for dbase connectiion
$host = “localhost”;
$user = “";
$pass = "”;
$database = “*******”;

// connec to dbase
$linkID = mysql_connect($host, $user, $pass) or die(“Could not connect to host.”);
mysql_select_db($database, $linkID) or die(“Could not find database.”);

//select info from table
$query1 = “SELECT * FROM jos_properties_products”;
$query2 = “SELECT * FROM jos_properties_images”;

// give back results
$resultID = mysql_query($query2, $linkID) or die(“Data not found.”);
$num = mysql_num_rows($resultID);

//Doctype declaration

/*

*/
//output as XML
$xml_output = “<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n”;
$xml_output .= “\n”;

//create XML file

if ($num != 0) {

//set location of XML file

$file= fopen("./XML/kyerofeed.xml", “w”);
$xml_output ="<?xml version=\"1.0\" encoding=\"UTF-8\" ?>\r\n";
$xml_output .="\r\n";
//while loop looking for results
while ($row = mysql_fetch_array($resultID)) {

//Make sure only published properties listed
if ($row[“published”] == “1”) {

// if statement to look through results and returns ID info
if ($row[“id”]) {
//$xml_output .="\t[id = “” . $row[“id”] . “”]\r\n";
$xml_output .="\t\t" . $row[“id”] . “\r\n”;//$xml_output .="\t\r\n";
} else {
$xml_output .="\t\t0\r\n";//$xml_output .="\t<id =“0”>\r\n";
//$xml_output .="\t\r\n";
}

// returns date info
if ($row[“refresh_time”]) {
// $xml_output .="\t[refresh_time ="" . $row[“refresh_time”] . “”]\r\n";
$xml_output .="\t\t" . $row[“refresh_time”] . “\r\n”;//$xml_output .="\t</refresh_time>\r\n";
} else {
$xml_output .="\t\t0\r\n";//$xml_output .="\t<refresh_time =“0”>\r\n";
//$xml_output .="\t</refresh_time>\r\n";
}
// returns ref info
if ($row[“ref”]) {
// $xml_output .="\t[ref ="" . $row[“ref”] . “”]\r\n";
$xml_output .="\t\t" . $row[“ref”] . “\r\n”;//$xml_output .="\t\r\n";
} else {
$xml_output .="\t\t0\r\n";/*$xml_output .="\t<ref =“0”>\r\n";
//$xml_output .="\t\r\n"; */
}

// returns price info
if ($row[“price”]) {
//$xml_output .="\t[price ="" . $row[“price”] . “”]\r\n";
$xml_output .="\t\t" . $row[“price”] . “\r\n”;//$xml_output .="\t\r\n";
} else {
$xml_output .="\t\t0\r\n";/* $xml_output .="\t<price =“0”>\r\n";
//$xml_output .="\t\r\n"; */
}

// returns sale info
if ($row[“available”]) {
// $xml_output .="\t[available ="" . $row[“available”] . “”]\r\n";
$xml_output .="\t\tSale\r\n";//$xml_output .="\t\r\n";
} else {
$xml_output .="\t\t0\r\n";/*$xml_output .="\t[available =“Sale”]\r\n";
//$xml_output .="\t\r\n"; */
}

// returns town info
if ($row[“locality”]) {
//$xml_output .="\t[locality ="" . $row[“locality”] . “”]\r\n";
$xml_output .="\t\t" . $row[“locality”] . “\r\n”;//$xml_output .="\t\r\n";
} else {
$xml_output .="\t\t0\r\n";/*$xml_output .="\t<locality =“0”>\r\n";
//$xml_output .="\t\r\n"; */
}

// returns province info
if ($row[“province”]) {
//$xml_output .="\t[province ="" . $row[“province”] . “”]\r\n";
$xml_output .="\t\t" . $row[“province”] . “\r\n”;//$xml_output .="\t\r\n";
} else {
$xml_output .="\t\t0\r\n";/* $xml_output .="\t<province =“0”>\r\n";
//$xml_output .="\t\r\n"; */
}

// returns location info
if ($row[“address”]) {
//$xml_output .="\t[address ="" . $row[“address”] . “”]\r\n";
$xml_output .="\t\t" . $row[“address”] . “\r\n”;//$xml_output .="\t\r\n";
} else {
$xml_output .="\t\t0\r\n";/* $xml_output .="\t<address =“0”>\r\n";
//$xml_output .="\t\r\n"; */
}

// returns bedrooms info
if ($row[“bedrooms”]) {
// $xml_output .="\t[bedrooms ="" . $row[“bedrooms”] . “”]\r\n";
$xml_output .="\t\t" . $row[“bedrooms”] . “\r\n”;//$xml_output .="\t\r\n";
} else {
$xml_output .="\t\t0\r\n";/*$xml_output .="\t<bedrooms =“0”>\r\n";
//$xml_output .="\t\r\n"; */
}

// returns bathrooms info
if ($row[“bathrooms”]) {
// $xml_output .="\t[bathrooms ="" . $row[“bathrooms”] . “”]\r\n";
$xml_output .="\t\t" . $row[“bathrooms”] . “\r\n”;//$xml_output .="\t\r\n";
} else {
$xml_output .="\t\t0\r\n";/* $xml_output .="\t<bathrooms =“0”>\r\n";
//$xml_output .="\t\r\n";*/
}

// returns type info
if ($row[“type”]) {
//$xml_output .="\t[type ="" . $row[“type”] . “”]\r\n";
$xml_output .="\t\t" . $row[“type”] . “\r\n”;//$xml_output .="\t\r\n";
} else {
$xml_output .="\t\t0\r\n";/* $xml_output .="\t<type =“0”>\r\n";
//$xml_output .="\t\r\n"; */
}

// returns garage info
if ($row[“garage”]) {
//$xml_output .="\t[garage ="" . $row[“garage”] . “”]\r\n";
$xml_output .="\t\t1\r\n";//$xml_output .="\t\r\n";
} else {
$xml_output .="\t\t0\r\n";/*$xml_output .="\t<garage =“0”>\r\n";
//$xml_output .="\t\r\n"; */
}

// returns pool info
if ($row[“extra_16”]) {
$xml_output .="\t[extra_16 ="" . $row[“extra_16”] . “”]\r\n";
// $xml_output .="\t\t" . $row[“extra_16”] . “\r\n”;//$xml_output .="\t</extra_16>\r\n";
} else {
$xml_output .="\t\t0\r\n";/* $xml_output .="\t<extra_16 =“0”>\r\n";
//$xml_output .="\t</extra_16>\r\n"; */
}

// returns built area info
if ($row[“covered_area”]) {
// $xml_output .="\t[covered_area ="" . $row[“covered_area”] . “”]\r\n";
$xml_output .="\t\t" . $row[“covered_area”] . “\r\n”;//$xml_output .="\t</covered_area>\r\n";
} else {
$xml_output .="\t\t0\r\n";/*$xml_output .="\t<covered_area =“0”>\r\n";
//$xml_output .="\t</covered_area>\r\n"; */
}

// returns plot size info
if ($row[“area”]) {
//$xml_output .="\t[area ="" . $row[“area”] . “”]\r\n";
$xml_output .="\t\t" . $row[“area”] . “\r\n”;//$xml_output .="\t\r\n";
} else {
$xml_output .="\t\t0\r\n";/* $xml_output .="\t<area =“0”>\r\n";
//$xml_output .="\t\r\n"; */
}
}

}

$xml_output .="";

//write XML doc
fwrite($file, $xml_output);
//close XML doc
fclose($file);
//print results with hyperlink to link to results
echo “The Kyero XML feed has been updated.\r\n”;
//<a href=\XML"results.xml">View the XML.";
} else {
echo “No Records found”;
}
//print results
//echo $xml_output;

?>
[/php]
I tried several options for adding in data from another table by joining, for example:

[php]//select info from table
$query = “SELECT * FROM jos_properties_products, jos_properties_images WHERE jos_properties_images.parent=jos_properties_products.id” or die (“Could not join tables like this”);
[/php]

and this:

[php]//select info from table
$query = “SELECT * FROM jos_properties_products, jos_properties_images ON jos_properties_images.parent=jos_properties_products.id” or die (“Could not join tables like this”);
[/php]

and although it didn’t exit at the “or die”, the data from the second table (jos_properties_images) do not appear to have been appended.

I need to add in a couple more tables, but I am sure that when I can get the syntax right I will be OK.

I would also appreciate comment on the following, which I hope will match up a number of entries on the second table to just one ID on the first.

Listing 2.

[php]for ($count = 1; $count = 10; $count += 1) {
//read a picture file
while ($row[“parent”] = $[“id”]) {
//$xml_output .="\t[area ="" . $row[“area”] . “”]\r\n";
$xml_output .="\t\t" . $row[“rout”] . “\r\n”;//$xml_output .="\t\r\n";
} else {
$xml_output .="\t\t0\r\n"; $xml_output .="\t<area =“0”>\r\n" or die (“Invalid code”);
//$xml_output .="\t\r\n";
}
[/php]

Any help greatly appreciated. This is my first attempt at writing php script, I am almost 60 years old and the grey cells aren’t as nimble as they used to be.

Many thanks

Dave
}

#7

drayarms,

Apologies for the late reply, but if you haven’t already realised - you didn’t quite copy my code. You have put the SQL query in a variable called $query, the rest of my code uses $sql. Hopefully this sorts it.

DaveinSpain,

Sorry, but you really should start a new post as although your issue falls in the same category as the title of this post, it requires a different line of thought and we can’t address multiple issues from multiple people all in one post each time like I’ve had to here. However I did have a quick look through, try the following syntax for joining tables:

SELECT
mt.`id`
,mt.`field`
,sd.`some_field`
,sd.`mytableid`
FROM
`my_table` AS mt
INNER JOIN
`some_data` AS sd
ON
mt.`id` = sd.`mytableid`
#8

Many thanks. I will give it a try, and if no joy will repost in a new thread.

Dave
Of all things I have lost, I miss my mind the most

#9

I made the suggested correction and got the following script:

display blogs <?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. $sql = "SELECT m.`firstname` , b.`title` , b.`entry` FROM `members` AS m INNER JOIN `blogs` AS b ON m.`id` = b.`id` ORDER BY b.`title` ASC"; $query = mysql_query($sql); if($query !== false && mysql_num_rows($query) > 0) { while(($row = mysql_fetch_assoc($query)) !== false) { echo "

".$row['title']."

"; echo "

".$row['entry']."
".$row['firstname']."

"; } } else if($query == false) { echo "

Query was not successful because:".mysql_error()."

"; echo "

The query being run was \"".$sql."\"

"; } else if($query !== false && mysql_num_rows($query) == 0) { echo "

The query returned 0 results.

"; } mysql_close(); //Close the database connection. ?>

Yet I get the error message: The query returned 0 resutls. Whcih clearly indicates that the query isn’t false but for some reason i can’t figure out, it wouldn’t print the results. And yes, when I query the 2 tables separately, I do get the results printed out. Please help!!

#10

Just to check - is there definitely at least 1 blog id that is the same as a member id?

#11

Smokey,
To answer your inquiry above, I’d love you to check out my newest post titled, “Matching Tables in SQL” which is like a follow up to this problem. As the PHP expert that you have demonstrated that you are, I’m pretty convinced that you will pinpoint the source of my puzzle. Thanks.

Sponsor our Newsletter | Privacy Policy | Terms of Service