আগামী ৩০ অক্টোবর -২০১৭ তারিখ থেকে শুরু হচ্ছে পাঁচ মাস ব্যাপী Professional Web Design and Development with HTML,CSS,Bootstrap,PHP,MySQl, AJAX and JQUERY কোর্সের ৮৬ তম ব্যাচ। আগ্রহীদেরকে অতিসত্বর মাসুদ আলম স্যার এর সাথে যোগাযোগ করতে অনুরোধ করা যাচ্ছে। স্যার এর মোবাইল: 01722817591, Email : [email protected] কোর্সের সিলেবাস এর জন্য এখানে ক্লিক করুন ।

PHP Basic Part-4 (php with MySQL)

[printfriendly]

What is Mysql?

  • Open source database system
  • like most modern Database Management Systems is based on the relational model, RDBMS.
  • Free
  • Run on Linux, Windows, Netware, AIX, and so on.
  • easily accessible through programming languages like PHP

Why Mysql?

  • Open source database
  • consistent fast performance & high reliability
  • ease of use
  • High Availability
  • Comprehensive Application Development
  • Management Ease
  • Lowest Total Cost of Ownership

Where You can get it?

  • You can download at mysql official website, mysql.com
  • You can get mysql that packaged with php like in AppServ
  • For portable application, you can try XAMPP , WAMP

Connecting to Database Use mysql_connect() function

Syntax:

mysql_connect(host name, user name, password);

mysql_connect() will get the correct value if it is succeed but false if it is fail.
 

Example

<?php
//the example of MySQL database connection
//connect.php
$continued = mysql_connect("localhost","root","");
if ($continued) {
echo ("Connection is succeed");
} else {
echo ("Connection is fail");
}
?>

Output:

Making Database Use mysql_create_db() function

Mysql_create_db() function is used to create a database. Usually, database is used in a application and it contains of some tables. 

Using mysql_create_db() such as:

mysql_create_db("database");

Example, create a file named create.php within your localhost server. Enter following code:
<?php
//the example of making MySQL database
//create.php
$continued = mysql_connect("localhost","root","");
if($continued){
    echo("Connection is succeed");
}else{
    echo("Connection is fail");
}
$make = mysql_query("CREATE DATABASE data_root ",$continued);

if($make){
    echo("<br><br>Database data_root succeeds in making");
}else{
    echo("<br><br>Database data_root fails in making");
}
?>

Output:

Making Table

PHP do not provide the special function of making tables with its fields. So in order to make the data table, it stills use the sintaks from database program (used MySQL) then this sintaks will be operated by using of mysql_query() function.

For example, make a employees database of one office, personal data table with the field of employees number, employees name, and employees address.

Create a file named create_table.php within www. Enter following code:

<?php
//the example of making MySQL database table
//create_table.php
$continued = mysql_connect("localhost","root","");
if($continued){
echo("Connection is succeed");
}else{
echo("Connection is fail");
}
$make = mysql_query("CREATE DATABASE employees",$continued);
if($make){
echo("<br><br>Employees database succeeds in making");
}else{
echo("<br><br>Employees database fails in making");
}
$order1 = "CREATE TABLE data_employees
(
employees_number int(10)AUTO_INCREMENT PRIMARY KEY,
name char(100),
address varchar(255)
)";
mysql_select_db("employees");

$make_table = mysql_query($order1);
if($make_table){
echo("<br><br>Table data_employees succeeds in making");
}else{
echo("<br><br>Table data_employees fails in making");
}
?>

Output:

The Data Type of Field

There are some type of MySQL field data, such as:

Numeric data type

It differentiates into two kinds of group, there are integer and floating point data type. For the most information of numeric data type, you just take a look at the table beneath:

Data type Gyration assess
TINYINT (-128) – 127 or 0 – 255
SMALLINT (-32768) – 32767 or 0 – 65535
MEDIUMINT (-8388608) – 8388607 or 0 – 16777215
INT (-2147683648) – 2147683647 or 0 – 4294967295
BIGINT (-9223372036854775808) – 9223372036854775807 or 0 – 18446744073709551615
FLOAT(X) (-3.402823466E+38) – (-1.175494351E-38), 0, and 1.175494351E-38 – 3.402823466E+38.
FLOAT The same as above
DOUBLE (-1.7976931348623157E+308) – (-2.2250738585072014E-308), 0, and 2.2250738585072014E-308 – 1.7976931348623157E+308

Those data type needs memory requirement as follows:

