Mysqli_Connect Vs Mysql_Connect

[timveer]

Hey all,

 

First time poster, several time owner (my bookshelf sags under the VisualQuickPro section)

 

Trying to port over some code from mysql to mysqli, but ran into a snag (actually I just can't get my head around it...)

 

I am used to, on an "includes()" function file (functions.inc), including my db connection scripts and several db related functions.

 

For example, I have a section at the beginning of the functions.inc file:

(note the code here is stripped and cleaned to be clearer)

 

$connect = mysql_connect($tbl_hostname, $tbl_username, $tbl_password) ;
$selectdb = mysql_select_db($tbl_database, $connect) ;

 

Further down I call a function:

 

function user_exists($user)
{
$total = mysql_query("SELECT COUNT(`user_id`) FROM `user` WHERE `user_name` = '{$user}'");
return (mysql_result($total, 0) == '1') ? true : false;
}

 

and it worked fine, however when I try to use mysqli I run into problems.

 

Re-working the functions.inc file it now includes:

 

$connect = mysqli_connect('localhost', 'root', 'password', 'users_db') ;

 

and the function looks like:

 

function user_exists($user)
{

$total = mysqli_query($connect, "SELECT COUNT(`user_id`) FROM `user` WHERE `user_name` = '{$user}'");
$row= mysqli_fetch_array($total);
return ($row['COUNT(`user_id`)'] == '1') ? true : false;
}

 

But I keep getting a "Undefined variable: connect in the file... etc, etc"

 

My question (to which I couldn't find a clear answer) - Do I need to include the database connector line inside every function calling to the db?

 

function user_exists($user)
{
$connect = mysqli_connect('localhost', 'root', 'password', 'users_db');
$total = mysqli_query($connect, "SELECT COUNT(`user_id`) FROM `user` WHERE `user_name` = '{$user}'");
$row= mysqli_fetch_array($total);
return ($row['COUNT(`user_id`)'] == '1') ? true : false;
}

 

Thanks for any pointers.

-t

[Larry]

This is a common problem when people make the switch. The standard MySQL functions will find and use an open database connection if one is not provided. The Improved MySQL functions need an explicit connection, so you have to pass that to the function and then use it within the MySQL functions.

[timveer]

Thanks Larry,

That's what I thought, but now I know for sure.

Keep up the good writings.

Cheers,

-t

EDIT: Hey Larry, quick thought. Is this applicable in both procedural and OOP approaches to using mysqli?

Thanks

-t

[Antonio Conte]

Yes, it is timveer.

 

In Object-oriented code, you would like to pass a Mysqli object to a function rather than just the connection.

 

This is an example from my NationController controller class that handles nations.

 

<?php

include('model/Nation.php');

class NationController {

   private $mysqli = null; // MySQLi-object

   /**
    * Public constructor for NationController
    *
    * @param Mysqli $mysqli    - A MySQLi-connection object. See DBController
    */
   public function __construct(Mysqli $mysqli) {
       if ($this->mysqli == null) {
           $this->mysqli = $mysqli;
       }
   }

   /**
    * Creates nation objects in model/Nation.php.
    *
    * Use static function Nation::getNations to return
    * the array holding the nations. The other functions
    * add nations to this array.
    *
    */
   public function getAll() {
       if ($result = $this->mysqli->query('SELECT * FROM abc_nations')) {
           while ( $row = $result->fetch_row() ) {
               new Nation($row[0], $row[1], $row[2]);
           }
       }
   }

   /**
    * Get nation by ID.
    *
    * @param int $id            - An identity number for a nation
    * @throws Exception        - InvalidArgument $id (integer)
    */
   public function getNationById($id) {
       if (!ctype_digit($id) && !is_int($id))
           throw new Exception('- ID must be an integer');

       if ($result = $this->mysqli->query('SELECT * FROM abc_nations WHERE id = '.$id.' LIMIT 1')) {
           while ( $row = $result->fetch_row() ) {
               new Nation($row[0], $row[1], $row[2]);
           }
       }
   }

   /**
    * Get Nation by Iso code
    *
    * @param String $iso        - The unique ISO number for a country
    */
   public function getNationByIso($iso) {
       $iso = $this->mysqli->real_escape_string($iso);
       if ($result = $this->mysqli->query('SELECT * FROM abc_nations WHERE iso like "'.strtoupper($iso).'" LIMIT 1')) {
           while ( $row = $result->fetch_row() ) {
               new Nation($row[0], $row[1], $row[2]);
           }
       }
   }

   /**
    * Get nation by name
    *
    * @param String $name        - The name of the country
    */
   public function getNationByName($name) {
       $name = $this->mysqli->real_escape_string($name);
       if ($result = $this->mysqli->query('SELECT * FROM abc_nations WHERE name like "%'.$name.'%" LIMIT 1')) {
           while ( $row = $result->fetch_row() ) {
               new Nation($row[0], $row[1], $row[2]);
           }
       }
   }

}

[Larry]

By the way, timveer, when you edit a post, instead of adding a new post, I don't see that you've added more, so I wouldn't have replied to this thread. Just so you know!

[timveer]

Antonio & Larry,

 

Thanks both for your responses.

 

@Antonio - wow.... That OOP stuff looks really interesting. I'll have to spend some more time looking at OO for my own mySQL use and project development.

 

Cheers,

-t

[Antonio Conte]

timveer: Don't do the same mistake I did. Objects are not something "better", cooler or faster. You can easily combine the two, and you really should. Small scrips is often better to develop procedural. The real advantages of OOP comes into play when you need to create applications and larger systems.

 

It would be good practice to switch MySQLI from procedural to OOP. It will allow you to understand more without re-inventing everything in OOP.

 

As Larry said, just use your connection as a parameter in each function. You are already doing good by separating logic from presentation in your functions.

 

And please not this: Try using this a username:

' '; SELECT * FROM SOME_TABLE

. Your functions are vulnerable to SQL Injection. Use Mysqli_real_escape_string() to prevent it. ; )