MySQL displaying results based on day

[Lukeidiot]

Hello fine phpfreak members.

 

I am having a brain cloud and cannot remember how to get these results out correctly.

 

Here is my mysql table:

 

And I need the data to show up like such:

 

NOTE: I have a table called "quotes" with

[datestamp] -> [Test quote] [Feb-5-2016] -> [test quote 2] [Feb-4-2016] that should be in the mix too.

 

thanks guys.

 

source site: http://lukeidiot.com

[mac_gyver]

you would start by storing the dates using a mysql DATE data type, so that you can do date comparisons (to select the range of dates you are interested in) and so that you can order the data by the date.

 

you would then run a query that selects the data you want in the order that you want it. you would probably want a secondary sort order on the description so that the links are in alphabetical order within any date. you would fetch the data into a multi-dimensional array, using the date as the main array key/index. this will give you a sub-array of data for each date.

 

because the quote data is not directly related to the links data, you would query for it separately and fetch it into its own array using the date as the array key/index.

 

you would then just loop over the main array to access each date, then loop over the sub-array for each date and output the data the way you want. you would use the date value from the main loop to access any quote(s) for that date in the quote data array.

[Lukeidiot]

you would start by storing the dates using a mysql DATE data type, so that you can do date comparisons (to select the range of dates you are interested in) and so that you can order the data by the date.

 

you would then run a query that selects the data you want in the order that you want it. you would probably want a secondary sort order on the description so that the links are in alphabetical order within any date. you would fetch the data into a multi-dimensional array, using the date as the main array key/index. this will give you a sub-array of data for each date.

 

because the quote data is not directly related to the links data, you would query for it separately and fetch it into its own array using the date as the array key/index.

 

you would then just loop over the main array to access each date, then loop over the sub-array for each date and output the data the way you want. you would use the date value from the main loop to access any quote(s) for that date in the quote data array.

Can you give me an example line of code for this?

"you would then run a query that selects the data you want in the order that you want it. you would probably want a secondary sort order on the description so that the links are in alphabetical order within any date. you would fetch the data into a multi-dimensional array, using the date as the main array key/index. this will give you a sub-array of data for each date."

 

Because I am confused if I need a foreach inside the while loop?

[Lukeidiot]

Can you give me an example line of code for this?

"you would then run a query that selects the data you want in the order that you want it. you would probably want a secondary sort order on the description so that the links are in alphabetical order within any date. you would fetch the data into a multi-dimensional array, using the date as the main array key/index. this will give you a sub-array of data for each date."

 

Because I am confused if I need a foreach inside the while loop?

Actually, here's what I came up with (didnt include the quotes tho, didnt really understand a good way to do it.)

 

<p>Quote of the day: <i>Don't watch the clock; do what it does. Keep going.</i></p>


<?php
$rowdates = mysqli_query($connect, "SELECT DISTINCT datestamp FROM links ORDER BY datestamp DESC");
$dates = array();
while($row = mysqli_fetch_assoc($rowdates))
{
    $dates[] = $row;
}
?>


<?php
foreach($dates as $value) { ?>
<h1><?php echo date('M d Y', strtotime($value['datestamp'])); ?></h1>
<strong>Today's Links</strong>
<ul>
<?php
    foreach($value as $key => $val) {


      $sql_links = mysqli_query($connect, "SELECT * FROM links WHERE datestamp = '$val' ORDER BY timestamp DESC, CHAR_LENGTH(description)");
      while($row_links = mysqli_fetch_array($sql_links)){
      ?>


      <li><a href="<?php echo $row_links['url']; ?>"><?php echo $row_links['description']; ?></a></li>


      <?php }
    } ?> </ul> <hr style="border: 1px dashed grey;">
<?php } ?>