Data type Memory requirement needs
TINYINT 1 byte
SMALLINT 2 bytes
MEDIUMINT 3 bytes
INT 4 bytes
INTEGER 4 bytes
BIGINT 8 bytes
FLOAT(X) 4 ifX <=24 or 8 if 25 <=X <=53
FLOAT 4 bytes
DOUBLE 8 bytes
DOUBLE PRECISION 8 bytes
REAL 8 bytes
DECIMAL(M,D) M+2 bytes if D>0, M+1 bytes if D=0(D+2, if M<d)< td=””> </d)<>
NUMERIC(M,D) M+2 bytes if D>0, M+1 bytes if D=0(D+2, if M<d)< td=””> </d)<>

String data type

The types of data that is included into String data such as:

Data type Gyration assess
CHAR 1-255 character
VARCHAR 1-255 character
TINYLOB, TINYTEXT 1-255 character
BLOB, TEXT 1-65535 character
MEDIUMLOB, MEDIUMTEXT 1-16777215 character
LONGLOB, LONGTEXT 1-4294967295 character
ENUM(‘element1′,’element2’,…) Maximal 65535 character
SET(‘element1′,’element2’,…) Maximal 64 element

Char and Varchar Data Type

Char() and Varchar() data type is the same in the principal but the amount of the memori requirement needs is different. The memory requirement needs for the type of char() data has the static character and the level of this need depends to the amount of character that is specified at the time of field declaration. Then, the level of the memory requirement needs of varchar() data type depends on how much the character weared plus 1 byte that is contain of the data of the amount of character weared.

For further information, see the example below:

Value CHAR(4) Depository memory VARCHAR(4) Depository memory
4 bytes 1 byte
‘ab’ ‘ab ‘ 4 bytes ‘ab’ 3 bytes
‘abcd’ ‘abcd’ 4 bytes ‘abcd’ 5 bytes
‘abcdefgh’ ‘abcd’ 4 bytes ‘abcd’ 5 bytes

Data Type of Date

There are few type of data field for date and time(hour) data as follow:

Data type Gyration assess
DATETIME ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’
DATE ‘1000-01-01’ to ‘9999-12-31’
TIMESTAMP ‘1970-01-01 00:00:00’ – 2037
TIME ‘-838:59:59’ to ‘838:59:59’
YEAR 1901 – 2155

Those data date type needs memory requirement as follows:

Data type Memory requirement needs
DATE 3 bytes
DATETIME 8 bytes
TIMESTAMP 4 bytes
TIME 3 bytes
YEAR 1 bytes

MySQL Syntax Orders

PHP do not provide special functions for data operation, so you should follow MySQL syntax orders. In order to do the data operation by using PHP, so here is the sequence as follow:

  1. Connection to database
  2. Declare a variable string that contains of the next MySQL syntax order
  3. Run the MySQL syntax by using of mysql_query() function. If the syntax will produce the next output, declaration one variable to accommodate the result
  4. Take the result of the run MySQL syntax by using the functions of mysql_fetch_array(), mysql_fetch_row(), mysql_num_rows, etc. It depends to the output wanted format

Inserting Data

Insert data into database, use the syntax:
INSERT INTO nam_table (field1,field2,…) VALUES (‘data1′,’data2’,…)

If the field parameter does not declare, the amount of the inserting data must have the same as the amount of the table field.

Example:

<?php
//the example of inserting data
//data_insert.php
mysql_connect("localhost","root","");//database connection
mysql_select_db("employees");
$order = "INSERT INTO data_employees
(name, address)
VALUES
('H.R. Dian',
'Jl. Raya Baru KOTABARU')";
//inserting data order

//declare in the order variable
$result = mysql_query($order);
if($result){
echo("<br>Input data is succeed");
}else{
echo("<br>Input data is fail");
}
?>

Output:


If you see through phpmyadmin

Creating Form Insert Data

php mysql basic step by step tutorial – part 9: In order to make this input data is ‘user friendly’, you can make a HTML form for input data.

example:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>

<head>
<title>Form Input Data</title>
</head>

<body>
<table border="1">
<tr>
<td align="center">Form Input Employees Data</td>
</tr>
<tr>
<td>
<table>
<form method="post" action="input.php">
<tr>
<td>Name</td>
<td><input type="text" name="name" size="20">
</td>
</tr>
<tr>
<td>Address</td>
<td><input type="text" name="address" size="40">
</td>
</tr>
<tr>
<td></td>
<td align="right"><input type="submit"
name="submit" value="Sent"></td>
</tr>
</table>
</td>
</tr>
</table>
</body>
</html>

