PHP MySQL

Create user_profile Table

CREATE TABLE user_profile
( 
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(200) NOT NULL,
  age INT NOT NULL,
  PRIMARY KEY(id)
);

Insert sample records to user_profile table

INSERT INTO user_profile(name,age)
 VALUES('Bob',39),
       ('Jing',41),
       ('Paul',42),
       ('David',40),
       ('Jamy',20),
       ('Christ',28);

Connect to the MySQL Server

<?php

define('HOST', 'localhost');
define('USER', 'user2000');
define('PASS', 'password2000');
define('DBNAME', 'w3cyberlearning');

$connection = mysql_connect(HOST, USER, PASS);

if (!$connection) {
	die("can not connect to the server!");
}
else {
	echo 'connected and ready to use';
}
mysql_close($connection);
?>

Select database

	$result_db = mysql_select_db(DBNAME);

	if (!$result_db) {
		die("The " . DBNAME . "database could not be selected");
	} else {
		echo "Database " . DBNAME . " is ready for use.";
	}

Connect and Select Database

<?php

define('HOST', 'localhost');
define('USER', 'user2000');
define('PASS', 'password2000');
define('DBNAME', 'w3cyberlearning');

$connection = mysql_connect(HOST, USER, PASS);

if (!$connection) {
	die("can not connect to the server!");
} else {
	$result_db = mysql_select_db(DBNAME);

	if (!$result_db) {
		die("The " . DBNAME . "database could not be selected");
	} else {
		echo "Database " . DBNAME . " is ready for use.";
	}
}
mysql_close($connection);
?>        
   

Query Table

<?php
define('HOST', 'localhost');
define('USER', 'user2000');
define('PASS', 'password2000');
define('DBNAME', 'w3cyberlearning');

$connection = mysql_connect(HOST, USER, PASS);

if (!$connection) {
    die("can not connect to the server!"); 
} else {
    $result_db = mysql_select_db(DBNAME);

    if (!$result_db) {
	die("The " . DBNAME . "database could not be selected");
    } else {
	$user_profile = @mysql_query('SELECT * FROM user_profile');
        
        if (!$user_profile) {
		die('<p>Error retrieving user profile from database!<br />' .
		'Error: ' . mysql_error() . '</p>');
	}
        else {

	     while ($user = mysql_fetch_array($user_profile)) {
		$id = $user['id'];
		$name = htmlspecialchars($user['name']);
		$age = $user['age'];

		echo "ID:$id, Name:$name, and Age:$age <br>";
	     }
        }
}
mysql_close($connection);
?>		

Count return rows

$num_rows = mysql_num_rows( $user_profile );

Insert Record to table

<?php
define('HOST', 'localhost');
define('USER', 'user2000');
define('PASS', 'password2000');
define('DBNAME', 'w3cyberlearning');

$connection = mysql_connect(HOST, USER, PASS);

if (!$connection) {
     die("can not connect to the server!");
} else {
     $result_db = mysql_select_db(DBNAME);

    if (!$result_db) {
	die("The " . DBNAME . "database could not be selected");
    } else {
	$user_name = "Johnny";
	$user_age = 28;

        $sql = "INSERT INTO user_profile (name, age) VALUES('{$user_name}',{$user_age})";
	$result_db = @mysql_query($sql);
	if (!$result_db) {
		die('<p>error insert into table!<br />' .
			'Error: ' . mysql_error() . '</p>');
	} else {
		echo "Successfully inserted {$user_name} to the table";
	}
    }
}
mysql_close($connection);
?>

Update Record

<?php
define('HOST', 'localhost');
define('USER', 'user2000');
define('PASS', 'password2000');
define('DBNAME', 'w3cyberlearning');

$connection = mysql_connect(HOST, USER, PASS);

if (!$connection) {
     die("can not connect to the server!");
} else {
     $result_db = mysql_select_db(DBNAME);

    if (!$result_db) {
	die("The " . DBNAME . "database could not be selected");
    } else {
	$user_name = "Johnny";
	$user_age = 28;

        $new_age = 29;

        $sql = "UPDATE user_profile SET age={$new_age} WHERE name='{$user_name}' AND age={$user_age}";
	$result_db = @mysql_query($sql);
	if (!$result_db) {
		die('<p>error update!<br />' .
			'Error: ' . mysql_error() . '</p>');
	} else {
		echo "Successfully update {$user_name}";
	}
    }
}
mysql_close($connection);
?>

Get affected rows when update records

$affected_rows = mysql_affected_rows();

Delete records from the table

• The records must existed on the table before you can delete.

<?php
define('HOST', 'localhost');
define('USER', 'user2000');
define('PASS', 'password2000');
define('DBNAME', 'w3cyberlearning');

$connection = mysql_connect(HOST, USER, PASS);

if (!$connection) {
     die("can not connect to the server!");
} else {
     $result_db = mysql_select_db(DBNAME);

    if (!$result_db) {
	die("The " . DBNAME . "database could not be selected");
    } else {
	$user_name = "Johnny";
	$user_age = 29;


        $sql = "DELETE FROM user_profile WHERE name='{$user_name}' AND age={$user_age}";
	$result_db = @mysql_query($sql);
	if (!$result_db) {
		die('<p>error delete!<br />' .
			'Error: ' . mysql_error() . '</p>');
	} else {
		echo "Successfully delete {$user_name}";
	}
    }
}
mysql_close($connection);
?>

Count the delete rows

$total_rows_delete =mysql_affected_rows() ;