mySQL Driver Problems

[TStall]

I've made a mysql driver that almost works... I've tested it on a page and it always gives me an error and doesn't work the way it should. I set up a test table with 6 entries, the test script should print the entires in 6 table rows - the script creates 6 table rows but it doesn't include the data, and the error returned is saying that it couldn't be fetched...

Here is the test script:

<?php
$root_path = '';
include($root_path . 'includes/extension.inc');
include($root_path . 'includes/global.' . $ext);
// these includes include the driver and create the connection

$db->query("SELECT * FROM driver_test");

echo "<table>\n";
while($test = $db->fetch())
{
    echo "<tr><td>$test['id']</td><td>$test['name']</td><td>$test['email']</td></tr>\n";
}
echo "</table>\n\n";

if($db->is_errors())
{
    $db->show_errors();
}

?>

And here is the driver itself

class MysqlDriver {

//#######################################################
//# Variables
//#######################################################
var $info = array(
                    'db_server'     => '',
                    'db_user'       => '',
                    'db_database'   => '',
                    'db_persistent' => false
                 );

var $error           = array();

var $connect_id      = 0;
var $query           = 0;
var $results         = array();
var $query_count     = 0;




//#######################################################
//# Functions
//#######################################################

/// Constructor
function MysqlDriver($db_server = '', $db_user = '', $db_pass= '', $db_database = '', $db_persistent = false)
{
    if(!($db_server == '' || $db_user == '' || $db_pass == '' || $db_database == ''))
    {
        $this->info['db_server']     = $db_server;
        $this->info['db_user']       = $db_user;
        $this->info['db_pass']       = $db_pass;
        $this->info['db_database']   = $db_database;
        $this->info['db_persistent'] = $db_persistent;
        return true;
    }

    else
    {
        $this->error[] = 'Insuffient number of arguments supplied to MysqlDriver';
        return false;
    }
}

//#------------------------------------------------------
//# Connection
//#------------------------------------------------------
/// Connect
function connect()
{
    if($this->info['persistent'])
    {
        $this->connect_id = @mysql_pconnect($this->info['db_server'],
                                            $this->info['db_user'],
                                            $this->info['db_pass']);
    }

    else
    {
        $this->connect_id = @mysql_connect($this->info['db_server'],
                                           $this->info['db_user'],
                                           $this->info['db_pass']);
    }

    if(!($this->connect_id))
    {
        $this->error[] = 'Could not establish mysql connection.'.$this->get_mysql_error();
        return false;
    }

    elseif(!(mysql_select_db($this->info['db_database'], $this->connect_id)))
    {
        $this->error[] = 'Could not select mysql database.'.$this->get_mysql_error();
        return false;
    }

    else
    {
        return true;
    }

}

/// Disconnect
function disconnect()
{
    if($this->result)
    {
        @mysql_free_result($this->result);
    }

    if(!mysql_close($this->connect_id))
    {
        $this->error[] = 'Could not close mysql connection.'.$this->get_mysql_error();
        return false;
    }

    else
    {
        return true;
    }
}

//#------------------------------------------------------
//# Queries
//#------------------------------------------------------
/// Set Query
function query($query = 0)
{
    if(!($query))
    {
        $this->error[] = 'Insufficient number of arguments supplied to MysqlDriver query()';
        return false;
    }

    else
    {
        $this->query = @mysql_query($query, $this->connect_id);
        if(!($this->query))
        {
            $this->error[] = 'Could not execute query.'.$this->get_mysql_error();
            return false;
        }

        else
        {
            return true;
        }
    }
}

/// Fetch Results
function fetch()
{
    $this->results = @mysql_fetch_array($this->query);
    if(!($this->results))
    {
        $this->error[] = 'Could not fetch array.'.$this->get_mysql_error();
        return false;
    }

    else
    {
        return $this->results;
    }
}

/// Free Result
function free_result()
{
    if(!(mysql_free_result($this->query)))
    {
        return false;
    }

    else
    {
        return true;
    }
}

/// Get Number of Rows
function get_num_rows()
{
    return mysql_num_rows($this->query);
}

/// Get Number of Affected Rows
function get_affected_rows()
{
    return mysql_affected_rows($this->connect_id);
}

/// Get Number of Queries Used
function get_query_count()
{
    return $this->query_count;
}




//#------------------------------------------------------
//# Errors
//#------------------------------------------------------
/// Check for errors
function is_errors()
{
    if(count($this->error) >= 1)
    {
        return true;
    }

    else
    {
        return false;
    }
}

/// Print out the errors
function show_errors()
{
    if(count($this->error) >= 1)
    {
        echo '<font face="Verdana" size="-1"><u>Warning: The following error(s) have occurred</u><br />';
        foreach($this->error as $value)
        {
            echo '<li>'.$value.'</li>';
        }
        echo '</font>';
    }
}

/// Return Formatted mySQL Errors
function get_mysql_error()
{
    $string = ' <small><b>('.@mysql_errno().': '.@mysql_error().')</b></small>';
    return $string;
}

}

