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
  • 180 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by smantscheff

Dani AI

Generated

Quick summary and where the original problem came from: correctly spotted the immediate syntax issue that prevented the extra condition from working, and the others correctly noted that the two join styles produce the same result for an inner join. Below are practical, low-risk steps and checks that keep the code correct, robust, and scalable.

Always parameterize and stop using the old mysql_ calls. Use prepared statements (mysqli or PDO) so string values like the menu name don’t need manual quoting and you avoid injection. For larger sets, focus on three things: 1) proper indexes on the join key and the filter column (node_id and menu_name), 2) SELECT only the columns you need (avoid SELECT ), and 3) verify with EXPLAIN to see which index is used and how many rows MySQL expects to examine. If a query still scans many rows, add or adjust indexes (consider a covering index that includes the filter plus returned columns).

Be careful where you put the filter when using OUTER joins: putting a condition on the right table in WHERE will convert a LEFT JOIN into an effective INNER JOIN; if you want unmatched left rows preserved, push the predicate into the ON clause instead. For pure INNER JOINs, WHERE vs ON for a simple equality filter is equivalent.

If you’re checking files with is_file() for every row, that can dominate runtime for large result sets. Options that keep page-time small: (a) keep the filesystem-validity in the DB (a cron job updates a boolean), (b) fetch just the rows you need (LIMIT / pagination) then check files, or (c) cache validated menus so page rendering doesn’t hit disk each request. Example PDO pattern to avoid quoting bugs and race conditions:

$pdo = new PDO($dsn,$user,$pass);
$stmt = $pdo->prepare('SELECT l.name, n.path FROM links l JOIN nodes n USING (node_id) WHERE l.menu_name = :menu');
$stmt->execute([':menu' => $menuName]);
while ($r = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $fp = $basePath . $r['path'];
    if (is_file($fp)) { /* build list item */ }
}

Use EXPLAIN plus a brief timed test (or slow-query log) to measure real differences before optimizing further.

Recommended Answers

All 7 Replies

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.