MDB2 unable to "see" table data

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
JohnK_UK
Forum Newbie
Posts: 5
Joined: Wed Jan 11, 2012 12:16 pm

MDB2 unable to "see" table data

Post by JohnK_UK »

Hi All,

I have an application that uses a database class that I wrote myself. I'd like to convert it to use the PEAR MDB2 package. The code below is a simple test script I wrote as an initial test. It appears the I can use MDB2 to connect to the server, create a database and create a table, but not to retrieve the data in the table. Simple calls to mysqli functions can access the data. The MySQL query browser confirms that the database and table exist, and that the table is populated.

I'm deliberately using the root user to avoid permissions problems.

Code: Select all

	
error_reporting(E_ALL);
ini_set("display_errors", 1);
	
require_once 'MDB2.php';

$dsn = 'mysqli://root:@localhost';
$mdb2 =& MDB2::factory($dsn);
if (PEAR::isError($mdb2)) die($mdb2->getMessage() ."<br>" .str_replace("\n", "<br>", print_r($result->getUserInfo(), true)));
else echo("Got the MDB2 connection...<br>");

$result = $mdb2->loadModule('Manager');
if (PEAR::isError($result)) die($result->getMessage() ."<br>" .str_replace("\n", "<br>", print_r($result->getUserInfo(), true)));
else echo("Loaded the Manager module... <br>");

//Create a database
$result = $mdb2->createDatabase('test_db');
if (PEAR::isError($result)) die($result->getMessage() ."<br>" .str_replace("\n", "<br>", print_r($result->getUserInfo(), true)));
else echo("Created the test database...<br>");
	
//Connect to the database
$result = $mdb2->setDatabase("test_db");
if (PEAR::isError($result)) die($result->getMessage() ."<br>" .str_replace("\n", "<br>", print_r($result->getUserInfo(), true)));
else echo("Connected to the test database...<br>");

//Create a table.  Copied from http://pear.php.net/manual/en/package.database.mdb2.intro-manager-module.php
$table_options = array(	'comment' => 'Repository of people',
					'charset' => 'utf8',
					'collate' => 'utf8_unicode_ci',
					'type'    => 'innodb',
				);
$definition = array (	'id' => array (	'type' => 'integer',
							'unsigned' => 1,
							'notnull' => 1,
							'default' => 0,
						),
				'name' => array ( 'type' => 'text',
							'length' => 255
						)
			);
$mdb2->createTable('beatles', $definition, $table_options);
if (PEAR::isError($result)) die($result->getMessage() ."<br>" .str_replace("\n", "<br>", print_r($result->getUserInfo(), true)));
else echo("Created the test table...<br>");
	
//Insert some data into the table
$result = $mdb2->exec("INSERT INTO  beatles (id, name) VALUES (1, \"John\"),(2, \"Paul\"),(3, \"George\"),(4, \"Ringo\")");
if (PEAR::isError($result)) die($result->getMessage() ."<br>" .str_replace("\n", "<br>", print_r($result->getUserInfo(), true)));
else echo("Inserted data into the table...<br>");
	
//Show the tables in the database.
$result = $mdb2->queryAll("SHOW TABLES");
if (PEAR::isError($result)) die($result->getMessage() ."<br>" .str_replace("\n", "<br>", print_r($result->getUserInfo(), true)));
else echo("Got the table array...<br>");
print_r($result);
echo("<br>" .count($result) ." rows retrieved.<br>");
	
//Show the data in the table.
$result = $mdb2->queryAll("SELECT * FROM beatles");
if (PEAR::isError($result)) die($result->getMessage() ."<br>" .str_replace("\n", "<br>", print_r($result->getUserInfo(), true)));
else echo("Got the data array...<br>");
print_r($result);
echo("<br>" .count($result) ." rows retrieved.<br>");
	
//Now see if we can use native PHP calls
$db = new mysqli("localhost", "root", "", "test_db");
if ($db->connect_error) die("Unable to connect to database: " .mysqli_connect_error());
else echo ("Got the MySQLi connection...<br>");
	
$result = $db->query("SELECT * FROM beatles");
if (!$result) die ("Unable to exceute query: " .$db->error);
else echo ("Got the result resource...<br>");
	
while($row = $result->fetch_assoc()) echo(str_replace("\n", "<br>", print_r($row, true)));
echo("Done.");
This produces the following output:

[text]Got the MDB2 connection...
Loaded the Manager module...
Created the test database...
Connected to the test database...
Created the test table...
Inserted data into the table...
Got the table array...
Array ( )
0 rows retrieved.
Got the data array...
Array ( )
0 rows retrieved.
Got the MySQLi connection...
Got the result resource...
Array
(
[id] => 1
[name] => John
)
Array
(
[id] => 2
[name] => Paul
)
Array
(
[id] => 3
[name] => George
)
Array
(
[id] => 4
[name] => Ringo
)
Done.[/text]

I get the same results regarless of whether I use the$mdb2->fetchAll() calls in the example, or any combinations of get*(), fetch*() or query*() functions.

I can't see what I'm doing wrong. Any advice would be appreciated.

Thanks in advance,

John
JohnK_UK
Forum Newbie
Posts: 5
Joined: Wed Jan 11, 2012 12:16 pm

Re: MDB2 unable to "see" table data

Post by JohnK_UK »

It was a bug. Now fixed. See https://pear.php.net/bugs/bug.php?id=19237.
Post Reply