Home Accessibility Courses Twitter The Mouth Facebook Resources Site Map About Us Contact
 
Python and Tcl - public course schedule [here]
Private courses on your site - see [here]
Please ask about maintenance training for Perl, PHP, Lua, etc
 
Moving from mysql to mysqli - simple worked example

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)

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

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

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


Back to
People in New York
Previous and next
or
Horse's mouth home
Forward to
TransAtlantic crossing - the starter
Some other Articles
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
4759 posts, page by page
Link to page ... 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96 at 50 posts per page


This is a page archived from The Horse's Mouth at http://www.wellho.net/horse/ - the diary and writings of Graham Ellis. Every attempt was made to provide current information at the time the page was written, but things do move forward in our business - new software releases, price changes, new techniques. Please check back via our main site for current courses, prices, versions, etc - any mention of a price in "The Horse's Mouth" cannot be taken as an offer to supply at that price.

Link to Ezine home page (for reading).
Link to Blogging home page (to add comments).

You can Add a comment or ranking to this page

© WELL HOUSE CONSULTANTS LTD., 2019: 404 The Spa • Melksham, Wiltshire • United Kingdom • SN12 6QL
PH: 01225 708225 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho

PAGE: http://www.wellho.net/mouth/4483_Mov ... ample.html • PAGE BUILT: Sat May 27 16:49:10 2017 • BUILD SYSTEM: WomanWithCat