ChenXiu Posted June 22, 2021 Share Posted June 22, 2021 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. Quote Link to comment https://forums.phpfreaks.com/topic/312963-tag-specific-date_stamp-from-mysql_result/ Share on other sites More sharing options...
requinix Posted June 22, 2021 Share Posted June 22, 2021 Keep track of the previous row. When the current row's year (or whatever you want to measure by) changes then you stick an ID on the table row. Quote Link to comment https://forums.phpfreaks.com/topic/312963-tag-specific-date_stamp-from-mysql_result/#findComment-1587476 Share on other sites More sharing options...
ChenXiu Posted June 23, 2021 Author Share Posted June 23, 2021 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. Quote Link to comment https://forums.phpfreaks.com/topic/312963-tag-specific-date_stamp-from-mysql_result/#findComment-1587479 Share on other sites More sharing options...
ChenXiu Posted June 23, 2021 Author Share Posted June 23, 2021 (edited) 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 June 23, 2021 by ChenXiu Quote Link to comment https://forums.phpfreaks.com/topic/312963-tag-specific-date_stamp-from-mysql_result/#findComment-1587480 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.