Jump to content

MySQL displaying results based on day


Go to solution Solved by Lukeidiot,

Recommended Posts

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:

ogbUnho.png

 

And I need the data to show up like such:

MNEe01d.png

 

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

Link to comment
https://forums.phpfreaks.com/topic/300740-mysql-displaying-results-based-on-day/
Share on other sites

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.

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?

  • Solution

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 } ?>
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.