CodingForums.com - In need of help understanding how to correctly connect and use MySQLi

CodingForums.com (http://www.codingforums.com/index.php)

- MySQL (http://www.codingforums.com/forumdisplay.php?f=7)

- - In need of help understanding how to correctly connect and use MySQLi (http://www.codingforums.com/showthread.php?t=286806)

In need of help understanding how to correctly connect and use MySQLi

I have created a testing code as I have not been able to understand how mysqli work or how I use it.

Below is the code I have setup that is currently showing the following error...

Fatal error: Call to a member function query() on a non-object
on line 20

Line 20 being

Code:

$result = $mysqli->query("$query");

Does anyone know of a website that shows examples of how to correctly use mysqli, I have tried php.net but so far not finding much help.

Code:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html><head><title>Untitled Document</title><meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

</head><body><?





/*        DATABASE ROUTINES        */

function dbConnect() {

  $dbhost = 'localhost';

  $dbuser = '****_****';        $dbpass = '****';        $dbname = '****_****';

                  // mysqli - start

                        $mysqli = mysqli_init();

                        $mysqli = new mysqli("$dbhost", "$dbuser", "$dbpass", "$dbname");

                        return  $mysqli;

}

/*                END OF DATABASE ROUTINES                */



//Function to query the database.

  function db_query($query) {

    //$result = mysql_query($query) or db_error($query, mysql_errno(), mysql_error());

        echo(":".$query.":");

        $result = $mysqli->query("$query");                //                Line 20

        return $result;

  }



$connected = dbConnect();



$getErrors = db_query("SELECT * FROM `errors` ORDER BY `id` DESC");



                  if(mysql_num_rows($getErrors)) {

                  echo '<div class="field" style="font-size:0.625em;">

                  <div class="wordwrap" style="float: left; width: 4.5em; padding-left: 1em;"><strong>id:</strong></div>

                  <br class="clearfloat">';



                  echo '

                  <div style="clear:both; padding-top: 0.2125em; border-bottom: 0.0625em dashed #E5E5E5;"></div>

                  </div>';

                                while($theErrors = mysql_fetch_assoc($getErrors)) {

                                  echo '<div class="field" style="font-size:0.625em;">

                                  <div class="wordwrap" style="float:left; width: 4.5em; padding-left: 1em;"> <em>'.stripslashes($theErrors['id']).'<br><br>'.stripslashes($theErrors['time']).'&nbsp;</em> </div>';

                                  echo '<div style="clear:both; padding-top: 0.2125em; border-bottom: 0.0625em dashed #999;"></div>

                                  </div>

                                  <br class="clearfloat">';

                                }

                  } else {

                        echo '<div class="field">No Errors yet!</div>';

                  }



?></body></html>

You got used to the autoglobal of the mysql resource (which was a really bad idea on zend's part). MySQLi doesn't exist outside of the scope it was created. You need to pass the resource to the functions in question.

PHP Code:


		
			
function db_query(MySQLi $mysqli, $query)
{
    . . .
}

If you have parametrized queries, you'll be wanting to use prepared statements instead. That I'd suggest writing a second function for, or modifying the first one to accept a third parameter array for the arguments, then determining if it should issue a ->query or ->prepare statement.

mysqli_init() is also not required. This is only required when you plan on modifying a few of the settings which must occur between the mysql_init and before the new mysqli() or mysqli_connect calls. There's not a whole lot of these configurations that require this though.

so in short i can not use a database connection in a function unless I setup a connection inside the function as well or pass the connection resources to the function as well.

here is my code now. but this time i get the following error

Parse error: syntax error, unexpected T_VARIABLE

on this line

Code:

$getErrors = db_query(MySQLi $mysqli, "SELECT * FROM `errors` ORDER BY `id` DESC");

Code:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html><head><title>Untitled Document</title><meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

</head><body><?





/*        DATABASE ROUTINES        */

function dbConnect() {

  $dbhost = 'localhost';

  $dbuser = '****_****';        $dbpass = '****';        $dbname = '****_****';

                  // mysqli - start

                        //$mysqli = mysqli_init();

                        $mysqli = new mysqli("$dbhost", "$dbuser", "$dbpass", "$dbname");

                        return  $mysqli;

}

/*                END OF DATABASE ROUTINES                */



//Function to query the database.

  function db_query(MySQLi $mysqli, $query) {

    //$result = mysql_query($query) or db_error($query, mysql_errno(), mysql_error());

        echo(":".$query.":");

        $result = $mysqli->query("$query");

        return $result;

  }



$connected = dbConnect();



$getErrors = db_query(MySQLi $mysqli, "SELECT * FROM `errors` ORDER BY `id` DESC");



                  if(mysql_num_rows($getErrors)) {

                  echo '<div class="field" style="font-size:0.625em;">

                  <div class="wordwrap" style="float: left; width: 4.5em; padding-left: 1em;"><strong>id:</strong></div>

                  <br class="clearfloat">';



                  echo '

                  <div style="clear:both; padding-top: 0.2125em; border-bottom: 0.0625em dashed #E5E5E5;"></div>

                  </div>';

                                while($theErrors = mysql_fetch_assoc($getErrors)) {

                                  echo '<div class="field" style="font-size:0.625em;">

                                  <div class="wordwrap" style="float:left; width: 4.5em; padding-left: 1em;"> <em>'.stripslashes($theErrors['id']).'<br><br>'.stripslashes($theErrors['time']).'&nbsp;</em> </div>';

                                  echo '<div style="clear:both; padding-top: 0.2125em; border-bottom: 0.0625em dashed #999;"></div>

                                  </div>

                                  <br class="clearfloat">';

                                }

                  } else {

                        echo '<div class="field">No Errors yet!</div>';

                  }



?></body></html>

ok i think i have it...

the thing that is confusing the issue for me is this -> i do not understand what it does.

whats is it, i really need to find out more about it. as the testing page now shows this error...

mysql_num_rows() expects parameter 1 to be resource, object given in

but i am guessing that this error is because i am using a mysql command and not a mysqli command to count the rows. I hope!

Now I am even more confused about 'determining if it should issue a ->query or ->prepare statement.'

just about to google that!

EDIT:

ok i think i will need the basic query functions, nothing that will repeat the query, only used the once and use the returned data and then kill the results.

i have found information about this 'arrow operator' and only found what it is called. But still not sure what it actually does.

This isn't correct here: $getErrors = db_query(MySQLi $mysqli, "SELECT * FROM `errors` ORDER BY `id` DESC");. You cannot provide a datatype hint during the calling of the function; I mean the signature of the function can accept the typehint:

PHP Code:


		
			
function db_query(MySQLi $mysqli, $qry);

You are correct, the error for mysql_num_rows exists since you cannot combine mysqli and mysql libraries. I also don't recommend using the function calls since they differ between mysqli_stmt and mysqli_result objects. Instead, use them in object oriented fashion as they both share the same property name. Unfortunately, there is no superclass governing both mysqli_stmt and mysqli_result, so you cannot typehint:

PHP Code:


		
			
function count_rows($result)

{

    if (!($result instanceof mysqli_Stmt) || !($result instanceof mysqli_result))

    {

        throw new InvalidArgumentException('Cannot only retrieve records from mysqli resultset object');

    }

    return $result->num_rows;

}

That would work with both mysqli_result and mysqli_stmt objects. Of course a custom function is optional.

-> is object oriented de-reference operator. It resolves an object to a property or method of the class' instance. To use in context as I've indicated with the third parameter of the method, that would be like so:

PHP Code:


		
			
function db_query(MySQLi $con, $sQry, array $params = array())

{

    if (!empty($params))

    {

        // bind this

        // This is an absolute pain in mysqli btw:

        if ($stmt = $mysqli->prepare($sQry))

        {

            $rf = new ReflectionMethod($stmt, 'bind_param');

            $aUseParams = array();

            foreach ($params AS $itm)

            {

                $aUseParams[] = &$itm;

            }

            // this is lazy, but I'ma do it anyway

            $sParamTypes = str_repeat('s', count($aUseParams));

            $rf->invokeArgs($stmt, array($sParamTypes, implode(', ', $aUseParams)));

            $result = $stmt->getResult();

        }

    }

    else

    {

        // execute this.

        $result = $mysqli->query($sQry);

    }

    return $result;

}

Doesn't look right, but I can't test that where I am. The problem with the bind is that it expects to bind via reference, and dynamic counts in MySQLi are not accommodated so you need to invoke a callable/closure/reflection type in order for it to process. But something does look a little off.

If that works as I intend it to, than it would ultimately return a mysqli_result regardless of if it used a statement or not. I can't be 100% sure it would work; I've used pretty much prepared statements exclusively since the mysqli was released.

ah I think I have cracked it... $result = db_query($mysqli, $query);
it is starting to make sense now.