Basic CRUD Functions for PHP + MYSQL + XML + HTML

First posted 29/06/2012

Download or see the project on Github (PHP + HTML only)

Database: test

CREATE TABLE `members` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `address` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
)

Xml file: members.xml

<members>
        <username>amnbhrmism</username>
        <address>1Malaysia 1Najib</address>
</members>

Db connect

<?php
        session_start();
 
        $host="localhost"; // Host name
        $username=""; // Mysql username
        $password=""; // Mysql password
        $db_name="test"; // Database name
 
        mysql_connect("$host", "$username", "$password") or die("cannot connect");
        mysql_select_db("$db_name") or die("cannot select DB");
?>

Create

HTML
<form method="post">
    <table>
		<tr>
			<td>Username: </td>
			<td><input name="username" type="text" size="32" /></td>
		</tr>
		<tr>
			<td>Address: </td>
			<td><input name="address" type="text" size="32" /></td>
		</tr>
	</table>
	<input type="submit" name="login" value="Submit"/>
</form>
PHP
<?php
if(isset($_POST['login'])){ // Retrieved from method form POST in HTML
	//Prevent SQL injections
	$username = mysql_real_escape_string($_POST['username']);
	$address = mysql_real_escape_string($_POST['address']);
 
	//Check to see if username exists
	$sql = mysql_query("SELECT username FROM members WHERE username = '".$username."'");
	if (mysql_num_rows($sql)>0){
		die ("Username taken");
	}
	 
	// Insert new record to database
	mysql_query("INSERT INTO members (username, address) VALUES ( '$userName', '$address')")
				or die (mysql_error());
	
	header('Location: //redirect URL');
	die;
?>
XML
<?php
if(isset($_POST['login'])){ // Retrieved from method form POST in HTML
	//Prevent SQL injections
	$username = mysql_real_escape_string($_POST['username']);
	$address = mysql_real_escape_string($_POST['address']);
 
	$xml = new SimpleXMLElement('<Students></Students>');
	$username = $xml->addChild('username', $username); // Assignment is not necessary*
	$xml->addChild('address', $address);
	
	// *To add Attribute to username
	$username->addAttribute('age', '15');
	
	// *To add Child for username
	$username->addChild('phone', '0135446654');
		
	$xml->asXML('members.xml'); // Saves new .xml file if not exist
	header('Location: //redirect URL');
	die;
}
?>

Remove

HTML
<form method="post">
    <input type="submit" name="ok" value="OK" />
    <input type="submit" name="cancel" value="Cancel" />
</form>
PHP
<?php
session_start();

if (isset($_POST['ok'])) {
    mysql_query("DELETE FROM members WHERE username='amnbhrmism'");
	echo 'Deleted';
}

if (isset($_POST['cancel'])) {
    header('location: // Redirect to some page');
}
?>
XML
<?php
session_start();

if (isset($_POST['ok'])) {
    $file = 'members.xml';
	unlink($file); // Deletes $file
	echo 'File deleted';
}

if (isset($_POST['cancel'])) {
    header('location: // Redirect to some page');
}

?>

Update

HTML
<form method="post">
    <input type="text" name="address" />
    <input type="submit" name="address" value="Save" />
</form>
PHP
<?php
session_start();

if (isset($_POST['update'])){
	$address = $_POST['address'];
	mysql_query("UPDATE members SET address = '".$address."' WHERE username = 'aiman'");
	header('location: // Redirect to some page');
}
?>
XML
<?php
session_start();

if (isset($_POST['update'])){
	$address = $_POST['address'];

	$edit = simplexml_load_file('members.xml'); //This line will load the XML file.
	$sxe = new SimpleXMLElement($edit->asXML());
	
	$sxe->address = $address;
	
	// output new XML string 
	$sxe->asXML('members.xml');
	header('location: // Redirect to some page');
}
?>

Display

PHP
<?php		
	// Use INNER JOIN to join foreign key tables
	$result = mysql_query("SELECT * FROM members");
	echo "<table border='1'>
	<tr>
		<th>Username</th>
		<th>Address</th>
	</tr>";

	while($row = mysql_fetch_array($result)){
		echo "<tr>";
		echo "<td>".$row['username']."</td>";
		echo "<td>" . $row['address'] . "</td>";
		echo "</tr>";
	}
	echo "</table>";
			
?>
XML
<?php
	$files = glob('*.xml'); // Retrieve all file put into an array $files
		
	foreach($files as $file){
	$xml = new SimpleXMLElement($file, 0, true);
		
	echo '
	<table>
	<tr>
		<th>Username</th>
		<th>Address</th>
	</tr>
	<tr>
		<td>Username: '.$xml->username.'</td>
		<!-- Display attribute: $xml->username['attribute'] -->
	</tr>
	<tr>
		<td>Address: '.$xml->address.'</td>				
	</tr>
	</table>';
}
?>
Show Comments

Get the latest posts delivered right to your inbox.