The result:

This HTML form will send two variable, $name and $address variable, into input.php file as describe in the ACTION parameter of FORM HTML

<?php
//the example of inserting data with variable from HTML form
//input.php
mysql_connect("localhost","root","admin");//database connection
mysql_select_db("employees");

//inserting data order
$order = "INSERT INTO data_employees
(name, address)
VALUES
('$name',
'$address')";

//declare in the order variable
$result = mysql_query($order);    //order executes
if($result){
echo("<br>Input data is succeed");
} else{
echo("<br>Input data is fail");
}
?>

After you have already made input.php, fill the input data and then click the sent button such as:


For the result:

If you see through phpmyadmin

As for the exercises, put some of data’s into employees database.

Display Data

In looking for one or more data in the database, you can use syntax such as:
SELECT field1,field2,… FROM name_table WHERE condition1,condition2,…ORDER BY name_field

If field displayed is all field from the table, so all of the name field itself does not have to be declared but it is enough to change with the sign * then all field will be accessed.

ORDER BY parameter shows the data that is organized based on which field you choose. The default sequence is from the smallest one (number sequence), from A-Z (letter sequence), and from the first data to the last data (time sequence).

You can reverse these sequence by adding DESC attribute. For example, we will search all of the database data of data_employees and show it based on the name field.

<html>
<head>
<title>Search data</title>
</head>
<body>
<table>
<tr>
<td align="center">EMPLOYEES DATA</td>
</tr>
<tr>
<td>
<table border="1">
<tr>
<td>NAME</td>
<td>EMPLOYEES<br>NUMBER</td>
<td>ADDRESS</td>
</tr>
<?
//the example of searching data
with the sequence based on the field name
//search.php
mysql_connect("localhost","root","");//database connection
mysql_select_db("employees");

$order = "SELECT * FROM data_employees ORDER BY name";
//order to search data
//declare in the order variable

$result = mysql_query($order);
//order executes the result is saved
//in the variable of $result

while($data = mysql_fetch_row($result)){
echo("<tr><td>$data[1]</td><td>$data[0]</td><td>$data[2]</td></tr>");
}
?>
</table>
</td>
</tr>
</table>
</body>
</html>

mysql_fetch_row() Function

Mysql_fetch_row() function takes the data from $result variable in per line. The first take is the top-ranking data line. The data that it takes is in the array shape where the element from array is the field of data table.

For example, in the program of data_employees, the syntax of $row = mysql_fetch_row($result) will produce:

<?php
//the example of searching data
//with the sequence based on the field name
//search.php
mysql_connect("localhost","root","");//database connection
mysql_select_db("employees");

$order = "SELECT * FROM data_employees ORDER BY name";
//order to search data
//declare in the order variable

$result = mysql_query($order);

//order executes the result is saved
//in the variable of $result

while($data = mysql_fetch_row($result)){
print_r($data);
}
?>

Output:
Array ( [0] => 1 [1] => Masud Alam [2] => Dhaka ) Array ( [0] => 2 [1] => Sohel Alam [2] => Bangladesh ) And so on until the while order get the false value then the restarting will be stopped.

mysql_fetch_array() Function

Beside using mysql_fetch_row() function in order to get the query result into database, you can also use mysql_fetch_array() function. It has the same function as mysql_fetch_row() function where the data reads line per line.

The difference of both function is that the result of mysql_fetch_array() function is in the array assosiatif shape. For example, if you use mysql_fetch_array() in the program of data_employees such as $row = mysql_fetch_array($result) will produce:

<?php
//the example of searching data
//with the sequence based on the field name
//search.php
mysql_connect("localhost","root","");//database connection
mysql_select_db("employees");

$order = "SELECT * FROM data_employees ORDER BY name";
//order to search data
//declare in the order variable

$result = mysql_query($order);

//order executes the result is saved
//in the variable of $result

while($data = mysql_fetch_array($result)){
print_r($data);
}
?>

Output:
Array ( [0] => 1 [employees_number] => 1 [1] => Masud Alam [name] => Masud Alam [2] => Dhaka [address] => Dhaka ) Array ( [0] => 2 [employees_number] => 2 [1] => Sohel Alam [name] => Sohel Alam [2] => Bangladesh [address] => Bangladesh )

Editing data

In editing the data, you can use the syntax as follow:

UPDATE name_table SET field1=new_value, field2=new_value, ...
WHERE condition1,condition2, ...

For example, we will try to edit one of the data from data_employees table:

file: edit.php for connection database

