[SOLVED] MySQLi prepared statement problem

[neromir]

Hey guys, I'm getting a really weird error with a MySQLi prepared statement, and I was wondering if any of you have run into this before or might have any ideas for me. 

 

I've got a set up where I'm trying to pull page names out of the database so I can send appropriate data requests to the controller assigned to that page. 

 

Table: Pages    
Column Name      Type
--------------------------
id               int(11)
RequestName      varchar(255)
Controller       varchar(255)
isPublic         int(11)

 

I'm trying to use MySQLi OO-approach to do prepared statements throughout the website.  Here's where the problem comes in: when I run a prepared statement against this table, the result comes back as 0 rows.  No error is given, just a 0 row resultset.  I tried running the same query through just a mysqli_query() (see below for actual code), after filling the parameter straight into the query string like normal, and it returns 1 row just fine.  Now, it's not that prepared/parameterized statements aren't working at all-- when I do a prepared statement with an int as the parameter, against the other two tables in the database, it works just fine-- one of them works fine with a string while the other doesn't.

 

Here's the code for my test page, which can be accessed at http://www.runicsystems.com/test.php  The Page test will actually dump some info run in the PageManager::getPageByRequest() function (see below), then does the var_dump right after that, which is showing up as a NULL.

 

require_once('model/ConnectionManager.php');
require_once('model/NewsManager.php');
require_once('model/UserManager.php');
require_once('model/PageManager.php');

$newsManager = new NewsManager();
$userManager = new UserManager();
$pageManager = new PageManager();

echo "<b>User test</b>: <br><br>";
var_dump($userManager->getUserByName("Daen"));
echo "<br><br>";
var_dump($userManager->getUserById(1));
echo "<br><br>";
echo "<b>Page test</b>:<br><br>";
var_dump($pageManager->getPageByRequest("admin"));
echo "<br><br>";
echo "<b>news Test</b>: <br><br>";
var_dump($newsManager->getNewsByTitle("Site Launch"));
ConnectionManager::getInstance()->disconnect();

 

The UserManager returns just fine on an int parameter, but when I call getUserByName("username") on it, it returns no rows.  the PageManager is the one with problems with both, while the NewsManager works just fine.

 

I've included the relevant code for the PageManager and ConnectionManager below.

 

The important thing to note with the PageManager is in getPageByRequest()-- I've marked the area of interest with some asterisks-- I've got it running two forms of the query.  The first one, run through ConnectionManager::executeQuery(), works just fine and returns the data.  The second one, run using the prepared statement, tells me there were no rows to retrieve.

 

require_once('ConnectionManager.php');
require_once('TableManager.php');
require_once('Page.php');

class PageManager extends TableManager
{
function __construct()
{

}

public function getPageById($id)
{
	parent::verifyConnection();

	$db = ConnectionManager::getInstance();

	$stmt = $db->prep_stmt("SELECT * FROM Pages WHERE id = ?");

	$stmt->bind_param('i', $id);
	$stmt->bind_result($pageId, $requestName, $controller, $isPublic);
	$stmt->execute();

	if($stmt->num_rows != 1)
	{
		$stmt->close();
		return null;
	}

	$stmt->fetch();

	$page = new Page($pageId, $requestName, $controller, $isPublic);
	$stmt->close();

	return $page;
}

public function getPageByRequest($request)
{
	parent::verifyConnection();

	$db = ConnectionManager::getInstance();

	/*********Area of interest**************/
	$rows = $db->executeQuery("SELECT * FROM Pages WHERE RequestName = '$request'");
	var_dump($rows);

	$stmt = $db->prep_stmt("SELECT * FROM Pages WHERE RequestName = ?");

	$stmt->bind_param('s', $request);
	$stmt->bind_result($pageId, $requestName, $controller, $isPublic);
	$stmt->execute();
	echo $stmt->error;
	echo $db->getError();
	echo "<br><br>";
	var_dump($stmt);
	echo "<br>num rows: ".$stmt->num_rows."<br>";
	/*********End Area of interest**************/

	if($stmt->num_rows == 0)
	{
		$stmt->close();
		return null;
	}

	$stmt->fetch();

	$page = new Page($pageId, $requestName, $controller, $isPublic);
	$stmt->close();

	return $page;
}
}

 

Here's the TableManager, for reference, which has a simple function used across all Managers except the ConnectionManager:

require_once('ConnectionManager.php');

class TableManager
{
protected function verifyConnection()
{
	if(!ConnectionManager::getInstance()->getIsConnected())
		ConnectionManager::getInstance()->makeConnection();
}
}

 

And here's the ConnectionManager, for reference:

class ConnectionManager
{
static private $instance;
private $connection;

function __construct()
{

}

public static function getInstance()
{	
	if(self::$instance)
		return self::$instance;

	self::$instance = new ConnectionManager();

	return self::$instance;
}

public function mySqlConnect()
{		
	$this->connection = new mysqli('localhost', 'database_name', 'myPass', 'mySchema');
}

public function disconnect()
{
	$this->connection->close();
}

public function getError()
{
	return $this->connection->error;
}

public function prep_stmt($query)
{
	return $this->connection->prepare($query);
}

public function executeQuery($query)
{
	$result = mysqli_query($this->connection, $query);

	$rows = array();

	for($i = 0; $i < mysqli_num_rows($result); $i++)
	{
		$rows[$i] = mysqli_fetch_array($result); 
	}

	return $rows;
}

public function makeConnection()
{
	$this->mySqlConnect();
}

public function getIsConnected()
{
	if($connection != null)
		return $connection->ping();
	else
		return false;
}
}

 

The PHP version I'm running on is 5.2.6, and MySQL version 5.0.45.

 

Additionally, I had MySQL log the queries it received in the general MySQL log, and everything looks to be fine (below).  Have any of you got any ideas on this?  I'd really appreciate any help you can give.  The only thing I can think of is if there's some sort of strange problem in between MySQL sending the data back, and the MySQLi objects losing it somewhere.

 

5 Connect     user@localhost on schema
	      5 Prepare     [1] SELECT * FROM Users WHERE username = ?
	      5 Execute     [1] SELECT * FROM Users WHERE username = 'Daen'
	      6 Connect     user@localhost on schema
	      5 Quit       
	      6 Prepare     [1] SELECT * FROM Users WHERE id = ?
	      6 Execute     [1] SELECT * FROM Users WHERE id = 1
	      7 Connect     user@localhost on schema
	      6 Quit       
	      7 Query       SELECT * FROM Pages WHERE RequestName = 'admin'
	      7 Prepare     [1] SELECT * FROM Pages WHERE RequestName = ?
	      7 Execute     [1] SELECT * FROM Pages WHERE RequestName = 'admin'
	      8 Connect     user@localhost on schema
	      7 Quit       
	      8 Prepare     [1] SELECT * FROM News WHERE Title = ?
	      8 Execute     [1] SELECT * FROM News WHERE Title = 'Site Launch'
	      8 Quit

[neromir]

It's been a couple of days-- does anybody have any ideas on this?

[MasterACE14]

you're not getting any answers cause there's too much to read :-/

[neromir]

Yeah, that's what I thought.  Oh well, that's what I get for trying to provide all the information-- kinda disappointing.  The problem statement is in the first 2 paragraphs (Correction-- first two paragraphs after the table description at the top).

 

Unfortunately I can't edit the original post to make it more reader-friendly.

[neromir]

I found the solution to the problem-- I needed to be calling $stmt->store_result() before checking $stmt->num_rows.  See http://us2.php.net/manual/en/mysqli-stmt.num-rows.php.