Displaying mysql with while & foreach in a proper way

Question

Im trying to display this table:

(To clarify: Uren = Hours, Maandag = Monday, Dinsdag = Tuesday, Woensdag = Wednesday, Donderdag = Thursday, Vrijdag = Friday.

Now im planning to show all data like this in different tables, on the same page in a some kind of "list":

Monday 
Hours: Monday   
1.     Index
2.     Index
3.     Index
4.     Index
5.     Index
6.     Index
7.     Index
8.     Index

  Tuesday
 etc. etc.

Same for all other days~

Only the first 8 hours all the time. Now i got this code:

public function notLoggedIn($whichtable, $whichrow, $link, $preference, $dag, $uur) { $this->newfunction = new functions;

    $getvalue = $this->newGet();
    $resultaat = DB_connect()->query("SELECT * FROM $whichtable WHERE $whichrow = '$preference'");

    $days = array("Maandag", "Dinsdag", "Woensdag", "Donderdag", "Vrijdag"); // take all dagen op
    $loop = array();

    while($rij = $resultaat->fetch_assoc())
    {
            $loop[] = $rij;
    }

    foreach ($days as $currentDay) 
    {   
    echo "<table style='float:left; width:100%;'>";     
        echo "<th></th><th style='width:100%; left:0; background-color:#9a136e; color:#fff; font-size:20px;'>
        <div style='margin-auto;'>$currentDay</div></th>";  
        foreach($loop as $test) 
        {   
            $day = $test[$currentDay]; 
            $uren = $test['Uren'];

            $getkleur = explode(",", $day); 

                    echo "<tr>"; ?> <th style='width:20%; display:$getkleur[5]; border-right:1px dotted #000;'>
                    <? $this->newfunction->linkToHoure($uren)?></th><?
                    echo "<td style='border-bottom:1px solid #999; display:$getkleur[5]; width:100%; height:40px;'>".$getkleur[0]."
                    <a style='font-size:24px; margin-top:-7px; position:absolute; right:0;' href='#'>></a></td></tr>

                    <tr><td></td><td style='width:100%;'><b><a style='background-color:$getkleur[4]; color:#fff;' href=''>".$getkleur[3]."</b>
                    <b><a style='color:#906;' href=''>". $getkleur[2]."</b></a></td></tr>";             
        } // close foreach
    echo "</table>";
    } 
    }// close notloggedin function

Now it does display everthing on the same page , but like this:

 1. (Index of monday). Monday
    1. (Index of Tuesday). Tuesday
    1. (Index of Wednesday). Wednesday
    1. (Index of Thursday). Thursday
    1. (Index of Friday). Friday
    2. (Index of Monday). Monday
    2. (Index of Tuesday). Tuesday
    2. (Index of Wednesday). Wednesday
    2. (Index of Thursday). Thursday
    2. (Index of Friday). Friday

Till 8... Instead of the example above. But it should get seperated in hours & days.

I got no idea how to solve this. All help is appriciated..

Answer 1

Oke I took a look and I think I've got what you want. First you make two tables in your db like:

A table for the daily events (I assumed they where school subjects. correct me If I'am wrong!):

CREATE TABLE IF NOT EXISTS `vakken` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `vaknaam` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

Insert some sample data:

INSERT INTO `vakken` (`id`, `vaknaam`) VALUES
(1, 'Nederlands'),
(2, 'Engels'),
(3, 'Duits'),
(4, 'Wiskunde'),
(5, 'Rekenen'),
(6, 'programming php'),
(7, 'Biologie'),
(8, 'Grieks'),
(9, 'Geschiedenis'),
(10, 'Scheikunde');

Last table for the daily overview:

CREATE TABLE IF NOT EXISTS `lesuren` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `les_id` int(11) NOT NULL,
  `uur` tinyint(2) NOT NULL,
  `dag` tinyint(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=43 ;

Also insert some sample data:

INSERT INTO `lesuren` (`id`, `les_id`, `uur`, `dag`) VALUES
(3, 4, 1, 1),
(4, 7, 2, 1),
(5, 5, 3, 1),
(6, 1, 4, 1),
(7, 0, 5, 1),
(8, 6, 6, 1),
(9, 3, 7, 1),
(10, 4, 8, 1),
(11, 10, 1, 2),
(12, 8, 2, 2),
(13, 9, 3, 2),
(14, 7, 4, 2),
(15, 6, 5, 2),
(16, 4, 6, 2),
(17, 3, 7, 2),
(18, 1, 8, 2),
(19, 10, 1, 3),
(20, 8, 2, 3),
(21, 9, 3, 3),
(22, 7, 4, 3),
(23, 6, 5, 3),
(24, 4, 6, 3),
(25, 3, 7, 3),
(26, 1, 8, 3),
(27, 10, 1, 4),
(28, 8, 2, 4),
(29, 9, 3, 4),
(30, 7, 4, 4),
(31, 6, 5, 4),
(32, 4, 6, 4),
(33, 3, 7, 4),
(34, 1, 8, 4),
(35, 10, 1, 5),
(36, 8, 2, 5),
(37, 9, 3, 5),
(38, 7, 4, 5),
(39, 6, 5, 5),
(40, 4, 6, 5),
(41, 3, 7, 5),
(42, 1, 8, 5);

As you can see the table only contains numbers. That is just the way we save stuff in db. We call that the model. Now we create some html we call that the view.

Fist make a php file with the name 'database.class.php' (Becouse I am so nice I made a simple db class for you with PDO = php data object. This is better than using the old function because they are deprecated):

<?php

    //Database class
    class db extends Pdo{

        private $inlog;
        private $pass;
        private $adres;
        private $dbname;

        public function __construct($dbname, $adres, $inlog, $pass){

            parent::__construct('mysql:dbname='.$dbname.';host='.$adres, $inlog, $pass);

            $this->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
            $this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        }

        public function doPrepare($queryString, $param){

            $query = $this->prepare($queryString);
            $query->execute($param);
            return $query;
        }

        public function doPrepareBind($queryString, $param){

            $query = $this->prepare($queryString);
            foreach($param as $par){
                switch($par[2]){
                    case 'int':
                        $query->bindParam($par[0], $par[1], PDO::PARAM_INT);
                        break;
                    case 'str':
                        $query->bindParam($par[0], $par[1], PDO::PARAM_STR);
                        break;
                    case 'blob':
                        $query->bindParam($par[0], $par[1], PDO::PARAM_LOB);
                        break;
                    default:
                        $query->bindParam($par[0], $par[1], PDO::PARAM_STR);
                        break;
                }
            }
            $query->execute();
            return $query;
        }
    }

?>

Now finally we can do some PHP. It actually is really short:

<?php

    require_once('database.class.php');

    // use construct like dbname - dbadres - login - pass
    $db = new db('test', 'localhost:3307', 'root', 'usbw');

    $query = $db->query("
                            SELECT l.`dag` , l.`uur` , v.`vaknaam` 
                            FROM  `lesuren` l
                            INNER JOIN  `vakken` v ON l.`les_id` = v.`id` 
                            ORDER BY l.`dag` , l.`uur` 
    ");

    $dagen = array(1 => 'Maandag',2 => 'Dinsdag',3 => 'Woensdag',4 => 'Donderdag',5 => 'Vrijdag');

    $html = '';
    $olddag = 0;
    while($row = $query->fetch()){

        if($olddag != $row['dag']){
            $html .= $dagen[$row['dag']].' <br>'; 
        }
        $html .= "$row[uur] - $row[vaknaam] <br>"; 
        $olddag = $row['dag'];
    }

    echo $html;

?>

You can see a live demo here (note my webserver in not always online): demo

Answer 2

First try switching the while and foreach parts.'For each day' should come first, then loop through the table with the actual day selected.

Formatting should be another part, but this ought to give you the desired 8 lines per day output.