Php use mysql or mysqli

PHP Connect to MySQL

PHP 5 and later can work with a MySQL database using:

• MySQLi extension (the «i» stands for improved)
• PDO (PHP Data Objects)

Earlier versions of PHP used the MySQL extension. However, this extension was deprecated in 2012.

Should I Use MySQLi or PDO?

If you need a short answer, it would be «Whatever you like».

Both MySQLi and PDO have their advantages:

PDO will work on 12 different database systems, whereas MySQLi will only work with MySQL databases.

So, if you have to switch your project to use another database, PDO makes the process easy. You only have to change the connection string and a few queries. With MySQLi, you will need to rewrite the entire code — queries included.

Both are object-oriented, but MySQLi also offers a procedural API.

Both support Prepared Statements. Prepared Statements protect from SQL injection, and are very important for web application security.

MySQL Examples in Both MySQLi and PDO Syntax

In this, and in the following chapters we demonstrate three ways of working with PHP and MySQL:

MySQLi Installation

For Linux and Windows: The MySQLi extension is automatically installed in most cases, when php5 mysql package is installed.

PDO Installation

Open a Connection to MySQL

Before we can access data in the MySQL database, we need to be able to connect to the server:

Example (MySQLi Object-Oriented)

$servername = «localhost»;
$username = «username»;
$password = «password»;

// Create connection
$conn = new mysqli($servername, $username, $password);

// Check connection
if ($conn->connect_error) die(«Connection failed: » . $conn->connect_error);
>
echo «Connected successfully»;
?>

Note on the object-oriented example above:

$connect_error was broken until PHP 5.2.9 and 5.3.0. If you need to ensure compatibility with PHP versions prior to 5.2.9 and 5.3.0, use the following code instead:

// Check connection
if (mysqli_connect_error()) die(«Database connection failed: » . mysqli_connect_error());
>

Example (MySQLi Procedural)

$servername = «localhost»;
$username = «username»;
$password = «password»;

// Create connection
$conn = mysqli_connect($servername, $username, $password);

// Check connection
if (!$conn) die(«Connection failed: » . mysqli_connect_error());
>
echo «Connected successfully»;
?>

Example (PDO)

$servername = «localhost»;
$username = «username»;
$password = «password»;

try $conn = new PDO(«mysql:host=$servername;dbname=myDB», $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo «Connected successfully»;
> catch(PDOException $e) echo «Connection failed: » . $e->getMessage();
>
?>

Note: In the PDO example above we have also specified a database (myDB). PDO require a valid database to connect to. If no database is specified, an exception is thrown.

Tip: A great benefit of PDO is that it has an exception class to handle any problems that may occur in our database queries. If an exception is thrown within the try < >block, the script stops executing and flows directly to the first catch() < >block.

Close the Connection

The connection will be closed automatically when the script ends. To close the connection before, use the following:

Источник

MYSQL vs MYSQLI vs PDO in PHP (Which One To Use!?)

Welcome to a quick tutorial on the difference between MySQL, MySQLi, and PDO in PHP. So you may have just started working with MySQL in PHP, and noticed that there are 3 different extensions to work with the database.

The main difference between the MySQL, MySQLi, and PDO extensions is:

• MySQL – The early PHP-MySQL extension, currently defunct and removed.
• MySQLi (MySQL Improved) – An improved version of the earlier MySQL extension.
• PHP Data Objects (PDO) – The modern database extension. Supports not just MySQL, but also other databases such as Firebird, SQLite, Postgre, and more.

Yes, the so-called “difference” is actually the development history of MySQL extensions in PHP itself. But just which one is correct, which one is “better”, and which should you use? Read on to find out!

TABLE OF CONTENTS

PHP MYSQL EXTENSIONS

All right, let us now get into the examples of using each MySQL database extension. Also, a simple “non-scientific non-professional” test to see which one performs the best.

PRELUDE) DUMMY DATABASE

