Hi my dear friends,
In my last post I wrote about the how we can read and display multiple rows of data from a mySQL database server. In that Post I used the following my SQL statement to read the data from the table.
$sql="SELECT * FROM material_transaction_register WHERE search1 LIKE '%$q%' ";
So now I will explain the structure of the statement. In the above SQL statement the SELECT , FROM, WHERE and LIKE are the keywords. The table name is the name of the table from where we want to fetch the Data. search1 is one of the column name. Now I will explain how it will work . This statement is saying that, select the table “table name” from the database which is mentioned in the connection parameter. And from that table verify the data from the column name “search1” . And also saying that the data should be “LIKE” the variable mentioned in the single quote. The percentage symbol shows that, any word before the variable and ANY WORD after the variable is not considering, that means if a sentence in the Column name search1 having the any word same like the “q” variable then that row will be selected for fetch. All this type of selected row will be fetch and asigned to the variable “sql”.
$sql1 = "SELECT * FROM table_name WHERE search1 = '$item_code' ";
In the second SQL statement is also work same as the first one. But in this case it will select the rows from the table_name table wherever the column name “search1” have the value exactly same as “item_code” variable. Here in the single quote we are declare a variable, you can also directly place any string there. So this SQL statement will send all the rows to the sql1 variable from the table wherever the search one column have data exactly equal to the item_code variable.
$sql="SELECT * FROM table_name ORDER BY id DESC LIMIT 25;";
Here is the third SQL statement. This one is one of the very important SQL statement, because it is generally used to read the entire data from a table. In the previous SQL statement we are used WHERE keyword to select the rows but in this case we are using ORDER BY keyword which will select all the rows in the column name “id” and DSE keyword is used to store this rows in descending order. Instead of DSE we can use ASC for ascending order. and the LIMIT keyword is used to limit the number of rows to 25 in this case. We can adjust this number as per the number of rows required. We can avoid to use the limit keyword along with it number, so that all the rows will be assigned to “sql” variable.
Here Also we can see that the keyword ORDER BY , DSC/ASC and LIMIT can also use with the SQL statement 1 and 2 given above as shown below. In this type of case the rows will be selected from the column name “search1” according to the condition. And it will be ordered by ascending or descending manner according to the column name “id” .
$sql="SELECT * FROM table_name WHERE search1 LIKE '%$q%' ORDER BY id DESC LIMIT 100 ";
$sql1 = "SELECT * FROM table_name WHERE search1 = '$item_code' ORDER BY id DESC LIMIT 100 ";
$sql1 = "SELECT * FROM input_vat_register WHERE id = 10 AND time_stamp = 1529359200 ";
Here the fourth SQL statement is the one of the greatest SQL statement. Because it is playing with multiple columns, here input_vat_ register is nothing but the name of the table. By using the WHERE keyword we are checking to column name “id” and “time_stamp”. Both conditions are connected with and operator, so whenever both condition will be satisfy the row will be selected for fetching. Instead of and we can use OR also, in that case if any one of the condition satisfied then particular row will be selected for fetching. Here the conditions are equal to operator but we can use less than or greater than or combination of both. We can also use this one to select a rose from the single column . For example if you have a column name “date” then we can check the condition of rows lying between two particular dates, by using the less than or greater than operator. Here we can connect more than two conditions also with operators but that one I am not tested. I hope by the use of these combination of things you can make various type of data reading operation from mySQL database table. If you have any doubt regarding this topics please give me a comment. So that I can help you. Thank you for reading this post.