Variable not recognised in INSERT but working in SELECT

Question

Basically I am using the variable $shopid to recognise which shop has been chosen. I am now trying to create a comment system to enable each shop page to be commented on. My SELECT query is recognising $shopid and enabling me to use it, when I try to use the same variable in my INSERT, it simply posts 0.

<?php

database connection

session_start();
    if (isset($_SESSION['logged'])){
    $s_userID = $_SESSION['userID'];
    $shopid = $_GET['page_id'];
    $str_shops = '';

        //bring shop data
        mysqli_select_db($db_server, $db_database);
        $query = "SELECT * FROM shops WHERE shopID = '$shopid'"; 
        $result = mysqli_query($db_server, $query); 
            if (!$result) die("Database access failed: " . mysqli_error($db_server)); 
            while($row = mysqli_fetch_array($result)){ 
                $str_shops .= "<div class='result'><strong>" .
                $row['image1'] . "<br><br>" . 
                $row['name']  . "</strong><br><br>" .
                $row['address'] . "<br><br>" . 
                $row['website'] . "<br><br>" . 
                $row['openinghours'] . "<br><div class='justifytext'>" .
                $row['more'] . "<br><br></div><strong>What do they sell?</strong><br><br><div class='justifytext'>" .
                $row['sold'] . "<br><br></div></div>";
            }

        //post comment  
        mysqli_select_db($db_server, $db_database);             
        $comment = $_POST['comment'];
        if ($comment != '') { 
                $query = "INSERT INTO comments (userID,shopID,comment) VALUES ('$s_userID', '$shopid', '$comment')"; 
                mysqli_query($db_server, $query) or 
                die("Insert failed: " . mysqli_error($db_server));  
                $commentmessage = "Thanks for your comment!"; 
        }           

    mysqli_select_db($db_server, $db_database);
    $query = "SELECT * FROM comments";
                $result = mysqli_query($db_server, $query);
                if (!$result) die("Database access failed: " . mysqli_error($db_server)); $i = 0;
                while($row = mysqli_fetch_array($result)){ $i++;
                $str_comments.= "<p><div id='displaycomments'>" . $row['username']. ", " . 
                            $row['commdate'] . ": <br>" .
                        $row['comment'] . "</div>";
        }

}


echo $str_shops;
echo $commentmessage;
echo $str_comments;

mysqli_close($db_server); 
?>

Can anyone see why this isn't working? I'm not getting an error, it is simply adding 0 to the shopID column in my table.

**Unrelated:** You are **wide open** to SQL injection attacks, and you will be hacked if you haven't been already. Please use prepared / parameterized queries to prevent this from happening. See also: [How can I prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/) — Amal Murali, Apr 13 '14 at 14:17
Try to echo your `$query`,copy output and execute in your sql administration tool(like phpmyadmin) .Then post results here. — potashin, Apr 13 '14 at 14:21

score 0 · Answer 1 · answered Apr 13 '14 at 14:24

0

My guess would be that your shopID column would be of INT datatype and you are passing a string to it in your insert statement, thats why 0 is being stored.
Try again by removing the single quotes around $shopid, like this-

INSERT INTO comments (userID,shopID,comment) VALUES ('$s_userID', $shopid, '$comment')"
                                                                  ^^^^^^^ remove the single quotes

answered Apr 13 '14 at 14:24

Kamehameha

5,423
1
23
28

That leaves me with: "Insert failed: 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 ' 'hello')' at line 1" (I typed hello into the comment box) – Kathryn Apr 13 '14 at 14:28
@Kathryn did you remove both the single quotes? What is the datatype of the column shopID? – Kamehameha Apr 13 '14 at 14:31
Yeah I removed both! It is: Name: shopID Type: int(11) Null: Yes Default: NULL, it is the same as my userID which is why I don't understand why it's not inserting – Kathryn Apr 13 '14 at 14:35
@Kathryn hmm... it should work after removing the quotes around $shop_id. Adding single quote inserts 0 in db . Can you echo out the query? – Kamehameha Apr 13 '14 at 14:39
it won't echo out the query for some reason? – Kathryn Apr 13 '14 at 14:49
@Kathryn it won't echo? `echo $query`? Is it not visible? – Kamehameha Apr 13 '14 at 14:53
Nope, nothing appears! :/ – Kathryn Apr 13 '14 at 14:55
@Kathryn `var_dump($query)`? – Kamehameha Apr 13 '14 at 15:01
INSERT INTO comments (userID,shopID,comment) VALUES ('10', '', 'comment'), that's with the single quotes, without it brings the error message – Kathryn Apr 13 '14 at 15:08
@Kathryn your $shopid variable does not seem to be set. That's why ''(converted to 0) is being inserted. When you removed the single_quotes, there would be no value, hence MySql raised an error. Try using the function [isset()](http://in2.php.net/isset) before you actually execute the query. That will allow you to check if `$shopid` actually is set before you use it. – Kamehameha Apr 13 '14 at 15:22
Ok thanks! It isn't set, but it is for the first query so I don't understand why it isn't set for the second – Kathryn Apr 13 '14 at 15:30
@Kathryn hmm... if your code is exactly as you've posted, it should not behave that way... Try var_dump($shopid) at different instances, to see where it exactly changes. And also, `$shopid = $_GET['page_id'];` before assigning the value of $_GET[] variable, check if it's set. – Kamehameha Apr 13 '14 at 15:37

Variable not recognised in INSERT but working in SELECT

1 Answers1