A query in MySQL is an expression which we can send to the database which returns a specific set of results based on the values and conditions we’ve set in the query string. In less technical terms, you can think of it as a statement which defines a question that we wish to ask the database.
An example might be to ask of the MySQL database, the following question:
I'd like to see a list of all employees in a company who get paid more than 30,000
Or perhaps a slightly more complex question
I'd like to see a list of all employees in a company who get paid more than 30,000 _and_ who work in the "Accounting" department
Now, these questions are all well and good, but before we can get an answer, we need to define some data that we’re going to draw our information from to answer these questions. Let’s define a list of employees working at an imaginary company, and assign them some attributes, such as job title, and salary. We can then use this data to fetch some answers, or results from our queries
| Employee Name | Salary | Department | Job Title |
| Jim | 29,500 | Sales | Staff |
| Bob | 33,000 | Sales | Manager |
| Jeff | 35,000 | Accounting | Manager |
| Alan | 27,000 | Accounting | Staff |
| James | 27,500 | Accounting | Staff |
To set up the data in this table in your own MySQL installation, open up PHPMyAdmin, or whatever other MySQL database management tool you’re using, and run the following query
DROP TABLE IF EXISTS `employees`; CREATE TABLE IF NOT EXISTS `employees` ( `name` varchar(64) NOT NULL, `salary` int(11) NOT NULL, `department` varchar(32) NOT NULL, `position` varchar(32) NOT NULL, PRIMARY KEY (`name`) ) TYPE=InnoDB; INSERT INTO `employees` (`name`, `salary`, `department`, `position`) VALUES ('Alan', 27000, 'Accounting', 'Staff'), ('Bob', 33000, 'Sales', 'Manager'), ('James', 27500, 'Accounting', 'Staff'), ('Jeff', 35000, 'Accounting', 'Manager'), ('Jim', 29500, 'Sales', 'Staff');
This will create a table named employees with the appropriate entries.
Don’t worry about the precise meaning of all the keywords here for the moment, we’ll cover things like the INSERT and CREATE keywords in a future tutorial. For now, all you need to do is enter the code above into your MySQL database, which can usually be achieved just by running an SQL query through the management tool by copying/pasting the above code.
If you’re not familiar with this process, or the section of the management tool required to carry out this task, refer to the previous section of this tutorial for a recap. Warning : If you already have a table named employees, this query will delete it and create a new one
For our first question, we would recieve the following answer : (Question - All employees getting more than 30,000)
Bob - 33,000 - Sales - Manager Jeff - 35,000 - Accounting - Manager
For our second question, we would recieve the following : (Question - All employees getting more than 30,000 and in the department “Accounting”)
Jeff - 35,000 - Accounting - Manager
We also see that we’re recieving answers with more data than we’re really interested in here. Perhaps we just want to know the name of the person getting more than 30,000, and we don’t need to see their job title or their position. How about “All employees getting more than 30,000, and just tell me their names”
This would get us
Bob Jeff
A lot simpler and, especially if we’re using tables with a great deal more information about each person, a lot quicker and more efficient.
Now lets see how we can take these real world examples and develop PHP-based code to perform identical operations, and display these results in our web browser
To perform queries using PHP, we make use of the PHP extension function mysql_query(). This function simply takes as its argument a MySQL string defining the query we wish to execute, like so
$result = mysql_query("SELECT * FROM employees WHERE salary > 30000");
Here, we’re running a query that is identical in every respect, to the question we asked first of all, to find all employees getting paid more than 30,000. Let’s take a closer look at what’s going on here
- $result - This is a variable which is assigned to the returned MySQL Resource - I’ll explain further on what this means, but for now, just think of the $result variable as containing the answer to the question we’re asking.
- mysql_query() - The function call which connects PHP to MySQL, and allows us to run queries on the data
- SELECT… - This is a MySQL Keyword and it defines what sort of action we wish to perform on the data held in the table. In this case, “SELECT” states that we wish to view a collection of entries based on our specified conditions (which we’ll get to in just a moment)
- * FROM employees - This defines that we wish to view all entries (*) from the table named employees. We can also specify that we wish to only see the results of certain columns from the table, by substituting the “*” for column names, such as “salary”, “name” or whatever you have named your columns
- WHERE salary > 30000 - This is the important bit of our query. Here, we state the Conditions we wish to check for, and in this case, we are stating that we wish MySQL to return only the results where the “salary” column contains a value greater than 30000. If we wished to extend this condition, to check for all employees that also work in Accounting, we would ask WHERE salary > 30000 AND department = “Accounting”. Notice that we use the same keywords and symbols as you would use in PHP when defining conditions, e.g. in IF statements - also note that we don’t need to use the double = symbol (==) when checking for equivalence, just one will do for MySQL!
After completing the query, the result of the question will be stored in the variable $result. In the case that we have not recieved any data back $result will be empty, (e.g, if we asked WHERE salary < 20000 we would get no answer) or if an error occurred, $result will be FALSE (e.g. if the MySQL query contains a typing error)
The following example shows how we can use the $result variable to print out our returned answers to the screen
$link = mysql_connect('localhost', 'root', 'pass'); //Connects to the database at "localhost" mysql_select_db ('test', $link); //Assuming you have a database named "test" set up // The following code assumes you have a table named "employees" on the "test" database $result = mysql_query("SELECT * FROM employees WHERE salary > 30000"); if($row = mysql_fetch_array($result)){ do { echo "Employee {$row["name"]} earns more than 30000"; } while ($row = mysql_fetch_array($result)); }
And this will provide the output
Employee Bob earns more than 30000 Employee Jeff earns more than 30000
If you’re running into errors while executing this code, or not seeing anything on the screen, you may not have set up your database connection correctly. If you don’t understand the code in the first couple of lines of the previous example, refer to the previous section [LINK] Setting up a database connection [/link]
Let’s look a little closer at what’s going on here…
- lines 1 & 2 - If you’re unfamililar with these commands, have a look at the previous tutorial, [LINK]Setting up a database connection.
- line 5 - This is the same statement we saw in action earlier. We assign to the variable $result, the result of the query “SELECT * FROM employees WHERE salary > 30000″
- line 6 - Here’s some new stuff for you. This if() statement checks that we have returned at least one result from our query. Remember that our question can return zero or more result rows for a query. In our case here, we’re going to have two result rows, and this if() statement assigns the first row to the array $row, via the use of the mysql_fetch_array() function. This function returns the next row from a MySQL result resource, and assigns it to an array.
- line 7 - If our query has returned at least one result, we then enter the loop condition, starting with a do() statement. Note that here, on the first time round the loop, we’re using the value of $row set in the if() statement. If there’s more than one result row, then the new row will be assigned within the while() statement. The following code will return inaccurate data
if($row = mysql_fetch_array($result)){ while ($row = mysql_fetch_array($result)) { //this won't work, as it will only output the second row from the result set echo "Employee {$row["name"]} earns more than 30000"; } }
because we are reassigning $row by calling while() before the loop, thereby replacing $row with the second row from our result set
- line 8 - Here’s where we access the values returned from the query, and assigned to $row in line 6. By assigning the value of the row to an array, we can access the array values by referring to them directly by name, e.g.
echo $row["name"] //Bob echo $row["salary"] //33000 //and then, after calling $row = mysql_fetch_array() again... echo $row["name"] //Jeff echo $row["salary"] //35000
You can see how, here, we’re placing the $row["name"] variable within the echo statement to output it to screen within the browser. With a little imagination, you can see how this can be extended to print out a variety of information about each returned result set.
Here are a few other examples using the SELECT keyword to retrieve information about our fictitious employees from the database
Find all employees earning less than 28,000 or more than 34,000
$result = mysql_query("SELECT name, salary FROM employees WHERE salary >= 34000 OR salary <= 28000"); if($row = mysql_fetch_array($result)){ do { echo "{$row["name"]} {$row["salary"]}"; } while ($row = mysql_fetch_array($result)); }
Which gives us
Alan 27000 James 27500 Jeff 35000
Here you can see that we’re only selecting name and salary from the employees table, since we have no interest in the other data
Find out information about the employee named “Jim”
$result = mysql_query("SELECT * FROM employees WHERE name = 'Jim'"); if($row = mysql_fetch_array($result)){ echo "{$row["name"]}, {$row["position"]}, earns {$row["salary"]} and is in the {$row["department"]} department"; }
Which gives us
Jim, Staff, earns 29500 and is in the Sales department
Find out how many employees are in Accounting
$result = mysql_query("SELECT COUNT(name) FROM employees WHERE department = 'Accounting'"); if($row = mysql_fetch_array($result)){ echo "There are {$row["COUNT(name)"]} employees in Accounting"; }
Which gives us
There are 3 employees in Accounting
Here, we’re making use of MySQL’s COUNT keyword, which can be used to quickly take a count of all items which match a specific condition. This is a faster method than retrieving all the values and then counting how many records are returned.
Find out how many employees are in each department
$result = mysql_query("SELECT department, COUNT(name) FROM employees GROUP BY department"); if($row = mysql_fetch_array($result)){ do { echo "There are {$row["COUNT(name)"]} employees in {$row["department"]}"; } while ($row = mysql_fetch_array($result)); }
Which gives us
There are 3 employees in Accounting There are 2 employees in Sales
Here, we introduce the MySQL GROUP keyword, which we won’t explain in too much detail yet (we’ll save that for a future tutorial - If you fancy jumping ahead, you can check out the tutorial on using Aggregate Functions to generate reports)
In simple terms, GROUP allows us to group together groups of results and return aggregate data for each group, rather than returning multiple entries all from the same grouping.
This example is here to show you the wide range of possibilities there are when working with MySQL for creating customised reports of any range of complexity. We’ll cover more of these possibilities later but first, we’re going to look at some more ways of manipulating table data and also some methods to keep the tables optimised and running smoothly.
September 23rd, 2008 at 7:17 am
How it can be possible more than row result in one row example
this is a data
name marks
abc 36
xyz 56
def 76
abc 86
abc 66
result this format
name marks
abc 36, 86, 66
xyz 56
def 76
how it can be possible plz. tell me
regards
parveen
September 23rd, 2008 at 8:18 am
Such a result could be obtained using the GROUP_CONCAT feature - taking as an example the employees table above, running the following code
SELECT department, GROUP_CONCAT(salary) FROM `employees` GROUP BY department
results in
Accounting 27000,27500,35000
Sales 33000,29500
Although you might find if the salary column is an INT type (try changing it to VARCHAR) you’ll get a BLOB result returned - which you can’t view easily in something like PHPMyAdmin
I think in your example the code would be
SELECT name, GROUP_CONCAT(marks) FROM table GROUP BY name
September 24th, 2008 at 11:41 am
result of marks field is not clear
SELECT name, GROUP_CONCAT(marks) FROM tblmarks GROUP BY name
name GROUP_CONCAT( marks )
abc [BLOB - 8 B]
ddd [BLOB - 2 B]
xyz [BLOB - 5 B]
September 26th, 2008 at 8:28 am
Thanx Sir
Findout Result
SELECT name, GROUP_CONCAT(cast(marks as char)) FROM table GROUP BY name
abc 5,2,6,1
ddd 4,1,6
xyz 1,2,4
i got the right solution but there is another problem i’m facing,
that group_concat information is not in sorted order. Can this information be sorted ?
regards
Parveen Sharma
abc 5,2,6,1
ddd 4,1,6
xyz 1,2,4
October 8th, 2008 at 9:22 pm
Seems to be a typo with the WHERE statement in
Find all employees earning less than 28,000 or more than 34,000
WHERE salary >= 34000 OR salary >= 28000″); ( more than 28000).
does not give the result in your example, should be
WHERE salary >= 34000 OR salary <= 28000″); (less than 28000)
Also all example output seems to display on one line, tried \n with no effect
using for the moment. Can’t seem to get any other break characters to work.
Great tutorial learning more from this than other site.
Good effort, thanks.
October 17th, 2008 at 8:41 am
possible more than row result in this format example
name marks
abc 36
xyz 56
def 76
abc 86
abc 66
def 76
Result is
abc xyz def
36 56 76
86 76
66
regards
Parveen Sharma
October 18th, 2008 at 10:36 am
its very urgent (query)
example
Table | Result is
name marks | abc xyz def
abc 36 | 36 56 76
xyz 56 | 86 76
def 76 | 66
abc 86
abc 66
def 76
regards
Parveen Sharma
November 11th, 2008 at 6:47 am
1) “To set up the data in this table in your own MySQL installation, open up PHPMyAdmin, or whatever other MySQL database management tool you’re using, and run the following query”. How to run the query?I already copy paste the statement and paste in SQL section using myphpadmin.How to show the result?
December 3rd, 2008 at 3:36 pm
I am a ‘Newbie’ to MySQL/Php, I have a project in which to design an online multipul choice questionnaire. I have designed the database, so that each question has a column and the answers are in numerical form (Very satisified=3, Satisfied=2 etc). The reports that will need to be run from the results are as follows:
Percentage of Very Satisifed Customers etc
Percentage of overall satisfaction (i.e adding all the resposes up and coming up with a percentage that quantifies the average %level of satisfaction)
I have read so many blogs etc that my brain is now fried. I would be grateful if someone could explain in simple terms please…. Would my queries be similar to the ones in the tutorial?
December 30th, 2008 at 3:27 am
All I can say is Great!
Thanks for this tutorials!