Asked  8 Months ago    Answers:  5   Viewed   31 times

I have got two arrows set up, click for next day, next two days, soon and previous day, two days ago, soon. the code seem not working? as it only get one next and previous day.

<a href="home.php?date=<?= date('Y-m-d', strtotime('-1 day', strtotime($date))) ?>" class="prev_day" title="Previous Day" ></a> 
<a href="home.php?date=<?= date('Y-m-d', strtotime('+1 day', strtotime($date))) ?>" class="next_day" title="Next Day" ></a>

is there a way if i click the next button, the date will continously change for the next day. for a moment it will only get one day ahead

 Answers

27
date('Y-m-d', strtotime('+1 day', strtotime($date)))

Should read

date('Y-m-d', strtotime(' +1 day'))

Update to answer question asked in comment about continuously changing the date.

<?php
$date = isset($_GET['date']) ? $_GET['date'] : date('Y-m-d');
$prev_date = date('Y-m-d', strtotime($date .' -1 day'));
$next_date = date('Y-m-d', strtotime($date .' +1 day'));
?>

<a href="?date=<?=$prev_date;?>">Previous</a>
<a href="?date=<?=$next_date;?>">Next</a>

This will increase and decrease the date by one from the date you are on at the time.

Wednesday, March 31, 2021
 
mattltm
answered 8 Months ago
96

You could also take a look at the DatePeriod class:

$period = new DatePeriod(
     new DateTime('2010-10-01'),
     new DateInterval('P1D'),
     new DateTime('2010-10-05')
);

Which should get you an array with DateTime objects.

To iterate

foreach ($period as $key => $value) {
    //$value->format('Y-m-d')       
}
Wednesday, March 31, 2021
 
CAMason
answered 8 Months ago
79

First of all, make sure that your name_l column is indexed. Then you can simply use the ORDER BY and the LIMIT clauses as follows:

SELECT * FROM personnel ORDER BY name_l, id LIMIT 0, 1;

Simply increment the 0 value in the LIMIT clause to select the next record within the ordered set. Therefore use LIMIT 1, 1 to get the second record, LIMIT 2, 1 for the third, etc.

To create an index on name_l you can use the CREATE INDEX command:

CREATE INDEX ix_index_name ON personnel (name_l);

Test case:

CREATE TABLE personnel (
   id int not null primary key, 
   name_l varchar(10), 
   name_f varchar(10)
);

CREATE INDEX ix_last_name_index ON personnel (name_l);

INSERT INTO personnel VALUES (1, 'Pacino', 'Al');
INSERT INTO personnel VALUES (2, 'Nicholson', 'Jack');
INSERT INTO personnel VALUES (3, 'De Niro', 'Robert');
INSERT INTO personnel VALUES (4, 'Newman', 'Paul');
INSERT INTO personnel VALUES (5, 'Duvall', 'Robert');

Results:

SELECT * FROM personnel ORDER BY name_l, id LIMIT 0, 1;
+----+---------+--------+
| id | name_l  | name_f |
+----+---------+--------+
|  3 | De Niro | Robert |
+----+---------+--------+
1 row in set (0.00 sec)

SELECT * FROM personnel ORDER BY name_l, id LIMIT 1, 1;
+----+--------+--------+
| id | name_l | name_f |
+----+--------+--------+
|  5 | Duvall | Robert |
+----+--------+--------+
1 row in set (0.00 sec)

SELECT * FROM personnel ORDER BY name_l, id LIMIT 2, 1;
+----+--------+--------+
| id | name_l | name_f |
+----+--------+--------+
|  4 | Newman | Paul   |
+----+--------+--------+
1 row in set (0.00 sec)

1st UPDATE: (Further to the comments below)

The above example is suitable mainly if you start by displaying the first record, and then you move sequentially to next, and maybe next again, and perhaps one back, etc. You could also easily move x steps forward and x steps backwards, but this does not appear to be required.

If you start from a random record however, it will be more difficult to adapt the query above to get the next and previous records.

