Navigation advice needed - JOINING TABLES

[klemme]

Hi all,

How can I make this nonsens i have written work?

I have 2 tables, one containing the urls I need to echo out in links - But I am not echoing out all of them. First I have to check in another table if there are any products in stock.

So I wanted to create a JOIN that could check the table with stock info.

I just havent used joins much before, and below u can probably see why..

(Normally I create a million on single select queries and store the results in variables because I am making mistakes every time I try to use joins..- but got to learn it to minimize code)

Hopefully someone can see what I am trying to do here:

$sql = mysqli_query($connection, "
SELECT url, link, varetype_id FROM produkt_navigation
INNER JOIN produkter ON produkt_navigation.brand_id = produkter.brand_id 
AND produkt_navigation.varetype_id = produkter.varetype_id  
WHERE produkter.stock > '0' ORDER BY produkter.produkt_nav_pos ASC");
while ($row = mysqli_fetch_array($sql))
{
	// Set class til hhv overskrifter og produktgrupper:
	  if ($row['varetype_id'] == 0)
	  {
		  $class = 'class="produkt_nav_overskrift"';	
	  }
		  else $class = 'class="produkt_nav"';
	echo '<a href="'.$row['url'].'" '.$class.'>'.ucfirst($row['link']).'</a>';	
}

Returns no result set - I know it looks weird :-)

Regards, Klemme

[karthik_ppts]

Did the code return any error message?

DO you really have 2 PK/FK in your linked tables? You should identify all fields in the query with a prefix (table) when creating this JOIN syntax:

SELECT produkt_navigation.url, produkt_navigation.link, produkt_navigation.varetype_id FROM produkt_navigation...

YOu can simplify this with aliases (AS syntax):

SELECT pn.url, pn.link, pn.varetype_id FROM produkt_navigation AS pn INNER JOIN produkter AS p ON pn.brand_id = p.brand_id AND pn.varetype_id = p.varetype_id ...

Although I don't know if that's the real issue here. I think it's the AND in the ON join clause:

SELECT pn.url, pn.link, pn.varetype_id FROM produkt_navigation AS pn INNER JOIN produkter AS p ON (pn.brand_id = p.brand_id AND pn.varetype_id = p.varetype_id) ...

may work?

[klemme]

This works perfect :-)

It writes out the navigation for product groups where stock is more than 0.

My plan was to create a brand-heading just above the product groups.
now it only shows: jeans, shirts trousers etc and not the brand just above

In the table "produkt_navigation", I have a row called type_id (1 = jeans, 2 = t-shirts etc. - AND 0 holds the url and link of the heading - and then I need to take out the url where type_id = 0)

So if the join finds that there is more than 0 in stock for a given product, is it then possible to in the query to "go back" into produkt_navigation and select url, link where type_id = 0, IF there has been found any stock in the "produkter tabel".

And then output the heading before each product group link, like this:

HEADING-LINK = LEVIS AND URL = /levis
PRODUKT-LINK = JEANS AND URL = /levis/jeans

I have written a comment inside the query where I think it may be possible? Or maybe im wrong?

// Undersider:
$sql = mysqli_query($connection, "
SELECT DISTINCT pn.url, pn.link, pn.varetype_id, pn.brand_id, pn.kategori 
FROM produkt_navigation AS pn 
INNER JOIN produkter AS p 
ON (pn.brand_id = p.brand_id AND pn.varetype_id = p.varetype_id AND pn.kategori = p.kategori)  
WHERE p.stock > '0' ORDER BY p.produkt_nav_pos, p.pos ASC

// Comment: 
IF any rows returned 
THEN SELECT pn.url, pn.link AS heading_url, heading_link WHERE type_id = 0 AND pn.brand_id = p.brand_id AND pn.kategori = p.kategori
");
while ($row = mysqli_fetch_array($sql))
{

// Set class til hhv overskrifter og produktgrupper:
if ($row['varetype_id'] == 0)
 {
  $class = 'class="produkt_nav_overskrift"';	
 }
  else $class = 'class="produkt_nav"';

// Then just before the product links outout the heading like this:
echo '<a href="'.$row['heading_url'].'" '.$class.'>'.ucfirst($row['heading_link']).'</a>';
		  
echo '<a href="'.$row['url'].'" '.$class.'>'.ucfirst($row['link']).'</a>'; // Product links

}

[klemme]

Or is it posible to use a union in the query?

$sql = mysqli_query($connection, "
SELECT DISTINCT pn.url, pn.link, pn.varetype_id, pn.brand_id, pn.kategori 
FROM produkt_navigation AS pn 
INNER JOIN produkter AS p 
ON (pn.brand_id = p.brand_id AND pn.varetype_id = p.varetype_id AND pn.kategori = p.kategori)  
WHERE p.stock > '0' ORDER BY p.produkt_nav_pos, p.pos ASC
UNION
SELECT pn.url, pn.link WHERE pn.brand_id = p.brand_id AND pn.type_id = '0' 
");

Something like that..?

[klemme]

I have chosen another solution, but thank you for the correction on the query!!
That has opened my eyes for joining tables a little bt, its not that complicated (when you know how to do it..)

I just put in stock as a new row in the table produkt_navigation, and then need to update that whenever a sale has occured. But then I dont need any joins and end up with a more simple query, which suits me fine :-)

Thank for the inputs!