marioluan/connection.php

## connection.php
<?php
  // openning connection
	$link = mysqli_connect('host', 'user', 'password', 'database')
		or die('Could not connect to server'.'<br>');

	// uncomment next line to debug
	//print 'connection opened'.'<br>';
?>

## delete-dogs.php
<?php
  // data validation and mysql delete query
	include 'delete.php';
	// mysql query response
	$error_message = '<p class="error">Ops... this dog is not on database!</p>';
	$success_message = '<p class="success">Dog successfuly deleted from database!</p>';

?>
<!DOCTYPE html>
<html lang="en-US">
<head>
	<title>PHP and MySQL web app - /php-codes/delete | Mario Luan</title>
	<meta charset="utf-8" />
	<link rel="stylesheet" href="http://www.marioluan.com.br/style.css" />
	<?php include 'includes/ga.php'; ?>
</head>
<body>
	<div class="container">
		<?php include 'includes/header.php'; ?>
		<main>
			<h2><a href="/php/">/php-codes</a>/delete</h2>
			<br>
			<h3>Delete your dog from database</h3>
			<form class="floatLeft" method="POST">
				<fieldset>
					<legend>Delete data</legend>
					<label for="name">Name</label>
					<?php
						// input validation error response
						if(isset($name_error)){
							print '<p><span class="error">'.$name_error.'</span></p>';
						}
					?>
						<input type="text" name="name" id="name" placeholder="little bear" maxlength="45" pattern="^[a-zA-Z]+((\s{1}[a-zA-Z]+)+|$)" required autofocus>
					<input type="submit" value="Delete" class="buttom">
						<?php
							// mysql query status
							if(isset($deleted_rows) && $deleted_rows == TRUE){
								print $success_message;
							} elseif(isset($deleted_rows) && $deleted_rows == FALSE){
								print $error_message;
							}
						?>
				</fieldset>
			</form>
		</main><!-- main ends -->
		<?php include 'includes/footer.php'; ?>
	</div><!-- container ends -->
</body>
</html>

## delete.php
<?php
  // check if variable are set and not NULL
	if(isset($_POST['name'])){
		// getting data from form
		$name = $_POST['name'];

		// convert strings to lowecase letters
		$name = strtolower($name);

		// NAME validation
		// lenght
		if(strlen($name) < 3 || strlen($name) > 45){
			$name_error = "*Please, enter at least 3 and no more than 45 characters";
		// regex to accept only letters
		} elseif(preg_match('/^[a-zA-Z]+((\s{1}[a-zA-Z]+)+|$)/', $name) == FALSE){
			$name_error = "*Only alphabetic characters allowed";

		// after the validation above, we're now ready to have fun with MySQL
		} else{

			// opening connection with MySQL
			include 'connection.php';

			// deleting data from database
			$delete_result = mysqli_query($link, "DELETE FROM DOGS WHERE name = '$name'");

			// let's store the number of affected rows
			$deleted_rows = mysqli_affected_rows($link);

			// checking if values were successfully deleted from database
			if($deleted_rows > 0){
				$delete_error = FALSE;
			} else {
				$delete_error = TRUE;
				//uncomment next line to debug
				//print  'delete_error: query returned = '.$deleted_rows.'<br>';
			}

			// closing connection
			mysqli_close($link);

			//uncomment next line to debug
			//print "connection closed";
		}
	}
?>

## footer.php
<?php
print '
  	<footer>
		<p>This is website\'s footer</p>
	</footer><!-- footer ends -->
'; ?>

