CODEWITHSUNDEEP
phpmysqldate
Paragon Jenko
Paragon Jenko

Reputation: 91

Insert into an SQL database in PHP using incremental Dates and Days

I am looking to insert data into an SQL database that has these columns.

id, date, day, Month, Projection, RecommendedStaff, StaffWages, TotalWages, Actual

I found the insert into statement which works when using it as a query to enter a constant, however I can't find the syntax for incrementing these values and for it to work successfully. The ID is auto-incremental therefore there is no issue with this just trying to understand how to increment date and day values.

For example:

Date / Day / Month

2017-03-20 / Monday / March

INSERT QUERY COMPLETED

2017-03-21 / Tuesday / March

OR WHEN MONTH CHANGES

2017-04-01 / Saturday / April

I want to be able to increment by a certain period of days based on the current date therefore my guess would be a while loop with the variable as the amount of days.

My current theory of incrementing values however it doesn't insert into mySQL database:

$date = date("Y-m-d");
    $sql = "INSERT INTO `Test` (date) VALUES (DATE_ADD(NOW(), INTERVAL 1 DAY))";

    if (mysqli_multi_query($conn, $sql)) {
        echo "New records created successfully";
    } else {
        echo "Error: " . $sql . "<br>" . mysqli_error($conn);
    }

Upvotes: 1

Views: 1855

Answers (2)

Yolo
Yolo

Reputation: 1579

Here is a PHP way you can achieve what you want.

$fDate = $date->format("Y-m-d");
$date = '2017-03-02';

$begin = new DateTime($date);
$end = new DateTime(date('Y-m-d', strtotime($date . '+ 1 year'))); // you can change + 1 year to what you need

$interval = new DateInterval('P1D'); // one day
//$interval = new DateInterval('P1W'); // one week

$daterange = new DatePeriod($begin, $interval, $end);

$stmt = $conn->prepare("INSERT INTO `Test` (`date`, `day`, `Month`) VALUES (?,?,?)");
$stmt->bind_param('sss', $fDate, $fDay, $fMonth);
foreach ($daterange as $date) { // loop through dates
    $fDate = $date->format("Y-m-d"); // format date to the format you need
    $fDay = $date->format("l"); // (lower case L) e.g. Sunday
    $fMonth = $date->format("F"); // e.g. January

    $stmt->execute();
}

You can see here how you can user strtotime() function.

Update After reading the question again in full ;)

I changed the loop to also give you day and month. You can format a date to whatever you need. See here for the parameters you can pass to the date function.

Upvotes: 2

Sagar Gangwal
Sagar Gangwal

Reputation: 7947

You can make a procedure and using that looping you can insert multiple records

As shown in below code if you want to insert 100 days records from current date,just replace that count_1 variable 10 to 100 and it will iterate your loop for 100 times and Make entry for 100 days .

DELIMITER $$
DROP PROCEDURE IF EXISTS test$$
CREATE PROCEDURE test()
BEGIN
   DECLARE count_1 INT DEFAULT 0;
   WHILE count_1 < 10 DO
      /**Sql statement**/
      SET count_1 = count_1 + 1;
      INSERT INTO `Test` (date) VALUES (DATE_ADD(NOW(), INTERVAL count_1 DAY));
   END WHILE;
END$$
DELIMITER ;

Hope this will helps.

Upvotes: 0

Related Questions