issue Updating table

Question

I have an app that I am trying to use to update a mysql table which has the following layout:

I am trying to get it so that the app sends in the URL the employee name and the in/out column value, and then my PHP script finds the person with the matching name and changes the in/out column value. Here is an example of an entry:

For some reason, when the in/out column should be changing to a 1, it remains at 0.

My Script is as follows:

<?php
// Input the credentials, clocktablet would be the database name
$con=mysqli_connect("localhost","tablet1","*****","clocktablet");
// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

// escape variables for security
$employeename = mysqli_real_escape_string($con, $_GET['employeename']);
$Clock = mysqli_real_escape_string($con, $_GET['Clock']);

//alters Track Table to display in/out status
$sql = "UPDATE track SET In/Out=$Clock WHERE EmployeeName=$employeename";

if (!mysqli_query($con,$sql)) {
  die('Error: ' . mysqli_error($con));
}

mysqli_close($con);
?>

Can anyone see anything wrong with the php script?

The table name is Track and the DB name is clocktablet, and I can confirm that the username and password inputted into the script is correct.

Missing quotes in column values and backtick in column name . Better use prepare statement!! — Saty, Aug 16 '16 at 10:13
Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) 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 statement and parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — RiggsFolly, Aug 16 '16 at 10:26
I would suggest changing your columns name from `in/out` to `in_out` or maybe even `shake_it_all_about` — RiggsFolly, Aug 16 '16 at 10:28

score 4 · Accepted Answer · answered Aug 16 '16 at 10:21

Missing quotes in column values and backtick in column name for exm In/Out .

Better use prepare statement!!

 //alters Track Table to display in/out status
  $smt = $con->prepare("UPDATE `track` SET `In/Out`=? WHERE `EmployeeName`=?");
  $smt->bind_param('is',$_GET['Clock'],$_GET['employeename']);
  $smt->execute();

To check number of affected rows by update query use

printf("Affected rows (UPDATE): %d\n", $con->affected_rows);

Passionate Coder · Answer 2 · 2016-08-16T10:22:18.083

0

Use single quotes around $employeename and backtick in column name for In/Out

$sql = "UPDATE track SET `In/Out`= $Clock WHERE EmployeeName= '$employeename'";

edited Aug 16 '16 at 10:22

answered Aug 16 '16 at 10:16

Passionate Coder

7,154
2
19
44

I think $employeename is just for example :) – Govind Samrow Aug 16 '16 at 10:17
yeah but whatever name his has will be string and string must have quotes – Passionate Coder Aug 16 '16 at 10:20
really it should be a [prepared statement and parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) – RiggsFolly Aug 16 '16 at 10:26

score 0 · Answer 3 · answered Aug 16 '16 at 10:17

Change

$sql = "UPDATE track SET In/Out=$Clock WHERE EmployeeName=$employeename";

To

$sql = "UPDATE track SET `In/Out`=$Clock WHERE EmployeeName='$employeename'";

                           ^ enclose column name with backtick

May be column name with backslash are allowed while creating a table. But, while inserting value for that column. It need to be enclosed with backtick, otherwise, it will through an error stating

1064 - 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 'in/out

Backtick

issue Updating table

3 Answers3