Upgrading to MySQLi - As easy as swapping out mysql for mysqli?

Question 1

After a lot of responses on a number of different posts I've seen, I'm stopping using mysql and am trying to upgrade my site to use mysqli.

My question is, based on the functions of mysql that I list below (which are the ones I currently use) can I do a blanket replace on 'mysql_' for 'mysqli_' and not break functionality? I've already done some research on each of these functions and none of the mysqli versions look like they wouldn't work, but I just need to be sure before I swap them all out.

query

fetch_array

connect

select_db

error

real_escape_string

num_rows

fetch_assoc

free_result

Question 2

Check out this SO question which provides answers to at least most of your functions.

For example:

mysql_connect will be replaced by mysqli_connect
mysql_error will be replaced by mysqli_error and/or mysqli_connect_error, depending on the context
mysql_query will be replaced by mysqli_query

Question 3

<?php
$sql = new mysqli('127.0.0.1','root','Qwert12345','plot_io_db');
//echo $sql->query('Select * From players');
?>

It will work. Just remove port from localhost (127.0.0.1)

Question 4

mysqli_connect("","username" ,"password","databasename");//Server name cannot be NULL

use loaclhost for server name(In Loacl)

<?php
    $con = mysqli_connect("localhost","username" ,"password","databasename");

    if (mysqli_connect_errno())
    {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }
?>

Or can use MySQLi Procedural

<?php
    $servername = "localhost";
    $username = "username";
    $password = "password";

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

    // Check connection
    if (!$con) {
        die("Connection failed: " . mysqli_connect_error());
    }
    echo "Connected successfully";
?>

EDIT 01

$servername = "localhost";
$username = "root";
$password = "";

Question 5

The short answer is: No, it will hurt performance.

The longer answer: VARCHAR fields are variable length, meaning, that the formatting of the database blocks cannot pre-account for the size of the data filling in there. Sure, there's a maximum length of the field, but other than that, you're off worse.

On the other hand, reading a VARCHAR and then interpreting it to fit into a floating point type will cost you. And it introduces new problem possibilities. Try to get all decimals that account for "11.99". Hope you bring the right number of decimal places, because in VARCHAR "11.99" != "11.990".

And then there's indexing. Usually, nobody indexed a decimal field, but on the off chance that you do, an index on a VARCHAR field will require more space.

All in all, it's not a good idea to save something in an unspecific field type. In the DB structure, it's usually considered best practice to be as specific as possible.

Question 6

DEFINER always refers to the user who created the stored procedure. This can only be 1 user.

If Mary wants to see Bobs procedure, she could call:

SHOW CREATE PROCEDURE proc_name

To see the code of the procedure. She could also call the following to see the code:

SELECT ROUTINE_DEFINITION FROM information_schema.ROUTINES WHERE SPECIFIC_NAME='proc_name'

Heres how to enable Mary to access the view of the procedure via MySQL-Workbench:

By default, Mary is not able to send the create statement to the SQL-Editor. But this is just a privilege thing. Mary just needs a basic SELECT privilege in the mysql.proc table. To do this, run the following SQL-Statement (via Command line or directly in the Workbench):

GRANT SELECT ON mysql.proc TO 'mary'@'%'

This command enables Mary to access the Create-Statement from all hosts. If you want to limit it to a specific host you would do something like:

GRANT SELECT ON mysql.proc TO 'mary'@'192.168.2.1'

If Mary has the SELECT privilege she should be able to see the procedure after doing Send to SQL Editor -> CREATE statement

NOTE: In order to run the GRANT-Command you need to be logged in as user who has the privilege to grant privileges (e.g. root-user)

+++++++++++++++++++++++++EDIT+++++++++++++++++++++++++

There is a "quick and dirty" way to achieve this for a large number of users without writing for each user a new command to grant the privilege: (make sure to do this with a user who has the privilege to insert rows via the Workbench)

Open a new SQL-Tab in your Workbench
Type SELECT * FROM mysql.tables_priv; and run it
Above the result-grid there should be the a small button which allows you to import data from a csv-File.
Create a CSV-File which looks like this:

%,mysql,jane,proc,[email protected],"2016-02-19 22:51:47",Select, %,mysql,max,proc,[email protected],"2016-02-19 22:51:47",Select, %,mysql,steve,proc,[email protected],"2016-02-19 22:51:47",Select, %,mysql,greg,proc,[email protected],"2016-02-19 22:51:47",Select, %,mysql,jamie,proc,[email protected],"2016-02-19 22:51:47",Select,

...further users

jane, max, steve,... would be your users. Leave the other columns the way they are.

Import your csv-File
Run FLUSH PRIVILEGES in an SQL-Window (reloades privileges from priv-tables)
Finished! All your users can now access Stored Procedures

Upgrading to MySQLi - As easy as swapping out mysql for mysqli?

Answers

php,mysql,mysqli