Trouble with a mysqli prepared statement

[SpringVark]

Hi again - newbie question, so advance apologies all round.

 

My connections ->prepare() method always returns false code below

 

<?php
    session_start();
    include '../include/db/db-open.php';
    mysqli_autocommit($mysqli, false);

    $insertPostString = "insert into posts values (null, ?, ?, now(), now(), ?, ?, 0, ?, 0, ?, ?, ?, ?, ?)";

    $stmt = $mysqli->stmt_init();
    if ($stmt = mysqli_prepare($mysqli, $insertPostString))
    {
        $stmt->bind_param(
            $_SESSION('post_postTypeId'),
            $_SESSION('post_postTypeInstanceId'),
            $_SESSION('post_dateToPublish'),
            $_SESSION('post_dateToExpire'),
            $_SESSION('post_publisherName'),
            $_SESSION('post_authorName'),
            $_SESSION('post_title'),
            $_SESSION('post_tagline'),
            $_SESSION('post_content'),
            $_SESSION('post_priorityId')
        );
        $stmt->execute();
        $stmt->close();
    }
    else
    {
        die("Could not prepare SQL statement: $insertPostString");
    }

    mysqli_autocommit($mysqli, true);
    include '../include/db/db-close.php';
?>

 

Any ideas? It has to be something obvious, I'm sure.

 

Advance thanks,

and regards.

[Daniel0]

Try having a look at the manual page again

 

http://dk.php.net/manual/en/mysqli-stmt.bind-param.php

[PFMaBiSmAd]

You are mixing Object oriented and Procedural coding. Pick one or the other.

 

For example, $stmt = mysqli_prepare() returns a bool true/false and assigns that to $stmt, but you are then trying to use $stmt as an object in $stmt->bind_param()

 

I suspect your actual problem is that $mysqli is not what you think it is, so when you use it as an object in $mysqli->stmt_init() and then use it as a link in mysqli_prepare($mysqli, $insertPostString) that it cannot work. What type is $mysqli in your connection code?

[SpringVark]

My connection code:

$mysqli = new mysqli('localhost','root','','wpspc');

 

The reason I starting switching to procedural code was because the mysqli_autocommit($mysqli, false); statement was failing when I tried calling it the OO way (Cannot call a static method from a dynamic context, or something like that).

 

Anyway, I've now changed the prepared statement to OO without any luck:

    $insertPostString = "insert into posts values (null, ?, ?, now(), now(), ?, ?, 0, ?, 0, ?, ?, ?, ?, ?)";
    if ($stmt = $mysqli->prepare($insertPostString))
    {}

 

(the if still fails).

 

Is it possible to be doing that when I'm still calling mysqli_autocommit($mysqli, false); procedurally? I really do need a rollback option because this thing is going to be transaction critical.

 

Thanks,

d.

[Mchl]

I've never had any problems with $mysqli->autocommit(); Perhaps show us how were you doing this.

 

Also try echoing $mysqli->error in else branch of this if.

[SpringVark]

Okay - I'm mosty there. Except that $stmt->execute() keeps on failing, and I don't know how to debug it.

 

Any suggestions?

[PFMaBiSmAd]

I'll guess the problem is that the first parameter of your $stmt->bind_param() is not a list of the types that correspond to each bind parameter. What is your current code?

[SpringVark]

I had in fact noticed the lack of a data description string before, and thus have been using it since. The code currently looks as below. It seems like a mysql error, but if I substitute values and run it in the MySql client console, it runs just fine. Is there any way to see the mySql return error in my browser? Or am I barking up the wrong tree (i.e. is the error still in my php?). Much thanks.

 

<?php
...
    if ($stmt = $mysqli->prepare($insertPostString))
    {
        $stmt->bind_param(
            'isssssssi',
            $_SESSION['post_postTypeId'],
            $_SESSION['post_dateToPublish'],
            $_SESSION['post_dateToExpire'],
            $_SESSION['post_publisherName'],
            $_SESSION['post_authorName'],
            $_SESSION['post_title'],
            $_SESSION['post_tagline'],
            $_SESSION['post_content'],
            $_SESSION['post_priorityId']
        );

        $postId = 0;
        if ($stmt->execute())  {

            $resultSet = $mysqli->query('select max(id) from posts');
            $result = $resultSet->fetch_object();
            $postId = $result->id;

            $insertWeekendReportString = "insert into reports values null, ?, ?, ? ";
            $stmt2 = $mysqli->prepare($insertWeekendReportString);
            $stmt2->bind_param('iss', $postId, $_REQUEST['reportStartDate'], $_REQUEST['reportEndDate']);

            if ($stmt2->execute()) {
                mysqli::commit();
            }
            else {
                $mysqli->rollback();
                die("Could not commit SQL statement: $insertWeekendReportString");
            }
        }
        else {
            $mysqli->rollback();
            die("Could not commit SQL statement: $insertPostString");
        }
...
?>

[SpringVark]

A quick update:

 

I realised that my problems were most likely being caused by some of my input to mySql - and indeed I was inserting single quotes without running the content through htmlspecialchars() first.

 

However, the result when I run the code with htmlspecialchars() has not changed - it still fails when I run $stmt->execute().

 

My biggest problem is debugging it... I simply don't have the php experience to know how!?

 

Thanks for your posts thus far.

d.

[Mchl]

You need not escape data while using prepared statements.

 

Is there any way to see the mySql return error in my browser?

 

Yes. Add

echo $mysqli->error;

BEFORE each $mysqli->rollback();

 

[edit]

 

Also this:

            if ($stmt2->execute()) {
                mysqli::commit();
            }

 

should be

 

            if ($stmt2->execute()) {
                $mysqli->commit();
            }

 

[edit]

 

And another one: you have 10 '?' in your $insertPostString but you only bind 9 parameters.

[SpringVark]

Thank you! The "echo $mysqli->error;" worked like a charm... and leads me to another obstacle

Incorrect date value: '' for column 'dateToExpire' at row 1

I want to be able to pass in a null value here. Apparently '' does not work, and neither does "null".

Let me try and leave a blank space, and get back to you here...

[Mchl]

Try null (without any quotes). Also make sure, that column is not set to NOT NULL

[SpringVark]

null worked. thanks.

I'm sorted now.

Very thankful to you all for your help - I've learnt a lot.

[Mchl]

For future reference. Instead of

INSERT INTO table VALUES (?,null,?)
you could use query like
[code]INSERT INTO table (column1, column3) VALUES (?,?)

 

You only need to specify columns you have data for, and all others will be inserted with default values or nulls.