Array Help

[johnrb87]

Hi

 

I have the following database table layout

 

INSERT INTO `user_menu` (`id`, `title`, `parent_id`,`url`) VALUES
(1, 'Home', 0,'index.htm'),
(2, 'Products', 1,'/products/'),
(3, 'Apple', 2,'/products/apple/'),
(4, 'Microsoft', 2,'/products/microsoft/');

 

Which the output would produce a menu such as

 

Home

  Products

      Apple

      Microsoft

 

My question is, if I write a QUERY such as

 

SELECT `title`, `url` FROM `user_menu` WHERE `url` = '/products/microsoft/';

 

How can I get PHP to output an ARRAY that looks like

 

<?php array("Home" => "index.htm", "Products" => "/products/", "Microsoft" => "/products/microsoft/"); ?>

 

I'm really struggling to do this.

 

Can anyone help.

 

Thanks very much

[jcbones]

SELECT
a.title, 
a.url,
b.title AS baseTitle,
b.url AS baseUrl,
c.title AS parentTitle,
c.url AS parentUrl
FROM
user_menu AS a
LEFT JOIN
user_menu as b
ON
b.id = a.parent_id
LEFT JOIN
user_menu AS c
ON
  c.id = b.parent_id
WHERE
a.url = '/products/microsoft/'

UNTESTED!!!

 

If the url belongs to a row that's parent ID is equal to an available ID, it will assign it to the `baseTitle` and `baseUrl`, and likewise if that tier also has a parent.  This query will only go 3 layers deep, if it even works at all.

[johnrb87]

thanks

 

the problem is that it can be made up of unlimited layers, so 3 deep would not really work on this

[jcbones]

You could also try.

 

SELECT
a.title, 
a.url,
a.parent_id,
b.id,
b.title AS baseTitle,
b.url AS baseUrl 
FROM
user_menu AS a
LEFT JOIN
user_menu as b
ON
b.id < a.parent_id
WHERE
a.url = '/products/microsoft/'

 

Then sort the results, via PHP, to get the desired links.

 

[johnrb87]

thanks for that help.

 

could you help by showing me the PHP code which would compile that?

 

thanks