PHP - Nested List Broken into Even Columns (fix and updates)

Question

I have a follow-up to a previous thread/question that I hope can be solved by relatively small updates to this existing code. In the other thread/question, I pretty much solved a need for a nested unordered list. I needed the nested unordered list to be broken up into columns based on the number of topics.

For example, if a database query resulted in 6 topics and a user specified 2 columns for the layout, each column would have 3 topics (and the related news items below it).

For example, if a database query resulted in 24 topics and a user specified 4 columns for the layout, each column would have 6 topics (and the related news items below it).

The previous question is called PHP - Simple Nested Unordered List (UL) Array. The provided solution works pretty well, but it doesn't always divide correctly. For example, when $columns = 4, it only divides the columns into 3 groups. The code is below.
Another issue that I'd like to solve was brought to my attention by the gentleman who answered the question. Rather than putting everything into memory, and then iterating a second time to print it out, I would like to run two queries: one to find the number of unique TopicNames and one to find the number of total items in the list.
One last thing I'd like to solve is to have a duplicate set of code with an update that breaks the nested unordered list into columns based on the number of news items (rather than categories). So, this would probably involve just swapping a few variables for this second set of code.

So, I was hoping to solve three issues:

1.) Fix the division problem when relying on the number of categories (unordered list broken up into columns based on number of topics)

2.) Reshape the PHP code to run two queries: one to find the number of unique TopicNames and one to find the number of total items in the list

3.) Create a duplicate set of PHP code that works to rely on the number of news items rather than the categories (unordered list broken up into columns based on number of news items)

Could anyone provide an update or point me in the right direction? Much appreciated!

    $columns = // user specified;

    $result = mysql_query("SELECT * FROM News");
    $num_articles = 0;

    // $dataset will contain array( 'Topic1' => array('News 1', 'News2'), ... )
    $dataset = array();
    while($row = mysql_fetch_array($result)) {
        if (!$row['TopicID']) {
            $row['TopicName'] = 'Sort Me';
        }
        $dataset[$row['TopicName']][] = $row['NewsID'];
        $num_articles++;
    }

    $num_topics = count($dataset);

    // naive topics to column allocation
    $topics_per_column = ceil($num_topics / $columns);

    $i = 0; // keeps track of number of topics printed
    $c = 1; // keeps track of columns printed
    foreach($dataset as $topic => $items){
        if($i % $topics_per_columnn == 0){
            if($i > 0){
                echo '</ul></div>';
            }
            echo '<div class="Columns' . $columns . 'Group' . $c . '"><ul>';
            $c++;
        }
        echo '<li>' . $topic . '</li>';
        // this lists the articles under this topic
        echo '<ul>';
        foreach($items as $article){
            echo '<li>' . $article . '</li>';
        }
        echo '</ul>';
        $i++;
    }
    if($i > 0){
        // saw at least one topic, need to close the list.
        echo '</ul></div>';
    }

UPDATE 12/19/2011: Separating Data Handling from Output Logic (for the "The X topics per column variant"):

Hi Hakre: I've sketched out the structure of my output, but am struggling with weaving the two new functions with the old data handling. Should the code below work?

/* Data Handling */

$columns = // user specified;

$result = mysql_query("SELECT * FROM News LEFT JOIN Topics on Topics.TopicID = New.FK_TopicID WHERE News.FK_UserID = $_SESSION[user_id] ORDER BY TopicSort, TopicName ASC, TopicSort, NewsTitle");

$num_articles = 0;

// $dataset will contain array( 'Topic1' => array('News 1', 'News2'), ... )
$dataset = array();
while($row = mysql_fetch_array($result)) {
    if (!$row['TopicID']) {
        $row['TopicName'] = 'Sort Me';
    }
    $dataset[$row['TopicName']][] = $row['NewsID'];
    $num_articles++;
}

/* Output Logic */

function render_list($title, array $entries)
{
    echo '<ul><li>', $title, '<ul>';
    foreach($entries as $entry)
    {
        echo '<li>', $entry['NewsID'], '</li>';
    }
    echo '</ul></li></ul>;
}

function render_column(array $topics)
{
    echo '<div class="column">';
    foreach($topics as $topic)
    {
        render_list($topic['title'], $topic['entries']);
    }
    echo '</div>';
}

hakre · Accepted Answer · 2011-12-03 01:51:23Z

1

You have not shown in your both questions what the database table is, so I can not specifically answer it, but will outline my suggestion.

