Database Scripts for PHP & MySQL

siveambrai October 30, 2014 IDIA 618 FA2014 Comments Off on Database Scripts for PHP & MySQL

Sanitize User Input:

<?php
function sanitizeString($var)
{
 $var = stripslashes($var);
 $var = htmlentities($var);
 $var = strip_tags($var);
 return $var;
}

function sanitizeMySQL($var)
{
 $var = mysql_real_escape_string($var);
 $var = sanitizeString($var);
 return $var;
}
?>

Attempt simple queries using MySQLi

<?php require 'connect_mysqli.php';

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<?php
/*1. Make a table

$sql = "CREATE TABLE bb_trial(
     id INT NOT NULL AUTO_INCREMENT,
     firstname VARCHAR(25),
     lastname VARCHAR(25), 
     PRIMARY KEY (id)
)";
if($con->query($sql)){
    echo "Table Made Successfully";
} else {
    echo "Create table attempt failed";
}
 
*/

//2. Collect user input
echo "<form action='trial.php' method='post'><input type='text' name='firstname' /><input type='text' name='lastname' /><input type='submit' value='Submit' /></form>";

//3. Insert it into table
if ($_POST['firstname'] == ""){
    echo "please enter a name";
} else {
    $firstname = $_POST['firstname'];
    $lastname = $_POST['lastname'];

    $sql = "INSERT INTO bb_trial (firstname,lastname) VALUES ( '" . $firstname . "', '" . $lastname . "')";
 if($con->query($sql)){
    echo "User added successfully.";
 } else {
    echo "Insert attempt failed.";
 }
 }

//4. Display table

$sql = "SELECT * FROM bb_trial";
$result = $con->query($sql);

while ($row = $result-> fetch_assoc()){
    echo "<p>" . {$row['firstname']} . " " . {$row['lastname']} . "</p>";
}

$result->close();
$con->close();

?>
</body>
</html>

Attempt simple queries using PDO:

<?php require 'connect.php';

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<?php
/*1. Make a table

$sql = "CREATE TABLE bb_trial(
    id INT NOT NULL AUTO_INCREMENT,
    firstname VARCHAR(25),
    lastname VARCHAR(25), 
    PRIMARY KEY (id)
)";
*/

//2. Collect user input
echo "<form action='trial.php' method='post'><input type='text' name='firstname' /><input type='text' name='lastname' /><input type='submit' value='Submit' /></form>";

//3. Insert it into table
if ($_POST['firstname'] == ""){
    echo "please enter a name";
} else {
    $firstname = $_POST['firstname'];
    $lastname = $_POST['lastname'];

$sql = "INSERT INTO bb_trial (firstname,lastname) VALUES ( '" . $firstname . "', '" . $lastname . "')";
    $statement = $con->prepare($sql);
    $statement -> execute();

//4. Display table

$sql = "SELECT * FROM bb_trial";
$statement = $con->prepare($sql);
$statement -> execute();

while ($row = $statement-> fetch(PDO::FETCH_ASSOC)){
    echo "<p>" . $row['firstname'] . " " . $row['lastname'] . "</p>";
}

$statement = null;

?>
</body>
</html>