Sunanda's Weblog

We were migrating from 5.0.45 to 5.0.67 version of MySQL in OpenSolaris.As part of this migration project had to test the working of PHP with MySQL database .

Here are the steps outlined:

1. Build the MYSQL 5.0.67 DB and the SVR4 packages namely SUNWmysql5r ,SUNWmysql5u and SUNWmysql5test .
   
2. Install the new MySQL packages on an OpenSolaris  installed system.
   
3. Make sure you have the PHP packagesSUNWphp524root,SUNWphp524usr,SUNWphp524core SUNWphp524-mysql and SUNWphp524-mysql-root already installed.These PHP packages form the base for the PHP Interpreter.
   
4. Create a default root user in MySQL DB and connect to the database

     
     #/usr/mysql/bin/mysqladmin -u root -password mysql
     #/usr/mysql/bin/mysql -uroot -pmysql 

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 28

Server version: 5.0.67 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use mysql;

Database changed

mysql> CREATE TABLE `cars` (

-> `id` int UNIQUE NOT NULL,

-> `name` varchar(40),

-> `year` varchar(50),

-> PRIMARY KEY(id)

-> );

Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO cars VALUES(1,'Mercedes','2000');

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO cars VALUES(2,'BMW','2004');

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO cars VALUES(3,'Audi','2001');

Query OK, 1 row affected (0.00 sec) 

mysql> select id ,name,year from cars;

 +----+----------+------+
| id | name     | year |
+----+----------+------+
|  1 | Mercedes | 2000 |
|  2 | BMW      | 2004 |
|  3 | Audi     | 2001 |
+----+----------+------+
3 rows in set (0.00 sec)


mysql>

5.Write a sample php script as shown below called "test.php"

<?php
$username = "root";
$password = "mysql";
$hostname = "localhost";

//connection to the database
$dbhandle = mysql_connect($hostname, $username, $password)
 or die("Unable to connect to MySQL");
echo "Connected to MySQL \n";

//select a database to work with
$selected = mysql_select_db("mysql",$dbhandle)
  or die("Could not select mysql:" .mysql_error());

//execute the SQL query and return records

$result = mysql_query("select id,name,year from cars");

if (!$result) {
    echo "Could not successfully run query ($sql) from DB: " . mysql_error();
    exit;
}

if (mysql_num_rows($result) == 0) {
    echo "No rows found, nothing to print so am exiting";
    exit;
}
$row=mysql_fetch_row($result);

// While a row of data exists, put that row in $row as an associative array
// Note: If you're expecting just one row, no need to use a loop
echo $row[0];
echo $row[1];
echo $row[2];
?>



6. Execute the script as

 /usr/php5/bin/php -f /tmp/test.php

7. The Displayed Output should look like this

[root@sa64-v20zc-blr03]/: /usr/php5/bin/php -f /tmp/test.php
Connected to MySQL
1Mercedes2000