Where have I gone wrong? Please take a look

[elentz]

I have a php page that sends post data to another page that is supposed to update the DB.  Here is the update page code

<?php
//Connect to MYSQL
$conn = mysqli_connect("localhost","root","","");

//Get Variables
$name = $_POST['keyname'];
$template = $_POST['templatename'];
$idk = $_POST['keyid'];
$type = $_POST['keytype'];
$value = $_POST['keyvalue'];
$label = $_POST['keylabel'];
$id = $_POST['id'];

print_r($_POST)/n;

$sql =("UPDATE keys SET keytype = '".$_POST['keytype']."',keyvalue= '".$_POST['keyvalue']."',keylabel= '".$_POST['keylabel']."' WHERE id = '".$_POST['id']."'");

if ($conn->query($sql) === TRUE) {
    echo "Record updated successfully";
} else {
    echo "Something is not right: " . $conn->error;
}
?>

This was the last code I tried.  I got this when I ran the page:

Array ( [pid] => 34 [name] => 1stTemplate [key] => Line Key 2 [type] => 78 [value] => 2339 [label] => chance )

Something is not right: 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 'keys SET keytype = '',keyvalue= '',keylabel= '' WHERE id = ''' at line 1

 

I have a similar page that doesn't use the id number variable just a single id number for one record like this:

sql = "UPDATE global
SET serverlocalip= '$localip', serverwanip= '$wanip', timeserver1= '$tserver1', timeserver2= '$tserver2', timezone= '$timezone',
userloginpwd= '$userpsswrd', adminloginpwd= '$adminpsswd', vlanlanenabled= '$enablelanvlan',vlanpcenabled= '$enablepcvlan', vlanlanid= '$lanvlanid',
vlanpcid= '$pcvlanid', vlanlanpriority= '$lanvlanpriority', vlanpcpriority= '$pcvlanpriority'
WHERE id=1";

And it works fine.

 

If I put in values instead of the $Post variavbles from the array the update works fine.  I am sure I am missing something very basic, but I just can't see it.

 

Thanks in advance for any help

[Jacques1]

*sigh*

 

You've been doing this for 10 years, you've been told several times to look out for SQL injections and use prepared statements. Haven't you learned anything?

[gizmola]

There is no mystery to this. The debugging information in the print_r you added, shows you the problem.

 

The $_POST has none of the keys you are trying to interpolate into the sql statement.

 

But as Jacques1 asked, why aren't you using bind variable and prepared statements? In the time it takes you to resolve this, you could change your code and have improved the quality and security of your system.

[elentz]

I may have signed up 10 years ago but doing this isn't my day job.  I get into it when needed  I fail to understand why the second example I gave works and the first one does.  They both use $_post info.  I do not understand using bind variables and prepared statements.  

[cyberRobot]

In the original post, you mentioned that print_r() gives you the following:

Array (
    [pid] => 34
    [name] => 1stTemplate 
    [key] => Line Key 2 
    [type] => 78 
    [value] => 2339 
    [label] => chance
)

 

The text in the square brackets is what's available for the array keys in $_POST. For example: 

$_POST['pid']
$_POST['name']

 

 

 

They both use $_post info.

 

The array keys available in $_POST are based on however you name the input fields in your form. The following page provides information about how PHP works with forms:

http://php.net/manual/en/tutorial.forms.php

Edited August 4, 2017 by cyberRobot

[cyberRobot]

I do not understand using bind variables and prepared statements.  

 

Perhaps you have already seen this, but there is a relatively quick tutorial on how prepared statements work for MySQLi here:

http://php.net/manual/en/mysqli.quickstart.prepared-statements.php

[ginerjm]

Hopefully you are well on the way to switching to the PDO extension and using prepared queries but...

 

In you original post you ask why one situation works and the other doesn't.  This is apples-and-oranges.  The two are completely unrelated.  Your first query statement is asking for values that apparently don't exist.  Where is 'keytype' or 'keyvalue' defined since they don't show up on your print_r output (if you posted THAT correctly.

 

PS - your second query statement (the orange) is flawed but that is probably because you copied it in here wrong.

 

PPS - Do you have php error checking turned on?  See my signature.

 

But most of all - DO make the PDO switch.