Divs according to mysql result in a loop

Question

First off, sorry if the title is confusing! I'm working on a part of a blog that determines how lists of recent posts (sometimes determined by a date) in a category are to be displayed. In the database, each category has a "type" set that determines whether the div it goes in is small or large.

I can get the results to display but each link in the list has its own div, instead of being contained in one "main" category div.

Shorter explanation: The database pulls results from the categories table and the posts table, as you can see in the query. The loop then runs so that the category boxes and the links in them can be created dynamically. The problem is that the divs, which are supposed to house a list of links, are instead wrapping around each individual link and not the set that is desired.

While the code below shows only two "types", I had planned on adding more in the future.

Here is the code

                <?
            require("classes/Database.class.php"); 

            $db = new Database(DB_SERVER, DB_USER, DB_PASS, DB_DATABASE); 
            $time = $_REQUEST['time'];

            $db->connect(); 

            $sql = "SELECT 
                cm.id,
                cm.title AS cmtitle,
                cm.sectionid,
                cm.type AS cmtype,
                cd.id,
                cd.time,
                cd.link,
                cd.title,
                cd.description,
                cd.sectionid AS sectionid
                FROM c_main AS cm
                JOIN c_data AS cd ON cd.sectionid=cm.sectionid
                WHERE cd.sectionid=cm.sectionid AND time = '".$time."' ORDER by id ASC";

            $rows = $db->query($sql); 


            while($record = $db->fetch_array($rows)){

                          //determine what div to use by checking "type"
            if ($record['cmtype'] == 'large') {
            ?>
            <div class="large" >
            <?
            } elseif ($record['cmtype'] == 'small') { 
            ?>
            <div class="small">
            <?

            } 

            for($x =0; $x <= $db->affected_rows; ++$x)
            {
            if ($record['cmtype'] == 'small') {
            echo $record['title'].'<br/>';
            } else {
            echo $record['title'].'<br/>'.$record['description'];

            }
            break;
            }
            echo '</div>';

            }
            $db->close(); 
            ?>   

Basically I am trying to have it like this:

    /-------------------\
   |    Category Title  |
   |  -relevant link    |
   |  -relevant link    |
   \____________________/ 
    /-------------------\
   |    Category Title  |
   |  -relevant link    |
   |  -relevant link    |
   \____________________/ 
 .... and so on for each subsequent category

And not this (which is how it's being outputted)

/-------------\
\relevant link/
 -------------
/-------------\
\relevant link/
 -------------
 etc.

Here is the mysql wrapper I'm currently using, if that's of any significance http://ricocheting.com/scripts/php_mysql_wrapper.php

Answer 1

Maybe I get this completely wrong, but if you want to display the elements grouped by their type I think you should order them by type straight away, so that order of elements is correct when you start outputting HTML code?

Answer 2

If you only want 2 divs, one large and one small, you should do ORDER BY cmtype, and then you will only need to create a div when the type changes or it is the first type.

Answer 3

If you are certain about your table contents you can use:

echo "<div class='" . $record['cmtype'] . "'>";

Answer 4

Maybe i am wrong in understanding your question, but you try to add all relevant link in a single DIV, then check your php result html (browser->view source)

Answer 5

your code will echo a div for every row, that is your problem/question.

my suggestion is to create two queries: one selecting all links of type A and the second query selecting all links of type B (WHERE type = '…')

then you'll have to print the starting tag of your first div:

echo '<div class="large">';

after that, loop your result and output your links:

while(($record = $db->fetch_assoc($rows)) !== FALSE){
  echo htmlspecialchars($record['title']),'<br/>';
}

close your div:

echo '</div>';

now you can start your second category, same game, same rules:

• open div
• loop results
• close div

~~~

another way like others said to order your result by type, then by id (ORDER BY type, id) and then check for a change in the row’s type:

$currenttype = '';
while(($record = $db->fetch_assoc($rows)) !== FALSE) {
  $currenttype = $record['cmtype'];
  echo '<div class="',htmlspecialchars($record['cmtype']),'">';

  while($record['cmtype'] == $currenttype
  && ($record = $db->fetch_assoc($rows)) !== FALSE) {
    // output link according to your type:
    echo htmlspecialchars($record['title']);
  }

  echo '</div>';
}

i assume this is what you wanted to achieve in the first place