PHP INSERT query fails to update data in table

Question

There doesn't seem to be any syntax error as the select query works. Yet when i try to insert the values, it is not updated in my table. There doesn't seem to be any error in the code. The query seems to be working fine in VS. I have checked posts with the same title and attempted to apply to my code none have worked so far. Here is my code where i first encountered the issue. My question is how do i remedy this and update the table in my database ?? Any help would be appreciated, thank you for reading

$ServerName = 'SQL2008.net.dcs.hull.ac.uk'; 
$connectionInfo = array("Database"=>"rde_505405");
$conn = sqlsrv_connect($ServerName,$connectionInfo);
if($conn== false)
{
    echo 'Connection could not be established';
    exit('Disconnecting');
}
else
{
    echo 'Database located!'; 
    echo '</br>';
    $StaffNUM = $_POST["SID"];
    $FirstName = $_POST['Fname'];
    $Surname = $_POST['Sname'];
    $Location = $_POST['Location'];
    $Date = date('Y-m-d h:i:sa'); 

    echo 'First Name: ', $FirstName; Echo'</br>,</br>';
    echo 'Surname: ', $Surname; Echo'</br>,</br>';
    echo 'Location: ', $Location;  Echo'</br>,</br>';
    echo 'Entered at: ', $Date;  Echo'</br>,</br>';

    $SQLquery = ("INSERT INTO Location (STAFFID, 'First Name', Surname, 
    Location, Time)
    VALUES ('".$StaffNUM."''".$FirstName."', '".$Surname."', 
    '".$Location."', '".$Date."');");
    $results = sqlsrv_query($conn, $SQLquery);
    echo '<br>';
    echo '<br>';
    echo 'Entry added successfully';    
  } 
sqlsrv_close($conn);

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 parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). — John Conde, May 09 '17 at 01:53
That code is horrible, but you're missing a comma between `'".$StaffNUM."'` and `'".$FirstName."',` — junkfoodjunkie, May 09 '17 at 01:56
@junkfoodjunkie thanks for input, no excuses need be made for my coding. — Tenkin, May 09 '17 at 02:01
@JohnConde Will definitely read up on this, thank you I started web dev two days ago trying to immerse in it. — Tenkin, May 09 '17 at 02:05

Thaiseer · Answer 1 · 2017-05-09T02:05:21.970

0

You are missing a comma between $StaffNUM and $FirstName

Please change the code for SQL query as:

$SQLquery = ("INSERT INTO Location (STAFFID, First Name, Surname, Location, Time) VALUES ('$StaffNUM', '$FirstName', '$Surname', '$Location', '$Date')");

this should work :)

edited May 09 '17 at 02:05

answered May 09 '17 at 01:57

Thaiseer

92
7

Hi there your suggestion unfortunately did not work, the table still displays Null in each cell – Tenkin May 09 '17 at 02:13

score 0 · Answer 2 · edited May 09 '17 at 07:07

I did in the end figure out what was wrong, my solution was to hardcode the query on VS which highlighted the errors. For instance I came to realise that column First Name was a poor name due to the space and removed the space to make it FirstName. I also modified @Thaiseer's correction adding '' to each variable. And finally my table datatype was set to datetime allowing me to use the function GETDATE()

Modified Code

$SQLquery = "INSERT INTO Location (STAFFID, FirstName, Surname, Location, Time) 
    VALUES ('$StaffNUM','$FirstName', '$Surname', '$Location', GETDATE())";

Hopefully this helps others who find themselves in a similar situation

score 0 · Answer 3 · edited May 09 '17 at 07:08

0

enclose the fields with `` and also do not use reserved words as a data field,

$SQLquery = ("INSERT INTO Location (`STAFFID`, `First Name`, `Surname`, `Location`, `Time`) 
    VALUES ('$StaffNUM', '$FirstName', '$Surname', '$Location', '$Date')");

edited May 09 '17 at 07:08

Imanuel

3,596
4
24
46

answered May 09 '17 at 03:18

apelidoko

782
1
7
23

PHP INSERT query fails to update data in table

3 Answers3