PHP Introduction - Lecture #18

PHP - DATABASE CONNECTIVITY

Hello All... welcome every one. I'm going to start PHP lectures for Very Beginners, who wants to learn PHP from very beginning. No matter if you do not have any basic programming concept. If you follow my tutorials I hope you will surely get some how good knowledge of PHP.

Today our Topic is

PHP-DATABASE

This is very important, very easy topic of this series this PHP-Database is required everywhere you will work for PHP. In this topic we will learn how to send form data to the MySQL server and also how to retrieve data from MySQL. To work with MySQL from PHP we need to connect MySQL with PHP through coding and for that connection we have a function `mysqli_connect` with four mandatory parameters these are `host name, username, password, database name`.

Host name will be localhost, username will be root, password will be left empty and database name will be with which database you want to connect.

 mysqli_connect(“localhost”, “root”, “”, “school”);

and it is good approach to store all these in a variable, school database that we created before, we are going to connect with this database through our PHP coding. And to check either the connection has been established successfully or not. We have another function that is ` mysqli_error() ` this function will tell us the exact error where we have done mistake. Either in host name, user name, password or in database name. And its parameter is taken the variable in which connection is stored. Here is an example through which you can be very clear.

<?php

$conn = mysqli_connect(“localhost”, “root”, “”, “school”) OR (mysqli_error($conn));

?>

If you run this program you will find no error and empty browser nothing to be displayed means connection is successfully established with MySQL in case you made any mistake in host name, user name, password or in database name then it will display what the exact error is. Now we will insert the form data into the MySQL database school that we created previously using PHP. First of all we will create a form

<form action=”#” method = “post”>

Student Name: <input type = “text” name = “student_name” />

Student Address: <input type = “text” name = “student_address” />

<input type = “submit” name = “register” value = “Register” />

</form>

Now PHP coding on the same page because path is `#` if path is other file name then you must create that file with PHP extension and there you must write this code:

<?php

            if(isset($_POST['register'])){

$student_name = $_POST['student_name'];

$student_address = $_POST['student_address'];

$qry = mysqli_query($conn, "INSERT INTO students VALUES(NULL, '$student_name', '$student_address')");

            if($qry){

            echo "inserted";

            }

            else{

echo mysqli_error($conn);

}

}

?>

When you execute this code only form fields will be shown and no any action of PHP coding will be executed because whatever we have written is under the block of IF condition, when button will be clicked following code will be executed. After that we have kept values of form fields in variables and through those variables we have inserted records in MySQL. mysqli_query() is a function that helps us to execute a query and it accepts two parameters one is connection link of database and other is SQL query. And whenever you are going to insert any string values try to give single quotation as we have given. And in last we are checking that if query has been executed successfully with no errors then there should print “inserted” otherwise show us an error where we have made a mistake. Below is the whole accurate coding of inserting form record. Remember to include connection whenever you are going to work with database at the top of your coding.

<?php

$conn = mysqli_connect("localhost", "root", "", "school") or die(mysqli_connect_error($conn));

?>

<form action="#" method="post">

Student Name: <input type="text" name="student_name"/>

<br>

Student Address: <input type="text" name="student_address"/>

<br>

<input type="submit" name="register" value="Register"/>

</form>

<?php

            if(isset($_POST['register'])){

$student_name = $_POST['student_name'];

$student_address = $_POST['student_address'];

$qry = mysqli_query($conn, "INSERT INTO students VALUES(NULL, '$student_name', '$student_address')");

            if($qry){

            echo "inserted";

            }

            else{

echo mysqli_error($conn);

}

}

?>

In INSERT query if you do not give column names and directly use VALUES then it will also work but values should be accurate and sequential as given in MySQL table. You can see pictorial view of coding and result below:

In the last third picture inserted is displayed means record has been successfully inserted according to condition applied in coding.

One thing I missed to inform you that here we have two form fields and in MySQL we had three columns one column is missing and that is student_id, if you remembered student_id column was AUTO INCREMENT, in case of AUTO INCREMENT if you don’t insert record MySQL will auto insert values itself. But in PHP coding while executing the query you need to put ‘NULL’ or empty quotation ‘’.

Thanks Guys. Any thing missing or any question from this topic you can comment below, I will be trying to solve and answer your question.

Our topic continues to `PHP-DATABASE`

Good Luck J