-- (A) USERS TABLE CREATE TABLE `users` ( `id` bigint(20) NOT NULL, `name` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ALTER TABLE `users` ADD PRIMARY KEY (`id`); ALTER TABLE `users` MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT; -- (B) DUMMY USERS INSERT INTO `users` (`id`, `name`) VALUES (1, 'Bevis'), (2, 'Cally'), (3, 'Harrison'), . 

If you are interested to run the “non-conclusive performance test”, here is the dummy table we are using, download the zip file above.

PART 1) LEGACY MYSQL EXTENSION

 mysql_select_db(DB_NAME, $mysql); mysql_set_charset(DB_CHARSET, $mysql); // (C) SELECT USERS $result = mysql_query("SELECT * FROM `users`"); while ($row = mysql_fetch_array($result, MYSQL_NUM)) < print_r($row); >mysql_free_result($result); mysql_close($mysql); // (TEST) TOTAL TIME TAKEN $taken = microtime(true) - $taken; echo "Total time taken $taken"; 

Now, the (original) MySQL extension has been totally removed in PHP 7. As such, I will just leave this here as an example for the people who may need to support legacy systems. Otherwise, please don’t use this outdated extension anymore.

PART 2) MYSQLI EXTENSION

connect_error) < exit($mysqli->connect_error); > $mysqli->set_charset(DB_CHARSET); // (C) SELECT USERS $result = $mysqli->query("SELECT * FROM `users`"); while ($row = $result->fetch_assoc()) < print_r($row); >$result->close(); $mysqli->close(); // (TEST) TOTAL TIME TAKEN $taken = microtime(true) - $taken; echo "Total time taken $taken";

That’s right, this is pretty much the same as the traditional MySQL, the only difference is that we are using new mysqli() instead. As for the usage, just trace through the script, it is straightforward as can be.

PART 3) PDO EXTENSION

 PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC ]); // (C) SELECT USERS $stmt = $pdo->prepare("SELECT * FROM `users`"); $stmt->execute(); while ($row = $stmt->fetch()) < print_r($row); >if ($stmt !== null) < $stmt = null; >if ($pdo !== null) < $pdo = null; >// (TEST) TOTAL TIME TAKEN $taken = microtime(true) - $taken; echo "Total time taken $taken"; 

Now, this PDO example should be very straightforward too. But the beauty of PDO actually lies with its compatibility with a wide range of databases – That is, this extension works with many other databases apart from MySQL. Also, there are many smart ways to fetch entries using PDO – fetch() , fetchColumn() , fetchAll() , and even down to setting how the array is arranged.

DOWNLOAD & NOTES

Here is the download link to the example code, so you don’t have to copy-paste everything.

SUPPORT

600+ free tutorials & projects on Code Boxx and still growing. I insist on not turning Code Boxx into a «paid scripts and courses» business, so every little bit of support helps.

EXAMPLE CODE DOWNLOAD

Click here for the source code on GitHub gist, just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

EXTRA BITS & LINKS

That’s all for this guide, and here is a small section on some extras and links that may be useful to you.

WHICH ONE IS “BETTER”?

So yep, there is a split-second difference in performance. I personally won’t make a huge fuss out of it, but if you are into “scientific professional performance tests” – Feel free to devise your own set of tests (and let us know the results).

MYSQL, MYSQLI, PDO – WHICH ONE SHOULD I USE?

I will highly recommend using PDO. Simply for its support for a range of databases, and the abstraction it provides. We only need to enable the respective PDO extension in php.ini , and the project is ready to work with databases other than MySQL. For example, “upgrading” from MySQL to Oracle Database.

REFERENCES & LINKS

• PDO – PHP
• MySQLi – PHP
• MySQL – PHP
• phpMyAdmin – A free PHP database manager. If you installed the XAMPP package, it is already installed at http://localhost/phpmyadmin .
• MySQL Workbench – The so-called official MySQL database manager by Oracle themselves.

THE END

Thank you for reading, and we have come to the end of this guide. I hope that it has helped you with your project, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!

Источник

Переход с mysql на mysqli в php 7

С развитием технологий программирования старые методы отбрасываются, а заместо них появляются более совершенные. Это можно видеть как в библиотеках jQuery — когда с ростом версии библиотек исчезает синтаксис старых функций, так и в php языке программирования.

На сегодня в php 7 исчезла стандартная команда для работы с БД mysql и большая часть функций, которая с ней связана. Теперь используется mysqli. Он доступен с версии php 5.3. Поэтому на 95% серверах лучше сразу программировать в новом формате под mysqli.

