Using MySQL with PHP - Tutorial by Herong

•

What is DaniWeb IT Discussion Community?

You're currently browsing the MySQL section within the Web Development category of DaniWeb, a massive community of 426,333 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,426 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our MySQL advertiser: Programming Forums

Thread Tools Display Modes

Views: 1953 | Replies: 0

•

Join Date: Jul 2005

Posts: 12

Reputation: mark1048 is an unknown quantity at this point

Rep Power: 4

Solved Threads: 0

mark1048

Offline

Newbie Poster

Using MySQL with PHP - Tutorial by Herong

Jan 18th, 2006

Configuring PHP with MySQL

Intro

To use PHP with MySQL, of course you need to install MySQL server on you system first. On my local system, I have MySQL server installed in \mysql directory. If you need help on installing MySQL on your system, please read "Herong's Notes on SQL". To make sure MySQL is running on my local system, I did this in a command window:

Help with Code Tags

(Toggle Plain Text)

>\mysql\bin\mysqld 

>\mysql\bin\mysqladmin ping
mysqld is alive

Getting started

Now edit \php\php.ini with:

Help with Code Tags

(Toggle Plain Text)

extension=php_mysql.dll

Then add \php\ext to the PATH system environment variable.

I think we are ready to test the configuration. Run this script:

Help with Code Tags

(Toggle Plain Text)

<?php # MySqlTest.php
# Copyright (c) 2002 by Dr. Herong Yang
# 
   $con = mysql_connect('localhost');
   print "MySQL server info = ".mysql_get_server_info()."\n";
   print "MySQL status = ".mysql_stat()."\n";
   mysql_close($con);
?>

You should get something like:

Help with Code Tags

(Toggle Plain Text)

C:\herong\php_20050403\src>php MySqlTest.php
MySQL server info = 5.0.2-alpha
MySQL status = Uptime: 1167  Threads: 1  Questions: 5  Slow querie...
  Flush tables: 1  Open tables: 0  Queries per second avg: 0.004

Cool, I can now access my MySQL server from PHP scripts.

PHP's MySQL Support

PHP's MySQL support comes from an extension library, php_mysql.dll, which offeres a number of functions:

mysql_connect() - Connects to a MySQL server, and returns a connection resource.

mysql_close() - Closes a MySQL connection resource.

mysql_get_server_info() - Returns a string of server information.

mysql_status() - Returns a string of server status.

mysql_query() - Sends a query to the server, and returns a result set resource.

mysql_affected_rows() - Returns the number of effected rows of the given result set, if the executed query is an INSERT or UPDATE statement.

mysql_num_rows() - Returns the number of rows of the given result set, if the executed query is a SELECT statement.

mysql_fetch_array() - Fetches a row from a given result set, and returns the row as an array with both numeric index, and column name map. It will return boolean false, if there is no row left in the result set.

mysql_free_result() - Frees the given result set.

MySQL Test - MySqlLoop.php

To show you some those functions should be used, I wrote this simple script, MySqlLoop.php:

Help with Code Tags

(Toggle Plain Text)

<?php # MySqlLoop.php
# Copyright (c) 2002 by Dr. Herong Yang
# 
   $con = mysql_connect('localhost');
   $rs = mysql_query('DROP DATABASE MyBase');
   $rs = mysql_query('CREATE DATABASE MyBase');
   $rs = mysql_query('USE MyBase');
   print "Creating a table...\n";
   $rs = mysql_query('CREATE TABLE MyTable (ID INTEGER,'
      .' Value INTEGER)');
   $n = 100;
   $i = 0;
   print "Inserting some rows to the table...\n";
   while ($i < $n) {
      $rs = mysql_query('INSERT INTO MyTable VALUES ('.$i.', '
         .rand(0,$n-1).')');
      $i++;
   }
   print "Query some rows from the table...\n";
   $rs = mysql_query('SELECT * FROM MyTable WHERE ID < 10');
   print "   ".mysql_field_name($rs,0)."   "
      .mysql_field_name($rs,1)."\n";
   while ($row = mysql_fetch_array($rs)) {
      print "    ".$row[0].'   '.$row[1]."\n";
   }
   mysql_free_result($rs);   
   mysql_close($con);
?>

Note that if the connection resource is not specified in a query call, the last connection resource will be used. If you run this script, you will get something like:

Help with Code Tags

(Toggle Plain Text)

Creating a table...
Inserting some rows to the table...
Query some rows from the table...
   ID   Value
    0   14
    1   91
    2   84
    3   16
    4   88
    5   51
    6   12
    7   19
    8   39
    9   5

Conclusion

PHP supports MySQL through an extension library.
Configuring PHP to with a MySQL server is simple.
Database functions are server dependent.

Last edited by happygeek : Oct 28th, 2006 at 10:12 am. Reason: Formatting

Free tutorial books for beginners on PHP, JSP, ASP, SQL, XML, ...
--> http://www.herongyang.com/