## header.php
<?php
print '
  	<header>
			<div class="top">
				<figure>
					<img src="http://www.marioluan.com.br/mario-luan.jpg" width="130" height="180" alt="Mário Luan" title="Do you know Mario?" />
				</figure>
				<div class="topRight">
					<h1>Mário Luan</h1>
					<div class="topRightBottom">
						<span class="pyComment"># Intro</span><br>
						<span class="pyKeyword">def</span>&nbsp;<span>who_is(name):</span>
						<ul>
							<li>name = name.lower()</li>
							<li><span class="pyKeyword">if</span> name == <span class="pyString">"mario"</span>:
								<ul>
									<li>course = <span class="pyString">"Computer Science"</span></li>
									<li>age = 22</li>
									<li>having_fun_with = (<span class="pyString">"JAVA"</span>, <span class="pyString">"Python"</span>, <span class="pyString">"PHP"</span>)</li>
									<li><span class="pyKeyword">return</span> course, age, having_fun_with[:3]</li>
								</ul>
							</li>
						</ul>
					</div><!-- topRightBottom ends -->
				</div><!-- topRight ends -->
			</div><!-- top ends -->
			<nav>
				<ul>
					<li><a href="http://www.marioluan.com.br/" title="go back to index">/home</a></li>
					<li><a href="https://gist.github.com/marioluan" title="pieces of code I\'ve writen using python and java">/codes</a></li>
					<li><a class="selected" href="http://www.marioluan.com.br/php/" title="simple apps I\'ve created using php and mysql">/php</a></li>
					<li><a target="_blank" href="http://www.marioluan.com.br/html/" title="websites I\'ve created using html5 and css">/html</a></li>
					<li><a href="http://www.marioluan.com.br/cv" title="my resume">/cv</a></li>
				</ul>
			</nav>
		</header><!-- header ends -->
'; ?>

## index.php
<!DOCTYPE html>
<html lang="en-US">
<head>
  <title>PHP and MySQL web app - /php-codes | Mario Luan</title>
	<meta charset="utf-8" />
	<link rel="stylesheet" href="style.css" />
	<?php include 'includes/ga.php'; ?>
</head>
<body>
	<div class="container">
		<?php include 'includes/header.php'; ?>
		<main>
			<h2>/php-codes</h2>
			<br>
			<h3>Manipulating data with PHP and MySQL</h3>
			<p>I've made an application with PHP and MySQL to insert, delete and select data from database, using the new MySQLi extension. I've also made both client-side and server-side validation using regular expression.</p>
			<br>
			<p>Click on the following links to play it:</p>
			<ul>
				<li><a href="/php/insert-dogs.php">/insert</a></li>
				<li><a href="/php/delete-dogs.php">/delete</a></li>
				<li><a href="/php/select-dogs.php">/select</a></li>
				<li><a class="selected" href="https://gist.github.com/4404841">view source-code</a></li>
			</ul>
		</main><!-- main ends -->
		<?php include 'includes/footer.php'; ?>
	</div><!-- container ends -->
</body>
</html>

## insert-dogs.php
<?php
  // form validation and mysql insert query
	include 'insert.php';
	// defining mysql query response
	$error_message = '<p class="error">Ops... this dog is already on database!</p>';
	$success_message = '<p class="success">Dog added to database!</p>';
?>
<!DOCTYPE html>
<html lang="en-US">
<head>
	<title>PHP and MySQL web app - /php-codes/insert | Mario Luan</title>
	<meta charset="utf-8" />
	<link rel="stylesheet" href="style.css" />
	<?php include 'includes/ga.php'; ?>
