PHP mysql query: sql error printing

How to write SQL using PHP to handle the data in MySQL database? In any database driven script we have to update, add, modify, data in the tables. By using PHP we can do all this using different functions available in PHP. We will start with very basic function, which will execute any query written in sql and can be applied to MySQL database.

SQL

Structured Query Language or popularly known as SQL is an universal language to handle database. An introduction and different types of sql command like select, insert, update etc you will get in the sql section of this site. There are some advance SQL commands like left join, linking of tables etc to study. If you are not comfortable with SQL any time you can refer the materials in sql section.

There are three steps involved in this process.

Connection to database
Build the query and execute
Display the data

1.Connecting to database

To manage data we have to connect to MySQL database and execute query to get our date. Here there are two ways to use PHP drivers to connect to MySQL and execute the functions for getting records.

One is using Portable Data Object ( PDO )
Second one is MySQLI ( MysQL Improved )

2.Executing Query using PHP Functions & SQL

Let us start with the function required to execute one query in PHP. Once you have connection established then we can execute sql command by using PHP function mysql_query(). Here is the syntax of the function.

Let us first write the query and store in a variable. We will write a query to create table.

$query="CREATE TABLE student ( id int(2) NOT NULL auto_increment, name varchar(50) NOT NULL
default '', class varchar(10) NOT NULL default '', mark int(3) NOT NULL default
'0', PRIMARY KEY (id) ) TYPE=MyISAM";

We have stored the sql create query in a variable $query and we will pass this as a parameter to the function like below.

$rt=$connection->query($query);

The above command will execute php_mysqli() the query ( stored in variable $query) and we can check the status of the query ( successful or not ) by checking the value of the variable $rt. $rt will be true if the query is successfully executed or it will return false. We will use php if command to check the status of the query.

if($rt){echo " Command is successful ";}
else
{echo "Command is not successful ";}

So from the above line we can know that the query has worked or failed. But we will not come to know about the error if the database has some error and the query has failed. To get the error message we have to use another function mysqli_error() to print the error message returned by MySQL database after executing the query. Here it is how to print the error message.

echo mysqli_error();

The above line will print the error returned by mysql database if the query fails to execute. You can read more on mysql error here.

The complete code is available below.

$query="UPDATE  student SET class='Five'";
if ($connection->query($query)) {
echo "Records Updated";
}else{
echo $connection->error;
}

Before executing the above code we must connect to mysql database by using mysqli connection string.

3. Display Data

We may be collecting some data or trying for some updating or changes in database. The response from the database has to be displayed to the user.
Displaying Data from MySQL table
PHP MySQL Collecting single record from MySQL PHP Code generator for PDO & mysqli

Subscribe to our YouTube Channel here

vexatiousjones	04-02-2016
Im getting a No Database Selected error when using this code. Im using PDO connection string linked aboe. Any Suggestions?

smo1234	06-02-2016
YOu need to connect to database and select database. Read the connection string link available here.

Ann Ekpe	26-03-2017
Good day . please I need your assistance.I am currently designing a webpage with members signing in and having a dashboard.I want to be able to send data from the database to each particular member , so anytime they login to the website they can view the data... Urgent HELP

smo1234	26-03-2017
You can store the message in a table and display inside login area, that is when member login is completed this message can be displayed. The message can be managed by adding or updating new message etc. Knowledge of any scripting language and Database handling is required.

PHP MySQL query with error printing

SQL

1.Connecting to database

2.Executing Query using PHP Functions & SQL

3. Display Data

Subscribe