I am trying to add 3 years to a date that is returned from my database

Question

I am trying to add 3 years to each date that is returned from myphpadmin database. The code pulls a date that the class was first taken (date_aerial) and i want to return a value such as date_aerial+3 years. So if the date_aerial given is 1-24-1994 I want the return value to be 1-24-1997. This has to be echoed for each ID that is pulled from the phpmyadmin database. So where aerial_refresh is located should be the value that adds 3 years to date_aerial and returns the value. Here is the code

<?php
$db_host = 'localhost'; 
$db_user = 'nick'; 
$db_pass = 'ramon';
$db_name = 'safetytraining'; 
?>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link href="safetytraining.css" rel="stylesheet" type="text/css" />
<title>Safety Training</title>
</head>
<body>
<div class="topnav">
 <a href="http://152.116.203.115/safetytraining.php">Home</a>
  <a  href="http://152.116.203.115/selectcourse.php">Select Course</a>
  <a href="http://152.116.203.115/calendar.php">Calendar</a>
  <a class="active" href="http://152.116.203.115/editcourse.php">Edit Course</a>
</div>
<br>
<h1>Facilities Tradesmen</h1>
<input type="button" onclick="location.href='http://152.116.203.115/search_aerial2.php';" value="Back to Search" style="float: right;"/><br>
<iframe id="txtArea1" style="display:none"></iframe>
<button  id="btnExport" onclick="fnExcelReport();" style="width:100px;height:50px;"> EXPORT </button><br>
</body>
<table width="100%" align="center" id="myTable" >
     <thead>
         <tr>
      <th  onclick="sortTable(0)"width="2%" >ID</th>
   <th onclick="sortTable(1)" width="2%" >First Name</th>
   <th onclick="sortTable(2)" width="2%" >Last Name</th>
   <th onclick="sortTable(3)" width="2%" >Supervisor Group</th>
   <th onclick="sortTable(4)" width="2%" >Trade</th>
   <th onclick="sortTable(5)" width="2%" >T-ID</th>
   <th onclick="sortTable(6)" width="2%" >C-ID</th>
   <th onclick="sortTable(7)" width="2%" >Department</th>
   <th onclick="sortTable(8)" width="2%" >Aerial Devices Training <br>#:NTCHST01</th>
   <th onclick="sortTable(9)" width="2%" >Refresh Date</th>
   <th onclick="sortTable(10)" width="2%" >Edit Data</th>


   
         </tr>
    </thead>
     <tbody>
</html>
 <?php
$conn = mysqli_connect($db_host, $db_user, $db_pass, $db_name);
if (!$conn) {
    die ('Fail to connect to MySQL: ' . mysqli_connect_error());   
}

$value1 = $_POST['searchTP'];
$value2 = $_POST['searchTR'];
$value3 = $_POST['searchTD'];
$value4 = $_POST['searchCD'];
$value5 = $_POST['searchDP'];
$value6 = $_POST['searchAD'];
$value10 = $_POST['searchSG'];
$value11=$_POST['date_aerial'];

$date = date_create('2000-01-01');
date_add($date, date_interval_create_from_date_string('10 years'));
echo date_format($date, 'm-d-Y');


echo '<br>';
echo "<font color=white size='4pt'> You searched for Tradesperson:</font><font color=red size='4pt'> $value1</font>";
echo '<br>';
echo  "<font color=white size='4pt'> You searched for Trade:</font><font color=red size='4pt'>  $value2</font>"; 
echo '<br>';
echo  "<font color=white size='4pt'> You searched for T-ID:</font><font color=red size='4pt'>  $value3</font>"; 
echo '<br>';
echo  "<font color=white size='4pt'> You searched for C-ID:</font><font color=red size='4pt'>  $value4</font>"; 
echo '<br>';
echo  "<font color=white size='4pt'> You searched for Department:</font><font color=red size='4pt'>  $value5</font>"; 
echo '<br>';


// Add conditional statement here that if value1 or value is blank then search the other.

