PHP for Beginners – Part 5

Author: Puneet Mehta Category: PHP, PHP for Beginners, Tutorials

Hey Guys!!! This is the last part of the tutorial series. In this tutorial you will learn to create a database, connect your database with PHP, and query the database(MySql) using SQL to insert,update,delete records. If you want to learn SQL you can learn form w3schools. After the end of this tutorial series you will surely not be a beginner in PHP language.

Download “php-for-beginners-sample-database-script.zip” php-for-beginners-sample-database-script.zip – Downloaded 1000 times – 7 KB

The first thing that you open phpMyAdmin, and create a database say “MyDatabase“. Then create a table named “testdatabase” and insert some data so you can play with them.Given below is the SQL for creating a table and inserting some records.

CREATE TABLE `players` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(255) NOT NULL,
`age` INT NOT NULL, `club` VARCHAR(255) NOT NULL
)
INSERT INTO `players` (`id`, `name`, `age`, `club`) VALUES
(NULL, 'David Beckham', '38', 'PSG'),
(NULL, 'John Terry', '35', 'Chelsea'),
(NULL, 'Lionel Messi', '26', 'FC Barcelona'),
(NULL, 'Cristiano Ronaldo', '28', 'Real Madrid FC'),
(NULL, 'Neymar da Silva', '21', 'FC Barcelona'),
(NULL, 'Steven Gerrard', '33', 'Liverpool'),
(NULL, 'Wayne Rooney', '27', 'Manchester United'),
(NULL, 'Robin van Persie', '29', 'Manchester United'),
(NULL, 'Rio Ferdinand', '34', 'Manchester United'),
(NULL, 'Ryan Giggs', '39', 'Manchester United');

