Inserting date into mysql database [i am using php and xamp mysql databse]

Question

<?php
    session_start();
    require 'dbconfig/config.php';
    //phpinfo();
?>
<!DOCTYPE html>
<html>
<head>
<title>Time Tracker</title>
<link rel="stylesheet" href="style1.css">
<script type="text/javascript">
      function clock() {
          var mytime = new Date();
          var seconds = mytime.getSeconds();
          var minutes = mytime.getMinutes();
          var hours = mytime.getHours();
          var currentTime = hours + ":" + minutes + ":" + seconds;
          document.getElementById("Timer").firstChild.nodeValue = currentTime;
      }
    </script>
</head>

<body>
    <div id="main-wrapper">
        <center><h2>Time Tracker</h2></center>
        <center><h3>Welcome <?php echo $_SESSION['username']; ?></h3></center>

<form  method="post">
    <div class="Time Fields">


 <tr>
      <td>Date</td> 

       <?php
    $username = $_SESSION['username'];
    $date=date('Y:m:d');
    $query = "select * from datetime where username='$username' and Date = '$date'";
    $query_run1 = mysqli_query($con,$query);
    $row = mysqli_fetch_array($query_run1,MYSQLI_ASSOC);

       ?>
    <input type="text" name="Date" value="<?php echo $row ['Date']; ?> " size=10>  
 </tr> 

 <tr>  
      <td>Time In</td> 

    <?php
    $username = $_SESSION['username'];
    $date=date('Y:m:d');
    $query = "select * from datetime where username='$username' and Date = '$date'";
    $query_run1 = mysqli_query($con,$query);
    $row = mysqli_fetch_array($query_run1,MYSQLI_ASSOC);

       ?>

      <input type="int" name="TimeIn" value="<?php echo $row ['TimeIn']; ?> " size=20 readonly>  
 </tr>


 <tr> 
      <td>Time Out</td>
       <span id = "Timer">00:00:00</span>   
       <script type="text/javascript">
       setInterval('clock()', 1000);
      </script>

 </tr> 


 <tr> 
      <td>Total Time</td> 
      <td><input type=text name=x3 size=20 value=""></td>  
 </tr>

<tr> 

      <td><button class="Submit" name="Submit" type="submit">Submit</button></tr>

</table> 
    </div>


    </div>
<?php
        if(isset($_POST['Submit']))
            {
            @$TimeOut=$_POST['Timer'];
             $query_run3 = mysqli_query($con,"insert into DateTime (TimeOut) values('$TimeOut')");  
            }
?>
</body>

</html>`<?php
    session_start();
    require 'dbconfig/config.php';
    //phpinfo();
?>
<!DOCTYPE html>
<html>
<head>
<title>Time Tracker</title>
<link rel="stylesheet" href="style1.css">
<script type="text/javascript">
      function clock() {
          var mytime = new Date();
          var seconds = mytime.getSeconds();
          var minutes = mytime.getMinutes();
          var hours = mytime.getHours();
          var currentTime = hours + ":" + minutes + ":" + seconds;
          document.getElementById("Timer").firstChild.nodeValue = currentTime;
      }
    </script>
</head>

<body>
    <div id="main-wrapper">
        <center><h2>Time Tracker</h2></center>
        <center><h3>Welcome <?php echo $_SESSION['username']; ?></h3></center>

<form  method="post">
    <div class="Time Fields">


 <tr>
      <td>Date</td> 

       <?php
    $username = $_SESSION['username'];
    $date=date('Y:m:d');
    $query = "select * from datetime where username='$username' and Date = '$date'";
    $query_run1 = mysqli_query($con,$query);
    $row = mysqli_fetch_array($query_run1,MYSQLI_ASSOC);

       ?>
    <input type="text" name="Date" value="<?php echo $row ['Date']; ?> " size=10>  
 </tr> 

 <tr>  
      <td>Time In</td> 

    <?php
    $username = $_SESSION['username'];
    $date=date('Y:m:d');
    $query = "select * from datetime where username='$username' and Date = '$date'";
    $query_run1 = mysqli_query($con,$query);
    $row = mysqli_fetch_array($query_run1,MYSQLI_ASSOC);

       ?>

      <input type="int" name="TimeIn" value="<?php echo $row ['TimeIn']; ?> " size=20 readonly>  
 </tr>


 <tr> 
      <td>Time Out</td>
       <span id = "Timer">00:00:00</span>   
       <script type="text/javascript">
       setInterval('clock()', 1000);
      </script>

 </tr> 


 <tr> 
      <td>Total Time</td> 
      <td><input type=text name=x3 size=20 value=""></td>  
 </tr>

<tr> 

      <td><button class="Submit" name="Submit" type="submit">Submit</button></tr>

</table> 
    </div>


    </div>
<?php
        if(isset($_POST['Submit']))
            {
            @$TimeOut=$_POST['Timer'];
             $query_run3 = mysqli_query($con,"insert into DateTime (TimeOut) values('$TimeOut')");  
            }
?>
</body>

</html>`<?php
    session_start();
    require 'dbconfig/config.php';
    //phpinfo();
