Converting Mysql To Mysqli

[Johnny2]

//I would like to convert this mysql code to a mysqlI version:

 

 

$sql=mysql_query("SELECT * FROM friends WHERE sex = 'F'");

$friendCount = mysql_num_rows($sql);

if ($friendCount > 0) {

while($row = mysql_fetch_array($sql)){

$name = $row["name"];

$age = $row["age"];

$favoriteColor = $row["favoriteColor"];

$list1 .= "Here is the name: ".$name."<br />Here is age: ".$age."<br />Here is the favorite color: ".$favoriteColor."<br /><br />";

}

}

 

 

 

//Here is my shot at the mysqlI version:

 

$sex="F";

 

$statement = $mysqli->prepare("SELECT * FROM friends WHERE sex = ?");

$statement->bind_param("s", $sex);

$statement->execute();

$statement->store_result();

if ($statement->num_rows != 0){

while($row = mysqli_fetch_array($mysqli)){

$name = $row["name"];

$age = $row["age"];

$favoriteColor = $row["favoriteColor"];

$list .= "Here is the name: ".$name."<br />Here is age: ".$age."<br />Here is the favorite color: ".$favoriteColor."<br /><br />";

}

}

$statement->close();

 

 

I'm pretty sure my problem is in the if-statement area (of the mysqlI version), but I have no idea what to put there. Can someone help me out please?

Edited March 23, 2013 by Johnny2

[falkencreative]

Just to be clear, what error are you currently getting with your version?

 

For a reference, you might check out http://www.killersites.com/community/index.php?/topic/3064-basic-php-system-view-edit-add-delete-records-with-mysqli/ which has examples of a variety of MySQLi queries.

[Johnny2]

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, object given

 

This is referring to the if-statement area (of the mysqlI version)

[Johnny2]

I tried using the examples you provided, but those examples were not prepared statements. My new try at the mysqlI statment looks like this:

 

 

$sex="F";

 

if ($result = $mysqli->prepare("SELECT * FROM friends WHERE sex = ?")){

$result->bind_param("s", $sex);

$result->execute();

$result->store_result();

if ($result->num_rows != 0){

while ($row = $result->fetch_object()){

$name = $row->name;

$age = $row->age;

$favoriteColor = $row->favoriteColor;

$list .= "Here is the result->num_rows: ".$result->num_rows."<br />Here is name: ".$name."<br />Here is age: ".$age."<br />Here is $favoriteColor: ".$favoriteColor."<br /><br />";

}

}

$statement->close();

}

 

 

I'm now getting this error referring to the while loop:

 

Fatal error: Call to undefined method mysqli_stmt::fetch_object()

 

 

I just need the mysqli code to do EXACTLY the same thing that the mysql code does (in my initial example).

Edited March 23, 2013 by Johnny2

[falkencreative]

Here's a better example demonstrating using a prepared statement with MySQLi. See http://devzone.zend.com/239/ext-mysqli-part-i_overview-and-prepared-statements/ for a more detailed explanation.

 

<?php

/* connect to db */
$mysqli = new mysqli("localhost", "user", "password", "world");
if (mysqli_connect_errno()) {
   printf("Connect failed: %s\n", mysqli_connect_error());
   exit();
}

/* prepare statement */
if ($stmt = $mysqli->prepare("SELECT Code, Name FROM Country WHERE Code LIKE ? LIMIT 5")) 
{
   /* bind variables */
   $stmt->bind_param("s", $code);
   $code = "C%";

   /* execute statement */
   $stmt->execute();

   /* bind variable results */
   $stmt->bind_result($col1, $col2);

   /* fetch values */
   while ($stmt->fetch()) {
       echo $col1 . ' ' . $col2 . '\n';
   }

   /* close statement */
   $stmt->close();
}

/* close connection */
$mysqli->close();

?>

[Johnny2]

Thank you Ben! That was what I was looking for! And I read through the information you linked for me.

 

I have another question now.

 

