PHP-MySQL getting data from multiple tables

[naczu]

I have a problem. I can't solve it. I am not good in PHP-MySQL.

       

I have three tables.

       

First Table : linkss    --> rows: linkid,link,orderlink

       

Second Table: sublinkss1 --> rows: sublink1id,sublink1,ordersublink1,link,linkid

       

Third table : sublinkss2 --> rows: sublink2id,sublink2,ordersublink2,sublink1,sublink1id

   

     

   

   

 

$get_data=mysql_query("select linkss.link, sublinkss1.sublink1, sublinkss2.sublink2 from linkss,sublinkss1, sublinkss2 where sublinkss1.linkid=linkss.linkid and sublinkss2.sublink1id=sublinkss1.sublink1id order by orderlink, ordersublink1, ordersublink2");
        
   

     while ($row=mysql_fetch_assoc($get_data)) {
        
        $data[$row["link"]][$row["sublink1"]][$row["sublink2"]]=array($row["link"],$row["sublink1"],$row["sublink2"]);
        
        }
    
    
    
        $smarty = new Smarty();
        
        $smarty->assign('data',$data);

 

and tpl file is this

 

   

<ul>
    {foreach key=link from=$data item=linkss}
    <li><a href="#">{$link}</a>
    <ul>
    {foreach key=sublink1 item=sublinkss1 from=$linkss }
    <li><a href="#">
    {$sublink1}
    </a>
    
    <ul>
    {foreach key=sublink2 item=sublinkss2 from=$sublinkss1 }
    <li><a href="#">
    {$sublink2}
    </a>
    
    </li>
    {/foreach}
    </ul>
    
    </li>
    {/foreach}
    </ul>
    
    </li>
    {/foreach}
    </ul>

For example some datas are stored in the database and I want output like this

 

<ul>

<li>Home Page

<ul><li>Projects</li></ul>

</li>

<li>About Us

<ul><li>History</li></ul>

<ul><li>Products

<ul><li>Product1</li></ul>

<ul><li>Product2</li></ul>

<ul><li>Product3</li></ul>

</li></ul>

</li>

<li>Contact Us</li>

</ul>

 

 

 

But these codes are not working properly. I tried to use inner join and left join but I couldn't handle it. Could you help me please. Thanks in advance

 

 

 

 

 

 

[cpd]

You must first establish if your actually receiving a result set with data in, or a result set at all. Add echo mysql_error(); after the mysql_query line; it should highlight any errors for you. Your data isn't normalised either so your going to have a hard time using that database design. Re-think the design of your database and do some research on normalisation.

 

A little advice on your post

[*]It may seem a little pedantic but your first and last sentences talk about you needing help and you having a problem. We know you have a problem else you wouldn't be here and we know you want help hence your post. Just highlight your issue within the first sentence and give examples

[*]You haven't said if your receiving any errors or anything. Just that no data appears. Saying, "the codes don't work properly" doesn't tell us anything, we know the code doesn't work else you wouldn't be here.

[*]Whenever your using some sort of template engine, assumed smarty here, highlight it to make it easier for us.

[naczu]

Yes you are right... I am so sorry...

 

For example in the database "Home Page" is stored  in linkss as link and it's linkid is "1"

and "Projects" is a sublink of Home Page and it is stored in sublinkss1 as sublink1.

 

after while loop; when I use

 

echo var_dump($data);

 

I get this error "Notice: Undefined variable: data in C:\xampp\htdocs\smarty\1\index.php on line 25"

 

But if I fill the sublinkss3 the array is working. namely, if I add SmallProject as sublinkss3 in the database;

 

echo var_dump($data);

 

it result  is

 

array(1) { ["Home Page"]=> array(1) { ["Projects"]=> array(1) { ["Small Project"]=> array(3) { [0]=> string(1) "1" [1]=> string(1) "1" [2]=> string(1) "1" } } } }

 

 

the array has 3 dimensions... if I use 2 links, it returns Null.... I want it to be 2 dimensions when I use 2 links... or I want it to be 1 dimension when I use  only one link...

 

I hope I could explain my problem. Thank you so much for your reply

 

 

 

 

 

 

 

 

You must first establish if your actually receiving a result set with data in, or a result set at all. Add echo mysql_error(); after the mysql_query line; it should highlight any errors for you. Your data isn't normalised either so your going to have a hard time using that database design. Re-think the design of your database and do some research on normalisation.

 

A little advice on your post

[*]It may seem a little pedantic but your first and last sentences talk about you needing help and you having a problem. We know you have a problem else you wouldn't be here and we know you want help hence your post. Just highlight your issue within the first sentence and give examples

[*]You haven't said if your receiving any errors or anything. Just that no data appears. Saying, "the codes don't work properly" doesn't tell us anything, we know the code doesn't work else you wouldn't be here.

[*]Whenever your using some sort of template engine, assumed smarty here, highlight it to make it easier for us.

[cpd]

Okay first thing is first, you need to redesign your database. You don't have a new table for each sublink. All links should be held in a single tale. You merely need the following columns:

 

• menuID
  
• displayName
  
• link
  
• parentID (DEFAULT NULL)

 

Any entry with a NULL parentID is a parent link. Anything with an ID is a child. This allows lots of children and its up to you how much you limit it - effectively a child link can also have many children and so on.

 

Once you've done that, re-write your code and if you encounter any problems come back. At the minute your current design is a lost cause.

[naczu]

yes I found the solution... thanks for your reply... here is solution.. But I still want to know how to manage dimensions of an array from mysql...

 

 

CREATE TABLE `menu` (
  `id` int(11) NOT NULL auto_increment,
  `label` varchar(50) NOT NULL default '',
  `link` varchar(100) NOT NULL default '#',
  `parent` int(11) NOT NULL default '0',
  `sort` int(11) default NULL,
  PRIMARY KEY  (`id`),
) ENGINE=MyISAM AUTO_INCREMENT=248 DEFAULT CHARSET=latin1;

 

 

and then here is the function

 

function display_children($parent, $level) {
    $result = mysql_query("SELECT a.id, a.label, a.link, Deriv1.Count FROM `menu` a  LEFT OUTER JOIN (SELECT parent, COUNT(*) AS Count FROM `menu` GROUP BY parent) Deriv1 ON a.id = Deriv1.parent WHERE a.parent=" . $parent);
    echo "<ul>";
    while ($row = mysql_fetch_assoc($result)) {
        if ($row['Count'] > 0) {
            echo "<li><a href='" . $row['link'] . "'>" . $row['label'] . "</a>";
		display_children($row['id'], $level + 1);
		echo "</li>";
        } elseif ($row['Count']==0) {
            echo "<li><a href='" . $row['link'] . "'>" . $row['label'] . "</a></li>";
        } else;
    }
    echo "</ul>";
}

 

and here to run the function call like this

 

display_children(0, 1);

 

 

[cpd]

What do you mean "manage dimensions of an array from MySQL"? That doesn't really make sense in itself unless your referring to the 1 dimensional array you can retrieve.