Select Query In Php By Example

Select query in php by example

Select query in php we had seen the selecting data from the database. If you read my previous article that explain the code for select query in php with mysql_num_rows function in php.

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`='700' 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 (Screenshot1)

Screenshot1

Now if you are not comfortable to look this example and understand what it does just look my article select query in php. As well as look at general articles using mysql recommended to read my all articles of Index of php. Now what we had done here is we select a specific field names from this table called food where unhealthy equals to u and calories equal to 700. So, its quite being specific and in this case we only retrieve one value that's the ice cream value here with the id of 4. Now what we will want to do is we able take user input in order to return the specific values. So, let say that i want the user to be able to specify if they want allow list of unhealthy food or healthy food or may be if they want to specify maximum and minimum calories amount for now we just going to for unhealthy and healthy food option.

Code for select query in php user input

<?php

require 'connect.inc.php';

?>



<form action="index.php" method="GET">

Choose a food type:

<select name="uh">

<option value="u">unhealthy</option>

<option value="h">healthy</option>

</select><br><br>

<input type="submit" value="Submit">

</form>





<?php



if (isset($_GET['uh'])&&!empty($_GET['uh'])) {

 

  $uh = strtolower($_GET['uh']);

 

  if($uh=='u'||$uh=='h'){



  $query = "SELECT `food`, `calories` FROM `food` WHERE `healthy_unhealthy`='$uh' 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 (Screenshot2)

Screenshot2

In Above Example We are taking User Input. Now see Code We have to define Connect.inc.php in require function at the top of the page. So, we will Finish Php Code after Require function

<?php

require 'connect.inc.php

?>

After that we will start html form to take users Input form action will be on same page index.php method is going to be Get. So, just we can see what's going on in the url bar at the top. Now what we are retrieving is something like inside index.php we are  aim for something like index.php?uh=u for unhealthy and index.php?uh=h for healthy. So, we are using this get method for url to specify value. So, what we want to do is we need to create field and i am going to say i am going to create select field and this is going to be called uh and inside we are going to have option. 

<form action="index.php" method="GET">

Choose a food type:

<select name="uh">

<option value="u">unhealthy</option>

<option value="h">healthy</option>

</select><br><br>

<input type="submit" value="Submit">

</form>

So, we will having 2 option which are healthy and unhealthy. So, option value equals we just say u for unhealthy food and option value equals h  for healthy food. Then Submit Button say input type equal to submit and value of that say submit. So, that will be input by the user So, Choose a food type: So, it will choose type healthy or unhealthy. Now we will create a query that allows users to retrieve the data according to the users choice. Its very useful in everyday applications. So, I am going to click on unhealthy and click submit You can see in url bar index.php?uh=u and here if we click on healthy and click on submit index.php?uh=h. So we are processing get data through url we can process in our query.

So, Now we will check this values are submitted. So, i am going to say if isset $_GET uh then proceed futher. And also the fields of the form is we will check whether it is not empty .

if (isset($_GET['uh'])&&!empty($_GET['uh'])) {

Now we can say uh equal $_GET['uh'] and also we do this strtolower. It will check whether this variable $uh is equal to h or u before perform query. So, we can grape value $uh. Above query you can check whether $uh variable is equal to u or h 

$uh = strtolower($_GET['uh']);

if($uh=='u'||$uh=='h'){

Now in query we will check whether where healthy_unhealthy  equals $uh which is user selected input will retrieve query according to the users's choice. So, If the user will click on healthy. The value of uh in form will be submitted h then in php script it will checks and grape data according to the user input by retrieving the query. You can see output Screenshot2

Read More

Select Query In Php

Connecting to a server and Database We had seen connection to server by selecting database. To make that connection script to more efficient way we can use include and require function in php. We use include and require function in php because we don't had to write every time connecting to the server and database script. Whenever we want to connect to server and database we can use include and require function in php which will require the file of connection which in this tutorials article i am naming connect.inc.php. I will have my connection script to connect.inc.php (Screenshot1).

connect.inc.php Screenshot1

This connection script connect.inc.php will allows us to grape the data from our database if it has been connected to server and database.


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. 

Read More

Connecting to a server and Database By Php

I will Demonstrate how we can connect with mysql database. Read Articles Creating Database which we had named a_database. We also seen Privileges. I had explain we can get our username and everything. Home -> Privileges Screenshot1. We can add new user for but now i am going to use root on localhost with no password. So i will show you how we can connect to our database and then select database which we had named a_database.

Screenshot1

We will connect to a server and database from php Script. 

Code for Connection to a server and database by php

<?php

$mysql_host = 'localhost';

$mysql_user = 'root';

$mysql_pass = '';



mysql_connect($mysql_host, $mysql_user, $mysql_pass);

?>

Output (Screenshot2)

Screenshot2

Now First thing we do that to call mysql_connect function Now this takes three arguments. The First arguments is the hostname So it will be localhost. second argument is the username root and password i don't had the password so i am going to leave this blank but we had to specify it. How we can make this bit better bit more easy to specify everything. I am actually be going to use variables. So, i am going to say $mysql_host equal to localhost , $mysql_user equal to root, and $mysql_pass equal to nothing. then i will substitute the values inside the mysql_connect function mysql_connect($mysql_host, $mysql_user, $mysql_pass). Output of the script will be blank so we can assume that everything has been connected. 

Screenshot3

Now For example we had had incorrect information like in script we had replace root with alex and connect with database it will show us warning:mysql_connect()[function.mysql-connect]:Access denied for user 'alex'@'localhost' (using password: NO) in C:\xampp\htdocs\series\databases\index.php on line 6 (Screenshot3) Because we does not had alex user in our Privileges. Now let say we are running live website and this error return to the user this kind of error that we don't want to show user if we had incorrectly specify the user name or password inside of our code. 

Code for Connection to a server and database by php

<?php

$conn_error = 'could not connect.'



$mysql_host = 'localhost';

$mysql_user = 'alex';

$mysql_pass = '';



@mysql_connect($mysql_host, $mysql_user, $mysql_pass) or die('$conn_error');

?>

Output (Screenshot4)

Screenshot4

I Don't want to show this kind of error to user if we had incorrectly write username or password in our code. So at the end of the mysql_connect function i will use logical operator in php (or) after that i am going to use die function with my specified message error. which is could not connect. Now what it will do if our username and password is correctly return it will give use blank it assumes it is connected. Otherwise it die page and show my specified error message could not connect. Still it will show the warning now you can hide this warning by error reporting in php or just put @ in mysql_connect function. We had to specify @ symbol before mysql_connect function to remove warning message. @ symbol will  remove the default error reporting from php just on this line. 

Code for connecting to a server and selecting database by php

<?php

$conn_error = 'could not connect.'



$mysql_host = 'localhost';

$mysql_user = 'root';

$mysql_pass = '';



$mysql_db = 'a_database';



@mysql_connect($mysql_host, $mysql_user, $mysql_pass) or die($conn_error);

mysql_select_db('$mysql_db') or die($conn_error);



echo 'Connected!';

?>

Output (Screenshot5)

Screenshot6

Code for connecting to a server and selecting database by php script. Firstly here we had connected to our server by mysql_connect function with passing parameters Such as $mysql_host corresponding to localhost which is our host name of the server. $mysql_user corresponding to root which is our privileges of database and $mysql_pass corresponding to the blank which is our password of the user to connect. Then we had used die function to show our specified message to the screen if the database is not connected it will kill the page and show our specified message. 

Screenshot6

Now for Selecting database in php mysql_select_db function is used which had one argument the database name. Here we had the database name a_database which is corresponding to the variable $mysql_db. After mysql_select_db function i had call die function if the database name is wrong it will show our specified error meassage for example in script code if i change the database name which does not exists in database You can see screenshot6.

Now Let see Connection to database in php with lot better way to code for the connection to the database. 

Code for Connecting to a server and database Better way.

<?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);

}

?>

Output (Screenshot7)

Screenshot7

Every-time user connected to server and database we don't want to show them message connected. If Its not connected we want to show our specified message. So, In this script i had NOT operators in If_If else Statement in php. We are checking condition for server and database with @mysql_connect function and @mysql_selected_db function. Remember here @ is used to remove the error reporting in php. Here i had Say if not mysql_connect or if not mysql_select_db found show specified error with die function in php. Here die function will kill the page and show error message which is specified in variable $conn_error for example could not connect. If condition of if statement is not true then die function will executed and will kill the page. As you can see in screenshot7 it does not show any error message which means it is successfully connected to server and database. 

Screenshot8

Now let's Change and see how it will show the error message above connection script if i change the $mysql_user variable root to alex you can see the output screenshot8. Or Else if i change the database name a_database to a_database1 you can see the output Screenshot8. It will show the error message which we had specified. So In this article We had learn how to connect to a server and database and how to handle errors related to connection to a server and database. 

Read More

PhpMyAdmin

Screenshot1

Introduction to database article i had wrote about why we use it databases. Now we need to find easy way manage our database. Before we start it we will connect to our database then we can add and retrieve data. Screenshot1 is the Screenshot of phpmyadmin and its freely available piece of software it does comes with xampp If we go to http://localhost in  You can see it represented with xampp control panel and on that page you can see in left navigation down tools and you can see phpmyadmin so, If you just click on that you can see screen of phpmyadmin like screenshot1. If you don't had xampp you running something else then you can download phpmyadmin copy overview and set you database setting in there so your server in which you working like localhost and then everything will be manage for you.

Screenshot2

First Thing we need to think about is the server. We are connecting to Server so the server name we are connecting to at moment our server name is localhost and this is case format for most mysql host-names for example if i had paid hosting and i was uploading to shared hosting account for example usually i can specify localhost and it will connect straight to that. where ever i can connect to the another database on another server i always had to specify the server name itself.

Screenshot3

Lets, See the Phpmyadmin -> Privileges (Screenshot2) In Privileges you can see the list of users. In Screenshot2 you can see the user root is twice with host 127.0.0.1 and host localhost and Password no because i am running on my computer i had not connected to the internet then there is no need for me to setup password obviously it will be better to edit the password however for now i had not setup the password so, In example you can see usage i am not going to be using password the password will be blank So, Now we had established the username here is root which can be seen in privileges and you can also add new user. So, if you want to add the users (Screenshot3) it is bit more friendly so you can say any username, host better is to keep localhost then password you can also generate password for more security of your password. So, we had done that now obviously we already spoken about password which is blank in this tutorials. So, we had established the server , username and the password.


Server: localhost
username: root
password: NULL

Screenshot4

This all we need to use to connect to our database Now we not going looking at that now we are just seen how you can find this information. Now Lets Go to Screenshot1. left side you can see the databases that i had created Years and months ago just think i have worked in general So, You can see database name for example cart, quotes, Videos, this are databases name and to create a new database we you create new database in Screenshot1. So, i can create new database just called a_database and will click create now you can see screenshot4 a_database had created and you can see in screenshot4.

Screenshot5

Screenshot6

Now In Screenshot5 You can see at home page a_database had listed on left navigation. Now you notice a_database and other database had contain the number in brackets because cart(1), cdcol(1) this numbers are the table inserted to the database in Videos(1) so there will be one table exist on the database called videos you can see in Screenshot6. In database videos it contain 1 table videos. So, in brackets it contain 1. So In a_database there are no table so there is no bracket besides its and when u click on a_database you can see the left navigation contain a_database listed with brackets zero because no table exist we also get message saying No tables found in database. Now Its Fair to have database and inside this we need to store tables we can't directly store data inside into the database it has to contain tables.

Screenshot7

Introduction to database article i had shown you user's table and upload table. So, I am going ahead and create table called user's. Now number of fields is the number of columns which we had in listed in table. Before we create a table think about how many columns or fields like we may need. For Example i am creating a user login databases. the minimum we need id because we need to assign a unique id to be a user , username field, and password field that's only we need to create user login session's. So, Here number of field's in table user's we need 3 field's now i click go Screenshot4. Now what's its going to do its going to transform to this screenshot7. Where i can create each field's inside table. Its look's like lot's of information but you don't had to specify all this box's in Screenshot7. But there are some important part you will need to specify.

For field is the field name So, First one i am going to choose id, username, and last one is password, So, i had specified my field name inside the table. Now i need to specify the type of the data i want to store.  Now storing different type of  data is always like storing the variable with different type of data and You can see we have very extensive list of data like Int reference Integer we will discuss lot different types of data in later article.  Now id remember is the number which will auto-increment will give each row unique value and You can see how it will works in the moment. Now id i will choose type int because it is integer. You can also define TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT, are all types of data which are numeric. SO, i am going to choose INT for this You can also choose something like SMALLINT, Medium Int, However if you expect 100 users on your website This is fine however you are taking to Billions of users then you need to stick with INT data types. So Length/values we don't need to worry about it. Now 2 fields we need to focous in id fields are index and Auto_Increment fields.

Now Remember I said the primary key is the field or column the unique column to specify the unique column. This is the unique number column. So, Index field we assign primary key to id field. And we tick to AUTO_INCREMENT and this allows us to every-time we create a record we are increment this value. So, it will start 0 will be default. Then you create record you have value 1 then 2 then 3 and so on.

Now Let's Come to Username and Password Now obviously we don't want to save the username and password in INT data types. We will assign the VARCHAR. Now VARCHAR stands for variable character. So, what we can do here is we must specify the length/values field So, username say no more then 30 character. So, we can specify this is 30. Password will be adjust the same it may be less then 30 Characters. But this is something you going to have specify in your program. Let say you have to create user login system. You have to say you know give max length to text field. And check inside php if the password specified the username specified Word not more then 30 because anything bigger then 30 attempting to be written into this table it will be cut off.

Default values is the values if the user does not inputted and it may be saved default if the field is leaved blank it will saved by default value. If we create stayloggedin column and this may be equal to 1 or 0 depending on  the user logged in. But by default it will be zero. If this make sense. Now we can come down and click on save.

Screenshot8

Now You can see in screenshot8. that we had created a table called users. And we got number 1 with a_database. which shows there is one table inside the a_database. So, You can see we got the information. You can see id underline which means its the primary key. And we got our type so you can see int is a length of 11. So, we can specify 11 digits of length in INT type. Its may be consider to much like i said if you got millions of users. You got VARCHAR 30. and password VARCHAR with 30 character. You can also see extra is defined auto_increment.

Screenshot9

Screenshot10

Now let see how to insert the data into this table because at the moment we got the table. But we don't got the data. Now this data you are going to inserting from the php. The form will check inside the php script of the username and password. However we can do manually from phpmyadmin if we wish to do so. So,i am going to click on insert Screenshot9. And id we can leave blank because remember its auto increment fields. We can specify left blank and it will automatically update. Now You can see the Screenshot10. We can also specify the rows of insertion we can also specify 40 rows for insertion for example. But Now I will Just Insert alex and Password and will click to go.

Screenshot11

Now You can see screenshot11 we have few things return here in Screenshot11. Firstly we see the conformation. We can see 1 row inserted which we have inserted. Inserted row id: 1 which is our auto-increment field. Now You can See In Screenshot11 the query had constructed for insertion of the data.


 INSERT INTO 'a_database'.'users' {
'id',
'username',
'password'
}
VALUES {
NULL, 'alex','password'
};

Earlier i had told you about query How they can insert data Here we got insert into a_database which is the name of database dot users which is table we are accessing a_database table which is users. id, username, password and then with the value Null, for id we are not specifying value for the field because its auto-incremental. alex for the username, and password field value is password. So, Null corresponds to id, alex corresponds to username, and password corresponds to password.

Screenshot12

Screenshot13

Screenshot12 you can see the representation of the data in our database. So let's insert another let's called this time billy  and password pass123. Now we had inserted another row with id 2 remember that's auto increment. We perform query but with different values. You can see in browse tab. Now we are adding another Columns say first name and last name. So, Let see how we can do this. In Structure tab You can see at down add certain amount of fields at the end of the table , at beginning of table and after specified fields in our table. So, in my case i will insert fields at the end of the table. You can see in screenshot13 I will add 2 fields. Then same as earlier i will specify the field , type , length/ values Like firstname, lastname in fields, VARCHAR in type and length values are 40.

Screenshot14

ALTER TABLE 'users' ADD 'firstname' VARCHAR (40) NOT NULL, ADD 'surname' VARCHAR (40) NOT NULL 

Here we are altering the users table with tow more fields firstname with varchar character 40 and not null it means it should contain some data. Surname varchar length 40 not null. So, this is our query to alter the table and add two fields at the end of the table. Now In Browse tab you can see the no data has been inserted to those fields so i will insert the data of the two existing fields in the table. For Updating the table in existing fields. It will Give us the query Screenshot15. So, every-time we perform query in phpmyadmin it usefully gives us the query back so  if your structure is clean to understand the concept of the queries and how to structure them and what they do and how they work Later on then its really good thing to do just play around like insert things, delete things, change things, and just note of the query.

Screenshot15

In this case what we will do we had updated the particular fields in Screenshot15. In this case we updated the billy garrett we had updated which id is 2 So our query is update database name a_database, table name users and we set fields name firstname equals billy and surname equals garrett where table name users id fields name value is 2. It will update the data of fields id where value is 2 it will insert the data into the fields of firstname and the lastname.

Screenshot16

You can see in screenshot16 our data has been updated successfully we had alter the fields and then update the fields. You can perform variety of operations inside phpmyadmin. Its extremely powerful tool. So, may be before you start using queries and thing like that inside php. Have a play around here and just understand how may sql is structured and its datatype. How to do things works. Then go over to the php scripting side of things. So, for now its just the quick instruction about phpmyadmin how to use it in ways we are talking about of tutorials there is also lot more functionality behind it. Its just brief introduction about phpmyadmin.

Read More

An Introduction to database

We are going to talking about databases. Now in this article. I am not going to teach you how to setup the database or how to write in database etc. But we just going to be talk about why database important in most real life applications. I am also going to talking about mysql database. Why we need to use the database now you may come across the database before access another database applications so where you can store values with pacific column name so, you can store this values as a array also. you can also do this with Microsoft excel. If you already seen file handling in php. You know we had store things hit counter value in a file and then we had open the file we read the value in and close the file we may then open the value of the file applicant to rewrite another value. This uses huge amount of strength and complication in as well on whole process. Now let say we want to write a value to a file what we doing is we were opening up that file so, reading file data we are then writing a value to a file. What happen if we want to say to include username , password , firstname, surname , website you may have written 100s of records with people username the password. You may want to create some type of login system for this. Now you not going to do file to store this data. You will use database to store which is more secure and quickly accessible. In this case we will use mysql database connection because its one of the fastest database available to you.

Screenshot1

Mysql database is hosted on to the server. So, it will have its own port number If you open xampp control panel application Screenshot1. You will see we had process running mysql and its running at this moment. From php you don't need to worry about accessing mysql database on to the server. But we do use some functions to connect our database. then we shall connect to our server. Then we use another functions connect to the databases itself. So, you can have variety of amount of databases within mysql. Then you had variety of table inside databases. Now database let say user's Example of user's database.

Example of user's database

database: tutorialsjackpot
--------------------------
table: Users
------------

id    username   password  firstname  surname  website
1     hello          hello123  hello          tell         hello.com
2     How        how456    how's  you         how.com

Here we had database structure in mysql database in users is the table name. Our database name is tutorialsjackpot and the table name is users. Now the way we can access this is we can select our table. Now we are storing the users into and then selecting different value's. So, i can execute something called query which will select specific amount of data from specific row. You also had field called id which is auto-incremental its increment every time. So, each record created. Its automatically increments and this add  unique property and we can define primary key for this id. Primary key is the unique figure unique column in your database.

Now let say i want to select some data so we can say select the username from 'users' where the id is equal to 2. Here users is the table name and this is what we can called query. Its just an English language. But what i had done here is construct general idea something called query about. query does allow us specific syntax to select information from parts. So, what we are doing here is we are selecting the username in column of username from users table where id is equal to 2 means it will give use how. So, this query return the value hello So you can see advance storing inside the database it may look complicated. At this state but when you start using inside php. Php makes it very easy for you to extract data even insert data and perform idea of queries. This done from mysql database. you can also have other tables.

Table: uploads
---------------

id     user_id      location
1      1               logo.jpg
2      1          php.jpg
3      2          art.jpg

we had table say uploads we may allow users to upload files. Now why it is going to be useful. Just add another columns called id, user_id and location. So, the id will again will be auto-increment. But this time user_id What we are doing here is we referencing user_id into the table of users columns id because that is the users id so you can called this columns name anything you like. But i recommended similar structure. So, User id met be 1 and the location of the file which been uploaded. may be logo.jpg and another file is also uploaded by same user so user_id while be 1 and name of the file may be php.jpg. and another file is uploaded by second user hello which user id is 2 so user_id referencing is 2 location art.jpg. So, now what we can do is we can perform the query to say let say user is logged in hello. What we would do we have its id to select things relative hello the id is 1 this is unique to the user hello. So we can say select column name from table name where column name is field value

Example

select location from 'uploads' where user_id is 1

Now What this query here will do it returns values where ever the user_id equals 1. SO, What essentially return.

Output of the query

1      1            logo.jpg

2      1       php.jpg

Now we had selected locations only So, what it will do is it will only print back logo.jpg , php.jpg data. So, what we can do is to speck this out we can select a specific column name So, we can save our time in a program our load time and we can save resources. So, Its essentially for using  database the reason we use its quick and easy way to add information's, that can be easily retrieve we don't have to deal with text files. we don't had to deal with space between file, or commas between characters, hello,bye,goodmorning, So, retrieve from the file we need to open file. we need to use explode function in php to read all this comma's,and to put this into a array. But in database you are creating something you can visualize how extract specific data. You can also update data very very easily with just a single query. So, that was the introduction to databases. Like i said we are going to be using database MySQL and SQL stands for structured query languages. Now was talking about queries. SQL stands for queries. we had structured query which to retrieve, update, delete, create anything inside our table. MYSQL is the name of the databases. we are going to be using.

Read More