trimming results of query

[tlholley]

I have a query that returns me a result of:

Home > Accessories > Hammock Add-Ons
Home > Accessories > Hammock Blankets
Home > Accessories > Hammock Pillows
Home > Hammock Chairs > Chair Stands
Home > Hammock Chairs > Hammock Chair Packages
Home > Hammock Stands > Metal Stands
Home > Hammock Stands > Wood Stands
Home > Hammocks > Brazilian Hammocks
Home > Hammocks > Mayan Hammocks
Home > Hammocks > Rope Hammocks

and I would like to turn it into something that looks more like:

Accessories
Hammock Add-Ons
Hammock Blankets
Hammock Pillows
Hammock Chairs
Chair Stands
Chair Stands
Hammock Chair Packages
Hammock Stands
Metal Stands
Wood Stands
Hammocks
Brazilian Hammocks
Mayan Hammocks
Rope Hammocks

I believe I would have to run two queries, the first to define the main group and the second to get the subcategories. I just don't know how to trim the results to make it look like I want.

Any help?

Thanks.

[harmor]

I think it'll be easier if you created a field for each subcategory, that way all you need to do is call that field.

table set up would be something like
id
category_1
category_2

Then you would have values, for example

id = 1
category_1 = Accessories
category_2 = Hammock Add-Ons

Then all echo the category_2 field

I hope this makes it easier for you in the long run.

[tlholley]

Unfortunately, this is not my database. This is the way the business has their database set up and I can only make queries into it. So, I have got to figure out a way to work with what I have.

[harmor]

Can you take a picture of the database layout or the code the displays the categoery results?

I would prefer an image of the database where the category names are stored though

[Sxooter]

Unless you have a LOT of entries, I'd just do it in code. And I'm the "do everything you can in the database" guy.

Create an array something like:

$home['accessories'][]="Hammock Add-Ons";
$home['accessories'][]="Hammock Blankets";
$home['accessories'][]="Hammock Pillows";
$home['accessories'][]="Hammock Chairs ";
$home['chair_stands'][]="Chair Stands";

and so on. you can use a loop and just assign them one at a time there.

[tlholley]

Harmor,

I don't really know what you mean by taking an image of the database.
Here is the code for the query:

<?php
$login = "netshops";
$password = "feedme";
$table = "hammocks";
$aid = "cd1";
$database = "netshopsaffiliates_com_-_datafeeds";
$connection = mysql_connect("207.234.208.224","$login","$password")
	or die ("Unable to connect.");
mysql_select_db($database)
	or die ("Unable to open database.");
$sql = "SELECT DISTINCT category FROM $table ORDER BY category";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
while ($row = mysql_fetch_array($result))
	{
		extract($row);
		echo "<table width='600' border='0' cellspacing='0' cellpadding='3'> \n";
		echo "<tr>\n";
		echo "<td valign='top'>\n<a href='list.php?cat=$category'> \n<b> $category </b> </a> \n<br>\n";
		echo "</tr>\n";
		echo "</table>\n";
	}
mysql_close($connection);
?>

Sxooter,

I would prefer something more dynamic as I am working with 90 different databases.

Thanks,
Thomas

[Sxooter]

Ummm. It can all be done in code, dynamically. I was showing what the array would look like AFTER you create it. Need some example code?

while ($row=pg_fetch_assoc($res)){
    $data[$row['topcat']][]=$row['bottomcat'];
}

[tlholley]

Sxooter,

Sorry, I don't know PHP. How would that fit in context to the query code that I gave?

Thanks.

[Sxooter]

It didn't. I started writing my post before you posted your code. It references the original post with the three level hierarchy you had listed. It should be easy enough to adapt to you code. But then, I haven't actually looked at your code. Looking at your code, it looks like you've got a those three levels in one single field? If so, then you could use a split command to break them up and make an array of them, then iterate the array to display it. That lets you easily modify your display code without rewriting everything.

[tlholley]

I looked up the split command.

I tried it, but it didn't print anything. I didn't get an error message either.

Am I close?

<?php
$login = "netshops";
$password = "feedme";
$table = "hammocks";
$aid = "cd1";
$database = "netshopsaffiliates_com_-_datafeeds";
$connection = mysql_connect("207.234.208.224","$login","$password")
	or die ("Unable to connect.");
mysql_select_db($database)
	or die ("Unable to open database.");
$sql = "SELECT DISTINCT category FROM $table ORDER BY category";
$result = mysql_query($sql);
list ($home, $cat1, $cat2) = split (">", $result);
$row = mysql_fetch_array($result);
while ($row = mysql_fetch_array($result))
	{
		extract($row);
		echo "<table width='600' border='0' cellspacing='0' cellpadding='3'> \n";
		echo "<tr>\n";
		echo "<td valign='top'>\n<b>$cat1</b><br><a href='list.php?cat=$category'> \n<b> $cat2 </b> </a> \n<br>\n";
		echo "</tr>\n";
		echo "</table>\n";
	}
mysql_close($connection);
?>

[liquorvicar]

you've got the split in the wrong place. you can't split $result as that is (or should be) a resource.

try this

$sql = "SELECT DISTINCT category FROM $table ORDER BY category";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
while ($row = mysql_fetch_array($result))
	{
		extract($row);
                list ($home, $cat1, $cat2) = split (">", $category);
		echo "<table width='600' border='0' cellspacing='0' cellpadding='3'> \n";
		echo "<tr>\n";
		echo "<td valign='top'>\n<b>$cat1</b><br><a href='list.php?cat=$category'> \n<b> $cat2 </b> </a> \n<br>\n";
		echo "</tr>\n";
		echo "</table>\n";
	}
mysql_close($connection);

[tlholley]

Fantastic!

One last little problem...

It prints

$cat1
$cat2

$cat1
$cat2

when I would really like it to print

$cat1
$cat2
$cat2

new$cat1
$cat2
$cat2

How can I make it print $cat1 once then each $cat2 under it, next $cat1 and each $cat2, etc. ?

This is the last piece to my puzzle.

Thanks so much!

[tlholley]

Is this close?

$sql = "SELECT DISTINCT category FROM $table ORDER BY category";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
while ($row = mysql_fetch_array($result))
	{
		extract($row);
                list ($home, $cat1, $cat2) = split (">", $category);
		echo "<b>$cat1</b> <br>\n";
		$i=0;
		echo "$cat2[$i++] <br> \n";
	}
mysql_close($connection);

I get this error:

Parse error: parse error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting ']'

[liquorvicar]

try this:

$lastcat1 = '';
$sql = "SELECT DISTINCT category FROM $table ORDER BY category";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
while ($row = mysql_fetch_array($result))
	{
		extract($row);
                list ($home, $cat1, $cat2) = split (">", $category);
		echo "<table width='600' border='0' cellspacing='0' cellpadding='3'> \n";
		echo "<tr>\n";
                echo "<td valign='top'>\n";
                if ($lastcat1!=$cat1) {
                             echo "<b>$cat1</b><br>";
                }
                 echo"<a href='list.php?cat=$category'> \n<b> $cat2 </b> </a> \n<br>\n";
		echo "</tr>\n";
		echo "</table>\n";
                $lastcat1 = $cat1;
	}
mysql_close($connection);

i think that's what you're after

[tlholley]

Thank you! Thank you! Thank you!

Did I say, "Thank you!"

Thank you!

[tlholley]

At one point I was so close...
I had that exactly except I forgot the last line,
"$lastcat1 = $cat1;"

I feel so much better...

[liquorvicar]

i find it so rude when you help people and they don't apologise 😉