PHP Inserting into mysql issue

Question

I am having trouble when I run the following command:

$stmt = $this->db->prepare("INSERT INTO checked_in (attendee_id) VALUES (?)");
$stmt->bind_param("i", $id);
$stmt->execute();
$stmt->close();

This is part of a bigger page but this is the part that isn't working. I first pull the $id from the SQL database so I know I have a connection. I just can't seem to get this update part to work.

mysql > CREATE TABLE checked_in ( 
        id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
        attendee_id int NOT NULL,
        check_in_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

This is the code I am using to build the table. Am I doing something wrong? I think my problem is that it can talk to the database but I don't know if I set up the table wrong where it is expecting the id and the timestamp to be manually entered. It works if I am on my sql prompt and use this command

mysql> INSERT INTO checked_in (attendee_id) VALUES (1);

Here is the main part of the program (in the process of adapting the tutorial to my own):

function checkIn() {

    // Check for required parameters
    if (isset($_POST["phone"]))  {

        // Put parameters into local variables
        $phone = $_POST['phone'];

        // Look up code in database
        $user_id = 0;
        $stmt = $this->db->prepare('SELECT id, first, last, email FROM attendees WHERE phone=?');
        $stmt->bind_param("s", $phone);
        $stmt->execute();
        $stmt->bind_result($id, $first, $last, $email);
        while ($stmt->fetch()) {
            break;
        }
        $stmt->close();

        // Bail if number doesn't exist
        if ($id <= 0) {
            sendResponse(400, 'Invalid number');
            return false;
        }   

        // Check to see if this device already redeemed 
        $stmt = $this->db->prepare('SELECT id FROM checked_in WHERE attendee_id=?');
        $stmt->bind_param("i", $id);
        $stmt->execute();
        $stmt->bind_result($checkedInID);
        while ($stmt->fetch()) {
            break;
        }
        $stmt->close();

        // Bail if number already checked in
        if ($checkedInID > 0) {
            sendResponse(403, 'Number already checked in');
            return false;
        }

        // Add tracking of redemption
        $stmt = $this->db->prepare("INSERT INTO checked_in (attendee_id) VALUES (?)");
        $stmt->bind_param("i", $id);
        $stmt->execute();

        // Return unlock code, encoded with JSON
        $result = array(
            "checked_in" => "true",
        );
        sendResponse(200, json_encode($result));
        return true;
    }
    sendResponse(400, 'Invalid request');
    return false;
        }
}

Ok so I checked my apache error logs and this is what it is saying:

[Fri Jul 25 15:06:41.996107 2014] [:error] [pid 16824] [client 127.0.0.1:52699] PHP Notice:  Undefined variable: db in /var/www/html/index.php on line 129
[Fri Jul 25 15:06:41.996171 2014] [:error] [pid 16824] [client 127.0.0.1:52699] PHP Fatal error:  Call to a member function prepare() on a non-object in /var/www/html/index.php on line 129

Are you checking for them? If not, add error reporting to the top of your file(s) right after your opening <?php tag error_reporting(E_ALL); ini_set('display_errors', 1); see if it yields anything. — Funk Forty Niner
– Funk Forty Niner, Commented Jul 25, 2014 at 18:39
Are you using this in conjunction with a form? How exactly are you using this? Show more code. Plus, use mysqli_error($db); depending on the DB variable. Which API are you using? Plus, try $stmt = $db->prepare instead of $stmt = $this->db->prepare it's hard to say what your DB connection is or if using a class. — Funk Forty Niner
– Funk Forty Niner, Commented Jul 25, 2014 at 18:42

Bjoern · Accepted Answer · 2014-07-25 18:57:01Z

4

If you bind your parameters while using a question mark in your queries, then you shouldn't use i as parameter. Use a numeric parameter instead.

Try

$stmt->bind_param(1, $id);

From them manual:

For a prepared statement using named placeholders, this will be a parameter name of the form :name. For a prepared statement using question mark placeholders, this will be the 1-indexed position of the parameter.

That said, please describe what $id is assigned to in this function.

edited Jul 25, 2014 at 18:57

answered Jul 25, 2014 at 18:40

Bjoern

16.3k4 gold badges47 silver badges50 bronze badges

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

3 Comments

ajk4550 Over a year ago

After turning errors on I get this: mysqli_stmt::bind_param(): Undefined fieldtype 1 (parameter 2) in /var/www/html/index.php on line 131

ajk4550 Over a year ago

The line you mentioned is line 131 by the way

Bjoern Over a year ago

If that doesn't work, try changing the question mark to something like :value and use this as bind parameter.

Foreba · Accepted Answer · 2014-07-25 18:39:43Z

0

Try this:

$stmt = $this->db->prepare("INSERT INTO checked_in (attendee_id) VALUES (?)");
$stmt->execute( array( $id ) );

should work.

If you want to check the error, you should do:

$stmt = $this->db->prepare("INSERT INTO checked_in (attendee_id) VALUES (?)");
$check = $stmt->execute( array( $id ) );

if ( $check ) {
    echo 'Nice work!';
} else {
    $error = $stmt->errorInfo();
    echo $error[2];
}

answered Jul 25, 2014 at 18:39

Foreba

4104 silver badges15 bronze badges

1 Comment

ajk4550 Over a year ago

It gives me this error: "mysqli_stmt::execute() expects exactly 0 parameters" and "Call to undefined method mysqli_stmt::errorInfo()"

Collectives™ on Stack Overflow

PHP Inserting into mysql issue

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related