• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 685
  • Last Modified:

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

0
radzeen
Asked:
radzeen
  • 2
  • 2
2 Solutions
 
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
 
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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now