It seems to be a problem with the fetch, but I dont know...

[CaptianChaos]

Good work on this class. I can see you've put some thought into Database Abstraction. By all means, continue your research, it's good for the soul to abstract their code.

I am sorry I don't have an answer to your question. That would require a lot of work. Instead I'll give you a heads up on DB.php from PEAR:

PHP now installs with PEAR, which, by default, comes with DB.php. You can view details at http://pear.php.net/. Most people only have to include( "DB.php" ), which should be in the PHP include path. For example:

The simplest way to perform a MySQL connection and loop through results is to use the PEAR DB.php class like this:

<?php
require_once( 'DB.php' );
define( 'MYDB_DSN', 'mysql://username:password@localhost/database' );

function getDBConnection($dsn)
{
 $aDB = DB::connect( $dsn );
 $aDB->setFetchMode( DB_FETCHMODE_ASSOC );
 return $aDB;
}

$aDB = getDBConnection( MYDB_DSN );
$aSQL = "SELECT * FROM tablename";
$aRows = $aDB->simpleQuery( $aSQL );
$aRowCount = $aDB->numRows( $aRows );
if ( is_int($aRowCount) && $aRowCount > 0)  {
 while( $aRow = $aDB->fetchRow( $aRows ) ) {
   print "------NEW ROW---------<br>\n";
   while ( list($key, $val) = each ($aRow)) {
     print "[ $key ] = $val<br>\n";
   }
 }
}
?>

I don't know what's wrong with this site's PHP color coding enhancement tool, but the define line is supposed to be:
define( 'MYDB_DSN', 'mysql://username:password@localhost/database' );

[TStall]

Thanks for the insight Captain 🙂

From what version of PHP is the DB.php included? Is it just the newest?

[Weedpacket]

Originally posted by CaptianChaos

I don't know what's wrong with this site's PHP color coding enhancement tool, but the define line is supposed to be:
define( 'MYDB_DSN', 'mysql://username:password@localhost/database' );

I think there's your answer: Disable Smilies in this Post.

But no, they shouldn't be enabled in [PHP] blocks in the first place.

[CaptianChaos]

It looks like a colon followed by just about anything is a smiley.

For example, I will do a series of colons with a, b, c, d, e, f, g, h, j, etc.:
:a
:b
:c
:d
:e
:f
:g
:h
:i
:j
:k
:l
:m
:n
:o
:p
:q

I stopped at q, tired of typing. So, that answers that question... I see I can disable them before my post, I should have done that.

TStall: I don't know how long it's been standard, at least since 4.2. The first package release was 2002-04-23, so it's been around for about a year now. I use it a lot.

The only complaint I have about DB.php is the lack mysql_insert_id function.

[The_Letter_J]

Does anyone know an fix for the actual code?

[CaptianChaos]

$this->results = @mysql_fetch_array($this->query);

You could try this instead:

$this->results = @mysql_fetch_assoc($this->query);

Also, do this to see what you are getting:

while($test = $db->fetch())
{
    echo "<tr><td colspan=3>
    print_r($test);
    echo "</td></tr>\n";
    echo "<tr><td>$test['id']</td><td>$test['name']</td><td>$test['email']</td></tr>\n";
}

[TStall]

Ok, I've got it, but its really weird :eek:

echo "<tr><td>".$test['id']."</td><td>".$test['name']."</td><td>".$test['email']."</td></tr>\n";

I had to get out of the string (using dot operator) and it works. That is super weird, I've never had a problem like that before..

And the reason I was getting my error was because in the driver, i put "if not results" (if fetch was a fail, basically) that it would through the error - the only problem with that is mysql_fetch_array doesn't return true/false on success/failure 😃

So everything is all good!

Thanks everyone for help, espeially Captain, if it wasn't for that print_r, I'd keep thinking my array was empty 😃

[CaptianChaos]

You can use curly brace around your vars like this:

echo "<tr><td>{$test['id']}</td><td>{$test['name']}</td><td>{$test['email']}</td></tr>\n";

I like doing this, it's easier to type than ". $var['whatever'].", in my opinion, and also easier to read. Just a suggestion, sorry I didn't catch that in the first place.

[CaptianChaos]

On the contrary, htmlspecialchars is your friend, you may want to try:

echo "<tr><td>".htmlspecialchars($test['id'])."</td><td>".htmlspecialchars($test['name'])."</td><td>".htmlspecialchars($test['email'])."</td></tr>\n";

[TStall]

Yep, I know, it was just supposed to be a simple test to test the driver and it turned to hell lol