Page 1 of 1

MySQLi and INSERT to mysql

Posted: Fri Mar 07, 2008 11:41 am
by trenches
Hi guys,

I'm new to php and mysql but have been trying to insert to my mysql database using insert with mysqli.

I seem to be able to do SELECT statements but I cant INSERT, the code seems to run but not insert and records.

Here's the code:

<?php

$mysqli = new mysqli('host', 'username', 'pssword', 'db');

/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}

$stmt = $mysqli->prepare("INSERT INTO establishments (ename) VALUES (?)");
$stmt->bind_param('s', $ename);
$ename = 'hotel1';

/* execute prepared statement */
$stmt->execute();

printf("%d Row inserted.\n", $stmt->affected_rows);

/* close statement and connection */
$stmt->close();

/* close connection */
$mysqli->close();
?>



Am I doing something obviously wrong?

Can anybody help!!?

Thanks in advance,

T.

Re: MySQLi and INSERT to mysql

Posted: Fri Mar 07, 2008 12:20 pm
by Sekka
Tried setting $ename before the bind param?

You have no validation on the prepare, bind, or execute. Are you 100% sure they are all working, no errors given?

Apart from that, 'odd' is the only word I would use to describe why that doesn't work. All seems ok.

Re: MySQLi and INSERT to mysql

Posted: Sat Mar 08, 2008 5:08 am
by trenches
Thanks for your advice...pretty new to php!

Managed to do some debugging and found out that i hadnt specified any default values for my fields in mysql. So my code was correct.

Thanks.

Re: MySQLi and INSERT to mysql

Posted: Sat Mar 08, 2008 2:14 pm
by Sekka
Your welcome.

Remember when programming, never assume things will always work, even if you are sure they will.

Place debugging on everything you can. Here is an example of one of my insert queries (slightly altered),

Code: Select all

private function query_Insert_SomeInsert () {
 
    // Load DB connection
    $db = DB_MySQL::getInstance ();
 
    // Prepare the query
    $query = $db -> prepare ("
    INSERT INTO sometable
    (
        somefield1,
        somefield2,
        somefield3,
        somefield4
    ) 
    VALUES 
    (
        ?,
        ?,
        ?,
        ?
    )
    ");
 
    // Check query was prepared
    if (!is_object ($query)) {
        trigger_error ($db -> error, E_USER_WARNING);
        return false;
    }
 
    // Bind parameters and check success
    if (!$query -> bind_param ("iiss", 
        $this -> properties['somefield1'],
        $this -> properties['somefield2'],
        $this -> properties['somefield3'],
        $this -> properties['somefield4']
    )) {
        return false;
    }
 
    // Execute the query
    if (!$query -> execute ()) { return false; }
 
    // Store the ID
    $this -> properties['id'] = $db -> insert_id;
 
    return true;
 
}