Date_ADD operation inside a Query

Beginners - Learning PHP
#1

Hi Gurus,
please help me on my small php project, I want to place the result of a date calculation inside a Query statement into the table, but it seems that the php cannot recognize the index (“next_cal”) from the Query.

<?php

try {
  $connection = new PDO($dsn, $username, $password, $options);


  $sql = "SELECT calrecord.cal_id, equipment.mach_no, equipment.brand, equipment.description, calrecord.employeenumber, equipment.cal_interval, calrecord.cal_date, calrecord.next_cal = DATE_ADD(calrecord.cal_date, INTERVAL equipment.cal_interval DAY)
  from calrecord
  INNER JOIN equipment ON calrecord.equip_id = equipment.equip_id Order By cal_date DESC";


  $statement = $connection->prepare($sql);
  $statement->execute();

  $result = $statement->fetchAll();
} catch(PDOException $error) {
  echo $sql . "<br>" . $error->getMessage();
}
?>

<h2>Calibration Record</h2>

<table>
    <thead>
        <tr>
            <th>Calibration ID</th>
            <th>Machine/Equipment No.</th>
            <th>Brand</th>
            <th>Description</th>
            <th>Employee Number</th>
            <th>Calibration Interval</th>
            <th>Calibration Date</th>
            <th>Next Calibration</th>
            <th>Edit</th>
        </tr>
    </thead>
    <tbody>
    <?php 
    foreach ($result as $row) : ?>

        <tr>
            <td><?php echo escape($row["cal_id"]); ?></td>
            <td><?php echo escape($row["mach_no"]); ?></td>
            <td><?php echo escape($row["brand"]); ?></td>
            <td><?php echo escape($row["description"]); ?></td>
            <td><?php echo escape($row["employeenumber"]); ?></td>
            <td><?php echo escape($row["cal_interval"]); ?></td>
            <td><?php echo escape($row["cal_date"]); ?> </td>
            <td><?php echo escape($row["next_cal"]); ?> </td>
            <td><a href="update-single-calrec.php?id=<?php echo escape($row["cal_id"]); ?>">Edit</a></td>
        </tr>
    <?php endforeach; ?>
    </tbody>
</table>

<a href="index.php">Back to home</a>

<?php require "templates/footer.php"; ?>
#2

That’s because this is wrong,

I don’t see how that would not throw an error. Are you trying to select it, or update it?

DATE_ADD(calrecord.cal_date, INTERVAL equipment.cal_interval DAY) as next_cal
#3

Hi astonsipher,

I was trying to Query via SELECT at the same time I would like to make DATE add operation on 2 columns (cal_interval ad cal_date) to appear on column next_cal. This would show the next calibration date of my equipment.

#4

If I am reading this correctly, you want to change the value of a column? Or you just want to see what that value would be with those date altered?

#5

Hi Astonecipher, I want to change the column with a date for the next calibration.
Can I do the date_add operation during UPDATE (adding rows)? Thanks

#6

A simple update is this,

UPDATE 
    calrecord SET next_cal = DATE_ADD(calrecord.cal_date, INTERVAL equipment.cal_interval DAY)
 WHERE
    {whatever the primary key is}

But with that being a calculated field, you are actually better off making a view and not actually having that column in the actual sense at all.

CREATE VIEW Next_Update AS 

    SELECT
        calrecord.cal_id,
        equipment.mach_no,
        equipment.brand,
        equipment.description,
        calrecord.employeenumber,
        equipment.cal_interval,
        calrecord.cal_date,
        DATE_ADD(calrecord.cal_date,
        INTERVAL equipment.cal_interval DAY)   as next_cal
    from
        calrecord   
    INNER JOIN
        equipment 
            ON calrecord.equip_id = equipment.equip_id 
    Order By
        cal_date DESC

Now whenever you call the view, you call it like any other query (1), it has the date based on the required calculations.

(1) SELECT * FROM Next_Update
#7

Hi astonecipher, thank you so much, the code is now displaying the correct view. Have a nice day.

Sponsor our Newsletter | Privacy Policy | Terms of Service