Page 1 of 1

Trouble Retrieving Data From Multiple Tables

Posted: Sat Jan 01, 2011 7:07 pm
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.

Re: Trouble Retrieving Data From Multiple Tables

Posted: Sun Jan 02, 2011 2:32 am
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";
?>

Re: Trouble Retrieving Data From Multiple Tables

Posted: Sun Jan 02, 2011 3:22 pm
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?

Re: Trouble Retrieving Data From Multiple Tables

Posted: Sun Jan 02, 2011 3:50 pm
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
?>

Re: Trouble Retrieving Data From Multiple Tables

Posted: Mon Jan 03, 2011 4:03 am
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?

Re: Trouble Retrieving Data From Multiple Tables

Posted: Mon Jan 03, 2011 10:03 am
by social_experiment
Rename the variable that contains your query to $sql.

Re: Trouble Retrieving Data From Multiple Tables

Posted: Sun Jan 09, 2011 10:14 pm
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!!

Re: Trouble Retrieving Data From Multiple Tables

Posted: Sun Jan 09, 2011 10:19 pm
by Benjamin
:arrow: Use

Code: Select all

 tags when posting code in the forums.

Re: Trouble Retrieving Data From Multiple Tables

Posted: Mon Jan 10, 2011 6:34 am
by social_experiment
What is the error message that you receive