You can make use of aggregation functions in mysql to obtain your news entries ordered and grouped by topics incl. their count. You can do two queries to obtain counts first, that depends a bit how you'd like to deal with your data.

In any case, using the mysql_... functions, all data you selected from the database will be in memory (even twice due to internals). So having another array as in your previous question should not hurt much thanks to copy on write optimization in PHP. Only a small overhead effectively.

Next to that before you take care of the actual output, you should get your data in order so that you don't need to mix data handling and output logic. Mixing does make things more complicated hence harder to solve. For example if you put your output into simple functions, this gets more easy:

function render_list($title, array $entries)
{
    echo '<ul><li>', $title, '<ul>';
    foreach($entries as $entry)
    {
        echo '<li>', $entry['NewsID'], '</li>';
    }
    echo '</ul></li></ul>;
}

function render_column(array $topics)
{
    echo '<div class="column">';
    foreach($topics as $topic)
    {
        render_list($topic['title'], $topic['entries']);
    }
    echo '</div>';
}

This already solves your output problem, so we don't need to care about it any longer. We just need to care about what to feed into these functions as parameters.

The X topics per column variant:

With this variant the data should be an array with one topic per value, like you did with the previous question. I would say it's already solved. Don't know which concrete problem you have with the number of columns, the calculation looks good, so I skip that until you provide concrete information about it. "Does not work" does not qualify.

The X news items per column variant:

This is more interesting. An easy move here is to continue the previous topic with the next column by adding the topic title again. Something like:

Topic A    Topic A    Topic B
 - A-1      - A-5      - B-4
 - A-2     Topic B     - B-5
 - A-3      - B-1      - B-6
 - A-4      - B-2
            - B-3

To achieve this you need to process your data a bit differently, namely by item (news) count.

Let's say you managed to retrieve the data grouped (and therefore sorted) from your database:

SELECT TopicName, NewsID FROM news GROUP BY 1;

You can then just iterate over all returned rows and create your columns, finally output them (already solved):

$itemsPerColumn = 4;

// get columns
$topics = array();
$items = 0;
$lastTopic = NULL;

foreach ($rows as $row)
{
    if ($lastTopic != $row['TopicName'])
    {
        $topic = array('title' => $row['TopicName']);
        $topics[] = &$topic;
    }
    $topic['entries'][] = $row;

    $items++;
    if ($items === $itemsPerColumn)
    {
        $columns[] = $topics;
        $topics = array();
        $lastTopic = NULL;
    }
}

// output
foreach($columns as $column)
{
    render_column($column);
}

So this is actually comparable to the previous answer, but this time you don't need to re-arrange the array to obtain the news ordered by their topic because the database query does this already (you could do that for the previous answer as well).

Then again it's the same: Iteration over the returned result-set and bringing the data into a structure that you can output. Input, Processing, Output. It's always the same.

Hope this is helpful.

answered Dec 3, 2011 at 1:51

hakre

200k55 gold badges454 silver badges865 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

webdude77 Over a year ago

Dear Hakre The database table is "News". I do believe your post is helpful, but I am having a hard time integrating each of the sets of code to the existing set of code (from the previous question/solution).

webdude77 Over a year ago

For the existing solution in the previous question, I had mentioned that it does not always work because of the following. I have test data that results in the query showing 9 topics with anywhere from 1-15 news items below each. When columns is set to 1, 2, 3 and 5, it works beautifully and displays it's respective number of columns. When set to 4 columns, it only renders 3 columns. When set to 6, 7 or 8 columns, it only renders 5 columns. So, I am not sure why it is behaving in this manner.

webdude77 Over a year ago

For the first one in which you recommend to get the data in order (so that I don't mix data handling and output logic), do the 2 functions (function render_list and function render_column) simply replace the output? Do I still use these 4 variables? Could it be possible to provide the complete set of code? $columns = // user specified; $result = mysql_query("SELECT * FROM News"); $num_articles = 0; // $dataset will contain array( 'Topic1' => array('News 1', 'News2'), ... ) $dataset = array();

webdude77 Over a year ago

For the second one you call "X news items per column variant", should I use also use the above 4 variables? Would you be able to integrate the code? P.S. I am trying to learn PHP and mySQL and have been doing do slowly for some time. I am still picking up some basic concepts.

hakre Over a year ago

To pick up basic concepts, draw yourself a picture first how things could work. You always have a list of your rows and then distribute them over the columns like you want to. It's better to work the shema out before starting to code otherwise it's hard to understand.

|

Collectives™ on Stack Overflow

PHP - Nested List Broken into Even Columns (fix and updates)

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related