MySQL Insert, Update, Delete data

Insert, Update, Delete all use the mysql_query() to act upon the connected database.

 

The difference is in the construction of the MySQL query that is being sent to the database.

 

Just as if the query was run from the command line, a new query (even a view / select) must be run for each operation:

 

<?php
	include('/var/www/dbConfig.php');

	$connection = mysql_connect( $server, $user, $passwd ) ;

	if( ! $connection ) { //check connected to the database server

		die('Error connecting to the database:' . mysql_error() ) ;
	}

	mysql_select_db('Academy') ; //select the database to be used

	function runView($query) { //function to run the query

		$resultSet = mysql_query( $query ) ;//run the query

		if( ! $resultSet){

			die('Error getting result set: ' . mysql_error() ) ;
		}

		while( $record = mysql_fetch_array( $resultSet ) ) {

			echo "CustID: {$record[CustID]}, " .
				 'First: ' . $record[First] . ', ' .
				 "Surname: {$record[Surname]}, " .
				 'Age: ' . $record[Age]. '<br>' ;
		}
	}

	echo "Here's the initial data:<br>" ;

	//construct a query to view the data
	$query = 'SELECT * FROM customers' ;
	runView( $query ) ;

	echo "<br>Insert data<br>" ;

	//construct the query to INSERT data INTO the customers table:	
	$query = "INSERT INTO customers (First, Surname, Age) VALUES ('Vanessa', 'Loopy', '44') " ;
	runView( $query ) ;

	//construct a query to view the data
	$query = 'SELECT * FROM customers' ;
	runView( $query ) ;

	echo "<br>Update data<br>" ;

	//construct the query to UPDATE data INTO the customers table:	
	$query = "UPDATE customers SET Surname = 'Strange' Where Surname = 'Loopy' " ;
	runView( $query ) ;

	//construct a query to view the data
	$query = 'SELECT * FROM customers' ;
	runView( $query ) ;

	echo "<br>Delete data<br>" ;

	//construct the query to INSERT data INTO the customers table:	
	$query = "DELETE FROM customers WHERE Surname = 'Strange' " ;
	runView( $query ) ;

	//construct a query to view the data
	$query = 'SELECT * FROM customers' ;
	runView( $query ) ;

	mysql_close( $connection ) ;
?>

 

Displays:

Here's the initial data:
CustID: 1, First: Pam, Surname: Frost, Age: 21
CustID: 2, First: James, Surname: Cross, Age: 20
CustID: 3, First: Andy, Surname: Jones, Age: 34 

Insert data
CustID: 1, First: Pam, Surname: Frost, Age: 21
CustID: 2, First: James, Surname: Cross, Age: 20
CustID: 3, First: Andy, Surname: Jones, Age: 34
CustID: 41, First: Vanessa, Surname: Loopy, Age: 44

 

Update data
CustID: 1, First: Pam, Surname: Frost, Age: 21
CustID: 2, First: James, Surname: Cross, Age: 20
CustID: 3, First: Andy, Surname: Jones, Age: 34
CustID: 41, First: Vanessa, Surname: Strange, Age: 44

 

Delete data
CustID: 1, First: Pam, Surname: Frost, Age: 21
CustID: 2, First: James, Surname: Cross, Age: 20
CustID: 3, First: Andy, Surname: Jones, Age: 34

Leave a Reply