join question

[raptor30506090]

Hi every one

 

can you please tell me if this is the same as an INNER JOIN

 

<?php

mysql_query("SELECT table1.id, table2.id FROM table1, table2 WHERE table1.id = table2.id ");

?>

[raptor30506090]

ok im trying to make a menu but keep having same problem is it the query ? any help would be great thanks

 

<?php 
$query = mysql_query("SELECT * FROM categorys INNER JOIN sub_categorys ON categorys.cat_id =  sub_categorys.cat_id");

while($row = mysql_fetch_array($query)){


echo "<ul>";
echo "<li>" . $row['category'] . "</li>";
echo "<ul>";
echo "<li>";
echo $row['sub_category'];
echo "</li>";
echo "</ul>";
echo "</ul>";

}
?> 

[awjudd]

You need to build up a list for each of the categories and then output it because you want all of the subcategories to be listed under it.

 

When you go through the query results either:

a - cycle and load all values into an array based on $array [ $row [ 'category' ] ] [] = $row [ 'sub_category' ]; then cycle through $array

b - keep track of the current category you are in and if it changes then emit the <ul> stuff

 

As for if the query is the issue, try it in something like phpMyAdmin to see.

 

Hope this helps.

 

~awjudd

[kickstart]

Hi

 

You want to create a new <ul> set of tags when the category changes, and close the previous set if they existed.

 

Something like this (not tested so excuse typos).

 

<?php 
$Category = '';
$query = mysql_query("SELECT * FROM categorys INNER JOIN sub_categorys ON categorys.cat_id =  sub_categorys.cat_id");

echo "<ul>";
while($row = mysql_fetch_array($query))
{
if ($row['category'] != $Category)
{
	if ($Category != '')
	{
		echo "</ul>";
	}
	$Category = $row['category'];
	echo "<li>" . $row['category'] . "</li>";
	echo "<ul>";
}
echo "<li>".$row['sub_category']."</li>";
}
if ($Category != '')
{
echo "</ul>";
}
echo "</ul>";
?> 

 

All the best

 

Keith

[raptor30506090]

Hi ManiacDan  kickstart

 

Ok now by going with kickstart it all seems to work the only thing is that it dont show the contact part it show Home About and Services with the submenu but no contact any ideas please

 

thanks so much

 

Daz

[kickstart]

Hi

 

Can you export your 2 tables with data and post them here. Then I can have a play to get it to work.

 

As it is I can only guess as to which items you mention are categories, which are sub categories and which relate to which.

 

All the best

 

Keith

[raptor30506090]

Hi

 

 

<?php

$categorys = array(
  array('cat_id'=>1,'category'=>'Home'),
  array('cat_id'=>2,'category'=>'About'),
  array('cat_id'=>3,'category'=>'Services'),
  array('cat_id'=>4,'category'=>'Contact')
);

// array.sub_categorys
$sub_categorys = array(
  array('sub_cat_id'=>1,'cat_id'=>1,'sub_category'=>'our history'),
  array('sub_cat_id'=>2,'cat_id'=>2,'sub_category'=>'why we started'),
  array('sub_cat_id'=>3,'cat_id'=>4,'sub_category'=>'about the company'),
  array('sub_cat_id'=>4,'cat_id'=>3,'sub_category'=>'our staff'),
  array('sub_cat_id'=>5,'cat_id'=>2,'sub_category'=>'plastering'),
  array('sub_cat_id'=>6,'cat_id'=>3,'sub_category'=>'plumbing'),
  array('sub_cat_id'=>7,'cat_id'=>1,'sub_category'=>'company policy')
);
?>

 

thanks

[kickstart]

Hi

 

Assuming the arrays data should have been in mysql tables then this does it for you.

 

<?php

$sql = "SELECT * 
FROM categorys
INNER JOIN sub_categorys ON categorys.cat_id = sub_categorys.cat_id
ORDER BY categorys.cat_id, sub_categorys.sub_cat_id";

$query = mysql_query($sql) or die(mysql_error()." $sql");

$Category = '';

echo "<ul>";
while($row = mysql_fetch_array($query))
{
if ($row['category'] != $Category)
{
	if ($Category != '')
	{
		echo "</ul>";
	}
	$Category = $row['category'];
	echo "<li>" . $row['category'] . "</li>";
	echo "<ul>";
}
echo "<li>".$row['sub_category']."</li>";
}
if ($Category != '')
{
echo "</ul>";
}
echo "</ul>";

?>

 

All the best

 

Keith

[raptor30506090]

Thanks Keith

 

ok the problem now is yes it brings up

 

•Services

    ◦our history

    ◦why we started

    ◦about the company

    ◦our staff

    ◦plastering

    ◦plumbing

    ◦company policy 

 

but nothing else no Home About or Contact

 

 

 

This works correct but dont now when connect to db to get the data

 

<?php

// array.categorys
$categorys = array(
  array('cat_id'=>1,'category'=>'Home'),
  array('cat_id'=>2,'category'=>'About'),
  array('cat_id'=>3,'category'=>'Services'),
  array('cat_id'=>4,'category'=>'Contact')
);

// array.sub_categorys
$sub_categorys = array(
  array('sub_cat_id'=>1,'cat_id'=>3,'sub_category'=>'our history'),
  array('sub_cat_id'=>2,'cat_id'=>3,'sub_category'=>'why we started'),
  array('sub_cat_id'=>3,'cat_id'=>3,'sub_category'=>'about the company'),
  array('sub_cat_id'=>4,'cat_id'=>3,'sub_category'=>'our staff'),
  array('sub_cat_id'=>5,'cat_id'=>3,'sub_category'=>'plastering'),
  array('sub_cat_id'=>6,'cat_id'=>3,'sub_category'=>'plumbing'),
  array('sub_cat_id'=>7,'cat_id'=>3,'sub_category'=>'company policy')
);

echo "<ul>";
foreach($categorys as $category){
echo "<li>";
	echo $category['category'];

	foreach($sub_categorys as $sub_category){
		if($category['cat_id'] == $sub_category["cat_id"]){
			echo "<ul><li>".$sub_category["sub_category"]."</li></ul>";
		}
	}
echo "</li>";
}
echo "</ul>";


?>

[kickstart]

Hi

 

Can you do an actual export of the data.

 

The code I posted did work when I set up a copy of what I believe the be the table layouts using the data you supplied in the arrays.

 

You code won't work properly at the moment as each sub category has its own unordered list.

 

All the best

 

Keith

[raptor30506090]

Thanks for this help

keith

17158_.gz

[fenway]

We're now well outside the scope of mysql -- you need help with php.