Asked  7 Months ago    Answers:  5   Viewed   44 times

I have a function that generates a prepared INSERT statement based on an associative array of column names and values to be inserted into that column and a table name (a simple string):

function insert ($param, $table) {
        $sqlString = "INSERT INTO $table (".implode(', ',array_keys($param)).') VALUES ('.str_repeat('?, ', (count($param) - 1)).'?)';
        if ($statement = $this->conn->prepare($sqlString)):
            $parameters = array_merge(array($this->bindParams($param), $param));
            call_user_func_array(array($statement, 'bind_param', $parameters));
            if (!$statement->execute()):
                die('Error! '.$statement->error());
            endif;
            $statement->close();
            return true;
        else:
            die("Could Not Run Statement");
        endif;
    }

My problem is that $this->conn->prepare (it's part of a class, conn is a NEW mysqli object, which works with no issues) returns false, but does not give me a reason why!

Here is a sample $sqlString that gets built for the prepare statement:

INSERT INTO students (PhoneNumber, FirstName, MiddleInit, LastName, Email, Password, SignupType, Active, SignupDate) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)

Can anyone see a problem with this parameterized statement? Any reason the prepare function would return false?

 Answers

29

I'm copying the solution into this answer so this can be given an upvote, otherwise the question will appear in the "unanswered questions" forever. I'm marking this answer CW so I won't get any points.

@Andrew E. says:

I just turned on mysqli_report(MYSQLI_REPORT_ALL) to get a better understanding of what was going on - turns out that one of my field names was incorrect - you'd think that prepare() would throw an exception, but it fails silently.

Wednesday, March 31, 2021
 
mattltm
answered 7 Months ago
78

you want the following:

$start = 1; $postsPerPage = 1;
$sql = "SELECT id, title, author, LEFT(description, 40) AS excerpt, 
               image_small, image_med, date 
        FROM posts 
        ORDER BY id DESC 
        LIMIT ?, ?";

$stmt = $connect->prepare($sql) or die ('error');
$stmt->bind_param('ii', $start, $postsPerPage);
$stmt->execute();
$stmt->bind_result($id, $title, $author, $excerpt, $image_small, $image_med, $date);

while($stmt->fetch()) {
  printf('<h1>%s</h1><p>%s <small> by %s on %s</small></p>',
    htmlspecialchars($title),
    htmlspecialchars($excerpt),
    htmlspecialchars($author),
    htmlspecialchars($date)
  );
}

this binds both question marks to integer (i) values of $start and $postsPerPage. do NOT use variables directly in prepared statements, because that would defeat the whole purpose of prepared statements (apart from eliminating parsing time)

Saturday, May 29, 2021
 
Gordnfreeman
answered 5 Months ago
58

A prepared statement can only execute one MySQL query. You can prepare as many statements as you want in different variables:

$stmtUser = $sql->prepare("INSERT INTO user (id_user, username, pw, email) VALUES (?,?,?,?)");
$stmtProc = $sql->prepare("INSERT INTO process (id_user, idp) VALUES (?,?);");

And then execute them later. If you want to ensure that neither one is ever run unless both are able to run, then you need to look into transactions, like Thomas said.

Also, a general tip: "call to member function on a non-object" is the standard error you get when prepare() fails and so $stmt isn't actually a prepared statement object. It usually means you need to look for an error in your prepare() statement rather than anything later.

Saturday, June 5, 2021
 
MGP
answered 5 Months ago
MGP
68

The way stored procedures work with prepared statements is a bit more complicated. PHP manual states that you've got to use session variables (MySQL sessions, not PHP)

INOUT/OUT parameter

The values of INOUT/OUT parameters are accessed using session variables.

So you could do it with

$connect=&ConnectDB();
// bind the first parameter to the session variable @uid
$stmt = $connect->prepare('SET @uid := ?');
$stmt->bind_param('s', $uid);
$stmt->execute();

// bind the second parameter to the session variable @userCount
$stmt = $connect->prepare('SET @userCount := ?');
$stmt->bind_param('i', $userCount);
$stmt->execute();

// execute the stored Procedure
$result = $connect->query('call IsUserPresent(@uid, @userCount)');

// getting the value of the OUT parameter
$r = $connect->query('SELECT @userCount as userCount');
$row = $r->fetch_assoc();               

$toRet = ($row['userCount'] != 0);

Remark:

I recommend to rewrite this procedure as a function with one IN parameter that returns INT.

Thursday, August 5, 2021
 
Shibbir
answered 3 Months ago
89

As the php.net link you found states, you cannot use bind variables for identifiers. You'll need a workaround. mysql_real_escape_char would certainly be one way.

Friday, August 6, 2021
 
JustSteveKing
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :