Get system date and update a feild in a table once selecting a button in a table

[NuzhatAhmed]

What I want to do is update the date/time field when the check box or a button is selected for that specific row only. on clicking the button it should get the current date and time and store it into the database in the date/time field. Is this possible to do?

 

here is a snapshot of my code so far.

<?php

$query1 = "SELECT * FROM software";

$result1 = mysql_query($query1);

 

$id = $row['SoftwareID'];

$time = time();

$timestamp = $_POST[date('Y-m-d H:i:s', $time)];

 

$query2 = "UPDATE software SET timestamp = '$timestamp' WHERE SoftwareID = '$id'" ;

?>

<br>

<table border ="1" width="justified"

       <tr>

        <th><center>Version Number </th></center>

    <th><center>Defect Number</th> 

        <th><center>Description</th> 

            <th><center>Date/Time</th> 

                <th><center>Ready?</th> 

                    </tr>

                    <?php

                    while ($row = mysql_fetch_array($result1)) {

                        $versionnumber = $row['VersionNumber'];

                        $defectnumber = $row['DefectNumber'];

                        $description = $row['Description'];

                        $timestamp = $row['timestamp'];

                        ?>

                        <tr>

                            <td><?php echo $versionnumber; ?></td> 

                            <td> <?= $defectnumber ?></td> 

                            <td> <?= $description ?></td> 

                        <form action="" method="post">

                            <td> <?= $timestamp ?></td> 

                            <td> <input type="submit"  value="Submit Time"></td></form>  

                        </tr>

 

                        <?php

                    }

                    ?>

 

 

 

                    </table>

                    </br>

 

 

insert.php

 

<?php

include("databaseConnection.php");

 

$versionnumber = $_POST [versionnumber];

$defectnumber = $_POST [defectnumber];

$description = $_POST [description];

 

 

$qry = "INSERT INTO software (versionnumber, defectnumber, description)

    VALUES

    ('$versionnumber','$defectnumber','$description')";

$result = mysql_query($qry);

 

if ($result) {

    echo "information submitted, ";

    header("location: developer_dashboard.php");

    exit();

} else {

    die("There was an error, try again later");

}

?>

 

 

[B_CooperA]

You might want to have to create those checkboxes first and give them names.. 

 

f.e.

<input type="checkbox" name="id_value" />

and then run the check

if(isset($_POST['id_value'])) {

// Insert time and date to the database

}

I also highly recommend that you dump the old mysql functions since they are deprecated in PHP 5.5.. Use PDO instead, the syntax is not hard to learn. For example:

$query2 = "UPDATE software SET timestamp = '$timestamp' WHERE SoftwareID = '$id'" ;

can be written with PDO like this:

// Create the PDO object

$connect = new PDO (DB_DSN, DB_USER, DB_PASS); // Define your own constants

try {
$query = "UPDATE software SET timestamp = :timestamp WHERE SoftwareID = :id";
$stmt = $connect->prepare($query);
$stmt->execute(array(
    ':timestamp' => $timestamp, // Bind the value
    ':id' => $id  // Bind the value 
));

} catch (PDOException $e) {
   $e->getMessage();
}

Also you shouldn't name your variables with the same name as MySQL datatypes.. 

Edited October 9, 2013 by B_CooperA

[vinny42]

Moving to PDO requires a lot of rewrites and has no benefits, so I'd suggest moving to mysqli instead (and to be careful about using prepared statements, they are not supposed to be the default choice)

[B_CooperA]

Moving to PDO requires a lot of rewrites and has no benefits, so I'd suggest moving to mysqli instead (and to be careful about using prepared statements, they are not supposed to be the default choice)

 

Well, this totally depends on user. Will it be bad practice to rewrite your SQL queries again with PDO since you're learning while you're rewriting them? I think not. But that's totally up to user. When I figured out that the mysql functions are going to be deprecated, I didn't even hesitate to look up for PDO.

 

However, both of them will do the thing. 

[NuzhatAhmed]

You might want to have to create those checkboxes first and give them names.. 

 

f.e.

<input type="checkbox" name="id_value" />

and then run the check

if(isset($_POST['id_value'])) {

// Insert time and date to the database

}

I also highly recommend that you dump the old mysql functions since they are deprecated in PHP 5.5.. Use PDO instead, the syntax is not hard to learn. For example:

$query2 = "UPDATE software SET timestamp = '$timestamp' WHERE SoftwareID = '$id'" ;

can be written with PDO like this:

// Create the PDO object

$connect = new PDO (DB_DSN, DB_USER, DB_PASS); // Define your own constants

try {
$query = "UPDATE software SET timestamp = :timestamp WHERE SoftwareID = :id";
$stmt = $connect->prepare($query);
$stmt->execute(array(
    ':timestamp' => $timestamp, // Bind the value
    ':id' => $id  // Bind the value 
));

} catch (PDOException $e) {
   $e->getMessage();
}

Also you shouldn't name your variables with the same name as MySQL datatypes.. 

Hi, Thank you for your reply! 

 

I am just a little confused on how it will update the time an date for that row only? I knwo it has the ID but what do I have to put in the html table for this to work? also what does bind the value mean?

 

Any advice would be appreciated.

 

Thanks again!

[B_CooperA]

Just create the checkboxes, give them a name and a value based on the id of it's row. Then create a new php file like update.php or so

and refer to that in the form action part. Then run a check if the submit button is pressed and remember to name your submit button.. If so update the date and time for that specific row

if(isset($_POST['submit'])) {

$id = $_POST['id'];
$timestamp = date("Y-m-d G:i:s");
// Create the PDO object

$connect = new PDO (DB_DSN, DB_USER, DB_PASS); // Define your own constants

try {
$query = "UPDATE software SET timestamp = :timestamp WHERE SoftwareID = :id";
$stmt = $connect->prepare($query);
$stmt->execute(array(
    ':timestamp' => $timestamp, // Bind the value
    ':id' => $id  // Bind the value 
));

} catch (PDOException $e) {
   $e->getMessage();
}

// Let's do a rowCount
$rows = $stmt->rowCount();

if($rows == 1) {
echo "Date and time was updated";
} else {
echo "Boo! Nothing happened";
}

}

[vinny42]

 

When I figured out that the mysql functions are going to be deprecated, I didn't even hesitate to look up for PDO.

 

 

It's that "PDO is the new black" that I find worrying. That, along with the tendency to use prepared statements for everything "because it's safer".

Examine the consequences of what you do before you tell someone else it's the right solution :-)

 

In real life you'll want to write a wrapper around PDO anyway, so PDO will only be used in one class, which means you can just as easily stick with mysqli.

 

But, back to the topic.

 

Don't bother the database with the overhead of a prepare when all you are going to do is update a record. In fact, definately don't prepare this because the chance is significant that the database can't use an index to find the correct record, and waste time didn a sequential scan.

The reason for using a prepared statement here is to escape the id value because that costs a roundtrip to the database, but preparing effectively replaces that call with an even slower roundtrip to prepare the query.

I would not be at all surprised if preparing is much slower than escaping.

 

 

Even more on topic; 

 

get the current date and time 

 

Does that mean the current time of day, or the time mentioned in the form?

For the current time you can use the SQL keyword NOW() or one of it's equivalents. If you want the time from the form you'll need to agree on a format to use in the form, and a way to convert it to what the database understands. Fortunately MySQL has str_to_date for that.