Johnny2 Posted March 23, 2013 Report Share Posted March 23, 2013 (edited) //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 Quote Link to comment Share on other sites More sharing options...
falkencreative Posted March 23, 2013 Report Share Posted March 23, 2013 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. Quote Link to comment Share on other sites More sharing options...
Johnny2 Posted March 23, 2013 Author Report Share Posted March 23, 2013 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) Quote Link to comment Share on other sites More sharing options...
Johnny2 Posted March 23, 2013 Author Report Share Posted March 23, 2013 (edited) 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 Quote Link to comment Share on other sites More sharing options...
falkencreative Posted March 24, 2013 Report Share Posted March 24, 2013 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(); ?> Quote Link to comment Share on other sites More sharing options...
Johnny2 Posted March 24, 2013 Author Report Share Posted March 24, 2013 (edited) 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 Quote Link to comment Share on other sites More sharing options...
falkencreative Posted March 24, 2013 Report Share Posted March 24, 2013 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++; } Quote Link to comment Share on other sites More sharing options...
phpNOvice Posted May 28, 2014 Report Share Posted May 28, 2014 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? Quote Link to comment Share on other sites More sharing options...
falkencreative Posted May 28, 2014 Report Share Posted May 28, 2014 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. Quote Link to comment Share on other sites More sharing options...
administrator Posted May 28, 2014 Report Share Posted May 28, 2014 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 Quote Link to comment Share on other sites More sharing options...
phpNOvice Posted May 30, 2014 Report Share Posted May 30, 2014 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. Quote Link to comment Share on other sites More sharing options...
phpNOvice Posted May 30, 2014 Report Share Posted May 30, 2014 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. Quote Link to comment Share on other sites More sharing options...
falkencreative Posted May 30, 2014 Report Share Posted May 30, 2014 I would highly advise against using two connections. I'm just saying that it's technically possible. Quote Link to comment Share on other sites More sharing options...
administrator Posted May 31, 2014 Report Share Posted May 31, 2014 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 Quote Link to comment Share on other sites More sharing options...
phpNOvice Posted June 1, 2014 Report Share Posted June 1, 2014 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.