Unable to input numbers into MySQL using php statement

Question

I am working on a website where an administrator can edit a schedule that they already created. They can click on any item on the schedule to edit it. For example, they can click on the shift start time and then it directs them to a page where they can update the value.

Unfortunately, I have not been able to get this to work for every value. It seems to be that the text values are working just fine, but I am getting a syntax error when it is a number.

Here is what I am using to update:

$type = $_GET['type'];
$value = $_GET['value'];
$week = $_GET['week'];
$newval = $_POST['newval'];
if(strlen($newval) > 0)
{
    include '../dbinfo.php';
    $type = $mysqli->real_escape_string($_POST['type']);
    $week = $mysqli->real_escape_string($_POST['week']);
    $tablename = $mysqli->real_escape_string("cs" . $_SESSION['squadron']);
    $newval = $mysqli->real_escape_string($newval);
    if((is_numeric($newval)))
    {
        $sql = "UPDATE $tablename SET $type=$newval WHERE week=$week";
    }
    else
    {
        $sql = "UPDATE $tablename SET $type='$newval' WHERE week=$week";
    }
    if($result = $mysqli->query($sql))
    {
        echo "Your specififed changed was completed successfully!<br>";
        echo "<a href='edit.php?week=" . $week . "'>Continue editing</a>";
    }
    else
    {
        echo mysqli_error($result);
    }
}

Changing a string results in the sql statement:

UPDATE cs14 SET shift_1_name='Test' WHERE week=1 (this works)

Changing a number results in the sql statement:

UPDATE cs14 SET shift_ 1_starttime=940 WHERE week=1 (this doesn't work)

It is giving me the MySQL error:

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 '1_starttime=940 WHERE week=1' at line 1

I have already researched this error, and I have checked the syntax over and over again. It doesn't work in phpmyadmin either. I have no idea what to check next!

Can anyone help me out with my syntax here??? Thanks!

is the column type you're using of type int (or other numeric) ? — Nick Andriopoulos
– Nick Andriopoulos, Commented Mar 20, 2013 at 15:18
Newval shouldn't be a string especially if your database is set to integer for that value. — karmafunk
– karmafunk, Commented Mar 20, 2013 at 15:19
column type for everything is varchar. When I tried to change a date (that had ints in it), it still gave me an error, so I don't believe it's a column error. I tried even without quotes but I get the same error. — brokekidweb
– brokekidweb, Commented Mar 20, 2013 at 15:20
You need to surround all variables in your queries with single quotes (table name with backticks), otherwise you're still open to SQL injection. Better: use parametrized queries. — Marcel Korpel
– Marcel Korpel, Commented Mar 20, 2013 at 15:21
The syntax error is there because of the space in shift_ 1_starttime. See my answer for solutions. — Husman
– Husman, Commented Mar 20, 2013 at 15:23

believe me · Accepted Answer · 2013-03-20 15:21:13Z

1

At the numeric update query put quotes around,

$sql = "UPDATE $tablename SET $type='$newval' WHERE week='$week'";

answered Mar 20, 2013 at 15:21

believe me

9081 gold badge5 silver badges24 bronze badges

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

Comments

Husman · Accepted Answer · 2013-03-20 15:19:08Z

0

The $type variable contains a space. Remove the space from it.

More specifically "shift_ 1_starttime" contains a space. Wherever your setting $type to "shift_ 1_starttime" remove the space from it. Or if thats how it is defined in the database surround it with backticks `

$sql = "UPDATE $tablename SET `$type`='$newval' WHERE week=$week";

answered Mar 20, 2013 at 15:19

Husman

6,93910 gold badges32 silver badges48 bronze badges

1 Comment

brokekidweb Over a year ago

Awesome! I was the space giving me the problem. I actually had the error on another page. I had a feeling it was something silly. Thanks for the help!

Collectives™ on Stack Overflow

Unable to input numbers into MySQL using php statement

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related