CODEWITHSUNDEEP
phpmysql
Sinagaman Eidelbert
Sinagaman Eidelbert

Reputation: 27

How to Insert time format in MySQL

I have form html like this:

<html>
    <head>
    <body>
        <form action='upload_data.php' method="POST" enctype="multipart/form-data">
            <input type="text" name="total_container">
            <button id="submit">Add</button>    
        </form>
    </body>
    </head>
</html>

upload_data.php

include 'connect.php';
$total_container = $_POST["total_container"];

if (!empty($total_container)){
    $fill="INSERT INTO `container`(`total_container`, `time`)VALUES($total_container,'blabla')";
    $data=mysql_query($fill);
    if(isset($data)){
        echo "<script>alert ('Great');</script>";
    } else {
        echo "<script>alert('No...');</script>";
    }
}

I want to fill my database table (container) like this:

id_block  | total_container | time     |
---------------------------------------
1         | 1               | 00:00:20 |
2         | 2               | 00:00:40 |
3         | 3               | 00:01:00 |
4         | 6               | 00:02:00 |
  1. id_block is auto_increment
  2. for every total_container, its multiplied with 20 seconds in time field.

so, what should I do in my Query?, I want to fill time field automatically, multiplied with 20 seconds.

Upvotes: 0

Views: 2132

Answers (5)

Luca Rainone
Luca Rainone

Reputation: 16458

try this 

INSERT INTO `container`(`total_container`, `time`) 
VALUES($total_container, SEC_TO_TIME($total_container*20) )

See reference for SEC_TO_TIME

Upvotes: 3

Ali Aboussebaba
Ali Aboussebaba

Reputation: 1162

Change your code like this:

<?php
include 'connect.php';
$total_container = $_POST["total_container"];
$time = date("H:i:s", $total_container);
if (!empty($total_container)){
$fill="INSERT INTO `container`(`total_container`, `time`)VALUES($total_container,'$time ')";
$data=mysql_query($fill);
if(isset($data))
            {
                echo "<script>alert ('Great');</script>";
            }
            else
            {
                echo "<script>alert('No...');</script>";
                        }
}

?>

Upvotes: 1

Konstantin Pereiaslov
Konstantin Pereiaslov

Reputation: 1814

First of all, always escape data you receive from user, since you're using old MySQL extension. Otherwise you're open to SQL injections.

Here is a code that builds a proper SQL query (I omitted a check for empty($total_container) for better understanding).

//Get data from user and escape
$total_container=mysql_real_escape_string($_POST['total_container']);
//Calculate time in seconds * 20
$time_in_seconds=$total_container*20;
//format as hh:mm:ss.
//Using gmdate('H:i:s',$time_in_seconds) for this is ok,
//but only if your $time_in_seconds is never going to exceed 24 hours
$time=sprintf('%02d:%02d:%02d', 
      ($time_in_seconds/3600), ($time_in_seconds/60%60), $time_in_seconds%60);
//build SQL query
$fill="INSERT INTO `container` (`total_container`, `time`)
       VALUES($total_container,'$time')";

Upvotes: 0

jankal
jankal

Reputation: 1128

Add a 

$time = $total_container*20; $time = '00:00:'.$time;

and edit the query to 

$fill="INSERT INTO `container`(`total_container`, `time`) 
       VALUES ($total_container,'$time')";

You also could use gmdate("H:i:s", $time); instead of $time = '00:00:'.$time; The method you use depends on the table structure. If time is a text filed, you can use both. But if time is a time field, it'll better to use the second example (gmdate).

Upvotes: 0

Pupil
Pupil

Reputation: 23948

<?php
$timeRaw = $total_container * 20;
$time = gmdate("H:i:s", $timeRaw);
$fill="INSERT INTO `container`(`total_container`, `time`)VALUES($total_container,'$time')";
?>

Hope it works for you.

Upvotes: 0

Related Questions