Updating deprecated stuff

[erikla]

Dear php freaks

 

I am working on updating the php code of my old Guestbook. I haven't been working on php for years, so I am very rusty. From searhing on php.net, I realize that quite a few of the commands in my old php code are deprecated. Here is a list.

 

@mysql_pconnect
mysql_select_db
mysql_query
mysql_fetch_array
mysql_close()
mysql_error()
global $HTTP_USER_AGENT
eregi

 

Regarding for example the mysql_query php.net says the following:

http://www.php.net/manual/en/function.mysql-query.php

 

But thare are several options, I see. Can someone guide me how to make the proper changes. Should I just replace the above commands with some newer ones?

 

Regards,

 

Erik

[mac_gyver]

if you are just interested in updating the mysql_ functions, using the equivalent mysqli_ functions will result in the least amount of modifications to the code. you will however need to learn the correct mysqli_ syntax (the mysqli_select_db, mysqli_query, mysqli_close, and mysqli_error all require the database connection variable as a parameter.)

 

for global $HTTP_USER_AGENT, use $_SERVER['HTTP_USER_AGENT']

 

for eregi, you would use preg_match(). the biggest change is the addition of delimiters around the search pattern.

 

however, there are other changes that accompany the depreciation of these functions, such as the removal of magic_quotes, which attempted to escape external data for use in database queries. you would currently use the mysqli_real_escape_string() function (or use prepared queries.)

 

i recommend that you read the php migration guides in the php.net documentation appendix to see what exactly has been changed over time in the php versions that you might need to address in your code.

[Jacques1]

Hi,

 

merely appending an “i” to all your mysql_* function calls isn't a good idea.

 

While this may work as a quick hack to get rid of the deprecation warnings, it doesn't get you anywhere in the long run. The whole point of the new database extensions is to introduce new features like the already mentioned prepared statements. If you use none of them, well, you might as well stick to the old extension and turn off the warnings.

 

In addition to that, MySQLi is very cumbersome to work with when you're actually starting to make use of the new features. A single prepared statement requires something around 5 function calls. I think that's the point when many people regret their decision.

 

You should actually compare the to new extensions and choose wisely. Personally, I recommend PDO. It's much more comfortable and offers a unified interface for every mainstream database system, not just MySQL.

 

There also a detailed tutorial for how to switch from the old MySQL extension to PDO.

Edited May 4, 2014 by Jacques1

[erikla]

Thanks a lot mac_gyver. Good explanations! I will try to replace the old mysql functions with the new mysqli_ functions, remembering the extra database parameter. Also I will try to take into account your other recommendations. If I don't succeed, I will return with more questions ... great website!

 

Erik

[erikla]

Jacques and mac_gyver: I see there are two options really: mysql with an 'i' added (simplified) or PDO. From the list in the link below they seem quite similar in "Feature comparison":

 

http://www.php.net/manual/en/mysqlinfo.api.choosing.php

 

Is mysql with an 'i' added an easy "quick-fix" compared to PDO, or are they equal good? I might be willling to go for the best solution if it is not too difficult ...

 

Erik

[Jacques1]

As I already said, I strongly recommend PDO over MySQLi.

 

The problem of MySQLi is that it's very cumbersome and, of course, limited to the MySQL database system. PDO is a unified interface for different database systems, and it's much more comfortable in many aspects.

 

A prepared statement in PDO:

