Member Avatar for BrianDickson

I am having great difficulty getting a query to work with multiple conditions. I have a very basic understanding, hence the two queries below (one commented out). I understand a smaller and more precise query would be returned by using INNER JOIN. Both queries work fine until I try to add an addition condition to the query: "AND links.menu_name = $menu_name".

$query = mysql_query("
SELECT links.link_name, links.menu_name, nodes.url, nodes.file_path 
FROM links, nodes 
WHERE links.node_id = nodes.node_id");	
//AND links.menu_name = $menu_name

/*
$query = mysql_query("
SELECT link_name, menu_name, url, file_path 
FROM nodes
INNER JOIN links
ON links.node_id = nodes.node_id
ON links.menu_name = $menu_name");	

*/

ATM things work if I do the additional condition check inside a while loop (see below), but I'm concerned this may be slower that adding the condition on the original query which I'm having trouble getting to work, particularly on larger returned sets. Can someone please shed some clarity.

while ($row = mysql_fetch_assoc($query)) {
	if ($row['menu_name'] == $menu_name){
		//CHECK TO SEE IF FILE EXISTS
		$check_file = $this->path['content'] . $row['file_path'];
		
		if (is_file($check_file)){
			$a_href = '<li><a href="' . $this->base_path . $row['url'] . '">' . $row['link_name'] . '</a></li>';
			$menu_row .= $a_href;
		}
		else {
			//echo 'illegal link';			
			$a_href = null;
		}
	}
}
  • 4 Contributors
  • 7 Replies
  • 168 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by smantscheff
Member Avatar for pritaeas

You forgot the singles quotes around $menu_name

In the second commented query, the second ON should be AND, and there too the single quotes are missing.

$query = mysql_query("
SELECT links.link_name, links.menu_name, nodes.url, nodes.file_path 
FROM links, nodes 
WHERE links.node_id = nodes.node_id
AND links.menu_name = '$menu_name'");
 
/*
$query = mysql_query("
SELECT link_name, menu_name, url, file_path 
FROM nodes
INNER JOIN links
ON links.node_id = nodes.node_id
AND links.menu_name = '$menu_name'");
*/
Member Avatar for BrianDickson

You forgot the singles quotes around $menu_name

In the second commented query, the second ON should be AND, and there too the single quotes are missing.

$query = mysql_query("
SELECT links.link_name, links.menu_name, nodes.url, nodes.file_path 
FROM links, nodes 
WHERE links.node_id = nodes.node_id
AND links.menu_name = '$menu_name'");
 
/*
$query = mysql_query("
SELECT link_name, menu_name, url, file_path 
FROM nodes
INNER JOIN links
ON links.node_id = nodes.node_id
AND links.menu_name = '$menu_name'");
*/

Thanks. Which query is better to use?

Edited by BrianDickson because: n/a
Member Avatar for smantscheff

Use the one which you understand better. From the MySQL point of view it makes no difference.

Member Avatar for debasisdas

1st one is conventional style SQL.
2nd is as per ANSI SQL.

Member Avatar for BrianDickson

1st one is conventional style SQL.
2nd is as per ANSI SQL.

I've echo'ed out the returned rows and fields of each query, both are the same, despite reading a separate article saying the 'conventional' style join would return huge tables. I suspect this wasn't the case for my example as I explicitly stated the fields to be returned.

Execution time-wise, the INNER JOIN query, seems faster, but this is only by hundredths of milliseconds, and I'm not sure how much interference my browser cache plays on this.

I would like to know which of the queries would more likely be more efficient on larger returned datasets?

Member Avatar for debasisdas

I don't think there will be any difference in performance as per the above mentioned queries if the Joins are proper.

Member Avatar for smantscheff

The mysql engine treats both queries as identical. Performance differences may be due to some additional parser activity, but these should not cost more than a few CPU cycles.

Edited by smantscheff because: n/a
commented: agree +9
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.