</head>
<body>
	<div class="container">
		<?php include 'includes/header.php'; ?>
		<main>
			<h2><a href="/php/">/php-codes</a>/insert</h2>
			<br>
			<h3>Add your dog to database</h3>
			<form class="floatLeft" method="POST">
				<fieldset>
					<legend>Insert data</legend>
					<label for="name">Name</label>
					<?php
						// input validation error response
						if(isset($name_error)){
							print '<p><span class="error">'.$name_error.'</span></p>';
						}
					?>
						<input type="text" name="name" id="name" placeholder="little bear" maxlength="45" pattern="^[a-zA-Z]+((\s{1}[a-zA-Z]+)+|$)" required autofocus>
						<br><br>
					<label for="age">Age</label>
					<?php
						// input validation error response
						if(isset($age_error)){
							print '<p><span class="error">'.$age_error.'</span></p>';
						}
					?>
						<input type="text" name="age" id="age" placeholder="in years, from 1-10" maxlength="2" pattern="^([1-9]|[1][0])$" required>
						<br><br>
					<label for="size">Size</label>
						<?php
							// input validation error response
							if(isset($size_error)){
								print '<p class="error">'.$size_error.'</p>';
							}
						?>
						<label class="reset"><input type="radio" name="size" id="size" value="s" checked>small</label>
						<label class="reset"><input type="radio" name="size" id="size" value="m">medium</label>
						<label class="reset"><input type="radio" name="size" id="size" value="l">large</label>
					<br><br>
					<input type="submit" value="Insert Data" class="buttom">
					<input type="reset" value="Clear" class="buttom">
						<?php
							// mysql query response
							if(isset($insert_error) && $insert_error == TRUE){
								print $error_message;
							} elseif(isset($insert_error) && $insert_error == FALSE){
								print $success_message;
							}
						?>
				</fieldset>
			</form>
		</main><!-- main ends -->
		<?php include 'includes/footer.php'; ?>
	</div><!-- container ends -->
</body>
</html>

## insert.php
<?php
  // check if variables are set and not NULL
	if(isset($_POST['name'], $_POST['age'], $_POST['size'])){

		// getting data from form
		$name = $_POST['name'];
		$age = 	$_POST['age'];
		$size = $_POST['size'];

		// converting strings to lowercase letters
		$name = strtolower($name);
		$size = strtolower($size);

		// NAME validation
		// lenght
		if(strlen($name) < 3 || strlen($name) > 45){
			$name_error = "*Please, enter 3-45 characters!";
		// regex to accept only letters
		} elseif(preg_match('/^[a-zA-Z]+((\s{1}[a-zA-Z]+)+|$)/', $name) == FALSE){
			$name_error = "*Only alphabetic characters allowed!";

		// AGE validation
		// length and max value
		} elseif( (strlen($age) > 2) || ($age < 1 || $age > 10) ){
			$age_error = "*Age (in years) of your dog in range of 1-10";
		// regex to accept only numbers
		} elseif(preg_match('/^([1-9]|[1][0])$/', $age) == FALSE){
			$age_error = "*Only numbers allowed";

		// SIZE validation
		// accept only s, m or l consoants
		} elseif($size != "s" && $size != "m" && $size != "l"){
			$size_error = "*Please, choose 's' for small, 'm' for medium or 'l' for large!";

		// after data validation, we're now ready to play with MySQL
		} else{

			// opening connection with MySQL
			include 'connection.php';

			// inserting data into database
			$insert_result = mysqli_query($link, "INSERT INTO DOGS(name, age, size) VALUES('$name', '$age', '$size')");

			// checking if the values were successfully inserted on database
			if($insert_result){
				$insert_error = FALSE;
			} else {
				$insert_error = TRUE;
				// uncomment next line to debug
				//print  'insert_error: '.mysqli_error($link).'<br>';
			}

			// closing connection
			mysqli_close($link);

			// uncomment next line to debug
			//print "connection closed";
		}
	}
?>

## select-dogs.php
<?php
  // opening connection with database
	include 'connection.php';

	// selecting data from database
	$select_result = mysqli_query($link, "SELECT * FROM DOGS ORDER BY name");
?>
<!DOCTYPE html>
<html lang="en-US">
<head>
	<title>PHP and MySQL web app - /php-codes/select | Mario Luan</title>
	<meta charset="utf-8" />
	<link rel="stylesheet" href="style.css" />
	<?php include 'includes/ga.php'; ?>
