Moving from mysql to mysqli - simple worked example

Main Content

Moving from mysql to mysqli - simple worked example

Archive - Originally posted on "The Horse's Mouth" - 2015-05-03 14:29:02 - Graham Ellis
We have a big codebase of PHP code that uses MySQL databases, much of it written using mysql_ functions which were not object orineted in nature, and have been paralleled by the more recent mysqli_ functions and methods for quite some time now. The older mysql library is now deprecated, with warnings it will be replaced in the future, and there's going to be a need to take steps to upgrade or retire code in the future.

Today, I've been looking at our MySQL image library, with some 10,000 pictures in it, and displaying tables from that library into a plain text window on my browser, using comma separated variables ... the reason being that a lot of my images need annotating, and the report is a good starting point for a text edit.

For instructional purposes, I've written this code twice for comparison

using mysql (source [here]);

  <php

  header("content-type: text/plain");

  mysql_connect("localhost","wellho","mypassword");

  mysql_select_db("wellho");

  $r = mysql_query("select iid, filename, descriptor from im_library");

  $out = fopen('php://output', 'w');

  while ($row = mysql_fetch_row($r)) {

    fputcsv($out,$row); }

  fclose($out);

  ?>

using mysqli (source [here]):

  <php

  header("content-type: text/plain");

  $connection = new mysqli("127.0.0.1","wellho","mypassword","wellho");

  $result = $connection->query("select iid, filename, descriptor from im_library");

  $out = fopen('php://output', 'w');

  while ($row = $result->fetch_row()) {

    fputcsv($out,$row); }

  fclose($out);

  ?>

That's a quick and easy first comparison to get you started if you need to; for new code, I'm going to suggest always using mysqli from now - but I'm not panicking yet about having to convert huge amounts of legacy code in the next short time space.

As a general good practise point, you're advised to limit specific database code to a few functions / methods for easy conversions later if you need to, and / or to look at database independent approaches too. See [here] for more wide ranging examples. And if you come on one of our PHP courses you'll be taken through various database issues and have a chance to discuss the best approach for yuor new applications - or to take your existing applications forward.

Note also in this example - use of special file handle php://output to route output via the file handlers to the browser (or other client) and the use of header to tell the browser that it's getting plain text rather than HTML.