querying, alias is returning NULL

Schweppesale · #1 (**permalink**) 10-21-04, 07:05

Quote:

Originally Posted by r937

please allow me to suggest that you (1) learn JOIN syntax, and (2) always use indentation and line breaks when writing SQL

Code:

SELECT jos_content.title
     , jos_content.introtext
     , jos_content.created_by
     , jos_content.sectionid
     , jos_content.id
     , jos_users.name
     , jos_sections.title AS 'sectitle'
     , jos_sections.id AS 'secid' 
  FROM jos_content
INNER
  JOIN jos_users
    ON jos_users.id = jos_content.created_by
INNER
  JOIN jos_sections 
    ON jos_sections.id = jos_content.sectionid

Thanks for the tip. You're probably right proper formatting; isn't inner join the same thing as join though?

SQL INNER JOIN Keyword

Schweppesale · #2 (**permalink**) 08-26-09, 17:18

Code:

	$result = mysql_query("SELECT jos_content.title, jos_content.introtext, jos_content.created_by, jos_content.sectionid, jos_content.id, jos_users.name, jos_sections.title AS 'jos_sections.sectitle', jos_sections.id AS 'jos_sections.secid' FROM jos_content, jos_users, jos_sections WHERE (jos_content.created_by = jos_users.id AND jos_content.sectionid = jos_sections.id)");
	$t = 0;
	while($row = mysql_fetch_array($result))
		{
			$entry[$t][0] = $row['id'];
			$entry[$t][1] = $row['title'];
			$entry[$t][2] = $row['name'];
			$entry[$t][3] = $row['sectionid'];
			$entry[$t][4] = $row['introtext'];
			$entry[$t][5] = $row['sectitle'];
			$t++;
		}

for some reason $row['sectitle'] is returning null. Any ideas as to what would cause this? It's only that one attribute.

Schweppesale · #3 (**permalink**) 08-26-09, 17:21

sorry, I cleaned it up a little bit :P

Code:

$query = "SELECT jos_content.title, jos_content.introtext, jos_content.created_by, ".
	"jos_content.sectionid, jos_content.id, jos_users.name, jos_sections.title AS 'jos_sections.sectitle', ".
	"jos_sections.id AS 'jos_sections.secid' FROM jos_content, jos_users, jos_sections ".
	"WHERE (jos_content.created_by = jos_users.id AND jos_content.sectionid = jos_sections.id)";
	$result = mysql_query($query);
	$t = 0;
	while($row = mysql_fetch_array($result))
		{
			$entry[$t][0] = $row['id'];
			$entry[$t][1] = $row['title'];
			$entry[$t][2] = $row['name'];
			$entry[$t][3] = $row['sectionid'];
			$entry[$t][4] = $row['introtext'];
			$entry[$t][5] = $row['sectitle'];
			$t++;
		}

still no luck though

Schweppesale · #4 (**permalink**) 08-26-09, 17:26

bangs head against mysql manual. lol, ok I was using the alias incorrectly.

Code:

$query = "SELECT jos_content.title, jos_content.introtext, jos_content.created_by, ".
	"jos_content.sectionid, jos_content.id, jos_users.name, jos_sections.title AS 'sectitle', ".
	"jos_sections.id AS 'secid' FROM jos_content, jos_users, jos_sections ".
	"WHERE (jos_content.created_by = jos_users.id AND jos_content.sectionid = jos_sections.id)";

r937 · #5 (**permalink**) 08-26-09, 20:16

please allow me to suggest that you (1) learn JOIN syntax, and (2) always use indentation and line breaks when writing SQL

Code:

SELECT jos_content.title
     , jos_content.introtext
     , jos_content.created_by
     , jos_content.sectionid
     , jos_content.id
     , jos_users.name
     , jos_sections.title AS 'sectitle'
     , jos_sections.id AS 'secid' 
  FROM jos_content
INNER
  JOIN jos_users
    ON jos_users.id = jos_content.created_by
INNER
  JOIN jos_sections 
    ON jos_sections.id = jos_content.sectionid

Schweppesale · #6 (**permalink**) 08-27-09, 10:20

Quote:

Originally Posted by r937

please allow me to suggest that you (1) learn JOIN syntax, and (2) always use indentation and line breaks when writing SQL

Code:

SELECT jos_content.title
     , jos_content.introtext
     , jos_content.created_by
     , jos_content.sectionid
     , jos_content.id
     , jos_users.name
     , jos_sections.title AS 'sectitle'
     , jos_sections.id AS 'secid' 
  FROM jos_content
INNER
  JOIN jos_users
    ON jos_users.id = jos_content.created_by
INNER
  JOIN jos_sections 
    ON jos_sections.id = jos_content.sectionid

Thanks for the tip. My code can definitely use some formatting, I was under the assumption that inner join and join where the same though though.

SQL INNER JOIN Keyword

r937 · #7 (**permalink**) 08-27-09, 13:07

Quote:

Originally Posted by Schweppesale

I was under the assumption that inner join and join where the same though though.

yes, sort of

first, JOIN equates to INNER JOIN, at least in mysql, but it's better always to write INNER JOIN and not just JOIN (in earlier versions JOIN equated to CROSS JOIN)

second, you had FROM jos_content, jos_users, jos_sections WHERE ... which technically is an inner join but this syntax is old style and should be rewritten with JOIN syntax

besides, if you wanted to change one of those joins to an outer join, you'd have to rewrite it anyway