$books_stmt = $database->prepare('
    SELECT
        title
    FROM
        books
    WHERE
        author = :author_id
        AND EXTRACT(YEAR FROM publication_date) = :year
');
$books_stmt->execute(array(
   'author_id' => $author_id,
    'year' => $publication_year,
));

foreach ($books_stmt as $book)
{
    echo html_escape('The title of the book is ' . $book['title']);
}

Pretty straightforward, right? You only need two methods: prepare() and execute().

 

Now the same thing with MySQLi:

$books_stmt = $database->prepare('
    SELECT
        title
    FROM
        books
    WHERE
        author = ?
        AND EXTRACT(YEAR FROM publication_date) = ?
');
$books_stmt->bind_param('ii', $author_id, $publication_year);
$books_stmt->execute();

$books_stmt->bind_result($title);
while ($books_stmt->fetch())
{
    echo html_escape('The title of the book is ' . $title);
}

That's ... not exactly straightforward. Now you need five methods: prepare(), bind_param(), execute(), bind_result() and fetch().

 

There's no reason for using MySQLi (except maybe for very special cases). I think people only do it because the name sounds familiar.

[TrickyInt]

Jacques1:

The PDO one could also be done like this:

<?php
$books_stmt = $database->prepare('
    SELECT
        title
    FROM
        books
    WHERE
        author = ?
        AND EXTRACT(YEAR FROM publication_date) = ?
');
$books_stmt->execute(array($author_id, $publication_year));

foreach ($books_stmt as $book)
{
    echo html_escape('The title of the book is ' . $book['title']);
}
?>

I'd go with PDO over MySQLi. I was too using the old Mysql_*, and just started learning PDO 2 weeks ago. I swiched to PDO, afted trying MySQLi.

 

I found MySQLi to be harder to learn, and with less and worse tutorials.

 

 

And Jacques01, this: "I think people only do it because the name sounds familiar." is very true. Did that myself

[erikla]

Interesting with PDO, indeed! Sounds as it is easier and more fluent to use, but that it does not cover as much as the alternative in a few situations ... I will try it. Below is my code, before I make any changes. I have omitted some error-handling code and also the details on how to display the content of the Guestbook. Basicly there are three files:

 

save.php: Saving guestbook data entered into in a HTML Form (the latter file not shown). 

mysql_functions.php:  Additional functions to handle the interaction with the database.

gbook.php:  The page to display the entire guestbook entrances, including the new one.

 

 

save.php

<?php

require("mysql_functions.php");

function write_in_guestbook($name, $epost, $contribution)  {
  global $REMOTE_ADDR;
  $ip = $_SERVER['REMOTE_ADDR'];
  $sql = "insert into guestbook(name,epost,contribution,ip,time)
         values('$name','$epost','$contribution','$ip',now())";
  
  open_connection_and_choose_db():
  sql_ask($sql);
  close_connection();
} 

write_in_guestbook(addslashes($name),addslashes($epost),addslashes($contribution));

header("Location: gbook.php");

?>

mysql_functions.php

<?php 

function open_connection_and_choose_db() {
  $server = "*********";
  $user = "*********";
  $password = "*********";
  $database = "*********";

  if (!@mysql_pconnect("$server","$user","password")) {
    udskriv_fejl("Kunne ikke oprette en forbindelse til MySQL.");
  }

  if(!mysql_select_db("$database")) {
    print_error("Was not able to choose the database: $database");
  }
}


function close_connection() {
  if(!mysql_close()) {
    print_error("Was not able to close the connection to MySQL!");
  }
}


function sql_ask_and_receive_answer($query) {
  $result = mysql_query($query);

  if(!$result) {
    print_error("Was not able to execute: <em>$query</em>");
  }

  while($row = mysql_fetch_array($result)) {
    $result_array[] = $row;
  }

  return $result_array;
}


function sql_ask($query) { 
  $result = mysql_query($query);

  if(!$result) {
    print_error("Was not able to execute: <em>$query</em>");
  }
}


function print_error($fejl) {
  echo "<p>$fejl";

  if ($mysql_fejl = mysql_error()) {
    echo "<br>The error is: <em>$mysql_fejl</em>";
  }

  exit;
}

?>

gbook.php

gbook.php

<?php 

require("mysql_functions.php");

if(!$start_nr || $start_nr < 0)  {
  $start_nr = 0;
}

$sql = "select id, name, epost, date_format(time, '%e/%c %Y, %H:%i') as time,
  contribution from guestbook order by time desc limit $start_nr, $number_at_a_time";
  $counter_sql = "select count(*) as number from guestbook";

open_connection_and_choose_db();
  $result = sql_ask_and_receive_answer($sql);
  $counting = sql_ask_and_receive_answer($counter_sql);
  close_connection();

extract($counting[0]);

+ MORE TO WRITE THE CONTENTS OF guestbook IN AN APPROPRIATE WAY ...

?>

I would de delighted if someone could push me in the right direction on how to make the changes in the PDO way ...

 

Regards,

 

Erik

[Jacques1]

Well, did you read the PDO tutorial from above? It's all explained there. Start rewriting the code, and when you have specific problems, ask here.

 

Some of the code is pretty weird, by the way. Why on earth do you use a persistent connection when you close it right away? Why do you use persistent connections at all? You do realize that the “p” stands for “persistent”, right?

 

And of course you'll have to get rid of all the old baggage like register_globals. Those bugs/features no longer exist in PHP.

[erikla]

Jacques1, I will follow your advice and read the tutorial in order to understand the logic behind PDO, and get back if I get stuck in specific tasks. I really can't answer your question, why I use a "persistent connection". Have never heard about it, to be honest! Maybe 12-15 years back I took the code from a webcafé and modified it for my own purposes. I was able to do so without having read books about MySQL. Therefore I am not aware of all the tiny (but important) details. I think I need to look at it a bit closer without, hopefully, to need to read entire books about it.

 

Erik

[gizmola]

Jacques1, I will follow your advice and read the tutorial in order to understand the logic behind PDO, and get back if I get stuck in specific tasks. I really can't answer your question, why I use a "persistent connection". Have never heard about it, to be honest! Maybe 12-15 years back I took the code from a webcafé and modified it for my own purposes. I was able to do so without having read books about MySQL. Therefore I am not aware of all the tiny (but important) details. I think I need to look at it a bit closer without, hopefully, to need to read entire books about it

Erik,

Don't worry about pconnect. It was an optimization technique that in general caused more problems than it prevented. It's part of a large area of knowledge having to do with the different ways that php can work with a web server, and the way different relational databases work.

 

Essentially pconnect was designed to create a pool of persistent mysql connections. When a php script runs, all the memory is instantiated at script start, and discarded at script end -- including all mysql connections. For this reason, there's no need to actually close mysql connections in scripts, because they are going to be closed anyways when the script finishes. However, in an apache/mod_php environment, php is actually part of the apache web server. Depending on how apache is configured, there is memory allocated for php scripts that gets reused a number of times. Since this memory is not discarded, scripts tend to run faster because the entire php environment does not have to be started for each request --- it's just reusing memory that was already allocated for a prior request.

 

In some complicated environments, the mysql server that php needs to talk to is running on a different server, and the network overhead and latency involved becomes a bottleneck. Also with some databases (oracle for example) starting up a connection to the database from a client takes a long time.

 

In the case of mysql, rarely are people in an environment where mysql is not running on the same machine over localhost. Secondly the mysql connection process is very lightweight and fast unlike oracle.

 

mysql_pconnect is a bit of a hack for reusing mysql connections in the apache/mod_php environment, for edge case setups, in environments where they expect sysops who understand all this stuff and can modify settings and performance tune settings to make sure that the problems that can be caused by persistent connection pooling don't creep into your environment.

 

There are any number of issues that can occur over time, when using _pconnect, and the problem is that these issues only emerge under load. I won't go into them, other than to say that using _pconnect in a standard one server LAMP environment is completely unwarranted at best.

 

Fortunately, you are already well into your exercise of converting to PDO/MySQL and there's no need to concern yourself with persistent connections at all now, however, if you're just the curious type, you can read some more about what problems they were hoping to address in the php manual: http://www.php.net/manual/en/features.persistent-connections.php