Difference between MySQL and MySQLi

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
pilau
Forum Regular
Posts: 594
Joined: Sat Jul 09, 2005 10:22 am
Location: Israel

Difference between MySQL and MySQLi

Post by pilau »

On the configuration file of vBulletin, it says:

Code: Select all

//	****** DATABASE TYPE ******
	//	This is the type of the database server on which your vBulletin database will be located.
	//	Valid options are mysql and mysqli.  Try to use mysqli if you are using PHP 5 and MySQL 4.1+
What's the difference between both DB types?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

they aren't different database types, but different interfaces for different versions of MySQL. MySQLi is intended to work with 4.1+ versions of MySQL. Whereas MySQL is simply for anything above 3.23 I believe.

It'll be pretty obvious if you chose the wrong one, as you'll get errors about connection issues or invalid passwords..
User avatar
AKA Panama Jack
Forum Regular
Posts: 878
Joined: Mon Nov 14, 2005 4:21 pm

Post by AKA Panama Jack »

Mysqli has some extra functions that allow you to do more things with the database but in most cases they are kind of overkill and little used.

The big thing for some people about Mysqli is you can use it as an object and interact with the database through that object. You can also extend the Mysqli object with your own functions and variables.

http://us2.php.net/manual/en/function.m ... onnect.php

An example from the PHP Manual...

Code: Select all

<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */

if (mysqli_connect_errno()) {
   printf("Connect failed: %s\n", mysqli_connect_error());
   exit();
}

/* Create table doesn't return a resultset */

if ($mysqli->query("CREATE TEMPORARY TABLE myCity LIKE City") === TRUE) {
   printf("Table myCity successfully created.\n");
}

/* Select queries return a resultset */

if ($result = $mysqli->query("SELECT Name FROM City LIMIT 10")) {
   printf("Select returned %d rows.\n", $result->num_rows);

   /* free result set */

   $result->close();
}

/* If we have to retrieve large amount of data we use MYSQLI_USE_RESULT */
if ($result = $mysqli->query("SELECT * FROM City", MYSQLI_USE_RESULT)) {

   /* Note, that we can't execute any functions which interact with the
       server until result set was closed. All calls will return an
        'out of sync' error */

   if (!$mysqli->query("SET @a:='this will not work'")) {
       printf("Error: %s\n", $mysqli->error);
   }

   $result->close();
}

$mysqli->close();
?>
Many hosting companies do not have Mysqli enabled so if you want maximum compatability you should use the Mysql functions instead of Mysqli unless you are using an abstraction layer or create database drivers for both.
pilau
Forum Regular
Posts: 594
Joined: Sat Jul 09, 2005 10:22 am
Location: Israel

Post by pilau »

Well I installed it on my local server so it's ok :)
Thanks guys.
Post Reply