Unknown column 'Bob' in 'field list'... php or mysql problem

Question

Hello,

I have a curious problem that I hope is something stupid I overlooked.

I am getting an error that usually occurs when there is a missing column in a mysql table, or a misspelled php variable: "Unknown column 'Bob' in 'field list'"...

Except 'Bob' is not the name of a column, its the VALUE I am trying to assign to the row. Why would php be confusing the two??

Here is my php function that I believe is the location of the error:

function recordGuest($id,$fname,$lname,$dinner){
    $conn = connect("wedding");
    $guest_query = "INSERT INTO guest (fname,lname,person_id)
                    VALUES (".$fname.",".$lname.",".$id.");";
    mysql_query($guest_query,$conn) or die(mysql_error());

    $guest_id_query = "SELECT id FROM guest WHERE person_id = ".$id.";";
    $guest_id_result = mysql_query($guest_id_query,$conn) or die(mysql_error());
    $guest_id = "";
    while($row = mysql_fetch_array($guest_id_result)){
        $guest_id = $row["id"];
    }

    $guest_dinner_query = " INSERT INTO guest_dinner (dinner_id,guest_id)
                            VALUES (".$dinner.",".$guest_id.");";
    mysql_query($guest_dinner_query,$conn) or die(mysql_error());
}

Here is the php code that processes the form and executes the above function:

<?php
include("functions.php");

$code = $_POST["code"];
$type = $_POST["type"];
$people = getPeople($code);
$ids = $people["ids"];
$email= "";

for($i = 0; $i < count($ids); $i++){
    $response = $_POST["response_".$i];
    $dinner = $_POST["dinner_".$i];
    recordResponse($ids[$i],$response);
    if($dinner != "null"){
        recordDinner($ids[$i],$dinner);
    }
    if($type == 3){
        $guest_responses = $_POST["guest_response_".$i];
        $guest_fname = $_POST["guest_fname_".$i];
        $guest_lname = $_POST["guest_lname_".$i];
        $guest_dinner_response = $_POST["guest_dinner_response_".$i];
        if($guest_dinner_response != "null"){
            recordGuest($ids[$i],$guest_fname,$guest_lname,$guest_dinner_response);
        }
    }
}
?>

Heres what my "guest" mysql table looks like:

guest
    id int auto inc (primary key)
    fname varchar
    lname varchar
    person_id int

Any help would be appreciated.

Answer 1

Enclose the string in single quotes i.e. 'Bob' before trying to insert the string values

Answer 2

Yes, try to enclose in sigle quotes like this :

VALUES ('".$fname."','".$lname etc

But better yet you should never insert stuff in the database that way, it open the risk of SQL injection and other bad stuff

unless you are using some draconian php version you can use PDO and use statement. Like in this example i nimbly stole from php.net

<?php
/* Execute a prepared statement by binding PHP variables */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->bindParam(':calories', $calories, PDO::PARAM_INT);
$sth->bindParam(':colour', $colour, PDO::PARAM_STR, 12);
$sth->execute();
?>