If this is the case, then you can simply keep an index counter of where you currently stand. You start with $index = 0, and display the first record by using ... LIMIT $index, 1. Then you display the next by incrementing $index by 1, and you display the previous by decrementing $index by 1.

If on the other hand, you will be rendering the personnel list, and then the user will click on one record (at random), you could also make this work with some help from the applications-side (php). Let's say you render the following ordered list to the user:

+----+-----------+--------+
| id | name_l    | name_f |
+----+-----------+--------+  // [Hidden info]
|  3 | De Niro   | Robert |  // Row 0 
|  5 | Duvall    | Robert |  // Row 1
|  4 | Newman    | Paul   |  // Row 2
|  2 | Nicholson | Jack   |  // Row 3
|  1 | Pacino    | Al     |  // Row 4
+----+-----------+--------+

Now if the user clicks on Newman Paul, you would have to pass the row=2 parameter to the page that will display the details of this employee. The page that renders the details of the employee now knows that Newman Paul is the 3rd row (row=2). Therefore to get the previous and the next records you would simply change the x in LIMIT x, 1 by row - 1 for the previous record and row + 1 for the next:

-- Previous 
SELECT * FROM personnel ORDER BY name_l, id LIMIT 1, 1;
+----+--------+--------+
| id | name_l | name_f |
+----+--------+--------+
|  5 | Duvall | Robert |
+----+--------+--------+
1 row in set (0.00 sec)


-- Next 
SELECT * FROM personnel ORDER BY name_l, id LIMIT 3, 1;
+----+-----------+--------+
| id | name_l    | name_f |
+----+-----------+--------+
|  2 | Nicholson | Jack   |
+----+-----------+--------+
1 row in set (0.00 sec)

2nd UPDATE:

You can use the following MySQL-specific query to get the record number within the ordered list of any random employee, which then can be used to get the previous and next records. Note however that this is not very efficient, and may degrade performance if you have thousands of records.

Let's say you are in employee Nicholson Jack.

You could do the following query:

SELECT p.id, p.name_l, p.name_f, o.record_number
FROM   personnel p
JOIN   (
        SELECT    id,
                  @row := @row + 1 AS record_number
        FROM      personnel
        JOIN      (SELECT @row := -1) r
        ORDER BY  name_l, id
       ) o ON (o.id = p.id)
WHERE  p.name_l = 'Nicholson' AND p.name_f = 'Jack';

Which returns this:

+----+-----------+--------+---------------+
| id | name_l    | name_f | record_number |
+----+-----------+--------+---------------+
|  2 | Nicholson | Jack   |             3 |
+----+-----------+--------+---------------+
1 row in set (0.00 sec)

Note that in the WHERE clause you could have used p.id = 2 if the id is known, instead of p.name_l = 'Nicholson' AND p.name_f = 'Jack'.

Now we can use the record_number field, which is 3 in this case, to get the previous and the next records, simply by using the original query from the top of this answer, and replacing LIMIT 2, 1 for the previous and LIMIT 4, 1 for the next. There you go:

The previous of Nicholson Jack:

SELECT * FROM personnel ORDER BY name_l, id LIMIT 2, 1;
+----+--------+--------+
| id | name_l | name_f |
+----+--------+--------+
|  4 | Newman | Paul   |
+----+--------+--------+
1 row in set (0.00 sec)

The next from Nicholson Jack:

SELECT * FROM personnel ORDER BY name_l, id LIMIT 4, 1;
+----+--------+--------+
| id | name_l | name_f |
+----+--------+--------+
|  1 | Pacino | Al     |
+----+--------+--------+
1 row in set (0.00 sec)
Wednesday, March 31, 2021
 
ManojGeek
answered 8 Months ago
24

You need to call DateInterval::format() to display that difference as a string.

echo $diff->format('%d days');

See the manual for all of the available formatting options.

Wednesday, July 7, 2021
 
mattltm
answered 4 Months ago
65

Perhaps two more queries would work ...

 select id,title from videos where id < $local_id order by id desc limit 1
 select id,title from videos where id > $local_id order by id asc limit 1
Tuesday, August 24, 2021
 
VinoCoder
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share