...

View Full Version : In need of help understanding how to correctly connect and use MySQLi



needsomehelp
01-31-2013, 03:55 PM
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


$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.



<!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>

Fou-Lu
01-31-2013, 04:08 PM
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.


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.

needsomehelp
01-31-2013, 04:38 PM
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.

needsomehelp
01-31-2013, 04:51 PM
here is my code now. but this time i get the following error

Parse error: syntax error, unexpected T_VARIABLE

on this line


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






<!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>

needsomehelp
01-31-2013, 05:04 PM
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!

needsomehelp
01-31-2013, 05:30 PM
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.

Fou-Lu
01-31-2013, 11:42 PM
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:


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:


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:


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.

needsomehelp
02-01-2013, 11:45 AM
ah I think I have cracked it... $result = db_query($mysqli, $query);
it is starting to make sense now.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum