Updating data in MySQL database

Question

I am having problem updating an existing row in my database. What I need to do is add a record to a field named "Time_Out". This field is on the same row as with the "Time_In", "username", and "date_added". The Time_In is working perfectly fine. This is the code I've used:

date_default_timezone_set('Asia/Taipei'); 
$date_added = date ("Y-m-d");
$time_added = date ("h:i:s:a:");
$user = $_SESSION['xxxx']['xxxxx'];
$con = mysqli_connect("localhost", "xxxx", "xxxx", "test");
$save = mysqli_query($con, "INSERT INTO time_logs (username, date_added, Time_In) VALUES('$user', '$date_added', '$time_added')");
if(!$con) {
    die('Could not connect to the database' . mysql_error());
    mysql_close($con);
}
else
    header("Location: etc.php");

For the Time_Out, I have removed the "INSERT INTO ..." line and changed it into:

$save = mysqli_query($con, "UPDATE time_logs SET Time_Out = $time_added WHERE username = $user");

but the record in the Time_Out field in my database is still showing 0:00. By the way, my date_added is set to Date and the Time_In and Time_Out is set to Time.

I would really appreciate it if someone could show me how to do this using PHP. Thank you in advance.

If you echo "UPDATE time_logs SET Time_Out = $time_added WHERE username = $user" what is it? — WizKid
– WizKid, Commented May 13, 2014 at 2:16
@WizKid I am sorry, I am new to MySQL and PHP. How do I exactly echo that line? — user3627135
– user3627135, Commented May 13, 2014 at 2:19
echo "UPDATE time_logs SET Time_Out = $time_added WHERE username = $user"; — WizKid
– WizKid, Commented May 13, 2014 at 2:20
do mysql_query('...') or die(mysql_error()); and see if you get an error — WizKid
– WizKid, Commented May 13, 2014 at 2:26

herezy · Accepted Answer · 2014-05-13 02:26:54Z

3

You're missing single quotes around your non-numeric data. Try:

$save = mysqli_query($con, "UPDATE time_logs SET Time_Out = '$time_added' WHERE username = '$user'");

answered May 13, 2014 at 2:26

herezy

3361 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3627135 Over a year ago

I can't believe I haven't tried that. It actually worked! Thank you so much!

Giacomo1968 · Accepted Answer · 2014-05-13 02:32:07Z

0

Your code has a few issues. First in this chunk you are using mysqli_* and mysql_* extensions mixed together when they should all be mysqli_*:

date_default_timezone_set('Asia/Taipei'); 
$date_added = date ("Y-m-d");
$time_added = date ("h:i:s:a:");
$user = $_SESSION['xxxx']['xxxxx'];
$con = mysqli_connect("localhost", "xxxx", "xxxx", "test");
$save = mysqli_query($con, "INSERT INTO time_logs (username, date_added, Time_In) VALUES('$user', '$date_added', '$time_added');");
if (!$con) {
    die('Could not connect to the database' . mysqli_error($con));
    mysqli_close($con);
}
else
    header("Location: etc.php");

Specifically it was in your if (!$con) { check. Look at the cleaned up example now. But also, your update does not have single quotes around string values:

$save = mysqli_query($con, "UPDATE time_logs SET Time_Out = $time_added WHERE username = $user");

So it should be like this:

$save = mysqli_query($con, "UPDATE time_logs SET Time_Out = '$time_added' WHERE username = '$user'");

But to make your life easier, you might want to reformat your queries like this:

$query = "UPDATE time_logs SET Time_Out = '" . $time_added . "' WHERE username = '" . $user . "';";
$save = mysqli_query($con, $query);

Note how I set the query in a separate string & then added concatenation to the string itself for the variables. This makes it easier to spot issues like this in text editors in my humble option. I also ended each of your queries with a semicolon (;) since that again makes it clearer to me that is the true end of the query statement.

answered May 13, 2014 at 2:32

Giacomo1968

25.3k11 gold badges78 silver badges106 bronze badges

2 Comments

user3627135 Over a year ago

I will surely apply this one to make my codes look less complicated. I have also replaced all the mysql to mysqli. Thank you for this tip and have a good day.

user3627135 Over a year ago

I would definitely do that after I have gained 15 reputations. I'm on 12 right now.

Collectives™ on Stack Overflow

Updating data in MySQL database

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related