CREATE TABLE `players` (

`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

`name` VARCHAR(255) NOT NULL,

`age` INT NOT NULL, `club` VARCHAR(255) NOT NULL

)

INSERT INTO `players` (`id`, `name`, `age`, `club`) VALUES

(NULL, 'David Beckham', '38', 'PSG'),

(NULL, 'John Terry', '35', 'Chelsea'),

(NULL, 'Lionel Messi', '26', 'FC Barcelona'),

(NULL, 'Cristiano Ronaldo', '28', 'Real Madrid FC'),

(NULL, 'Neymar da Silva', '21', 'FC Barcelona'),

(NULL, 'Steven Gerrard', '33', 'Liverpool'),

(NULL, 'Wayne Rooney', '27', 'Manchester United'),

(NULL, 'Robin van Persie', '29', 'Manchester United'),

(NULL, 'Rio Ferdinand', '34', 'Manchester United'),

(NULL, 'Ryan Giggs', '39', 'Manchester United');

How to include one file within another file

<?php
include("somefile.php);
include_once("somefile.php);
require("somefile.php);
require_once("somefile.php);
?>

<?php

include("somefile.php);

include_once("somefile.php);

require("somefile.php);

require_once("somefile.php);

There are 4 types of function that we can choose for including a file in another file:

include – It will always include the file, irrespective if file was included earlier, gives a warning if could not find the file and continues the script
include_once – – It will include the file only if file was not included earlier, gives a warning if could not find the file and continues the script
require – It will always include the file, irrespective if file was included earlier, stops the script if file not found.
require_once – It will include the file only if file was not included earlier, stops the script if file not found.

If you have some critical file that is very necessary for the script, like database connection file, use require function. If you have some template file for html designing purpose you can use include function. If you want to include a file which include common function use include_once or including a function file multiple time will gives “function redeclare” error.

Create a configure file to get started.

<?php
define("DB_HOST", "localhost");
define("DB_USER", "root");
define("DB_PASSWORD", "");
define("DB_DATABASE", "testdatabase");
require("functions.php");
//connect with the server
$conn = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD) or die(printErrorMessage("Sorry! could not connect <br><b>". mysql_error()."</b>"));
if ($conn) {
mysql_select_db(DB_DATABASE) or die(printErrorMessage("Sorry! could not connect with database <br><b>". mysql_error()."</b>"));
}
/***Load dummy Values**/
#loadSampleData();
/*******************************************/
?>

<?php

define("DB_HOST", "localhost");

define("DB_USER", "root");

define("DB_PASSWORD", "");

define("DB_DATABASE", "testdatabase");

require("functions.php");

//connect with the server

$conn = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD) or die(printErrorMessage("Sorry! could not connect <br><b>". mysql_error()."</b>"));

if ($conn) {

mysql_select_db(DB_DATABASE) or die(printErrorMessage("Sorry! could not connect with database <br><b>". mysql_error()."</b>"));

}

/***Load dummy Values**/

#loadSampleData();

/*******************************************/

Create an index.php file which will be like home page for all your project.

<table id="tableshow">
<tr>
<td colspan="4">
<div style="text-align:left;">
<form method="get" name="searchForm" action="<?php $_SERVER['PHP_SELF']; ?>">
<label ><strong>Search player by name:</strong> <input type="text" name="s" value="<?php $_GET["s"]; ?>" ></label> &nbsp;
<input type="submit"  />
</form>
</div>
</td>
</tr>
<tr>
<td colspan="4">&nbsp;</th>
</tr>
<tr>
<th>Name</th>
<th>Age</th>
<th>Club</th>
<th>Action</th>
</tr>
<?php
// get the search paramenter
$s = addslashes(trim($_GET["s"]));
$s = ($s <> "" ) ? $s : "";
if ($s <> "") {
$seachSQL = " AND name LIKE '%$s%' ";
}
$sql = "SELECT * FROM players WHERE 1 $seachSQL";
$query = mysql_query($sql);
// display all players
while($rs = mysql_fetch_array($query)) {
?>
<tr>
<td><?php echo stripslashes($rs["name"]) ?></td>
<td><?php echo stripslashes($rs["age"]) ?></td>
<td><?php echo stripslashes($rs["club"]) ?></td>
<td><a href="edit.php?id=<?php echo ($rs["id"]) ?>">Edit</a>&nbsp;&nbsp; <a href="delete.php?id=<?php echo ($rs["id"]) ?>" onClick="javascript:return confirm('Are you sure you want to delete?');" >Delete</a></td>
</tr>
<?php
}
?>
</table>

<tr>

<label ><strong>Search player by name:</strong> <input type="text" name="s" value="<?php $_GET["s"]; ?>" ></label>  

</form>

</div>

</td>

</tr>

<tr>

</tr>

<tr>

<th>Action</th>

</tr>

<?php

// get the search paramenter

$s = addslashes(trim($_GET["s"]));

$s = ($s <> "" ) ? $s : "";

if ($s <> "") {

$seachSQL = " AND name LIKE '%$s%' ";

}

$sql = "SELECT * FROM players WHERE 1 $seachSQL";

$query = mysql_query($sql);

// display all players

while($rs = mysql_fetch_array($query)) {

<tr>

<td><a href="edit.php?id=<?php echo ($rs["id"]) ?>">Edit</a>   <a href="delete.php?id=<?php echo ($rs["id"]) ?>" onClick="javascript:return confirm('Are you sure you want to delete?');" >Delete</a></td>

</tr>

<?php

}

</table>

Different methods to fetch data from the database.

<?php
$sql = "SELECT * FROM players WHERE 1 ";
// Returns rows as associative array
$arr1 = mysql_fetch_array(mysql_query($sql));
echo "<pre>";
print_r($arr1);
echo "</pre>";
// Return rows as objects
$arr1 = mysql_fetch_object(mysql_query($sql));
echo "<pre>";print_r($arr1); echo "</pre>";
// return row as an enumerated/number array
$arr1 = mysql_fetch_row(mysql_query($sql));
echo "<pre>";
print_r($arr1);
echo "</pre>";
?>

<?php

$sql = "SELECT * FROM players WHERE 1 ";

// Returns rows as associative array

$arr1 = mysql_fetch_array(mysql_query($sql));

echo "<pre>";

print_r($arr1);

echo "</pre>";

// Return rows as objects

$arr1 = mysql_fetch_object(mysql_query($sql));

echo "<pre>";print_r($arr1); echo "</pre>";

// return row as an enumerated/number array

$arr1 = mysql_fetch_row(mysql_query($sql));

echo "<pre>";

print_r($arr1);

echo "</pre>";

For adding a new player click on the Add new player link it will redirect to insert.php page where you can add as many player as you like.

<?php
// script to add new player
if (isset($_REQUEST["sub"])) {
// addslashes is used here for preventing sql injection
$p_name = trim(addslashes($_REQUEST["p_name"]));
$p_age = trim(addslashes($_REQUEST["p_age"]));
$p_club = trim(addslashes($_REQUEST["p_club"]));
if ($p_name <> "" && $p_age <> "" && $p_club <> "") {
$sql = "INSERT INTO `players` (`id`, `name`, `age`, `club`) VALUES (NULL, '$p_name', '$p_age', '$p_club');";
$query = mysql_query($sql);
if ($query) {
printSuccessMessage( "Record added successfully");
} else {
printErrorMessage( "could not insert into database. Please try again");
}    
} else {
printErrorMessage( "All fields are mandatory");
}
}
?>
<form method="post" action="" name="db">
<table style="margin:0 auto;width:280px;padding-top:30px;">
<tr>
<td>Player name: </td>
<td><input type="text" name="p_name" /> </td>
</tr>
<tr>
<td>Player Age: </td>
<td><input type="text" name="p_age" /> </td>
</tr>
<tr>
<td>Player Club: </td>
<td> <input type="text" name="p_club" /> </td>
</tr>
<tr>
<td></td>
<td> <input type="submit" name="sub" value="Save" /> </td>
</tr>      
</table>
</form>

<?php

// script to add new player

if (isset($_REQUEST["sub"])) {

// addslashes is used here for preventing sql injection

$p_name = trim(addslashes($_REQUEST["p_name"]));

$p_age = trim(addslashes($_REQUEST["p_age"]));

$p_club = trim(addslashes($_REQUEST["p_club"]));

if ($p_name <> "" && $p_age <> "" && $p_club <> "") {

$sql = "INSERT INTO `players` (`id`, `name`, `age`, `club`) VALUES (NULL, '$p_name', '$p_age', '$p_club');";

$query = mysql_query($sql);

if ($query) {

printSuccessMessage( "Record added successfully");

} else {

printErrorMessage( "could not insert into database. Please try again");

}

} else {

printErrorMessage( "All fields are mandatory");

}

<tr>

<td>Player name: </td>

</tr>

<tr>

<td>Player Age: </td>

</tr>

<tr>

<td>Player Club: </td>

</tr>

<tr>

</tr>

</table>

</form>

To edit a player click on the edit link on the listing. It will redirect you to the edit.php form where you can edit the player data.

<?php
// Update the player data
if (isset($_REQUEST["sub"])) {
// addslashes is used here for preventing sql injection
$pid = trim(addslashes($_REQUEST["pid"]));
$p_name = trim(addslashes($_REQUEST["p_name"]));
$p_age = trim(addslashes($_REQUEST["p_age"]));
$p_club = trim(addslashes($_REQUEST["p_club"]));
if ($p_name <> "" && $p_age <> "" && $p_club <> "" && $pid <> "") {
$sql = "UPDATE `players` SET `name` = '$p_name', `age` = '$p_age', `club` = '$p_club' WHERE `id` = $pid;";
$query = mysql_query($sql);
if ($query) {
printSuccessMessage ("Record updated successfully");
} else {
printErrorMessage( "Could not update Record. Please try again");
}    
} else {
printErrorMessage( "All fields are mandatory");
}
}
?>
<?php
$id = $_REQUEST["id"];
$sql = "SELECT * FROM players WHERE id = $id";
$query = mysql_query($sql);
$rs = mysql_fetch_array($query); // return an associate array of results
?>
<form method="post" action="" name="db">
<input type="hidden" value="<?php echo $_REQUEST["id"]; ?>" name="pid" />
<table style="margin:0 auto;width:280px;padding-top:30px;">
<tr>
<td>Player name: </td>
<td><input type="text" name="p_name" value="<?php echo stripslashes($rs["name"]) ?>"  /> </td>
</tr>
<tr>
<td>Player Age: </td>
<td><input type="text" name="p_age" value="<?php echo stripslashes($rs["age"]) ?>" /> </td>
</tr>
<tr>
<td>Player Club: </td>
<td> <input type="text" name="p_club"  value="<?php echo stripslashes($rs["club"]) ?>" /> </td>
</tr>
<tr>
<td></td>
<td> <input type="submit" name="sub" value="Update" /> </td>
</tr>      
</table>
</form>

<?php

// Update the player data

if (isset($_REQUEST["sub"])) {

// addslashes is used here for preventing sql injection

$pid = trim(addslashes($_REQUEST["pid"]));

$p_name = trim(addslashes($_REQUEST["p_name"]));

$p_age = trim(addslashes($_REQUEST["p_age"]));

$p_club = trim(addslashes($_REQUEST["p_club"]));

if ($p_name <> "" && $p_age <> "" && $p_club <> "" && $pid <> "") {

$sql = "UPDATE `players` SET `name` = '$p_name', `age` = '$p_age', `club` = '$p_club' WHERE `id` = $pid;";

$query = mysql_query($sql);

if ($query) {

printSuccessMessage ("Record updated successfully");

} else {

printErrorMessage( "Could not update Record. Please try again");

}

} else {

printErrorMessage( "All fields are mandatory");

}

<?php

$id = $_REQUEST["id"];

$sql = "SELECT * FROM players WHERE id = $id";

$query = mysql_query($sql);

$rs = mysql_fetch_array($query); // return an associate array of results

<tr>

<td>Player name: </td>

</tr>

<tr>

<td>Player Age: </td>

</tr>

<tr>

<td>Player Club: </td>

</tr>

<tr>

</tr>

</table>

</form>

Finally to delete a player click on the delete link on the listing page, it will redirect you to the delete.php which will delete the player record.

<?php
$id = $_REQUEST["id"];
$sql = "DELETE FROM players WHERE id = $id";
$query = mysql_query($sql);
if ($query) {
printSuccessMessage ("Record deleted successfully");
} else {
printErrorMessage( "Could not delete record. Please try again");
}    
?>

<?php

$id = $_REQUEST["id"];

$sql = "DELETE FROM players WHERE id = $id";

$query = mysql_query($sql);

if ($query) {

printSuccessMessage ("Record deleted successfully");

} else {

printErrorMessage( "Could not delete record. Please try again");

}

Download “php-for-beginners-sample-database-script.zip” php-for-beginners-sample-database-script.zip – Downloaded 1000 times – 7 KB

Hope this code helps you. 🙂

Was this article/content helpful?

Yes, thanks!

Not really

Thanks! Your feedback helps us to improve PHPHive.info

Tags:PHP, PHP Basic, PHP Basics, PHP for Beginners, PHP Functions, PHP Lessons, PHP Tutorials, PHPHive, Tutorials

How to include one file within another file

Share this:

Related Posts

Leave a Reply