Jump to content

Tag specific date_stamp from mysql_result


ChenXiu

Recommended Posts

Displaying date records in an HTML table from a "select datetime" mySQL query makes a HUGE page.

<tr><td>2021-08-15 07:12:03</td></tr>
<tr><td>2021-11-09 14:38:06</td></tr>
<tr><td>2020-12-10 13:37:35</td></tr>
<tr><td>2020-10-29 14:52:45</td></tr>
<tr><td>2020-11-21 01:56:32</td></tr>
<tr><td>2020-07-18 09:23:53</td></tr> // I would use <tr><td id="tagged">2020-07-18 09:23:53</td></tr>
<tr><td>2020-05-15 08:09:35</td></tr>
<tr><td>2020-04-07 12:17:05</td></tr>
        |
(thousand more lines)

Jump to desired month?
When page loads, I can use a <select> input and a "<body onLoad...scrollIntoView>" javascript to "jump" down to a date range.
For example, if I want the page to jump to "records starting with July 2020," I would want to jump to the "2020-07-18" line above.

When using while($row = mysqli_fetch_array($result)), Is there an easy/efficient way for mySQL to "tag" the row where July 2020 starts?

Thank you.

Link to comment
Share on other sites

I know. But I don't know how to do that, other than with a bunch of complicated string manipulation:

$query = "SELECT datetime FROM myTable order by datetime desc;";
$result = mysqli_query($db,$query);
if($result->num_rows == 0) {
echo 'No Data Found';
} else {

while($row = mysqli_fetch_array($result)) {

// The tagged row should be: 2020-07-18 09:23:53
// Therefore, "anything after 2020-06-30 ***"

echo '<tr><td>'.$row["datetime"].'</td></tr>';

}

All I know how to do is convoluted "if (substr($row["datetime"]......." manipulations. It is a mess. There must be a mysql trick. Or a complicated extra mysql query like

$queries = "SELECT * FROM datetime where datetime between '2020-07-01' and last_day('2020-07-01') order by datetime asc";

And use substr to capture the first row

And the use an "if next row == captured aforementioned substr, then tag the row"

Any thoughts?

Thank you.

Link to comment
Share on other sites

Table consists of the past 1000 dates.
To put an id tag in the first table row on-or-after July 1, 2020, I have to use 2 mySQL queries.
There has to be a more efficent way of doing the following:
 

$search = $db->query( "select datetime from myTable where datetime > last_day('2020-06-01') order by datetime asc limit 1" )->fetch_assoc();
if (isset($search)) {
  $tag = $search["datetime"];
  $mainQuery = $db->query("SELECT datetime FROM myTable order by datetime desc");
    if (isset($mainQuery)) {
      while($row = $mainQuery->fetch_assoc()) {
        if($row["datetime"] == $tag) {
        echo '<tr><td id="tag">' . $row["datetime"] . '</td></tr>';
        } else {
        echo '<tr><td>' . $row["datetime"] . '</td></tr>'."\n";
      }
    }
  }
}

Thank you.

Edited by ChenXiu
Link to comment
Share on other sites

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.