Trouble Retrieving Data From Multiple Tables

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
drayarms
Forum Contributor
Posts: 134
Joined: Fri Dec 31, 2010 5:11 pm

Trouble Retrieving Data From Multiple Tables

Post by drayarms »

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.

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.
Last edited by Benjamin on Sun Jan 09, 2011 10:17 pm, edited 1 time in total.
Reason: Added [syntax=php] tags.
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Trouble Retrieving Data From Multiple Tables

Post by social_experiment »

A JOIN statement has the word 'JOIN' in it. You need to revise your query.

Code: Select all

<?php
$query = "SELECT random, time_field ROM demonstration_tbl JOIN demonstration2_tbl ON demonstration_tbl.id = demonstration2_tbl.id";
?>
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
drayarms
Forum Contributor
Posts: 134
Joined: Fri Dec 31, 2010 5:11 pm

Re: Trouble Retrieving Data From Multiple Tables

Post by drayarms »

Ok I got the following query from another forum, suggested by someone,

Code: Select all

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

I replaced my original select query with this one and on running the script, all I got was a blank page. I suspect that the while clause at the second part of the script is faulty. Any suggestions?
Last edited by Benjamin on Sun Jan 09, 2011 10:17 pm, edited 1 time in total.
Reason: Added [syntax=mysql] tags.
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Trouble Retrieving Data From Multiple Tables

Post by social_experiment »

Maybe this line of code. The logic is wrong.

Code: Select all

<?php if ($r = mysql_query ($query)) ?>
It should look like

Code: Select all

<?php
 $r = mysql_query($query);
 //
 if ($r) {
  // display records
 }
 // rest of the code
?>
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
drayarms
Forum Contributor
Posts: 134
Joined: Fri Dec 31, 2010 5:11 pm

Re: Trouble Retrieving Data From Multiple Tables

Post by drayarms »

Ok I pieced together this script from several suggestions

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


    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 "<h3>".$row['title']."</h3>";
						echo "<p>".$row['entry']."<br />".$row['firstname']."</p>";
					}
				}
				else if($query == false)
				{
					echo "<p>Query was not successful because:<strong>".mysql_error()."</strong></p>";
					echo "<p>The query being run was \"".$sql."\"</p>";
				}
				else if($query !== false && mysql_num_rows($query) == 0)
				{
					echo "<p>The query returned 0 results.</p>";
				}


mysql_close(); //Close the database connection.





?>



And I got this result:


Query was not successful because:Query was empty

The query being run was ""

Any suggestions as to what might be going wrong?
Last edited by Benjamin on Sun Jan 09, 2011 10:18 pm, edited 1 time in total.
Reason: Added [syntax=php] tags.
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Trouble Retrieving Data From Multiple Tables

Post by social_experiment »

Rename the variable that contains your query to $sql.
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
drayarms
Forum Contributor
Posts: 134
Joined: Fri Dec 31, 2010 5:11 pm

Re: Trouble Retrieving Data From Multiple Tables

Post by drayarms »

I made the suggested correction and got the following script:




Code: Select all

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML1.0 Transitional//EN"
 
 
 
"http//www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 
 
<html xmlns="http://www.w3.org/1999/xhtml"> 
 
 
 
 
 
 <head> 
 
  <meta http-equiv="content-type" content="text/html; charset=iso-8859-1" /> 
 
  <title>display blogs</title> 
 
  <link rel="stylesheet" type="text/css" href="file.css"/> 
  
 
 </head> 
 
 
 
 
<body>

<?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 "<h3>".$row['title']."</h3>";
						echo "<p>".$row['entry']."<br />".$row['firstname']."</p>";
					}
				}
				else if($query == false)
				{
					echo "<p>Query was not successful because:<strong>".mysql_error()."</strong></p>";
					echo "<p>The query being run was \"".$sql."\"</p>";
				}
				else if($query !== false && mysql_num_rows($query) == 0)
				{
					echo "<p>The query returned 0 results.</p>";
				}


mysql_close(); //Close the database connection.





?>


</body> 
 
</html>


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!!
Last edited by Benjamin on Sun Jan 09, 2011 10:18 pm, edited 1 time in total.
Reason: Added [syntax=php] tags.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Trouble Retrieving Data From Multiple Tables

Post by Benjamin »

:arrow: Use

Code: Select all

 tags when posting code in the forums.
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Trouble Retrieving Data From Multiple Tables

Post by social_experiment »

What is the error message that you receive
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
Post Reply