 |
| connect.inc.php Screenshot1 |
Code for Connecting to a server and database with require function in php
connect.inc.php (Screenshot1)
<?php
$conn_error = 'could not connect.';
$mysql_host ='localhost';
$mysql_user = 'root';
$mysql_pass = '';
$mysql_db = 'a_database';
if(!@mysql_connect($mysql_host, $mysql_user, $mysql_pass) || !@mysql_select_db($mysql_db)) {
die($conn_error);
}
?>
Index.php (Screenshot2)
 |
| Screenshot2 index.php |
<?php require 'connect.inc.php'; echo 'OK'; ?>
Output (Screenshot3)
 |
| Screenshot3 |
As you can see example of screenshot1, screenshot2 and the output on Screenshot3. It shows us the connection had been established by server and database had been selected. So, first thing we had to check that we had require file correctly. The file exists. Secondly we should check whether we had connected properly. we don't had kill page in connection script with die function in php. Require function must be defined at the top of your page.
 |
| Sceenshot4 |
Now let's go to phpmyadmin You can see in details description about phpmyadmin. Now i will insert some data to phpmyadmin in database for example out database name is a_database. So we need the table first inside the database. I will create table called food means name of table is food and number of fields i will take 4 Id, food , calories, healthy_unhealthy. id will be primary and autoincrement. calories data type will be int. food data type will be varchar. healthy and unhealthy. Then i will save it. Table has been created You can see in screenshot4. For healthy or unhealthy we will store h or u for this. Now Will go for insert row inside the table You can see read my article phpmyadmin how to create table in phpmyadmin and how we can insert data into the table inside phpmyadmin. How to insert data inside phpmyadmin (Screenshot5).
 |
| Screenshot5 |
Now let say i want to select and display all data into the browser. To display all data we need to perform query as we discuss it will select all the data.
Code for Performing Select query In Php
index.php
<?php
require 'connect.inc.php'; /*connecting to server and database by php*/
$query = "SELECT `food`, `calories` FROM `food` ORDER BY `id`";
if ($query_run = mysql_query($query)) {
echo 'Query success.';
}else{
echo mysql_error();
}
?>
Output(Screenshot6)
 |
| Screenshot6 |
Now For Example Inside Our Database we had records Something You can See In Screenshot5. Food Table Name and rows are id, food, calories, healthy, unhealthy are rows in our database phpmyadmin. Now we will run select query it will select rows in database food, calories from table name is our food and it will displayed in order by id which is unique field in our database.
 |
| Screenshot7 |
To execute the query we need to call mysql_query function with parameter of the query which we had written to select rows such as food and calories from our table food in our database a_database. You will come to understands when we will echo out the query result here i had just shown you how to execute the query to select the data from table. mysql_error will show any error in syntax in this example(Screenshot7). If I had Written wrong query then it will return the error message For example In above example i add comma after calories you can see output in screenshot7. By this example we can see our query work or not it will now show any data.
Code for Select query in php and fetching data to browser.
<?php
require 'connect.inc.php'; /*connecting to server and database by php*/
$query = "SELECT `food`, `calories` FROM `food` ORDER BY `id`";
if ($query_run = mysql_query($query)) {
while($query_row = mysql_fetch_assoc($query_run)){
$food = $query_row['food'];
$calories = $query_row['calories'];
echo $food. ' has '.$calories.'
calories.<br>';
}
}else{
echo mysql_error();
}
?>
Output (Screenshot8)
 |
| Screenshot8 |
In this example i had grape data form our query and display on browser. The best way to do that is While loops in php because we had multiple rows to return. You look we had Pizza, salad. Query is just going to return if we echo out the values. Its just going to return one of the rows. What we want to do is we want to return all of the rows. So, we do this using while loop in php. Inside our while loops condition we need to assign variable and we need to create associative array from our results. Now remember in associative array contains a key which in the textual form and a value. In this case associative array will return from the table inside the database. The key's will be id , food, calories, healthy_unhealthy. In case of our query we had selected food and calories so our associative array will contain key, food, calories and value will be
pizza 1000
salad 200
So, we can return them in the loop. So, what i had done is i have to say while( $query_row = mysql_fetch_assoc ($query_run)). This peice of code will fetch values in associative array assoc is actually associative array references $query_run which is executing the select query. So, we had execute our query by $query_run = mysql_query($query) . The above piece of code will execute the query by mysql_query function which will reference to the select query in php. It will pass argument of the select query for executing the select query mysql_query function is used. To fetch the data in associative array by executing the query mysql_fetch_assoc function is used which takes argument of the executed query $query_run.
mysql_fetch_assoc we had assigning in $query_row. So, $query_row is now an array because we grape our data from our query as an associative array and put in this variable called $query_row. Now we will echo out data of each row. Now how we do this usually for best practice I assign the variables. So, what i do $food = $query_row['food'];, here $query_row['food'] is the element of the array. I do $calories = $query_row['calories'];. So, we got variable name called food and we got variable name called calories. They both equal to the reference of this array remember using square brackets Because it is associative array we taking while loop for the field called food and field name calories. SO, this food here and calories here Corresponds to the database inside table rows are food, and calories. So, firstly when the loop is executed it will $food will be pizza and $calories is going to equal 1000. Second time when loop execute remember we using while loop $food is going to be equal to salad and calories going to be equal to 200.
Now with echo statement in php we are echoing out echo $food. ' has '.$calories.' piece of code will echo out food first then it has been concatenate in php with has and it will echo out calories again concatenate in php and calories it will make sense in sentence that pizza has 1000 calories. First time it will goes in the loop it will return pizza has 1000 calories. Second time it goes in loop it will return salad has 200 calories.(Screenshot8)
Now for the first time you can see that the query has return data we looping through each of the rows that has been return by our query and we are echoing out results. So, now essential what we had done we had get data from our database by php. Now we will change the query so we can retrieve different information. So, for example i want salad first then pizza
Code for Select query in php to fetch data in descending order
<?php
require 'connect.inc.php'; /*connecting to server and database by php*/
$query = "SELECT `food`, `calories` FROM `food` ORDER BY `id` DESC";
if ($query_run = mysql_query($query)) {
while($query_row = mysql_fetch_assoc($query_run)){
$food = $query_row['food'];
$calories = $query_row['calories'];
echo $food. ' has '.$calories.'
calories.<br>';
}
}else{
echo mysql_error();
}
?>
Output (Screenshot9)
 |