Рассмотрим методы перехода с mysql на mysqli

Подключение к БД (старое mysql):
mysql_connect($database_host, $database_user, $database_password);
mysql_query(«SET NAMES ‘utf8′»);
mysql_select_db($database_name);

Подключение к БД (новое mysqli):
$connect = mysqli_connect($database_host, $database_user, $database_password, $database_name);
mysqli_query($connect, «SET NAMES utf8»);

В обоих примерах:
$database_host — хост сервера
$database_user — пользователь БД
$database_password — пароль пользователя БД
$database_name — выбранная БД

Заметьте в новом подключении используется всего одна строка (переменная $connect), в которой сразу прописаны все данные. И в новом формате чаще всего mysql будет заменяться на mysqli.

Рассмотрим создание таблиц:

Старый синтаксис:
mysql_query(«create table IF NOT EXISTS reitingpeopl (id int not null AUTO_INCREMENT, name TEXT(100000) not null, emails TEXT(100000) not null, PRIMARY KEY(id) ) DEFAULT CHARACTER SET utf8 «);

Новый синтаксис:
mysqli_query($connect, «create table IF NOT EXISTS reitingpeopl (id int not null AUTO_INCREMENT, name TEXT(100000) not null, emails TEXT(100000) not null, PRIMARY KEY(id) ) DEFAULT CHARACTER SET utf8 «);

Здесь все как и раньше, только mysql_query меняется на mysqli_query и добавляется $connect для соединения с БД.

Теперь рассмотрим циклы:

Старый синтаксис:
$podresult = mysql_query(«select * from reitingpeopl where «);
while ($podrow=mysql_fetch_array($podresult))

Новый синтаксис:
$podresult = mysqli_query($connect, «select * from reitingpeopl where «);
while ($podrow=mysqli_fetch_array($podresult))

В новом синтаксисе используется mysqli_fetch_array вместо mysql_query и пишется $connect для подключения к БД.

В принципе это основы. Приведу пару примеров, которые также могут вами использоваться:
mysql_real_escape_string($_POST[«yourid»]) меняется на: mysqli_real_escape_string($connect, $_POST[«yourid»])
mysql_query(«select * from reitingpeopl»); меняется на: mysqli_query($connect, «select * from reitingpeopl»);

Еще популярные примеры команд (просто добавьте на конце i):
mysqli_fetch_row()
mysqli_fetch_assoc()
mysqli_fetch_array()
mysqli_num_rows()
mysqli_insert_id()
mysqli_close()

Если что-то вы не нашли, то это можно будет легко отыскать в справочниках. В любом случае mysqli в php 7 работает намного быстрее, чем mysql, поэтому не задумываясь переходите в новый формат!

Источник

ADODB — Switch from Mysql to Mysqli

In the light of MySQL to soon be deprecated, I need to move a large website using ADODB from MySQL to MySQLi. Now I have looked up a few topics on Stackoverflow and thanks to the community I already have a genral idea of what needs to be done. Best topics on the matter are those ones: ADODB mySQLi Connection Switch large website from MySQL to MySQLi However, I do still need a bit more clarification on my particular case, where ADODB is being used. This is what I use to connect to the DB:

define('DBHOST', 'db_host'); define('DBUSER', 'db_user'); define('DBPASS', 'db_pass'); define('DBNAME', 'db_name'); include('adodb/adodb.inc.php'); $db = ADONewConnection('mysql'); $db->Connect(DBHOST,DBUSER,DBPASS,DBNAME) or die("Database not found!"); 

$db = ADONewConnection('mysql'); 

$db = ADONewConnection('mysqli'); 

That’s the easy part, I guess. Now since I am using ADODB, do I also need to change all instances of MySQL_* functions to MySQLi_* or ADODB takes care of this automatically? I think I know the answer but anyhow have to ask. My most common MySQL_ functions are:

mysql_insert_id() mysql_query() mysql_fetch_array() mysql_num_rows() mysql_escape_string() mysql_connect() mysql_select_db() mysql_error() 

Most common usage is like $variable = mysql_insert_id(); or $v1 = mysql_query($v); Is there anything else I should take into consideration when moving from MySQL to MySQLi for ADODB?

Источник