Page 1 of 1

Matching tables in sql

Posted: Mon Jan 10, 2011 4:23 pm
by drayarms
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

Code: Select all

$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


Code: Select all

$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.

Code: Select all

// 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.

Code: Select all

                     //define the query.
                     $query = "INSERT INTO blogs (blog_id, title, entry, blog_date) VALUES (0, '{$_POST['title']}', '{$_POST['entry']}', NOW())";



Select query:

Code: Select all

$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 "<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>";
				}


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!!!

Re: Matching tables in sql

Posted: Fri Jan 14, 2011 10:27 am
by Jade
Your member_id and blog_id fields are set to auto-increment . You shouldn't be inserting a values into them, mysql will create generate the ID for you. Next, are you sure you have data in your tables?

Re: Matching tables in sql

Posted: Sat Jan 15, 2011 2:04 am
by drayarms
@Jade, yes I do have data in both tables. I successfully registered two members and posted a couple of blogs. And I know for sure that the data I entered was successfully posted to the blogs table because I was able to display this data with a select query, and also in the above select query, I was able to retrieve the data from blogs when I used left join, and from members when I used right join.