$sql = "SELECT ID, first_name, last_name, supervisor_group, trade, t_id, c_id, department_number, date_aerial, aerial_refresh
        FROM peopleinfo WHERE
  (last_name LIKE '%$value1%' )
  and 
  (trade LIKE '%$value2%')
  and
  (t_id LIKE '%$value3%')
  and
  (c_id LIKE '%$value4%')
  and
  (department_number LIKE '%$value5%')
  and
  (date_aerial LIKE '%$value6%')
  and
  (date_lock LIKE '%$value7%')
  and
  (date_confine LIKE '%$value8%')
  and
  (date_fall LIKE '%$value9%')
  and
  (supervisor_group LIKE '%$value10%')";
  
 
  

 
 
$query = mysqli_query($conn, $sql);
 
if (!$query) {
    die ('SQL Error: ' . mysqli_error($conn));
}
 
while ($row = mysqli_fetch_array($query))
{
    echo '<tr>
   <td>'.$row['ID'].'</td>
    <td>'.$row['first_name'].'</td>
    <td>'.$row['last_name'].'</td>
    <td>'.$row['supervisor_group'].'</td>
            <td>'.$row['trade'].'</td>
            <td>'.$row['t_id'].'</td>
   <td>'.$row['c_id'].'</td>
            <td>'.$row['department_number'].'</td>
   <td>'.$row['date_aerial'].' </td>
   <td>'.$row['aerial_refresh'].' </td>
   <td><a href="edit.php?ID=' . $row['ID'] . '">Edit</a></td>
        </tr>';
  
}  

mysqli_free_result($query);
mysqli_close($conn);
?>
 </tbody>
</table>

Any help would be great thanks in advance.

`phpMyAdmin` = a app written in PHP that helps with MySQL Database administration. The database is MYSQL — RiggsFolly, Sep 04 '18 at 22:47
Look at [the manual](http://php.net/manual/en/datetime.add.php) there is a perfect example in there already — RiggsFolly, Sep 04 '18 at 22:49
Your script is wide open to [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's — RiggsFolly, Sep 04 '18 at 22:51

score 1 · Accepted Answer · edited Sep 04 '18 at 23:07

1

You can put in your select this "DATE_ADD (date_aerial, INTERVAL +3 Year)"

Like this

$sql = "SELECT ID, first_name, last_name, supervisor_group, 
                trade, t_id, c_id, department_number, 
                DATE_ADD (date_aerial, INTERVAL +3 Year), 
                aerial_refresh 
        FROM peopleinfo 
        WHERE (last_name LIKE '%$value1%' ) 
        and (trade LIKE '%$value2%') 
        and (t_id LIKE '%$value3%') 
        and (c_id LIKE '%$value4%') 
        and (department_number LIKE '%$value5%') 
        and (date_aerial LIKE '%$value6%') 
        and (date_lock LIKE '%$value7%') 
        and (date_confine LIKE '%$value8%') 
        and (date_fall LIKE '%$value9%') 
        and (supervisor_group LIKE '%$value10%')";

using this function you'll do not need to do nothing on your code.

let me know if it's work for you.

edited Sep 04 '18 at 23:07

RiggsFolly

93,638
21
103
149

answered Sep 04 '18 at 22:53

Marcelino Truocchio

26
4

Hi Marcelino it would be much clearer if you made the contents of that comment an edit to your original answer (use the `edit` link below the text of your answer). – Nick Sep 04 '18 at 23:02
And [here is the doc](https://meta.stackexchange.com/questions/22186/how-do-i-format-my-code-blocks) on how to format code – RiggsFolly Sep 04 '18 at 23:05
Thanks Nick I just did not see the link. – Marcelino Truocchio Sep 04 '18 at 23:05
It did not work it gave me this error "SQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '), aerial_refresh FROM peopleinfo WHERE (last_name LIKE '%%' ) an' at line 1" – Sep 05 '18 at 11:04

I am trying to add 3 years to a date that is returned from my database

1 Answers1