?>
<!DOCTYPE html>
<html>
<head>
<title>Time Tracker</title>
<link rel="stylesheet" href="style1.css">
<script type="text/javascript">
      function clock() {
          var mytime = new Date();
          var seconds = mytime.getSeconds();
          var minutes = mytime.getMinutes();
          var hours = mytime.getHours();
          var currentTime = hours + ":" + minutes + ":" + seconds;
          document.getElementById("Timer").firstChild.nodeValue = currentTime;
      }
    </script>
</head>

<body>
    <div id="main-wrapper">
        <center><h2>Time Tracker</h2></center>
        <center><h3>Welcome <?php echo $_SESSION['username']; ?></h3></center>

<form  method="post">
    <div class="Time Fields">


 <tr>
      <td>Date</td> 

       <?php
    $username = $_SESSION['username'];
    $date=date('Y:m:d');
    $query = "select * from datetime where username='$username' and Date = '$date'";
    $query_run1 = mysqli_query($con,$query);
    $row = mysqli_fetch_array($query_run1,MYSQLI_ASSOC);

       ?>
    <input type="text" name="Date" value="<?php echo $row ['Date']; ?> " size=10>  
 </tr> 

 <tr>  
      <td>Time In</td> 

    <?php
    $username = $_SESSION['username'];
    $date=date('Y:m:d');
    $query = "select * from datetime where username='$username' and Date = '$date'";
    $query_run1 = mysqli_query($con,$query);
    $row = mysqli_fetch_array($query_run1,MYSQLI_ASSOC);

       ?>

      <input type="int" name="TimeIn" value="<?php echo $row ['TimeIn']; ?> " size=20 readonly>  
 </tr>


 <tr> 
      <td>Time Out</td>
       <span id = "Timer">00:00:00</span>   
       <script type="text/javascript">
       setInterval('clock()', 1000);
      </script>

 </tr> 


 <tr> 
      <td>Total Time</td> 
      <td><input type=text name=x3 size=20 value=""></td>  
 </tr>

<tr> 

      <td><button class="Submit" name="Submit" type="submit">Submit</button></tr>

</table> 
    </div>


    </div>
<?php
        if(isset($_POST['Submit']))
            {
            @$TimeOut=$_POST['Timer'];
             $query_run3 = mysqli_query($con,"insert into DateTime (TimeOut) values('$TimeOut')");  
            }
?>
</body>