| Screenshot9 |
Here In This example i want salad to come first then pizza. I want last edited row to come first. So, that simple in select query $query = "SELECT `food`, `calories` FROM `food` ORDER BY `id` DESC"; here desc reference to descending order. You can see screenshot9. IF we want to do ascending order you had seen by default its in ascending oder but if we want in any case ascending order query will be $query = "SELECT 'food', 'calories' FROM 'food' ORDER BY 'id' ASC" ASC reference to ascending order (Screenshot8).
Example to get particular data from the database if we want particular rows Let say we want list of healthy food.
Code for select query in php for particular rows
<?php
require 'connect.inc.php';
$query = "SELECT `food`, `calories` FROM `food` WHERE `healthy_unhealthy`='h' ORDER BY `id` DESC";
if ($query_run = mysql_query($query)) {
while ($query_row = mysql_fetch_assoc($query_run)) {
$food = $query_row['food'];
$calories = $query_row['calories'];
echo $food.' has '.$calories.' calories.<br>';
}
}
else {
echo mysql_error();
}
?>
Output (Screenshot11)
 |
| Screenshot10 |
 |
| Screenshot11 |
In this example Let Firstly add some records. So we have clear idea what going on. Insert data into phpmyadmin For example data pasta calories may be 600 and we say it must be healthy so give value of h. Food say Ice - cream calories 700 and we can say it is unhealthy we denote that with u. You can see screenshot10 we had inserted data into phpmyadmin. Now I will built query which will retrieve data of only healthy food which us denoted in database with h means the row of healthy_unhealthy which contain h will be displayed into descending order. So, The query will be SELECT `food`, `calories` FROM `food` WHERE `healthy_unhealthy=`h` ORDER BY `id` DESC So, where `healthy_unhealthy equal to h will retrieve the healthy food from the database. You can do same for unhealthy food to change h with u into the query for example query for retrieving unhealthy food into the database.
$query = "SELECT `food`, `calories` FROM `food` WHERE `healthy_unhealthy`='u' ORDER BY `id` DESC";
 |
| Screenshot12 |
Code for select query in php with mysql_num_rows function
<?php
require 'connect.inc.php';
$query = "SELECT `food`, `calories` FROM `food` WHERE `healthy_unhealthy`='h' AND `calories`='100' ORDER BY `id` DESC";
if ($query_run = mysql_query($query)) {
if (mysql_num_rows($query_run)==NULL) {
echo ' No result found.';
}else{
while ($query_row = mysql_fetch_assoc($query_run)) {
$food = $query_row['food'];
$calories = $query_row['calories'];
echo $food.' has '.$calories.' calories.<br>';
}
}
}
else {
echo mysql_error();
}
?>
Output (Screenshot13)
 |
| Screenshot13 |
There is also function in php that allow us to return an integer with amount of rows that as been returns. So, for example if we return no values so, we got healthy_unhealthy equal u and calories equal 100. So, what we are doing now query we are selecting where food is unhealthy and calories is equal to 100. Now if we look in our table (Screenshot10). Let see unhealthy records so we got unhealthy pizza which calories is 1000 and we got unhealthy ice cream which is 700. Now no records exits. So, There is no record for the calories equal to 100 and it should be unhealthy. So, what's going to be happen if we execute query it will return nothing to such because no such records is exits. So, what happen if we want to display message to the user saying Nothing's been found. So, No result has been return from this query.
So, what we can do we can use mysql_num_rows function. So, if $query_run equal to mysql_query function query execute inside this if loop we can do is say another if loop for mysql_num_rows we need to references $query_run. So, the query actually been run equals equals 0 or NULL then say something otherwise loop through the data. There is no point to run the while loop if there does not has any data to return. Here if mysql_num_rows function returns 0 or null so we had put error message saying no results found. So, we run the query if statement in php and we saying if number of rows return from the query is equal to 0 or null say no result found otherwise we looping through data has been found because if it does not equal to 0 or null. It must have at least one row then we can loop through and grape the data. So, you can see in screenshot13 No results found.
Now what happen if we change the query little bit say calories equal to 700 for example
$query = "SELECT `food`, `calories` FROM `food` WHERE `healthy_unhealthy`='h' AND `calories`='700' ORDER BY `id` DESC";
 |
| Screenshot14 |
Try this code in above example by changing the select query in php. You will see the screenshot14 output. So, this query will return one row. Therefore this if statement evaluate to false and we run while block. So, we can got ice cream has 700 calories displayed into the browser. So, usually using this simple function and using while loop. You can see select all rows in our database.
