Join Query menu

[zirax]

Hello,

I'm busy with developing my very own CMS and now i'm trying to create a dynamically menu with mysql now i have the following code and it works perfect:

    <ul id="nav">
        <li class="first"><a href="#">Home</a></li>
        <?php
        $sCSql = mysql_query("Select * From cms_menu_categories");
        
        while($test = mysql_fetch_assoc($sCSql)){
            if(!empty($test['c_Pages'])){
            echo '<li><a href="#">'.$test['c_Name'].'</a><ul>';
                $testing = explode(' - ', $test['c_Pages']);
                
                foreach($testing as $line){
                    echo '<li><a href="/'.strtolower($test['c_Name']).'/'.strtolower($line).'">'.$line.'</a></li>';
                }
            echo '</ul>';
        ?>
        
        <?php
        }else{
            echo '<li><a href="#">'.$test['c_Name'].'</a><ul>';
        }
        }
        ?>
        </ul>

but i want to work with 2 database tables named: "cms_menu_categories" and "cms_menu_pages"

and the linking between the two tables is a field "p_cat_id" and "c_id"

so i want if i create a page i can assign a categorie to it and it wil be created like the code above, without the explode,

does someone has the time to create the join query , or atleast explain it to me how to link the two tables.

Kind regards,
Kevin

 

[foxx]

the thing is, you can't like.. join another table's results into one result, not if there were more of those results, you know, join would work only if one category had ONE page assigned, but categories definitely have more pages assigned,.. so you have to,

1) get categories with your "SELECT * FROM cms_menu_categories"

2) run foreach or while or whatever on it and inside that foreach you do another query for pages, so like

$query = "SELECT * FROM cms_menu_categories";   
$result = mysql_fetch_assoc($query);

$pagesForCat = array();
foreach ($result as $row) {
  $pageQuery = "SELECT * FROM cms_menu_pages WHERE p_cat_id = ".$row['c_id'];
  $pages = mysql_fetch_assoc($pageQuery);
  
  $pagesForCat[$row['c_id']] = $pages;
}

so with this you get array which's key is your cat id with array of all that category pages inside

3) when you are printing your categories, you just do like

<ul>
<?php
foreach ($result as $row) {
  echo '<li>'.$row['category_name'];

  if (isset($pagesForCat[$row['c_id']])) {
     echo '<ul>';

     foreach ($pagesForCat[$row['c_id']] as $page) {
        echo '<li>'.$page['page_name'].'</li>';
     }

     echo '<ul>';
  }
  echo '</li>'; 
}
?>
</ul>

this would print for example

<ul>
  <li>Colours
    <ul>
      <li>Blue</li>
      <li>Green</li>
    </ul>
  </li>
</ul>

so.. that's it, I suppose it's not really clear, but if you got at least some brains, you should get the point

 

[jMerliN]

Learn SQL

You must be registered to see links

.

Also, duck Oracle for ruining the MySQL website and making the enterprise editions cost almost as much as Oracle does. Think we need LibreSQL now.

And contrary to what foxx says, you can do the same thing with 1 query. Learn about joins:

You must be registered to see links

)

You must be registered to see links

 

[foxx]

postgres is fine

 

[jMerliN]

postgres is not fine

Fixed that for you. I would use MSSQL and face the cost of licensing long before I'd use postgres. MySQL is merely vastly superior as a small scale database, and now Oracle seems to be trying to push its enterprise MySQL customers away towards MSSQL, because nobody in their right mind is going to buy Oracle, it's a horrendous product and it's too expensive.

Need to just fork MySQL like OO is being forked, take EVERYTHING away from Oracle that was open source in Sun, because Oracle is anti-opensource, and it says bad things to have 2 of the biggest OSS applications in their possession.

 

[foxx]

Learn SQL

You must be registered to see links

.

Also, duck Oracle for ruining the MySQL website and making the enterprise editions cost almost as much as Oracle does. Think we need LibreSQL now.

And contrary to what foxx says, you can do the same thing with 1 query. Learn about joins:

You must be registered to see links

)

You must be registered to see links

so you telling me you can do

