MYSQL Join

[SchweppesAle]

hi, I'm trying to join these two tables.  Was wondering if someone could tell me what's wrong with the following bit of code.  I've looked at both tables and there's at least 1 or 2 rows where AuthorID and userid are identical; however no output is display(the work text).

 

global $mainframe;
$db =&JFactory::getDBO();
$query = "SELECT * ".
"FROM #__AuthorList, #__magazine_users".
"WHERE #__AuthorList.AuthorID = #__magazine_users.userid";
/*$query = "SELECT * FROM #__magazine_users";*/
$db->setQuery( $query, 0, $count );
$rows = $db->loadObjectList();	


foreach($rows as $row)
{
	echo "<table border = 1 width = 500>";	
	echo "<tr><td>";

	echo "work";



	echo "</td></tr></table>";
}

[Daniel0]

You shouldn't use that kind of join. Make an explicit join using the JOIN keyword instead:

 

SELECT * FROM #__AuthorList AS a
  INNER JOIN #__magazine_users AS u
    ON a.AuthorID = u.userid;

 

You can read more about joins here: http://www.phpfreaks.com/tutorial/data-joins-unions

[SchweppesAle]

You shouldn't use that kind of join. Make an explicit join using the JOIN keyword instead:

 

SELECT * FROM #__AuthorList AS a
  INNER JOIN #__magazine_users AS u
    ON a.AuthorID = u.userid;

 

You can read more about joins here: http://www.phpfreaks.com/tutorial/data-joins-unions

 

thanks, one question though; how would I go about pulling the name column from Authorlist whenever the ON(conditional?) holds true.  Here's what I'm using so far:

 

global $mainframe;
$db =&JFactory::getDBO();/*
$query = "SELECT * ".
"FROM #__AuthorList, #__magazine_users".
"WHERE #__AuthorList.AuthorID = #__magazine_users.userid";*/

$query = "SELECT * FROM #__AuthorList AS a
  INNER JOIN #__magazine_users AS u
    ON a.AuthorID = u.userid";

$db->setQuery( $query, 0, $count );
$rows = $db->loadObjectList();	


foreach($rows as $row)
{
	echo "<table border = 1 width = 500>";	
	echo "<tr><td>";

	echo "work";
	echo "<br/>";
	$name = $row->name;
	echo $name;



	echo "</td></tr></table>";
}

 

it will output the work statement, I don't think I'm pulling each column entry out correctly though.

[Daniel0]

SELECT a.name FROM #__AuthorList AS a
  INNER JOIN #__magazine_users AS u
    ON a.AuthorID = u.userid

[sasa]

hi, I'm trying to join these two tables.  Was wondering if someone could tell me what's wrong with the following bit of code.  I've looked at both tables and there's at least 1 or 2 rows where AuthorID and userid are identical; however no output is display(the work text).

 

global $mainframe;
$db =&JFactory::getDBO();
$query = "SELECT * ".
"FROM #__AuthorList, #__magazine_users".
"WHERE #__AuthorList.AuthorID = #__magazine_users.userid";
/*$query = "SELECT * FROM #__magazine_users";*/
$db->setQuery( $query, 0, $count );
$rows = $db->loadObjectList();	


foreach($rows as $row)
{
	echo "<table border = 1 width = 500>";	
	echo "<tr><td>";

	echo "work";



	echo "</td></tr></table>";
}

insert some spaces after #__magazine_users or before WHERE

[SchweppesAle]

SELECT a.name FROM #__AuthorList AS a
  INNER JOIN #__magazine_users AS u
    ON a.AuthorID = u.userid

 

hi, I'm trying to select all columns *. However I'm also trying to cycle through each entry after we combine the two tables (for rows where AuthorID is the same as userid). 

 

After which I'd like to cycle through each of these rows and output the name for entry.  Only problem is the following statement won't output the names as it loops. 

 

foreach($rows as $row)
{
	echo "<table border = 1 width = 500>";	
	echo "<tr><td>";

	echo "work";
               $name = $row->name;
	echo $name;




	echo "</td></tr></table>";
}

[SchweppesAle]

kind of experimenting with the two methods, for some reason I still can't pull data I need from each entry.

 

global $mainframe;
$db =&JFactory::getDBO();
$query = "SELECT * ".
"FROM #__AuthorList, #__magazine_users  ".
"WHERE #__AuthorList.AuthorID = #__magazine_users.userid";

/*
$query = "SELECT * FROM #__AuthorList AS a
 INNER JOIN #__magazine_users AS u
   ON a.AuthorID = u.userid";*/

$db->setQuery( $query, 0, $count );
$rows = $db->loadObjectList();	


foreach($rows as $row)
{
	echo "<table border = 1 width = 500>";	
	echo "<tr><td>";

	echo "work";
	echo "<br/>";

	$name = $row->name;
	echo $name;



	echo "</td></tr></table>";
}

 

output:

Document Management System Review (2009-03-16 14:00:50)

Welcome to The Progressive Accountant (2009-03-04 04:17:19)

Podcast intro sample (2009-03-11 14:12:37)

List Authors (2009-03-09 19:46:48)

Login (2009-03-09 19:43:13)

Help Your Clients Optimize Their Inventory: Phitch 9.0 (2009-03-12 01:00:00)

Project Insight: Web 2.0, Web-Based Project Management (2009-03-10 01:00:00)

AICPA Establishes 'Economic Crisis' Blog (2009-03-09 02:16:49)

Fortune Names Intuit, 'Most Admired,' Again (2009-03-09 02:13:10)

All Things QuickBooks (2009-03-09 01:00:00)

NASBA Announces Ed 'The Rainmaker' Robinson, CPA, to Keynote National CPE Expo (2009-03-09 02:09:04)

Contributing Authors:

 

(in tables)

work

work

work

work

work

work

work

work

work

 

update:

tried the following method as well; still no good.

 

$row = mysql_fetch_array($query);

while($row = mysql_fetch_array($query)){
	echo ($row['name']);
}