</head>
<body>
	<div class="container">
		<?php include 'includes/header.php'; ?>
		<main>
			<h2><a href="/php/">/php-codes</a>/select</h2>
			<br>
			<h3>List of entries on database</h3>
			<table>
				<caption>Dogs' table</caption>
				<colgroup span="3"></colgroup>
				<thead>
					<tr>
						<th>Name</th>
						<th>Age</th>
						<th>Size</th>
					</tr>
				</thead>
				<?php  // get data from each row of the table until the last row is reached ?>
				<?php while($result = mysqli_fetch_array($select_result, MYSQLI_NUM)){ ?>
				<tr>
					<td class="alignLeft"><?php print $result[0]; // name ?></td>
					<td class="alignCenter"><?php print $result[1]; // age ?></td>
					<td class="alignCenter"><?php print $result[2]; // size ?></td>
				</tr>
				<?php } ?>
			</table>
		</main><!-- main ends -->
		<?php include 'includes/footer.php'; ?>
	</div><!-- container ends -->
</body>
</html>
<?php
	// free all memory associated with $select_result
	mysqli_free_result($select_result);

	// closing connection
	mysqli_close($link);
	// uncomment next line to debug
	//print "connection closed";
?>
	<?php
	// openning connection
	$link = mysqli_connect('host', 'user', 'password', 'database')
	or die('Could not connect to server'.'<br>');

	// uncomment next line to debug
	//print 'connection opened'.'<br>';
	?>
	<!DOCTYPE html>
	<html lang="en-US">
	<head>
	<title>PHP and MySQL web app - /php-codes \| Mario Luan</title>
	<meta charset="utf-8" />
	<link rel="stylesheet" href="style.css" />
	<?php include 'includes/ga.php'; ?>
	</head>
	<body>
	<div class="container">
	<?php include 'includes/header.php'; ?>
	<main>
	<h2>/php-codes</h2>
	<br>
	<h3>Manipulating data with PHP and MySQL</h3>
	<p>I've made an application with PHP and MySQL to insert, delete and select data from database, using the new MySQLi extension. I've also made both client-side and server-side validation using regular expression.</p>
	<br>
	<p>Click on the following links to play it:</p>
	<ul>
	<li><a href="/php/insert-dogs.php">/insert</a></li>
	<li><a href="/php/delete-dogs.php">/delete</a></li>
	<li><a href="/php/select-dogs.php">/select</a></li>
	<li><a class="selected" href="https://gist.github.com/4404841">view source-code</a></li>
	</ul>
	</main><!-- main ends -->
	<?php include 'includes/footer.php'; ?>
	</div><!-- container ends -->
	</body>
	</html>
	<?php
	// opening connection with database
	include 'connection.php';

	// selecting data from database
	$select_result = mysqli_query($link, "SELECT * FROM DOGS ORDER BY name");
	?>
	<!DOCTYPE html>
	<html lang="en-US">
	<head>
	<title>PHP and MySQL web app - /php-codes/select \| Mario Luan</title>
	<meta charset="utf-8" />
	<link rel="stylesheet" href="style.css" />
	<?php include 'includes/ga.php'; ?>
	</head>
	<body>
	<div class="container">
	<?php include 'includes/header.php'; ?>
	<main>
	<h2><a href="/php/">/php-codes</a>/select</h2>
	<br>
	<h3>List of entries on database</h3>
	<table>
	<caption>Dogs' table</caption>
	<colgroup span="3"></colgroup>
	<thead>
	<tr>
	<th>Name</th>
	<th>Age</th>
	<th>Size</th>
	</tr>
	</thead>
	<?php // get data from each row of the table until the last row is reached ?>
	<?php while($result = mysqli_fetch_array($select_result, MYSQLI_NUM)){ ?>
	<tr>
	<td class="alignLeft"><?php print $result[0]; // name ?></td>
	<td class="alignCenter"><?php print $result[1]; // age ?></td>
	<td class="alignCenter"><?php print $result[2]; // size ?></td>
	</tr>
	<?php } ?>
	</table>
	</main><!-- main ends -->
	<?php include 'includes/footer.php'; ?>
	</div><!-- container ends -->
	</body>
	</html>
	<?php
	// free all memory associated with $select_result
	mysqli_free_result($select_result);

	// closing connection
	mysqli_close($link);
	// uncomment next line to debug
	//print "connection closed";
	?>