PHP/MySQL Joining help

[l3rodey]

Hi guys,

I am not very good with Joins, and thus I do things what most of you will call discusting. I already know this so go easy. 

Can someone help me with a join? I have googled it but don't understand as it's not my db.

 

This is what I do. 

<?php
			$featuredProducts = mysql_query("SELECT * FROM products WHERE status='1' AND featured='1' ORDER BY product_id ASC LIMIT 12", $liquor);
			while ($featured = mysql_fetch_object($featuredProducts)) {

				$category = mysql_query("SELECT * FROM categories WHERE category_id=$featured->category_id LIMIT 1", $liquor);
				while ($cat = mysql_fetch_object($category)) {
					$slug = $cat->slug;
				} mysql_free_result($category);
		?>
		
			<li><a href="./<?php echo $slug ?>/<?php echo $featured->slug; ?>"><?php echo $featured->name; ?></a></li>

		<?php
			
			} mysql_free_result($featuredProducts);
		?>

I need to build a URL so I need to know the category slug which is in the db, so I do another lookup make a valuable and use it... but I know you can do a join here and make this so much tidier. Can someone please show me how a join would make the above query proper? 

[Barand]

The second query is finding the category whose id matches that in the product. So that is the candidate for the join.

 

I.E.

FROM products
INNER JOIN categories ON products.category_id = categories.category_id

or

FROM products
INNER JOIN categories USING (category_id)

Do not use "SELECT * ". Specify the columns you need (and use aliases)

SELECT 
    p.name
  , p.slug as prodslug
  , c.slug as catslug
FROM FROM products p
    INNER JOIN categories c 
        ON p.category_id = c.category_id 
WHERE p.status='1' AND p.featured='1'
ORDER BY p.product_id 
LIMIT 12

See my signature re mysql_ library

[l3rodey]

The second query is finding the category whose id matches that in the product. So that is the candidate for the join.

 

I.E.

FROM products
INNER JOIN categories ON products.category_id = categories.category_id

or

FROM products
INNER JOIN categories USING (category_id)

Do not use "SELECT * ". Specify the columns you need (and use aliases)

SELECT 
    p.name
  , p.slug as prodslug
  , c.slug as catslug
FROM FROM products p
    INNER JOIN categories c 
        ON p.category_id = c.category_id 
WHERE p.status='1' AND p.featured='1'
ORDER BY p.product_id 
LIMIT 12

See my signature re mysql_ library

 

Hey thanks man! and I am echoing how? <php echo $featured->prodslug; ?> is that correct is that how I echo it?

And I am using mysql clearly what is the mysqli option? 

( I'm super behind clearly ) just trying to learn. 

[Jacques1]

The database interface for the 21st century is PDO.

[l3rodey]

The database interface for the 21st century is PDO.

Thanks, I will look at this and see what I can find but how do I display data? I have the query setup as this:

$query = "
				SELECT 
				p.name
				, p.slug as prodslug
				, c.slug as catslug
				FROM FROM products p
				INNER JOIN categories c 
				ON p.category_id = c.category_id 
				WHERE p.status='1' AND p.featured='1'
				ORDER BY p.product_id 
				LIMIT 12
			";

			if ($result = $liquor->query($query)) {
				while ($row = $result->fetch_assoc()) {
		?>
		
			<li><a href="./<?php echo $row->catslug ?>/<?php echo $row->prodslug; ?>"><?php echo $row->name; ?></a></li>

		<?php
				} $result->free();
			}
		?>

but nothing shows? what am I doing wrong with the displaying of data?

[Jacques1]

I recommend you switch to PDO now. Then we can stop wasting time on code that will be rewritten anyway.

 

Your code above fetches the rows as associative arrays (that's what the _assoc means), but then your trying to access the data as if the rows where objects (using the -> operator). You need to choose either objects or associative arrays. Both at the same time isn't possible.