I’ve been stuck for several days on this problem of retrieving data from multiple tables. I got alot of useful hints and scripts from this forum (thanks to everyone who helped), but never seemed to get anywhere. Then I came to realize the problem wasn’t the queries I used but the fact that PHP could not match the two tables I was trying to retrieve data from. When I used outer left and right joins, the desired results got printed from one of them tables but not the other, but when I used inner join, I got an error message. So I’m going to display below, both tables, the insert query for both tables, and the select query and hopefully, someone can point out where I’m going wrong.
Table 1
$query = "CREATE TABLE members (
member_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
username VARCHAR( 50 ) NOT NULL UNIQUE,
firstname VARCHAR( 50 ) NOT NULL ,
lastname VARCHAR( 50 ) NOT NULL ,
title VARCHAR(10),
password VARCHAR( 50 ) NOT NULL ,
primary_email VARCHAR(100),
secondary_email VARCHAR(100),
register_date VARCHAR( 50 ) NOT NULL ,
ip VARCHAR( 50 ) NOT NULL ,
UNIQUE (username)
)";
Table 2
$query = "CREATE TABLE blogs (
blog_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
member_id INT UNSIGNED,
like_id INT UNSIGNED,
title VARCHAR( 500 ) NOT NULL,
entry VARCHAR( 2000 ) NOT NULL ,
blog_date VARCHAR( 50 )
)";
Notice that the common column the member_id column
Insert query 1. This query registers new users (members) and the data is inserted into members table.
// Check if he wants to register:
if (!empty($_POST[username]))
{
// Check if passwords match.
if ($_POST[password] != $_POST[password2])
exit(“Error - Passwords don’t match. Please go back and try again.”);
// Assign some variables.
$date = mktime(0,0,0,date("m"),date("d"),date("Y"));
$ip = $_SERVER[REMOTE_ADDR];
require_once("config.php");
// Register him.
$query = mysql_query("INSERT INTO members
(member_id, username, firstname, lastname, password, register_date, ip)
VALUES (0, '$_POST[username]','$_POST[firstname]','$_POST[lastname]','$_POST[password]','$date','$ip')")
or die ('<p>Could not register user becasue: <b>' .mysql_error().'</b>.</p>');
echo "Welcome". ucfirst$_POST[username]. "! You've been successfully reigstered! <br /><br />
Please login <a href='login.php'><b>here</b></a>.";
exit();
}
Insert table 2. This query inserts data into the blogs table.
//define the query.
$query = "INSERT INTO blogs (blog_id, title, entry, blog_date) VALUES (0, '{$_POST['title']}', '{$_POST['entry']}', NOW())";
Select query:
$sql = "SELECT
blogs.title,blogs.entry,members.firstname
FROM
blogs
LEFT OUTER JOIN
members
ON
blogs.member_id = members.member_id
ORDER BY
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.
”;}
I am tempted to think there has to be a member_id row in the insert query of blogs table, but if that’s the case, what would be the corresponding value? And if not, where did I go wrong. Somebody please help!!!