Page 1 of 1

mysqli binding problem

Posted: Sun Jun 10, 2012 12:11 am
by Gonzalos
Hello I been trying to execute a query with mysqli and It gives error in the prepare line

Code: Select all

$mySqli = $this->openConnection();
$stmnt = $mySqli->prepare("SHOW COLUMNS FROM ?");
After this line I make the binding of the ? value, why is giving me error? I donĀ“t post the next part of the code because it just gives error in this line, it is the same if i comment or not the next lines.

Thanks

Re: mysqli binding problem

Posted: Mon Jun 11, 2012 2:21 am
by faust
does the rest of your code successfully uses the db? Maybe something is wrong with your class, and $this is not an actual object...

Re: mysqli binding problem

Posted: Mon Jun 11, 2012 4:54 pm
by Gonzalos
$this is correct here, If I do var_dump ($mySqli); it gives back the connection.
The rest of the code does use the information that have extracted from the DB. I think this code was working fine on mysqli 5.0.92, now my server was update to mysqli 5.0.95 and most of the prepare statements does not work.

Any idea?

Thanks very much for your answer.

Re: mysqli binding problem

Posted: Mon Jun 11, 2012 11:09 pm
by Gonzalos
I post all the classes, maybe someone can try and find out the problem:

db.php

Code: Select all

<?php
class baseDal  {
	function __construct($host="", $user="", $password="", $database="")
	{
		if (empty($host)
		|| empty($user)
		|| empty($password)
		|| empty($database))
		{
			$this->host = "localhost";
			$this->user = "root";
			$this->password = "";
			$this->database = "db1";
		}
		else
		{
			$this->host = $host;
			$this->user = $user;
			$this->password = $password;
			$this->database = $database;
		}
	}

	protected function openConnection() {
		$mySqli = new mysqli($this->host, $this->user, $this->password, $this->database);
		return $mySqli;

	}
}
Call to the function

Code: Select all

class dbOperations extends baseDal   {
	function __construct($host='', $user='', $password='', $database='') {
		parent::__construct($host, $user, $password, $database);
	}
	
	function extractFields ($table){
		$mySqli = $this->openConnection();
		$stmnt = $mySqli->prepare("SHOW COLUMNS FROM ?"); //HERE THE ERROR APPEARS
                $stmt->bind_param('s', $table);
		$result = $stmnt->execute();
		if (!$result)
		{
			return false;
		}
		$stmnt->bind_result($field, $type, $null, $key, $default,$extra);
		while ($stmnt->fetch()) {
			$field = array('Field'=>$field, 
							'Type'=>$type);
			$fields[] = $field;
		}
		$stmnt->close();
		return $fields;
	}
}

Re: mysqli binding problem

Posted: Tue Jun 12, 2012 12:34 am
by ejsexton82
It looks like you mispelled the variable $stmnt:

Code: Select all

// Incorrect
$stmnt = $mySqli->prepare("SHOW COLUMNS FROM ?");
$stmt->bind_param('s', $table);

// Correct
$stmnt = $mySqli->prepare("SHOW COLUMNS FROM ?");
$stmnt->bind_param('s', $table);
Try that and see if that helps. Good luck!

Re: mysqli binding problem

Posted: Tue Jun 12, 2012 6:14 am
by Gonzalos
Sorry It was my mistake, in my php code was fine, the misspelling was just here.

Still the error that gives me is : Fatal error: Call to a member function bind_param() on a non-object
In the same line $stmnt->bind_param('s', $table);

If I don't use the bind_param ex:

Code: Select all

$stmnt = $mySqli->prepare("SHOW COLUMNS FROM " . $table);
It works fine, so the problem is in the binding.

Any idea?

Thanks

Re: mysqli binding problem

Posted: Tue Jun 12, 2012 9:46 pm
by ejsexton82
Okay, this is from the PHP Manual:
[Parameter Markers (?)] are legal only in certain places in SQL statements. For example, they are allowed in the VALUES() list of an INSERT statement (to specify column values for a row), or in a comparison with a column in a WHERE clause to specify a comparison value.

However, they are not allowed for identifiers (such as table or column names)...

-- The PHP Group. PHP: mysqli::prepare - Manual. Updated 2012-06-08.
You might try something like this instead:

Code: Select all

$stmnt = $mySqli->prepare(sprintf("SHOW COLUMNS FROM `%s`;",
		strtr($table,array('`'=>''))));
Hope that helps. Good luck!

Re: mysqli binding problem

Posted: Wed Jun 13, 2012 11:49 pm
by Gonzalos
Thank you very much for your answer thats clarify the problem!.