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.
(written 2015-05-03)

 H112 - PHP - Further Web Page and Network Handling
  [220] When to use Frames - (2005-02-19)
  [314] What language is this written in? - (2005-05-17)
  [345] Spotting a denial of service attack - (2005-06-12)
  [356] Sudoku helper or sudoku cheat - (2005-06-23)
  [372] Time calculation in PHP - (2005-07-08)
  [376] What brings people to my web site? - (2005-07-13)
  [410] Reading a news or blog feed (RSS) in your PHP page - (2005-08-12)
  [425] Caching an XML feed - (2005-08-26)
  [443] Server side scripting of styles to suit the browser - (2005-09-12)
  [451] Accessing a page via POST from within a PHP script - (2005-09-26)
  [484] Setting the file name for a downloaded document - (2005-11-03)
  [537] Daily Image Santafied - (2005-12-22)
  [542] Morning image, afternoon image - (2005-12-26)
  [565] Using PHP to output images, XML, Style sheets, etc - (2006-01-15)
  [603] PHP - setting sort order with an associative array - (2006-02-13)
  [675] Adding PHP tags to an old cgi program - (2006-04-08)
  [767] Finding the language preference of a web site visitor - (2006-06-18)
  [789] Hot answers in PHP - (2006-07-02)
  [847] Image maps for navigation - a straightforward example - (2006-08-28)
  [904] Of course I'll tell you by email - (2006-10-25)
  [936] Global, Superglobal, Session variables - scope and persistance in PHP - (2006-11-21)
  [1009] Passing GET parameters through Apache mod_rewrite - (2006-12-27)
  [1114] PHP Image upload script - (2007-03-21)
  [1183] Improving searches - from OR to AND? - (2007-05-11)
  [1187] Updating a page strictly every minute (PHP, Perl) - (2007-05-14)
  [1210] PHP header() function - uses and new restrictions - (2007-05-30)
  [1355] .php or .html extension? Morally Static Pages - (2007-09-17)
  [1379] Simple page password protection - PHP - (2007-10-04)
  [1485] Copyright and theft of images, bandwidth and members. - (2007-12-26)
  [1495] Single login and single threaded models - Java and PHP - (2008-01-04)
  [1496] PHP / Web 2 logging - (2008-01-06)
  [1505] Script to present commonly used images - PHP - (2008-01-13)
  [1515] Keeping staff up to date on hotel room status - (2008-01-22)
  [1518] Downloading data for use in Excel (from PHP / MySQL) - (2008-01-25)
  [1549] http, https and ajp - comparison and choice - (2008-02-22)
  [2632] Shipping a test harness with your class in PHP - (2010-02-12)
  [2679] How to build a test harness into your PHP - (2010-03-16)
  [2729] Uploading a document or image to its own URL via a browser - (2010-04-18)
  [2918] Downloading a report from the web for further local analysis - (2010-08-13)
  [3036] Sending out an email containing HTML from within a PHP page - (2010-11-07)
  [3432] 3 digit HTTP status codes - what are they, which are most common, which should be a concern? - (2011-09-11)
  [3540] Easy session example in PHP - keeping each customers data apart - (2011-12-06)
  [3568] Telling which ServerAlias your visitor used - useful during merging domains - (2012-01-04)
  [3918] Multiple page web applications - maintaining state - PHP - (2012-11-10)
  [4070] Passing variable between PHP pages - hidden fields, cookies and sessions - (2013-04-26)

H109 - PHP - Input / Output
  [114] Relative or absolute milkman - (2004-11-10)
  [616] printf - a flawed but useful function - (2006-02-22)
  [653] Easy feed! - (2006-03-21)
  [709] Handling huge data files in PHP - (2006-05-04)
  [997] Most recent file in a directory - PHP - (2006-12-18)
  [1094] PHP fread - truncated data - (2007-02-27)
  [1096] Sample script - FTP to get a file from within PHP - (2007-03-01)
  [1113] File and URL reading in PHP - (2007-03-20)
  [1442] Reading a file multiple times - file pointers - (2007-11-23)
  [1780] Server overloading - turns out to be feof in PHP - (2008-09-01)
  [2964] An introduction to file handling in programs - buffering, standard in and out, and file handles - (2010-09-21)
  [3029] PHP data sources - other web servers, large data flows, and the client (browser) - (2010-11-04)
  [3159] Returning multiple values from a function call in various languages - a comparison - (2011-02-06)
  [3424] Divide 10000 by 17. Do you get 588.235294117647, 588.24 or 588? - Ruby and PHP - (2011-09-08)

H113 - Using MySQL Databases in PHP Pages
  [104] mysql_connect or mysql_pconnect in PHP? - (2004-10-30)
  [515] MySQL - an FAQ - (2005-12-03)
  [572] Giving the researcher power over database analysis - (2006-01-22)
  [581] Saving a MySQL query results to your local disc for Excel - (2006-01-29)
  [647] Checking for MySQL errors - (2006-03-15)
  [666] Database design - get it right from first principles - (2006-04-02)
  [723] Viewing images held in a MySQL database via PHP - (2006-05-17)
  [915] Paging through hundreds of entries - (2006-11-05)
  [937] Display an image from a MySQL database in a web page via PHP - (2006-11-22)
  [947] What is an SQL injection attack? - (2006-11-27)
  [1010] Dates, times, clickable diarys in PHP - (2006-12-28)
  [1561] Uploading to a MySQL database through PHP - examples and common questions - (2008-03-02)
  [1983] Keeping PHP code in database and running it - (2009-01-09)
  [2071] Setting up a MySQL database from PHP - (2009-03-08)
  [2259] Grouping rows for a summary report - MySQL and PHP - (2009-06-27)
  [2320] Helping new arrivals find out about source code examples - (2009-08-03)
  [2432] Using print_r in PHP to explore mysql database requests - (2009-10-01)
  [2447] MySQL stored procedures / their use on the web from PHP - (2009-10-10)
  [2561] The future of MySQL - (2010-01-03)
  [2628] An example of an injection attack using Javascript - (2010-02-08)
  [3035] How to display information from a database within a web page - (2010-11-07)
  [3455] MySQL, MySQLi, PDO or something else - how best to talk to databases from PHP - (2011-09-24)
  [4378] What FGW passengers want to talk about / and PHP programming to find out - (2015-01-01)

Back to People in New York

Previous and next or Horse's mouth home

Forward to TransAtlantic crossing - the starter

Starting MySQL. ERROR! The server quit without updating PID file - how we fixed it.
Newark to New York - the PATH suburban railway
Misusing statistics? - the seedy side of election campaigning
TransAtlantic crossing - the starter
Moving from mysql to mysqli - simple worked example
People in New York
Extracting data from backups to restore selected rows from MySQL tables
Crossing between continents
Pictures from around Rekjavik
Home in Melksham to Gatwick Airport - easy by public transport