How to convert a Mysql connection to Mysqli?

Question

I decided to listen to some users to make the conversion but I need your help because I have done a lot of research and nothing works.

The first file is:

<?php 
include_once("/pasta/connectserver.php");
$table = "rjpsync_tag, rjpsync_ipserver";
function assoc_query_2D($sql, $id_name = false){
  $result = mysql_query($sql);
  $arr = array();
  $row = array();
  if($result){
    if($id_name == false){
      while($row = mysql_fetch_assoc($result))
        $arr[] = $row;
    }else{
      while($row = mysql_fetch_assoc($result)){
        $id = $row['id'];
        $arr[$id] = $row;
      }
    }
  }else
      return 0;

  return $arr;
}
function query_whole_table($table, $value = 'ipserver1, ipsserver, ipserverfim'){
    $sql = "SELECT $value FROM $table WHERE idserver = 1";
  return assoc_query_2D($sql);
}
$export_str = "";
$result = query_whole_table($table);
foreach($result as $record){
  $export_str .= implode("",$record);
}
file_put_contents("/pasta/ipserver1.php", $export_str);
?>

And the second file is:

<?php
include_once("/pasta/connectserver.php");
function salvaip() {
$ip = file_get_contents('/pasta/myip.txt','r');
$ip = mysql_escape_string($ip);
$sql = "UPDATE rjpsync_ipserver SET ipsserver='".$ip."' WHERE idserver=1";
if (mysql_query($sql)) {
return true;
}
fclose($ip);
}
salvaip();
?>

These two files are linked to:

# /pasta/connectserver.php
<?PHP
include_once("/pasta/config.php");
$db_host1     = "$s_ipserver1:$db_porta1";
$db_link1     = mysql_connect($db_host1, $db_user1, $db_password1) or die (mysql_error ());
$db_connect1  = mysql_select_db($db_name1, $db_link1);
?>

And I wish it turns out to be on the following:

# /pasta/connectserveri.php
<?PHP
include_once("/pasta/config.php");
$db_host1     = "$s_ipserver1";
$db_connect1  = mysqli_connect($db_host1, $db_user1, $db_password1, $db_name1, $db_porta1) or die (mysqli_error ());
?>

Answer 1

To migrate a code that uses mysql_* functions to mysqli some changes are required such as adding new parameters or changing their order in function calls and also using available features such as Prepared statements and transaction control.

listar_legado.php

$link = mysql_connect('host', 'usuario', 'senha');
mysql_select_db('banco');
$sql = 'SELECT * FROM tabela';
$result = mysql_query($sql) or die(mysql_error());

while($item = mysql_fetch_assoc($result)){
    echo $item['campo1'] .'-'. $item['campo2'] .'<br>';
} 

new.php list

$link = mysqli_connect('host', 'usuario', 'senha', 'banco');
//mysqli_select_db($link, 'test'); //Alternativa para selecionar/trocar o banco...
$sql = 'SELECT * FROM tabela';
$result = mysqli_query($link, $sql) or die(mysqli_error($link));

while($item = mysqli_fetch_assoc($result)){
    echo $item['campo1'] .' - '. $item['campo2'] .'<br>';
}

Alternative of listing:

$result = mysqli_fetch_all($result, MYSQLI_ASSOC);

foreach($result as $item){
   echo $item['campo1'] .' - '. $item['campo2'] .'<br>';
}

mysqli_connect() allows you to pass/select the base name as the fourth parameter is also possible to do this with the function mysqli_select_db() its use is suitable when it is necessary to exchange the database (if it is on the same server) because it is common for a system to access several databases.

mysqli_fetch_* returns only one record while mysqli_fetch_all() returns an array containing all records at once, the second parameter indicates how the data will be accessed(MYSQLI_NUM numeric array, MYSQLI_ASSOC associative and MYSQLI_BOTH array associative and numeric. There is no option for an array of objects. ) later if it is omitted the pattern will be MYSQLI_NUM.

---

Insert, update, delete, dynamic queries and Prepared statements

Prepared statements avoid sql Injection in this way is not necessary to use mysql_real_escape_string for each string type value and end that sea of single/double quotes.

insert_legacy.php

//conexão omitida

$nome = 'nome\'s ); drop table ....';
$id = 1;
$nome = mysql_real_escape_string($nome);

$sql = "INSERT INTO tabela (id, nome) VALUES($id, '$nome')";
mysql_query($sql) or die(mysql_error());

insert_new.php

//conexão omitida

$sql = 'INSERT INTO tabela (id, nome) VALUES(?,?)';

$stmt =  mysqli_prepare($link, $sql);
$id = 1;
$nome = 'nome\'s ); drop table .... ';
mysqli_stmt_bind_param($stmt, 'is', $id, $nome);

if(!mysqli_stmt_execute($stmt)){
    echo mysqli_error($link);
}

mysqli_prepare() creates a prepared query based on the string passed($sql), after that the placeholders are replaced ? by their respective values in mysqli_stmt_bind_param($stmt, type, $vars) where $stmt is the prepared query, type are the types of each value i => whole, s => text/varchar fields etc... d => float/double and b => blob and $vars are the variables($id, $nome...) if no error happens the instruction is executed through mysqli_stmt_execute().

Related:

Select with Prepared statements Mysqli

Mysqli bind with an array of values

Answer 2

The problem you are facing is probably with function mysql_query.

She had a change in the method signature and we need to pass the link of DB as first parameter (documentation):

mysqli_query ( mysqli $link , string $query )

That is, for all occurrences of mysql_query($var), replace with mysql_query($db_connect1 , $var)

Using an IDE, it is quite simple to change all your files from the replace of a regular expression:

Pattern of the search

mysql_query\(($var)\)

Replace with

mysqli_query\(\$db_connect1, $1\)

Example in rule.

Answer 3

Sometimes a gold valley orientation solved a problem in hours I am to solve a few weeks ago, thanks again.

Here are the files changed to work with MYSQLI.

<?php 
include_once("/pasta/connectserver1i.php");
$table = "rjpsync_tag, rjpsync_ipserver";
function assoc_query_2D($sql, $id_name = false){
global $db_connect1;
  $result = mysqli_query($db_connect1, $sql);
  $arr = array();
  $row = array();
  if($result){
    if($id_name == false){
      while($row = mysqli_fetch_assoc($result))
        $arr[] = $row;
    }else{
      while($row = mysqli_fetch_assoc($result)){
        $id = $row['id'];
        $arr[$id] = $row;
      }
    }
  }else
      return 0;

  return $arr;
}
function query_whole_table($table, $value = 'ipserver1, ipsserver, ipserverfim'){
    $sql = "SELECT $value FROM $table WHERE idserver = 1";
  return assoc_query_2D($sql);
}
$export_str = "";
$result = query_whole_table($table);
foreach($result as $record){
  $export_str .= implode("",$record);
}
file_put_contents("/pasta/ipserver1.php", $export_str);
?>

And the second.

<?php
include_once("/pasta/connectserver1i.php");
function salvaip() {
global $db_connect1;
$ip = file_get_contents('/pasta/myip.txt','r');
$ip = mysqli_escape_string($db_connect1, $ip);
$sql = "UPDATE rjpsync_ipserver SET ipsserver='".$ip."' WHERE idserver=1";
if (mysqli_query($db_connect1, $sql)) {
return true;
}
fclose($ip);
}
salvaip();
?>