Skip Navigation Links
EZWEB
PHPExpand PHP

Using MySQL with PHP


Connection

The first thing you need is a connection

To open a connection to mySQL use the mysql_connect function

Syntax: 
$connection=mysql_connect(“host”,
”user”,”password”);
  • mysql_connect( )
    • host is usually “localhost”
      • connection to MySQL from local server
    • user
      • will have only privileges granted in mysql database
    • password
      • the password of the user
    • $connection
      • if a connection is made, a link identifier is returned, with which we can reference the open connection

Error Handling

  • PHP functions to handle MySQL errors
    • mysql_errno( )
      • returns the error number
    • mysql_error( )
      • returns the textual version of the error
  • to handle errors gracefully
    • @ sign to suppress error messages
    • die( ) function

Syntax:

$connection = @mysql_connect( DB_HOST, DB_USER, DB_PW ) or die( 'Could not connect to MySQL: Error number ' . mysql_errno( ) . ': ' . mysql_error( ) );


Close a connection

mysql_close($connection);


Select the Database

Use the mysql_select_db function

Code Example:

1

 
$connection=mysql_connect(“localhost”,

” webuser”) or die(“Cannot connect”);

2

$database = survey;

3

$db=mysql_select_db($database) or die(“Cannot select database);


Database Security

For better security you should use variables in the connection statement

Example:

1

<?

2

$HOST=”localhost”;

3

$USER=”web_user”;

4

$PASSWORD=”webpw”;

5

$connection=mysql_connect($HOST,$USER,$PASSWORD) or die(“Could not connect”);

For even better security put the variables in a separate file and use a require function to combine it. Make sure you save the file with a PHP extension

Code Example:

1

<?

2

define( ‘DB_USER’, ‘webuser’ );

3

define( ‘DB_PW’, ‘’);

4

define( ‘DB_HOST’, ‘localhost’ );

5

define( ‘DB_NAME’, ‘survey’` );

6

?>

Save as: db_params.php

PHP Example:

1

<?

2

require( ‘db_params.php’ ); 

3

$connection=mysql_connect(DB_HOST,DB_USER,DB_PW) or die(“Could not connect”);

4

$db=mysql_select_db($DB_NAME,$connection) or die(“Cannot select database);

5

?>


Sending queries

To interact with the database you create a query and assign it to a variable. Send it to mySQL using the mysql_query
Function

The mysql_query function takes one parameter – the query string

Code Example:

4

$query=”Select * from mysurvey”;

5

$result=mysql_query($query) or
die(“Cannot execute query”);

You can specify the connection if you have more than one open

Code Example:

4

$query=”Select * from mysurvey”;

5

$result=mysql_query($query,$connection) or die(“Cannot execute query”);
  • For INSERT, UPDATE, DELETE queries the $result will be either TRUE or FALSE
  • For SELECT queries the $result will contain a pointer to the data returned by the query
  • The $result will be FALSE if query was unsuccessful


Getting and using the data

Use the mysql_fetch_array( ) function to get the data from the temporary location. It retrieves one row of data. Use a loop to get more data.

Syntax:

$row=mysql_fetch_array(result,typeOfArray);

Type of Array:
returns each row as an indexed or associative array

Constants for type of array

CONSTANT

EXAMPLE

MYSQL_ASSOC

$row[ ‘column_name’ ]

MYSQL_NUM

$row[ 0 ]

MYSQL_BOTH

$row[ 0 ] or $row[ ‘column_name’ ]

MYSQL_ASSOC             -      key/value pair using column names
MYSQL_NUM          -      key/value pair using numbers
MYSQL_BOTH        -      both types of keys

Code Example:

 

while( $row = mysql_fetch_array( $result, MYSQL_ASSOC) )

 

// do something

 

}

Code Example:

 

$q = 'SELECT * FROM users ORDER BY myname';

 

$result = mysql_query( $q );

 

while( $row = mysql_fetch_array( $result, MYSQL_ASSOC ) ){

 

echo $row[‘myname'] . " " . $row['comments'] ."<br />";

 

}

Use the extract function to split the array into variables that have the same name as the key

Code Example:

 

while($row=mysql_fetch_array($result))

 

{

 

extract($row);

 

echo “$myname: $comments<br />”;

 

}

In Class Lab:

1

<?

2

define( ‘DB_USER’, ‘webuser’ );

3

define( ‘DB_PW’, ‘’ );

4

define( ‘DB_HOST’, ‘localhost’ );

5

define( ‘DB_NAME’, ‘survey’` );

6

?>

Save as: db_params.php

In Class Lab:

1

<?

2

require( ‘db_params.php’ ); 

3

$connection=mysql_connect(DB_HOST,DB_USER,DB_PW) or die("Could not connect");

4

$db=mysql_select_db($DB_NAME) or die(“Cannot select database);

5

$q = 'SELECT * FROM mysurvey ORDER BY myname';

6

$result = mysql_query( $q );

7

while($row=mysql_fetch_array($result))

8

{

9

extract($row);

10

echo "$myname: $comment<br />";

11

}

12

mysql_close($connection);

13

?>

Save as: ViewData.php


Passwords
Earlier versions of mysql encrypted the password to 16 bits, newer versions encrypt to 32. PHP, depending upon the version, may only encrypt to 16 bits. You can fix it by running the following command from the mysql monitor:

use mysql;
UPDATE user SET password = OLD_PASSWORD( 'your password' ) WHERE user = 'root';
FLUSH PRIVILEGES;





Free Tutorials and Training