<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<table>
<tr>
<td align="center">EDIT DATA</td>
</tr>
<tr>
<td>
<table border="1">
<?php
mysql_connect("localhost","root","");//database connection
mysql_select_db("employees");

$order = "SELECT * FROM data_employees";
$result = mysql_query($order);
while ($row=mysql_fetch_array($result)){
echo ("<tr><td>$row[name]</td>");
echo ("<td>$row[employees_number]</td>");
echo ("<td>$row[address]</td>");
echo ("<td><a href=\"edit_form.php?id=$row[employees_number]\">Edit</a></td></tr>");
}

?>
</table>
</td>
</tr>
</table>
</body>
</html>

The picture above is edit.php file where this file will show overall data in the table, then there is edit menu in the last column.

If you click the edit menu, it will bring the program to execute edit_form.php file. Edit_form.php file will show a form to edit the data which have been selected in the previous form. The mechanism is that the user chooses one of the data that will be edited in the first form (edit.php file) by clicking the edit menu in the right column. You can see edit_form.php program file as follow:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Form Edit Data</title>
</head>

<body>
<table border=1>
<tr>
<td align=center>Form Edit Employees Data</td>
</tr>
<tr>
<td>
<table>
<?php
mysql_connect("localhost","root","");//database connection
mysql_select_db("employees");
$id=$_REQUEST['id'];

$order = "SELECT * FROM data_employees where employees_number='$id'";
$result = mysql_query($order);
$row = mysql_fetch_array($result);
?>
<form method="post" action="edit_data.php">
<input type="hidden" name="id" value="<?php echo "$row[employees_number]"?>">
<tr>
<td>Name</td>
<td>
<input type="text" name="name"
size="20" value="<?php echo "$row[name]"?>">
</td>
</tr>
<tr>
<td>Address</td>
<td>
<input type="text" name="address" size="40"
value="<?php echo "$row[address]"?>">
</td>
</tr>
<tr>
<td align="right">
<input type="submit"
name="submit value" value="Edit">
</td>
</tr>
</form>
</table>
</td>
</tr>
</table>
</body>
</html>

Result:

By clicking the edit button, the program goes to the fourth program, edit_data.php file, which brings three variable such as $id variable which contains of employees number data, $name variable which contains of employees name data, and $address variable which contains of employees address. In order to know whether the data is already change or not, the program is re-instructed to edit.php file with the order of header (“location:edit.php”). Here is the edit_data.php program file:

<?php
mysql_connect("localhost","root","");//database connection
mysql_select_db("employees");

$name=$_REQUEST['name'];
$address=$_REQUEST['address'];
$id=$_REQUEST['id'];
//echo $name;
$order = "UPDATE data_employees SET name='$name', address='$address' WHERE employees_number='$id'";
mysql_query($order);
header("location:edit.php");
?>

Deleting data

For deleting the data, use the syntax as follow:

DELETE FROM name_table WHERE condition1,condition2,…
As for example, we will add menu to delete the data in table edit data such as:

echo ("<td><a href=\"delete.php?id=$row[employees_number]\">Delete</a></td></tr>")

Delete menu will bring the program into delete.php file where it is used to delete the data which have been selected in the above form.

Delete.php file as follow:

<?php
mysql_connect("localhost","root","");//database connection
mysql_select_db("employees");
$id=$_REQUEST['id'];
$order = "DELETE FROM data_employees WHERE employees_number='$id'";
mysql_query($order);
header("location:edit.php");
?>
Hi, My name is Masud Alam, love to work with Open Source Technologies, living in Dhaka, Bangladesh. I graduated in 2009 with a bachelor's degree in Engineering from State University Of Bangladesh, I'm also a Certified Engineer on ZEND PHP 5.3, I served my first five years a number of leadership positions at Winux Soft Ltd, SSL Wireless Ltd, CIDA and MAX Group where I worked on ERP software and web development., but now i'm a co-founder and Chief Executive Officer and Managing Director of TechBeeo Software Consultancy Services Ltd. I'm also a Course Instructor of ZCPE PHP 7 Certification and professional web development course at w3programmers Training Institute - a leading Training Institute in the country.
4 comments on “PHP Basic Part-4 (php with MySQL)
  1. Sir How Are You. I think U r fine. I am also fine, but I face some problems.
    I can’t display image in my pnpcms . pls sir What’s the code of display image in the content article.

    Please sir help me.

  2. My brother recommended I might like this website.
    He was totally right. This post actually made my day.
    You cann’t imagine just how much time I had spent for this info! Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *