Tag Archives: SELECT

PHP/MySQL Querying Using SELECT

Posted on by
Reply

PHP/Mysql & SELECT

We have been looking at SELECT statement for sometime and I would like to apologise in advance but we are going to have to go through it again in this article. However , we are not just going over how to query rows and columns using SELECT , we are going to look at it from PHP language point of view and see how we can return those rows into a webpage. Of course , I am simplifying the whole process slightly because there will be topics we won’t be going into such as pagination. Perhaps in future. If you have forgotten all about SELECT statement , here is the link to my previous article explaining it in more details.

Lets take a brief look at the SELECT statement and this is how the syntax should look ,

SELECT column_name(s)
FROM table_name

This is how the whole PHP/MySQL SELECT works ,

$con = mysql_connect("host-name","user-name","user-password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("database-name", $con);

$result = mysql_query("SELECT * FROM Employee");

while($row = mysql_fetch_array($result))
  {
  echo $row['FirstName'] . " " . $row['LastName'];
  echo "";
  }

mysql_close($con);

Of course we have been looking at “mysql_connect” function too many times. So if we are to remove all those items that are not necessary to our objective and view only those that are important items , this is what we been talking about all this time ,

$result = mysql_query("SELECT * FROM Employee");

while($row = mysql_fetch_array($result))
  {
  echo $row['FirstName'] . " " . $row['LastName'];
  echo "";
  }

First we send a query , take note that unlike C# , all that PHP does is to send a command , a string , to the MySQL database. It has no control over how MySQL handles it or whether it is even a legal SQL statement. Then it passes the resultant array to the $row and using while loop , it goes over the entire array printing the result in each new line HTML BR. It is similar to “Console.WriteLine” except you need to specify the new line explicitly.

SELECT Statement Conclusion

I think this should do for now to the PHP/Mysql side of the tutorials and tomorrow onwards , we go back into C#. Mainly because this is what I will be doing in my part-time degree so need to concentrate more into C# rather than PHP. Perhaps , next time I can go into PHP again.

SELECT Statement with AND & OR Operators

Posted on by
4

Lets take a breather from JOIN statements we been looking at and lets look at AND and OR operators to be used in conjunction with with the SELECT statement to query information from the database.

Here is a sample SELECT statement using WHERE clause


select emp_name as 'Name', emp_sex as 'Sex', emp_salary as 'Salary' from employee where emp_sex = 'Male';

This will return you with 3 rows , excluding the lone female we have in the sample database.

Name Sex Salary
Michael Bay Male 90000.00
John Evan Male 5000.00
George Chan Male 50000.00

Its all fine but what if you want to select those who are BOTH Male AND has salary of more than 10000.00? Of course , this mean only two out of three will qualify. SO how do we do this in SQL?


select emp_name , emp_sex, emp_salary from employee where emp_sex = 'Male' and emp_salary > 5000;

Here we are looking for employees who are both Male and must have salary of more than 5000. The important thing to note is that using “AND’ means both the conditions must be satisfied. The query result for this statement is of just 2 rows.

Name Sex Salary
Michael Bay Male 90000.00
George Chan Male 50000.00

So what about OR? Try it. Replace ‘and’ with ‘or’ in the previous statement , like this ..


select emp_name , emp_sex, emp_salary from employee where emp_sex = 'Male' or emp_salary > 5000;

Guess what is the result from this query? If you say all three rows are returned as the first query then you are right. Because using ‘OR’ operator , only one of the conditions must be right. Either the employee is Male or has salary more than 5000. Since John Evan is Male , it qualifies him even though his salary is not more than 5000.

Hope this helps if you are wondering how is it possible to apply two conditions in SQL Select statement. See ya!

SQL JOIN statements

Posted on by
Reply

Lets get back into the JOIN statements we were discussing before I got carried over by the LightSwitch. As I was saying , there are a few types of JOIN statements. Here they are ..

  • JOIN: Return rows when there is at least one match in both tables
  • LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
  • RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
  • FULL JOIN: Return rows when there is a match in one of the tables

And there are two types of default JOIN statements , inner and outer JOINs. Before we get dizzy , lets see some examples of each type.

*FYI : INNER JOIN aka JOIN and in some databases RIGHT JOIN is called RIGHT OUTER JOIN.

SQL JOIN statement syntax ,

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

SQL LEFT JOIN statement syntax ,

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

SQL RIGHT JOIN statement syntax ,

SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

SQL FULL JOIN statement syntax ,

SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name

Next time lets see how we can use them on our sample database :P

More of SELECT statement using sample database

Posted on by
Reply

For this tutorial , pls refer to the SQL Database Example Page for the sample database. We will be using it for our examples below. Lets recap what we had done so far.

In brief , we were introduced to the SELECT statement , which is used for querying data from the database tables. It can be used to return all the rows and columns , such as


SELECT * FROM TABLE_NAME;

or could be refined to return only the rows meeting specific criteria.


SELECT * FROM TABLE_NAME WHERE COLUMN_HEADER = 'SOME_STRING';

In our sample database , lets say we want to return only those male characters , meaning sex = ‘Male’ ( Note that for MySQL , a string is enclosed within either single quote or double quote. http://dev.mysql.com/doc/refman/5.1/en/string-syntax.html )


mysql> select * from anime where sex = 'Male';
+----+-----------------+--------------+----------------------+------+
| id | name            | address      | email                | sex  |
+----+-----------------+--------------+----------------------+------+
|  1 | Monkey D. Luffy | Amazon Lily  | monkey@eastblue      | Male |
|  2 | Uzumaki Naruto  | Konohagakure |  naruto@Konohagakure | Male |
+----+-----------------+--------------+----------------------+------+
2 rows in set (0.00 sec)

mysql> select * from anime where sex = "Male";
+----+-----------------+--------------+----------------------+------+
| id | name            | address      | email                | sex  |
+----+-----------------+--------------+----------------------+------+
|  1 | Monkey D. Luffy | Amazon Lily  | monkey@eastblue      | Male |
|  2 | Uzumaki Naruto  | Konohagakure |  naruto@Konohagakure | Male |
+----+-----------------+--------------+----------------------+------+
2 rows in set (0.00 sec)

Both will return the same result. Now , suppose you don’t want the whole set of information , just the name of the male anime characters. Then instead of * , enter ‘name’.


mysql> select name from anime where sex = 'Male';
+-----------------+
| name            |
+-----------------+
| Monkey D. Luffy |
| Uzumaki Naruto  |
+-----------------+
2 rows in set (0.00 sec)

Ok but you want to return the name as “Anime Heroes” instead of just “name” as in the column header. In that case using “name as ‘something’”. 


mysql> select name as "Anime Heroes" from anime where sex = 'Male';
+-----------------+
| Anime Heroes    |
+-----------------+
| Monkey D. Luffy |
| Uzumaki Naruto  |
+-----------------+
2 rows in set (0.00 sec)

So we just seen a few ways to manipulate the queries. Those are simple yet effective. Pls know them and know them well. Till next time , see ya!

SQL statement ( SELECT )

Posted on by
Reply

Today we dive into the best-known SQL statement , SELECT statement. It is basically used to query rows from the database tables. Full syntax of SELECT statement is as below. There may be difference between each database engines , for those , pls consult your database guide.

SELECT [ALL | DISTINCT] column1[,column2] FROM table1[,table2] [WHERE "conditions"] [GROUP BY "column-list"] [HAVING "conditions] [ORDER BY "column-list" [ASC | DESC] ]

At its most basic form , it is 

SELECT * FROM TABLE_NAME;

which will return the user with all the rows from the selected table.

To be more specific , you could add in the WHERE clause. Its form is , 


SELECT * FROM TABLE_NAME WHERE COLUMN_NAME = DATA;

For example , 


SELECT * FROM USER WHERE NAME = "JOHN";

which will return a row or rows where the NAME column contains the word “JOHN”.

Hope you learn some basic SQL statement with this! Hope to see you again!