PHP Mysql

Basic Tutorial Examples to Learn PHP MySQL

MySQL is currently the most popular open source database server in existence. On top of that, it is very commonly used in conjunction with PHP scripts to create powerful and dynamic server-side applications.

We will look at how to use PHP and MySQL database to store information on the web and include it into your website.

Just make sure that you have installed PHP & MySQL database server with xampp and have created vertual host for accessing your website. So developers get ready to learn PHP & MySQL within couple of hours.

End of the tutorial you can do:
  • Connect a PHP script to a MySQL Database
  • Select Database
  • Fetch data from table and render
  • Insert, Update ad delete with PHP script

Make a Connection to the Database

PHP to connect to the MySQL database server at 'localhost', using the username stored in $database_username and the password in $database_password. After you have connected to the database server you must then select the database you wish to use. 

 
 <?php
 // Connects to MySql Database
 $server = 'localhost'; //or ip address of database server
 $database_name = 'mydb';
 $database_username = 'root';
 $database_password = '******';

 $conn = mysql_connect($server, $database_username, $database_password) or die('Could not connect Mysql Server : '. mysql_error());

 /* select any database on the MySQL server as the default (current) database for subsequent transactions */
 $mysql_select_db($database_name, $conn) or die('Could not connect Mysql Database : '. mysql_error());

?>

Execute the SQL Statement: mysql_query

For SELECT, SHOW, DESCRIBE, EXPLAIN and other statements returning a result set, mysql_query() returns a result set on success, or FALSE on error.
For other type of SQL statements like INSERT, UPDATE, DELETE, or DROP, mysql_query() returns TRUE on success or FALSE on error.

Once connected to the database let’s try to retrieve some information from the ‘user’ MySQL table.

 
 <?php
 
 // Collects data from "user" table
 $sql = "SELECT * FROM user";
 $resultSet = mysql_query($sql, $conn) or die("Could not execute the query." . mysql_error());

?>
Note: In this case the whole content of the user table is now contained in a special array with the name $resultSet.

Find the number of rows in the result set

Before you can go through the data in your $resultSet variable, you must know how many database rows there are.

 
 <?php
 
 $numrows= mysql_num_rows ($resultSet );

?>

The method mysql_num_rows() returns the row count for a SELECT statement, whereas mysql_affected_rows() returns the number of rows affected by a DELETE, INSERT, REPLACE, or UPDATE statement.

 
 <?php
 
 $numrows= mysql_affected_rows ($conn );

?>

Retrieve the Data From the Result Set

The mysql extension has multiple methods to retrieve the data from a result set.

Fetch single row from MySql

 
 <?php
 
 $sql = "SELECT * FROM user order by id desc limit 1";
 $resultSet = mysql_query($sql) or die(mysql_error());
 
 //Fetch a result row as an associative array, a numeric array, or both
 $row = mysql_fetch_array( $resultSet ); 

 OR

 //Get a result row as an enumerated array
 $row = mysql_fetch_row( $resultSet ); 

 OR

 //Fetch a result row as an associative array
 $row = mysql_fetch_assoc( $resultSet ); 

 OR

 //Fetch a result row as an object
 $row = mysql_fetch_object( $resultSet ); 


?>
Note: You can use any one method as per your need and get the data from $resultSet.

Fetch Multiple rows from MySql

 
 <?php
  $sql = "SELECT * FROM user order by id desc limit 20";
  $resultSet = mysql_query($sql) or die(mysql_error());

 //To print get all rows use followign code
 while($rows = mysql_fetch_array( $resultSet ))
 {
  Print "First Name: ".$rows['firstname'] . " ";
  Print "Last Name: ".$rows['lastname'] . " 
";
  Print "Phone Number: ".$rows['phone'] . " 
";
  Print "Address: ".$rows['address'] . " 
";
 }

?>

Clean Up: Release the Resources

Once the required data has been retrieved from the result set, it is a recommended practice to free up the connection to the database server so that further queries can be executed. 

 
 <?php
 
  mysql_free_result($resultSet);
 
?>
Note: It returns TRUE on success or FALSE on failure.

Close Mysql Connection

The method mysql_close() closes the non-persistent connection to the MySQL server that is associated with the specified database connection handle. If the connection handle is not specified, by default, the last connection opened by mysql_connect() is assumed.

The following statement in the sample PHP script closes the database connection.

 
 <?php
 mysql_close($conn);
?>

Comments

Post a Comment

Popular posts from this blog

Detecting user's screen size and resolution

If you're developing a site, you should carefully consider how your web pages appear on different kinds of screens. Today people access website on devices with different types of screens, you should account for some factors that affect the way your web pages appear on these devices. Javascript one simple way is to identify the screen width with the help of javascript and then render the webpage accordingly. Following is sample code to identify screen width and set it in cookie. <?php if(isset($_COOKIE["device_resolution"])) $screen_res = $_COOKIE["device_resolution"]; else //cookie is not found set it using Javascript { ?> <? } echo "Your screen resolution is set at ". $screen_res; ?> PHP With php, it is very easy to identify device using $_SERVER['HTTP_USER_AGENT']. Usining strpos() function find position of first occurrence of a string. <?php $iphone = strpos($_SERVER['HTTP_USER_AGENT'],"iPho...
Read more

autoload class

The __autoload() magic function is used to dynamically load classes. Whenever PHP encounters a non-existent class, it will first call the __autoload() function, and only then declare an error. This can be used to load classes on-the-fly. error_reporting (E_ALL); if (version_compare(phpversion(), '5.1.0', '<') == true) { die ('PHP5.1 Only'); } // Constants: define ('DIRSEP', DIRECTORY_SEPARATOR); // Get site path $site_path = realpath(dirname(__FILE__) . DIRSEP . '..' . DIRSEP) . DIRSEP; define ('site_path', $site_path); $registry = new Registry; // Set some data $registry->set ('name', 'Don'); // Get data, using get() echo $registry->get ('name'); // Get data, using array access echo $registry['name'] function __autoload($className) { // Assume that all class files are located in the same dir and subdirs $fname = str_replace('::', DIRECTORY_SEPARATOR, $className) . '.php...
Read more

MySql Slow Queries

MySQL has a slow query log which can be enabled if you want to identify the queries that are causing problems for a website or application. Checking slow queries is one of the most important steps in optimizing and tuning mysql. This article shows how to identify those 'slow queries' that need special attention and proper optimization. First let’s check on the server if slow query logging is enabled on MySql. On Windows cd c:\xampp\mysql\bin bin>mysqladmin var | find "log_slow_queries" On linux bin>mysqladmin var |grep log_slow_queries Enable the slow query log MySQL prior to 5.1.0 requires a change to the MySQL my.cnf file and a restart in order to log slow queries. From MySQL 5.1.0 you can apparantly change this dynamically without having to restart. Too make the change permanent, you could add the following lines under the [mysqld] section of your my.ini or my.cnf configuration file: >vi /etc/my.cnf [mysqld] # Activate the Slow Q...
Read more