array(
 array(
  "id_cat" => 1,
  "name_cat" => "colours",
  "text_cat" => "this is a colours category",
  "pages" => array(
     array(
       "id_pge" => 1,
       "idcat_pge" => 1,
       "name_pge" => "blue",
       "text_pge" => "blue page" 
     ),
     array (
       "id_pge" => 2,
       "idcat_pge" => 1,
       "name_pge" => "green",
       "text_pge" => "green page" 
     )
  )
  array (
    "id_cat" => 2,
    "name_cat" => "cars",
    "text_cat" => "this is a cars category",
    "pages" => array(
      array(
        "id_pge" => 3,
        "idcat_pge" => 2,
        "name_pge" => "ford",
        "text_pge" => "yeah fords"
      )
    )
  )

)

with just one query? cool, always wanted to be able to do that, teach me

 

[jMerliN]

so you telling me you can do

array(
 array(
  "id_cat" => 1,
  "name_cat" => "colours",
  "text_cat" => "this is a colours category",
  "pages" => array(
     array(
       "id_pge" => 1,
       "idcat_pge" => 1,
       "name_pge" => "blue",
       "text_pge" => "blue page" 
     ),
     array (
       "id_pge" => 2,
       "idcat_pge" => 1,
       "name_pge" => "green",
       "text_pge" => "green page" 
     )
  )
  array (
    "id_cat" => 2,
    "name_cat" => "cars",
    "text_cat" => "this is a cars category",
    "pages" => array(
      array(
        "id_pge" => 3,
        "idcat_pge" => 2,
        "name_pge" => "ford",
        "text_pge" => "yeah fords"
      )
    )
  )

)

with just one query? cool, always wanted to be able to do that, teach me

Sure you can. Joins are magical. Just do a bit of reading.

 

[foxx]

I tried it, I failed, so give me an example please, it would come handy, really.

---------- Post added at 12:58 PM ---------- Previous post was at 12:57 PM ----------

If it's not something exclusive to mysql, because I'm using that nasty postgres.

---------- Post added at 01:00 PM ---------- Previous post was at 12:58 PM ----------

with which I'd always end up with something like this

array(
 array( 
  "id_cat" => 1, 
  "name_cat" => "colours", 
  "text_cat" => "this is a colours category", 
  "id_pge" => 1, 
  "idcat_pge" => 1, 
  "name_pge" => "blue", 
  "text_pge" => "blue page"   
 )

 array( 
  "id_cat" => 1, 
  "name_cat" => "colours", 
  "text_cat" => "this is a colours category", 
  "id_pge" => 2, 
  "idcat_pge" => 1, 
  "name_pge" => "green", 
  "text_pge" => "green page" 
 )
)

 

[jMerliN]

You seem to be complaining about a little redundancy.

 

[foxx]

I'm not complaining about anything, I just want to know how achieve the result I've described here, without searching for it, because I well tried, guess I don't know the right keyword. If you know how to do it, it'd be a great help to both me and the OP.

 

[jMerliN]

MySQL doesn't know anything about PHP arrays. MySQL will gladly return all of the same information to you with 1 query, though. So do your job and turn that resultset into the array you want.

 

[foxx]

I know right, that's why I told him to do it the way I told him.

Because I have no idea how do you turn this

array(
 array( 
  "id_cat" => 1, 
  "name_cat" => "colours", 
  "text_cat" => "this is a colours category", 
  "id_pge" => 1, 
  "idcat_pge" => 1, 
  "name_pge" => "blue", 
  "text_pge" => "blue page"   
 )

 array( 
  "id_cat" => 1, 
  "name_cat" => "colours", 
  "text_cat" => "this is a colours category", 
  "id_pge" => 2, 
  "idcat_pge" => 1, 
  "name_pge" => "green", 
  "text_pge" => "green page" 
 )
)

into this

array( 
 array( 
  "id_cat" => 1, 
  "name_cat" => "colours", 
  "text_cat" => "this is a colours category", 
  "pages" => array( 
     array( 
       "id_pge" => 1, 
       "idcat_pge" => 1, 
       "name_pge" => "blue", 
       "text_pge" => "blue page"  
     ), 
     array ( 
       "id_pge" => 2, 
       "idcat_pge" => 1, 
       "name_pge" => "green", 
       "text_pge" => "green page"  
     ) 
  ) 
  array ( 
    "id_cat" => 2, 
    "name_cat" => "cars", 
    "text_cat" => "this is a cars category", 
    "pages" => array( 
      array( 
        "id_pge" => 3, 
        "idcat_pge" => 2, 
        "name_pge" => "ford", 
        "text_pge" => "yeah fords" 
      ) 
    ) 
  ) 

)

in a sufficient, better, faster, cleaner, whatever way than my method.

Also this

Sure you can. Joins are magical. Just do a bit of reading.

pretty much sounded like the result would be directly that, just with a bit of reading..

---------- Post added at 02:51 PM ---------- Previous post was at 02:49 PM ----------

or well I do, by looping throught it and reforming it according to idcat_pge, but hey, I'd rather do it my way than this

 

[jMerliN]

I know right, that's why I told him to do it the way I told him.

Because I have no idea how do you turn this

array(
 array( 
  "id_cat" => 1, 
  "name_cat" => "colours", 
  "text_cat" => "this is a colours category", 
  "id_pge" => 1, 
  "idcat_pge" => 1, 
  "name_pge" => "blue", 
  "text_pge" => "blue page"   
 )

 array( 
  "id_cat" => 1, 
  "name_cat" => "colours", 
  "text_cat" => "this is a colours category", 
  "id_pge" => 2, 
  "idcat_pge" => 1, 
  "name_pge" => "green", 
  "text_pge" => "green page" 
 )
)

into this

array( 
 array( 
  "id_cat" => 1, 
  "name_cat" => "colours", 
  "text_cat" => "this is a colours category", 
  "pages" => array( 
     array( 
       "id_pge" => 1, 
       "idcat_pge" => 1, 
       "name_pge" => "blue", 
       "text_pge" => "blue page"  
     ), 
     array ( 
       "id_pge" => 2, 
       "idcat_pge" => 1, 
       "name_pge" => "green", 
       "text_pge" => "green page"  
     ) 
  ) 
  array ( 
    "id_cat" => 2, 
    "name_cat" => "cars", 
    "text_cat" => "this is a cars category", 
    "pages" => array( 
      array( 
        "id_pge" => 3, 
        "idcat_pge" => 2, 
        "name_pge" => "ford", 
        "text_pge" => "yeah fords" 
      ) 
    ) 
  ) 

)

in a sufficient, better, faster, cleaner, whatever way than my method.

Also this

pretty much sounded like the result would be directly that, just with a bit of reading..

---------- Post added at 02:51 PM ---------- Previous post was at 02:49 PM ----------

or well I do, by looping throught it and reforming it according to idcat_pge, but hey, I'd rather do it my way than this

Multiple queries is always worse when you only need 1. If you do 7 queries to add something to some convoluted database setup, make a SP that does all that work and pass it the parameters it needs. Doing a select * on some menu then for each row doing a select from a table is an abysmally slow solution. First, it can't be cached, the individual operations always incur a penalty. Second, each query invokes I/O which ALWAYS causes context switches because for 7ms your computer is doing a SHITLOAD of things. A single more complex join query can be cached, and there's a potential that the implementation of that join is quite a bit more efficient than a select* followed by a few select where's.

You only need to order by the information you consider a key (in your PHP array), the database will return the information so that this key is the same for the next N entries, and hell, you can even add on a count column if you want to make the array building code a bit more efficient, you simply ignore the redundant bits and build the array how you want it.

And finally, this should only be done once per session and cached, regardless. With something like memcached this will be cached almost certainly but you still incur an I/O penalty each time the query happens, but if you throw it in the session, you don't get a penalty except when the session in question gets recycled out of cache, which is something you can control and for an active user is not likely. Also, straight disk writes/reads are generally faster than establishing a TCP connection then fetching some data that way.

 

[Madison]

Multiple queries is always worse when you only need 1.

I don't fully agree with you on this point.

It all depends on the amount of data you are dealing with. The main issue with doing separate database calls is computation time. If you are dealing with the difference between using one or two queries, often you will not see a big difference in the computation time if your dataset is relatively small.

Sometimes it is far better to write simple easy-to-read code that has extra database calls in it, instead of writing something more complicated that shaves off a few ms. Think maintainability.

Considering the OP is asking how to do some menu work for a CMS, I can't imagine that the tables being used are going to hold thousands of records. I am guessing they are all less than a hundred, and I don't see how that would make a noticeable difference.

If you are dealing with a huge set of data in the database, then you want to fully optimize your queries and run as few as possible. In that situation the suggestion of using less database calls is definitely where you want to start in order to speed things up. However, I have seen terribly written sql statements that were worse than running multiple database calls. (Sadly I can recall having to fix things someone else wrote with this problem on more than one occasion.)

I think in this case foxx's solution would work just fine for what the OP is doing. It might not be the most elegant optimized solution, but it should be easily implementable and maintainable for a lesser experienced coder.

 

[jMerliN]

I don't fully agree with you on this point.

It all depends on the amount of data you are dealing with. The main issue with doing separate database calls is computation time. If you are dealing with the difference between using one or two queries, often you will not see a big difference in the computation time if your dataset is relatively small.

Sometimes it is far better to write simple easy-to-read code that has extra database calls in it, instead of writing something more complicated that shaves off a few ms. Think maintainability.

Considering the OP is asking how to do some menu work for a CMS, I can't imagine that the tables being used are going to hold thousands of records. I am guessing they are all less than a hundred, and I don't see how that would make a noticeable difference.

If you are dealing with a huge set of data in the database, then you want to fully optimize your queries and run as few as possible. In that situation the suggestion of using less database calls is definitely where you want to start in order to speed things up. However, I have seen terribly written sql statements that were worse than running multiple database calls. (Sadly I can recall having to fix things someone else wrote with this problem on more than one occasion.)

I think in this case foxx's solution would work just fine for what the OP is doing. It might not be the most elegant optimized solution, but it should be easily implementable and maintainable for a lesser experienced coder.

A basic join is considered beginner level SQL, and it also happens to be better in almost every measure for this problem, so it makes little sense to use multiple queries to perform the same task. And if this CMS code is going to be invoked by a page loaded by any substantial number of users, the size of the data is only a partial factor. This isn't really optimization, it's just better practice.

I optimized code a few weeks ago for a web app that used many many split queries like this on what I consider small datasets at around 10k rows, this app took over 40 seconds to perform a simple database action, re-writing the code (using about 40% less code btw) to use a single, very slightly more complex query, reduced that by a factor of around 500x. It's also more readable to anyone who understands SQL. And you shouldn't be using SQL if you haven't at least read a little documentation or a book on it, select x from y is wholly insufficient to properly use a database.

 

[foxx]

it was bullcrap

 

[foxx]

hmpf.. whoops, sql result would look differently, my bad