switching mysql query to object based mysqli query

Switching to mysqli. Normally I use mysql_query for php development (sample code snippet attached). Assuming gpc is turned off.

Please provide me code accomplished the same by using object based mysqli query.
1. proper method prepare and bind_param / real_escape_string (instead of using addslash)
2. making query
3. get the mysql_error() - should I use try catch
4. count the number of rows of result set
5. retrieve data from query
7. view the final queried SQL statement (for debugging) - in my code I will simply write echo $SQL; die();

Thanks in advance. Please ask me if you have problem understanding what I am asking..
$userId = addslashes($_POST['userId']);
 
$SQL = "SELECT * FROM tb_user WHERE userId = '$userId'";
$RS = mysql_query($SQL) or die(mysql_error());
$COUNT = mysql_num_rows($RS);
while($ROW = mysql_fetch_array($RS)){
  
  //retrieving data method 1
  $userNo = $ROW[0];
  $userAge = $ROW[1];
 
  //retrieving data method 2 
  $userNo = $ROW['userNo'];
  $userAge = $ROW['userAge'];
 
}

Open in new window

radzeenAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve BinkCommented:
Here's an example for you:
<?
$link = new mysqli("hostname","username","password","db_name");
if (mysqli_connect_errno()) {
  // connection failed.  report and...
  die();
}
 
// be sure to escape all data!!!
$userId = $link->real_escape_string($_POST['userId']);
$query = "SELECT * FROM tb_user WHERE userId='$userId'";
if (!($result=$link->query($query))) {
  // query failed.  report and...
  die();
}
$row_count = $result->num_rows;
while ($row=$result->fetch_array()) {
  //retrieving data method 1
  $userNo = $row[0];
  $userAge = $row[1];
 
  //retrieving data method 2 
  $userNo = $row['userNo'];
  $userAge = $row['userAge'];
}
?>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
radzeenAuthor Commented:
Thanks this is what exactly I want.. but the problem is about the connection.. Normally in mysql method i usually i will create conn.php with the code in the code snippet. Then I will include conn.php in any page before creating the mysql related code. I have to include conn.php only once even I have more than 1 mysql query in the same page.

The question is can I do the same with mysqli by creating $link (which you provide) in conn.php or I have to create the link everytime I have mysqli query?

Thanks a lot in advance.







<?php
$serverName = 'localhost';
$serverUser = 'root';
$serverPassword = 'xxx';
mysql_pconnect($serverName, $serverPassword, $db_password) or trigger_error(mysql_error(),E_USER_ERROR);
mysql_select_db($db_name);
 
unset ($serverName,$serverUser,$serverPassword );
?>

Open in new window

0
Steve BinkCommented:
You can create $link at the beginning of a script, either inline or in an include file, and it will be used globally.  Remember that any variable not created inside a class or function has a global scope.  See the example below for other scope hints, and see here for the official explanation:

http://www.php.net/manual/en/language.variables.scope.php

Also, keep in mind that you are using mysql_pconnect() in your function-based code.  This functions a little differently in that the connection can stay open between scripts.  There are arguments both for and against, but I generally recommend to not use them unless you have a pressing reason.  The object-based example I provided does not use a persistent connection.




<?
// this is dbconnect.php, an include file
$link = new mysqli("hostname","username","password","db_name");
 
// $link is available here
?>
 
<?
// this is page1.php, an actual file
 
// $link is not available here
 
require('dbconnect.php');
// now $link is available
 
function myfunc1() {
  // $link is not available here
  $link2 = new mysqli("hostname","username","password","db_name");
}
 
// $link2 is not available outside of function myfunc1()
 
function myfunc2() {
  global $link;
  // $link is available here
  // $link2 is not
}
 
function myfunc3($linkvar) {
  // $linkvar could be $link (see below)
}
 
$x = myfunc3($link);
$y = myfunc3($link2);  // <-- warning: undefined variable on $link2 reference
?>

Open in new window

0
radzeenAuthor Commented:
Thank you routinet.. you help is really great and shoot away my headache!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.