</html>`<?php
    session_start();
    require 'dbconfig/config.php';
    //phpinfo();
?>
<!DOCTYPE html>
<html>
<head>
<title>Time Tracker</title>
<link rel="stylesheet" href="style1.css">
<script type="text/javascript">
      function clock() {
          var mytime = new Date();
          var seconds = mytime.getSeconds();
          var minutes = mytime.getMinutes();
          var hours = mytime.getHours();
          var currentTime = hours + ":" + minutes + ":" + seconds;
          document.getElementById("Timer").firstChild.nodeValue = currentTime;
      }
    </script>
</head>

<body>
    <div id="main-wrapper">
        <center><h2>Time Tracker</h2></center>
        <center><h3>Welcome <?php echo $_SESSION['username']; ?></h3></center>

<form  method="post">
    <div class="Time Fields">


 <tr>
      <td>Date</td> 

       <?php
    $username = $_SESSION['username'];
    $date=date('Y:m:d');
    $query = "select * from datetime where username='$username' and Date = '$date'";
    $query_run1 = mysqli_query($con,$query);
    $row = mysqli_fetch_array($query_run1,MYSQLI_ASSOC);

       ?>
    <input type="text" name="Date" value="<?php echo $row ['Date']; ?> " size=10>  
 </tr> 

 <tr>  
      <td>Time In</td> 

    <?php
    $username = $_SESSION['username'];
    $date=date('Y:m:d');
    $query = "select * from datetime where username='$username' and Date = '$date'";
    $query_run1 = mysqli_query($con,$query);
    $row = mysqli_fetch_array($query_run1,MYSQLI_ASSOC);

       ?>

      <input type="int" name="TimeIn" value="<?php echo $row ['TimeIn']; ?> " size=20 readonly>  
 </tr>


 <tr> 
      <td>Time Out</td>
       <span id = "Timer">00:00:00</span>   
       <script type="text/javascript">
       setInterval('clock()', 1000);
      </script>

 </tr> 


 <tr> 
      <td>Total Time</td> 
      <td><input type=text name=x3 size=20 value=""></td>  
 </tr>

<tr> 

      <td><button class="Submit" name="Submit" type="submit">Submit</button></tr>

</table> 
    </div>


    </div>
<?php
        if(isset($_POST['Submit']))
            {
            @$TimeOut=$_POST['Timer'];
             $query_run3 = mysqli_query($con,"insert into DateTime (TimeOut) values('$TimeOut')");  
            }
?>
</body>

</html>

After clicking on submit, when "$query_run3" is run, the time is not updated in the database. It is displayed as 00:00:00. The javascript used here gives a running clock and I want this to get submitted and update in the data base field when I click on Submit. If this is not a possibility:- my exact requirement is:- I need to create a input type field where in I can enter time in 24 hour format(html input field) so that when I click on submit it gets updated in the database.

Thanks in advance.

You are wide open for SQL injection. Since you're using mysqli, take advantage of [prepared statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [bind_param](http://php.net/manual/en/mysqli-stmt.bind-param.php). — aynber, Feb 07 '17 at 19:41
`$TimeOut` probably is not in a valid format. Mysql expects it to be in `YYYY-MM-DD` format. Check what you're getting, and convert it before you try to insert it. — aynber, Feb 07 '17 at 19:42
Apologies ...The title I wrote is wrong. It's actually inserting time into SQL database...I used span for 'time out' and need to declare that in PHP and insert the time format into the database — Manoj, Feb 07 '17 at 20:05
Thank you @dhruvSaxena for the post...But i have a small problem with changing the span to input field ..The format is 00:00:00 which is OK for me...But it's not ticking as before ...I mean...In the previous approach...It used to be a running clock ...Keeps on changing the seconds...But now it remains at 00:00:00....The time is not changing ..It's just stuck at 00:00:00..Please help ..Thanks in advance — Manoj, Feb 08 '17 at 16:11
@Dhruv Saxena. Actually my requirement is "when clicked on SUBMIT button the running time should get inserted into database". The code used is as follows, providing the link for code pen which includes php coding as well for you to understand: Below is the link http://codepen.io/Manoj279/pen/qRLWJm — Manoj, Feb 11 '17 at 20:14

score 0 · Answer 1 · edited May 23 '17 at 11:45

0

It seems that you need a couple of HTML fixes:

Add action attribute to the form:

<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">

Since the Javascript function clock() specifically targets the HTML element with id="Timer", you could use it to act on an input field which is a part of the <form> being submitted. Remember to add the name attribute to this field. The fix, therefore, is actually quite simple, as you'd see below. I'm not sure how comfortable you are having the <span> changed to an <input> field though:

<input id="Timer" name="Timer" disabled="disabled" value="00:00:00">
In Javascript, change:

document.getElementById("Timer").firstChild.nodeValue = currentTime;

To

document.getElementById("Timer").value = currentTime;

If you want to retain the <span> and yet use its value in form submission, here's a basic idea on how to go about it:

You'd need a hidden <input> field. For example: <input type="hidden" name="TimerCopy" id="TimerCopy" value="">
When the form is submitted, prevent the default action (temporarily) and copy over the value of <span id="Timer"> to <input id="TimerCopy"> using Javascript / jQuery
Submit the form programatically using Javascript / jQuery
Receive in PHP the TimerCopy field as:

$TimeOut = $_POST['TimerCopy'];

And the rest should go on as usual.

EDIT: A minimal working code:

    <input id="Timer" name="Timer" disabled="disabled" value="00:00:00">

    <script>
     function clock() {
      var mytime = new Date();
      var seconds = mytime.getSeconds();
      var minutes = mytime.getMinutes();
      var hours = mytime.getHours();
      var currentTime = hours + ":" + minutes + ":" + seconds;
      document.getElementById("Timer").value = currentTime;
     }
       setInterval('clock()', 1000);
    </script>

Notes:

Reference link for preventing form submit and submitting it manually again: Submit form after calling e.preventDefault()
As suggested in the comments, please consider switching over to MySQLi Prepared Statements (or PDO)

edited May 23 '17 at 11:45

Community

1
1

answered Feb 07 '17 at 20:38

Dhruv Saxena

1,336
2
12
29

Thank you @dhruvSaxena for the post...But i have a small problem with changing the span to input field ..The format is 00:00:00 which is OK for me...But it's not ticking as before ...I mean...In the previous approach...It used to be a running clock ...Keeps on changing the seconds...But now it remains at 00:00:00....Thanks in advance – Manoj Feb 08 '17 at 15:54
Could you create a [jsfiddle](https://jsfiddle.net/) or [codepen](http://codepen.io/pen/) and share the link to demonstrate it better please? – Dhruv Saxena Feb 08 '17 at 16:29
Thanks for the fiddle link. Sorry that I didn't explicitly state in my answer, but the code ` ` should have been retained. Once you reinstate that part of the code, it should work just as before. So, the link to the working version is: http://codepen.io/anon/pen/apagvK – Dhruv Saxena Feb 08 '17 at 18:47
hey this is what i included as well. I did'nt mention it in jsfiddle beacuse it was not allowing me to write the tags for javascript, but i included the tags in my code that you mentioned . I could not get the functionality working. i really wonder how the same code cannot work as expected in Jsfiddle and also in my code but in codepen!!! – Manoj Feb 08 '17 at 19:27
JSFiddle mandates that all Javascript / jQuery is written in the tab designated for it (sans of course the ` – Dhruv Saxena Feb 08 '17 at 19:39
@Manoj Please check the updated answer, I've included a minimal working code that can be saved as an HTML file directly. – Dhruv Saxena Feb 08 '17 at 19:55
yes i got it. Thank you so much for the help and now getting to other functionality ..when i click on submit button to insert this time into mysql database it is still stored as 00:00:00 but not the running time. You can refer to the same code where in you have php tagsin the last lines of code. If i perform this action i.e. try to insert using INSERT, time is getting stored in 00:00:00 this format.How to store this as time(some thing like 12:30:02 etc...) – Manoj Feb 08 '17 at 20:34
@Manoj, Sorry, I'm unable to follow the note above. What specifically have you tried? Why do you think the value is not being passed? Have you tried `echo $_POST["Timer"]` to cross-verify? Are the other suggestions made in the answer implemented - declaring a form, setting its action attribute, including the timer field within the form? You might need to change `isset($_POST['Submit']) ` to `isset($_POST['Timer']) ` - have you considered that? If `echo` works, then the same should be passed to the DB table too. You've got to debug and find out where the broken link is. – Dhruv Saxena Feb 08 '17 at 20:47

Inserting date into mysql database [i am using php and xamp mysql databse]

1 Answers1