Day 12
Hello and welcome to day12.Today we are going to learn about:
- Selecting Data (From MySQL)
- Selecting Selective Data
- Selecting Data Through PHP And Display Selected Data
Let’s get started.
Selecting Data & Selecting Selective Data:
One of the most important thing while working with dynamic data transfer for any web-application is, data search and select. We do search within our database for different purposes like, we need some data to display on our web-pages, we need data to update, delete or edit. Or we need data to use in further processing within our application for different activities. Select and search means almost same for MySQL. When ever we search for some data, if MySQL finds it, selects it. So, to select some data from tables of our database we need to write select query. Through this query MySQL searches the data that matches our query parameters and givesthat data back to us. So, selecting or searching the data from tables is the same thing. Just like insert query we execute the select query. Only the difference is we get results in return of execution of select query. Let’s try to understand this in PHPMyAdmin Command-Prompt first.
So, here we are in our users table. Let’s go to SQL i.e. the command prompt of MySQL. Here you can see the default query of select is already there. So, whenever we come here the first query which command prompt shows is select query. Let’s push enter from the keyboard and this query executes and searches for data in this table. So, theseare the results of the query we just ran. Now, let’s try to understand the syntax of select query. This is the basic structure of select query. Let’s just remove, where 1, don’t bother about it right now. So, we start the query with ‘SELECT’ reserved word and thensteric ‘*’. This means select everything or search everything, then ‘FROM’ as simple as English Language and then table name ‘users’. So, this query will select or search all data and display everything from users table. Now click the tab below here ‘GO’ and this time it’s showing data for our query.
SELECT * FROM `users`
Get back to the command prompt and this time we want to select data, only from our userID column. So, instead of steric, we mention the name of the column ‘userID’ here. So, we are issuing a command to select all ‘userID’ From ‘users’ table. Press ‘GO’ and here we have data only of one column we mentioned and that is ‘userID’
SELECT `userID` FROM `users`
And here we go, only data of one column and that is ‘userID’ no other data. So, we can select any column or all columns’ data with our queries by just specifying what we need. Now, let’s select the data which is in ‘name’ column only. So, get back to the command prompt and just change ‘userID’ with ‘name’ and there we have only data of name column.
SELECT `name` FROM `users`
One thing more you can guess from this screen that our edit, delete and select buttons are missing. This is because in our selection we don’t have the primary key selected and that is ‘userID’. So, if you want to have edit, delete and select buttons on your screen, you must have a primary key within your columns that you selected.
So, let’s selectthe whole data in ‘userID’ that’s also primary key of this table, and data of ‘name’ column. Just put a comma between the names of columns to select data from multiple columns. That’s easy and straight forward enoug.
SELECT ‘userID’, ‘name’ FROM `users`
Now, you can see we have the data for both of these columns and we have our buttons back to edit, delete, select or to alter the data. Now let’s try to get data from three columns this time including ‘username’
SELECT ‘userID’, ‘name’, ‘username’ FROM `users`
And you can see we selected the whole data of these three columns. Now let’s move little further and try to get some specific record from users’ table as well as from columns. So, to select some specific record or set of records, we specify additional information using ‘WHERE’ reserved word. After this keyword we tell what the data should be similar to. In this case, I just want the record where the ‘userID’ is equals to 2. So, it’s very easy to get some specific data. Hit ‘GO’. You can see only data having ‘userID’ equals to 2 has been selected.
SELECT `userID`, `name` FROM `users` WHERE userID = 2
You can perform this functionality on any of your columns. Let’s get a data with specifying a name. So,
SELECT
`userID`, `name` FROM `users` WHERE name = ‘’
Let’s copy ‘James’ from DB and put it within single quotes as it’s type in DB is ‘VARCHAR’, so we need to provide it as a string
SELECT `userID`, `name` FROM `users` WHERE name =
‘James’
Click ‘Go’ and here we have all those records where the name is ‘James’, we get two records having same name, userID 2 and userID 15. So, this is how we can search and select some specific data from table or from specific columns, depending on our needs.
Same thing we can do with ‘username’, let’s copy any username from DB and paste it in here, hit ‘Go’ and you can see the only relevant data selected, where this username was found.
Let’s assume we want to select some data from the table where the data is almost like the parameters we supply. For this we use ‘LIKE’ reserved word, instead of equals ‘=’. We use ‘%’ sign to use as a wild card, that means the point where we put this percent sign is indicating MySQL, don’t worry, here can be anything and look for other parameter to match. I am writing letter ‘a’ after this wild card, it means MySQL should search all those names that contain letter ‘a’ at the end or ‘a’ is their last letter. Let’s try this. Here you see we don’t find any matching record. It means there is no any record in column ‘name’ that ends with letter ‘a’.
Let me just paste previous query again to save some time and this time after the wild card ‘%’ put ‘sa’ and after this another wild card ‘%’. So, what we want to select here is, get the record from users table where only the data in ‘name’ column contains ‘sa’ anywhere in its name. Let’s click ‘GO’, and you can see MySQL found two results, userID 1 and 14. We actually did not select ‘name’ column but our search or selection wasonly based on this column. I hope you are getting my point clearly.Let’s verify our search results from the full view of users’table. Here you can see only this record, userID 14 has ‘sa’ within its name column and userID 1 has ‘sa’ in its name column. There is no any other record in this whole table which has ‘sa’ anywhere in its ‘name’ column.
Now, let’s try this one last time. I am just changing ‘sa’ with ‘ja’ and appending wild card at the end of ‘ja%’. Let’s run it and now again it found two records, userID 2 and 15. Let’s verify from the full table data view. Another thing you might have noticed that the selection is not considering the letter case. You can either put upper, lower or mix letter case, it wouldn’t matter for selection. So, here is userID 2 and here is 15 starting with ‘ja’.
We can select and arrange data in the order we want through this select query. Not complex at all. Let’s just remove WHERE altogether and put ‘ORDER BY’ reserved word and then we need to mention the column name according to which we need order data, for the moment let’s just keep it ‘userID’ and the order type,descending or ascending. You can choose either of these. Let’s put ‘DESC’ for descending, as we want it in descending order.
SELECT
`userID`, `username` FROM `users` ORDER BY `userID` DESC
Now MySQLwill select data of these two columns and will arrange data in descending order by userID. Click ‘Go’ and here we go. You can see, we get all the data arranged in descending order by userID, starting from ‘userID’ 15 to lowest one that is userID 1. We can arrange or order data by any column. Let’s do it with ‘name’ column. And get data ‘ORDER BY `name` ASC’ as we want data in ascending order this time.
SELECT `userID`, `username` FROM `users` ORDER BY `name`
ASC
Let’s view the selected data. Here you can see all the data has been arranged and sorted in ascending orderalphabetically from A to Z by ‘name’ column, starting from empty record to the last letter it found.
Many times we need to select data by grouping some kind of records. This functionality is often used in E-commerce websites where we have some specific client has purchased or sold many items and we want to see the total amount he spent or earned or something like that. So, on the occasions like this, we have a very handy functionality of GROUP BY. Let’s use this.
SELECT * FROM `users` GROUP BY `name`
So, let’s GROUP BY ‘name’, and see what it gives to us. So, here we get the selected data and you can see the name column is not repeating any name, it has made the groups where it found the duplicate data in ‘name’ column. In full table view you can see here, lots of names repeating again and again like Rosahn, Stone Cold, James and Saud. So, what our selection query did, it selected the data and grouped the duplicate datawithin ‘name’ column and returned it.
There is another thing that is very useful and used quite often is ‘LIMIT’. You can limit your selection upto some specific number of results. Let’s say, you want only first three results out of your ‘users’ table. Just put,
SELECT * FROM `users` LIMIT 3
Click ‘GO’ and you can see the very first three results have been selected. Let’s try it again for only 1 result. Put 1 instead of 3 and here we are with one result selected only.
Now if we want to exclude some record and want to get rest of all, then we can use ‘!=’, indeedwe can use all of the arithmetic comparison symbols we studied about in our previous tutorials. Like >, <, !=, = etc. For now let’s, get the data where userID not equals to 4. Click Go.We have all of the users table data except the record where userID is 4, you can see that’s missing.
We can also, select the maximum or minimum value from any column where type is INT or its family. Just put MAX around the field or column name, and you will get maximum id. Remove this Where 1. Click ‘Go’And here we get one record with the maximum userID only. Another thing we can do is we can name the new column resulted from our query, so put right after ‘as userID’ and we can get other columns’ data which belongs to this record as well.
SELECT MAX(userID), as userID, name FROM users
And here you can see we get one record containing the maximum userIDas userID that we defined in the query and data from name column as well or relevant record. Same thingwe can do with Minimum,just replace MAX with MIN and that’s it. Here we are going to select the record having minimum userid.
SELECT MIN(userID) as userID, name FROM users
We canaddall the valuesof a column by using SUM function. Let’s change MIN with SUM, and this is going to SUM all the values in this column and will return the result back to us. Just click ‘GO’ and here we have the resultant of sum of all the values in ‘userID’.
SELECT SUM(userID) as userID, name FROM users
What about the rest of the data in other columns. All of the other columns will show the first record found when the search query executed. So, in all of these three cases, MAX, MIN and SUM,MySQL returned first data that was found when query executed from all the other columns along with the calculated column result.
So, to prove this, let’s get the data from few of other columns too. Let’s take the data from , ‘username’, and ‘joined’ along with previously defined, SUM(userID) and name.
SELECT SUM(userID) as userID, name, username, joined
FROM users
Now let’s run this query now, and here you can see 120 is the sum of all of the values in userID column and from all other columns only the first record that was found by our query is being displayed. We can also verify it from the full view of users’ table.
We can also, search data for different values and parameters from our table. For this we can define more than one parameters in WHERE clause. Let’s do this.
SELECT userID FROM users WHERE userID = 1 AND name =
‘Saud’
So, in this query we want the record where the userID should be equals to 1 and also the ‘name’ should be equals to ‘Saud’. So, if any of these two conditions is not met, MySQL will return an empty result. Click ‘GO’ and here we find a record containing userID 1, this is the record where both the conditions met and so this record was selected.
Now, we want complete record against the search based on our parameters. For this just change ‘userID’ with stericand conditions will remain same, let’s change the name to ‘James’. So, we are commandingMySQL to select all the records where the userID is 1 and name is james.
SELECT * FROM users WHERE userID = 1 AND name =
‘James’
Let’s run this query, and here we get an empty query result. MySQL was unable to find a single record which fulfills these both conditions.
Now, if we change this AND with OR, then what happens. Let’s change it. I would like you to recall the logical operators, we studied in detail in our day2 tutorial. These ‘AND’ and ‘OR’ work in the same manner here too. Now, I hope you must have guessed what type of result we should expect. Let’s click GO. This time we get many results. So, what happened, what MySQL did search for.
SELECT * FROM users WHERE userID = 1 OR name = ‘James’
Actually, MySQL selected all those records where, userID equals to 1, or the name equals to ‘James’. So, any record where any of our condition is met or becomes true, MySQL selected that data. So, you can see here, first record has the userID equals to 1, which is our first search criteria, and the other two records have the name ‘James’.
We can supply as many search criteria or parameters as we want to select the required records. Just put AND, or ‘OR’ whatever suits your need. Also, you can specify one column having different values to search as many times as you want. So, let’s put another OR here and specify ‘userID’ equals to 5.
SELECT * FROM users WHERE userID = 1 OR name = ‘James’
OR userID = 5
Let’s run this query, and off-course, now we have another record containing userID equals to 5. We can also, use other clauses with where clause or we can mix different clauses to get some particular data out of the database. Let’s just take another example to get sorted or ordered data along with specifying the WHERE clause. Let’s just put ORDER BY at the end of the query and sort data by ‘name’ columnin descending order.
SELECT * FROM
users WHERE userID = 1 OR name = ‘James’ OR userID = 5 ORDER BY name DESC
So, now same records will be displayed again but these will be in descending order by name. Let’s have a look at the results. Here you can see same records in alphabetical order from Z to A. So, this is how we use select query to select the records out of our database according to our needs. There are many other formats of this query, lots of other usages, lots of techniques you can use in this select query statement. The better you understand the select query and its uses, better results and performance you get out of MySQL as well as from you web-application. Also, better and targeted query writing helps you develop anefficient and light weightedweb-application.
Selecting Data Through PHP And Display Selected Data:
Now, we’ve learnt quite sufficient about select query i.e. how to select data from database. Now it’s time to use the select query inside PHP and learn how to getdata to display or use it for different purposes on our website pages. So, let’s do this now.
Here I have created a new file and named select_display.php. So, first thing I need to do, you might have guessed, I need to connect to MySQL and select the database. Instead of doing this from beginning, let’s just require our previously created connection file on day10. Here we go and now we have connected with MySQL and selected DB. Now to write any query for MySQL you know the keyword mysql_query and then parenthesis, and then ‘or die(mysql_error())’ and then just concatentate with __LINE__ to show at which line the error is. This is same old stuff we have studied before. Now let’s write a very simple select query that will getall data out of database. ‘SELECT all from users where userID equals to 3’.
$qr = mysql_query("
SELECT * FROM users WHERE userID = 3
") or die(mysql_error().'Error at Line: '.__LINE__);
As a result of this query, you know, we will get only one record that has userID equals to 3. Let’s put our whole query execution in a variable. We need to make a conditional statement if. So, if our select query executes only then we will proceed further. In the execution area we need to do something more to get results. As you know that we are not in MySQL now but working with PHP in PHP pages. So, the records we get from MySQL are not shaped to work with PHP, we cannot show them directly. So, we need to get these records and we will ask MySQL to fetch those records to us that is PHP. I am just going to use a built in function ‘mysql_fetch_array’. What does this function do? This function just takes the executed query and fetch all the resulting records of this query in an array within PHP. So, let’s put $qr as an argument of this built-in-function. And just give a variable name to this array $result. Let’s go ahead and print_r this array and see what this array has.
If($qr){
$result
= mysql_fetch_array($qr)
print_r($result);
}
Let’s run this PHP file in browser and here you can see we have data in this array. If you watch this array carefully, you can see at key 0 the value is 3 and at key userID value is 3, next at key 1 value is Clark and at key ‘name’ value is Clark again. So, this mysql_fetch_array is giving us duplicate arrays. One indexed and second one is associative. So, this function is little slowerand we’ll use it rarely. Let’s change it to some clever function. Let’s change array to assoc.This minor change is going to make our code very sharp and fast to execute. As this will give us only one array and that is associative array. Let’s check this out in browser.
If($qr){
$result
= mysql_fetch_assoc($qr)
print_r($result);
}
You can see only one associative array. You can use anyone of these two functions but I recommend to go with assoc, andif somewhere you feel that this associative array is not enough to get the results what you want, only then you should go with other option .Now here, only the purpose is to get the records out of MySQLas an array, so we might be able to display or use the records in our PHP pages. One record results in one complete associative array.
As $result is an array, we have made it an associative array which contains one record only. So, let’s call each key to get relevant value separately.
$result[‘userID’];
This userID is the name of the column for which we want to get data of this specific record. Next is ‘name’ and then ‘username’. Let’s put some br tags between these. I must say, you should not confuse yourself with this functionality. $result is just an associative array we created by fetchingMySQL results into it and now we are just calling each value by its key. Not a big deal. We have set it up all and let’s check it out in the browser, refresh the page and here we go. You can see 3, Clark and clark123 is the record we get against the query we wrote above.
Now if we look to our query carefully, we see that this query is going to select only one record and not more than one, because we have specified the userID, which is a primary and auto-increment field. So, there cannot be a duplicate entry is eventually we get only one record.
Now often when we run the query thatselects several records out of the table or database. Let’s change the query to select all the records from the users table.
$qr = mysql_query("
SELECT * FROM users
") or die(mysql_error().'Error at Line: '.__LINE__);
Now this query will select all records from users’ table. Just have a look at users table and you can see we have 15 different records. Now let’s get back to our code. Let me just comment out all of these outputs, as I just want to show you something interesting. Now we do have all the records with in our query and we have fetched it as an associative array, so, let’s have a look by print_r, and let’s see what it has. Refresh the browser and you would be surprised to see result. Aren’t you? This array contains only one record and that is very first record in the table. Although you can see here, we have written the query to select all records from users table. There are 15 records in our users’ table. So, what is wrong, or what is not working?
The actual thing is that our query executed welland selected all the records, but we are fetching only first record and assigning it to $result array. So to get all the records we need to loop through this mysql_fetch_assoc($qr) and within every loop we will assign next record to the array and then get the data out of that array. Let’s use our while loop. This while loop will keep on looping through until and unless mysql_fetch_assoc($qr) has something in it. In every single iteration one record will be assigned to $result array and we will display it’s data, in the next iteration again $result will get another record and we will show the data from the array, so this loop will keep on looping through until the last record is fetched. Now let’s take this print_r within while loop as we want to view all the arrays.
while($result = mysql_fetch_assoc($qr)) {
print_r($result);
}
Refresh the browser and here you can see we have all the records. We have all 15 records here. Let’s put the pre tags to see it in more readable fashion. Now you can see all 15 records and one array consists of one complete record. As each record is an associative array. Just keep in mind we need to stay within while loop. One more time, what happened here, our while loop helped us looping through the selected records and fetch_assoc took all the records one by one and converted those to associative array and through while loop we assigned and displayed all of them.
Now bring everything that we did to display our first record within while loop execution area. Remove all other unnecessary items like pre tag and comment out print_r too. Align our code a bit. Let’s put an hr tag at the end of each record. So, at the end of each loop the last thing to execute is a horizontal line. Purpose of this horizontal line is just to differentiate each record.
while($result = mysql_fetch_assoc($qr)) {
echo$result['userID'];
echo ‘<br>’;
echo$result['name'];
echo ‘<br>’;
echo$result['username'];
echo<hr>;}
Let’s go to the browser and refresh it. Here we go. You can see all the records separated by horizontal lines and we have successfully displayed all of them.
Now it’s the time to embed our PHP code within HTML page, so we can display nicely formatted data to our site visitors. Just for demonstration purpose, I will show the output within a table. Let’s close and open PHP tags so that we can write HTML freely within this area. Make sure you do this within while loop execution area. Here is a very simple basic table having only one table row <tr> right now and show all table headings <th> at the top of all the columns with this th tag. Let’s have a look in the browser. You can see we have heading with every record. So, with each iteration loop headings will be repeated before every record, and we obviously don’t want this. Headings are always once at the top of the table. So, for this, we need to cut out this portion of table out of the loop, and just past it here after if statement. Close and open PHP tags here again and just paste the table’s starting portion here. Now back in to the loop execution area. Now we need to have a table row which will consist data, so, let’s create this and create three tds, so that, we can display each of the column data here. Let’s storeall these values in variables and simply echo these variables within these tds. So, within if conditional statement, here we started our table and put headings here so these don’t repeat themselves. Then within while loop we placed a row and each cell containing the data, this row is in loop so every record will create a row with each iteration of loop. We don’t want to close the table with each iteration, so we take this table closing tag out of the loop and paste it within the if statement just before closing curly braces.
if($qr) {
?>
<table border="1">
<tr>
<th>
User ID
</th>
<th>
Name
</th>
<th>
User Name
</th>
</tr>
<?php
while ($result = mysql_fetch_assoc($qr)) {
//print_r($result);
//Format all the data here and display on the page
$userID = $result['userID'];
$name = $result['name'];
$username = $result['username'];
?>
<tr>
<td>
<?= $userID ?>
</td>
<td>
<?= $name ?>
</td>
<td>
<?= $username ?>
</td>
</tr>
<?php
}
?>
</table>
<?php
}
Now we have all setup. We have executed the query, we grabbed all the records out of the query, fetched them all in to the array and assigned $result, then within each loop we displayed all the data out of each record in a table. Let’s browse this now. Hit refresh and you can see our data is formatted using HTML tags. Let’s give a border to the table. And you can see now all the recordsdisplayed like a report. You can display all the fields, if you like to, but for now, I have just displayed three of them. So, this is how we use PHP code within HTML pages to format data fetched from database directly. This is the actual procedure of making dynamic pages using PHP.
This is all for now. The code we studied today is one of the most important activities you will be working with, while programming. If you don’t understand what’s going on, repeat tutorials and watch them again. Devote and force yourself to today’s session until and unless you feel and think, OK it’s fine, I can do it now. Try to code on your own. I am smelling that youmight not enjoy today’s session, due to lots of things happening around. But if you lose your temperament here you will lose everything you gained in the last 11 days of this traing course. Just take deep breaths, have some water, don’t try to absorb everything right in one sitting, take a walk, do other stuff and come back again with a fresh mind.Just remember one exciting thing that this is the second last day to complete ‘PHP In 15 Days – Guaranteed’ training course, you have learnt a lot, only few minor things remaining. Don’t lose your heart and courage. Stick with it just for a while and you will be really comfortable with this. See you in the last official training day 13, as on day 14 and 15, we are just going to utilize all the techniques we have learnt to create an Admin Panel with Login System. Take care!