Let's say you wanted to SELECT * ,let's say 85 columns, of a database table with our query. From what I'm gathering, (and I could be wrong), I would have to type in 85 different variables to bind the results to.

 

$stmt->bind_result(85 separate variables HERE);

 

What a pain in the butt THAT would be...

 

Isn't there an easier way, so that I could SELECT *, but only assign five of them to work with? I mean you can pick and choose which gathered info to work with in the following example: (yes, I realize that this example is an sql query and not a prepared-mysqli one like I am looking for)

 

 

$sql=mysql_query("SELECT * FROM friends WHERE sex = 'F'");

$friendCount = mysql_num_rows($sql);

if ($friendCount > 0) {

while($row = mysql_fetch_array($sql)){

$name = $row["name"];

$age = $row["age"];

$favoriteColor = $row["favoriteColor"];

}

}

 

 

And one more question...

How do I get a row count (like the one on line 2 of this example) in the prepared-mysqli version?

Edited March 24, 2013 by Johnny2

[falkencreative]

If you have 85 columns in a database, I'd think your database is designed wrong, and you need to be splitting that up into multiple tables. Also, from a database load perspective, it's easier on the database if you are selecting specific columns rather than selecting them all with "*". That aside, it is possible to use get_result() instead. See

 

http://php.net/manual/zh/mysqli-stmt.bind-result.php (see comment #2)

http://php.net/manual/en/mysqli-stmt.get-result.php

 

For counting rows, just use a variable, and add to it every time you have a while loop. As a very rough example:

 

$i = 1;
while ($stmt->fetch()) {
   // work with the database here...

   echo "Row: " . $i;
   $i++;
}

[phpNOvice]

Hello Ben,

 

I have understood your example in better example demonstrating using a prepared statement with MySQLi.

 

This is now my current Connect.php codes

<?php
	$host = "localhost";
	$dbusername = "root";
	$dbpassword = "nopassword";
	$dbname = "student";
		 
	$link_id = mysql_connect($host, $dbusername, $dbpassword);
	if(!$link_id){
		die(mysql_error("Can`t Connect To database")); 
	}
	else{
		$db = mysql_select_db($dbname, $link_id);
	}	  
	if(!$db){
		die(mysql_error("Can`t select database")); 
	}	  
	return;
?>

I suppose this is the deprecated one. What if im going to follow your instructions in converting this to mysqli. 

Would it affects my other codes that uses

include Connect.php

is this process of converting to mysqli is complicated i believe i need to convert this whole system to mysqli?

[falkencreative]

The process of converting mysql to mysqli isn't complicated... but yes, you have to convert everything over. The only way you can have both MySQL and MySQLi going at the same time is if you have two separate connections.

[administrator]

 

 

The only way you can have both MySQL and MySQLi going at the same time is if you have two separate connections.

 

Don't do it. Pick MySQLi or PHPPDO ... don't mix them like that. 

 

Stef

[phpNOvice]

Well, this is my first time i heard to use two connections. Whoah!! i've got so interested in doing two connections,how do we do it-- simultaneusly or alternately? But anyways maybe this will just bother my mind cause im not really have enough knowledge. I think i have to focus first on how to convert my given mysql to mysqli.

But i like those two insights you have suggested on me.

[phpNOvice]

Hello Sir Stefan,

 

Please give me an idea how to find the equivalent codes i have given in mysqli.

Basically it is converting mysql to mysqli.

[falkencreative]

I would highly advise against using two connections. I'm just saying that it's technically possible.

[administrator]

Hello Sir Stefan,

 

Please give me an idea how to find the equivalent codes i have given in mysqli.

Basically it is converting mysql to mysqli.

We have a mini course on using MySQLi ... but just search for a simple example of MySQLi (Ben created one on this forum) and try that.

 

Stef

[phpNOvice]

thanks Ben and Stefan,

 

I'll come back to this post after i completed and run first my whole system in mysql....

 

It's better i'll finish this first before i'l get concentrating it converting it to my_sqli.

 

Thank you both.