How to display rows in columns way

Today I would like to share how to display records in columns way. For those people lazy solve using IF ELSE statement, this is a good way to solve it.

This is the scenario:

tbl_results

student coursecode result
James
PHP
60
Mark
ASP
85
Mark
PHP
83
James
ASP
91


tbl_students

student age
gender
James
20
male
Mark
21
male


We want the output display like this way:

student PHP
ASP
James
60
91
Mark
83
85


This is the SQL statement:

 

SELECT student, php.result as PHP, asp.result as ASP FROM tbl_students
JOIN tbl_results php ON(tbl_students.name = php.student AND php.coursecode='PHP')
JOIN tbl_results asp ON(tbl_students.name = asp.student and asp.courseCode='ASP')


What if in another case, Mark did not take ASP exam, with above code, Mark records would not be display, because of using JOIN (INNER JOIN),
it means both records must be inside the table.
It can be easily solve by using LEFT JOIN tbl_results to solve this issue.

Reference: http://www.omandev.net/2007/08/sqltip2-how-to-display-rows-as-columns.html

MySQL Reorder Sequence Number

I am sure a lot peoples want to have their own sequence number order for their table.
For example menu list, not everyone would like to order by `name` or `id`.
What normally people do in website is give a text box file to fill in the sequence number which the most easiest way and direct way.
What you have to do just update the number that user key-in.

Today I would like to share for what if you have a new record and u want to put it in the middle?
To do in the normal way you have to retype and change the order/sequence number. But I would like to share with you all simple way.
MySQL tbl_MENU table:

ID NAME SEQ
1 Home 1
2 Blog 2
3 Download 3
4 Contact Us 4
5 About Us 5

Now I would like to add a new Menu which "Latest News" after the "Home".  First of course you need to insert a new data first.

INSERT tbl_menu(ID, NAME, SEQ) VALUES (NULL, 'Latest News', 6);

ID NAME SEQ
1 Home 1
2 Blog 2
3 Download 3
4 Contact Us 4
5 About Us 5
6 Latest News 6

Now I would like to change Seq No. 6 to Seq No. 2

After that we can run this query to update the sequence in this way:

UPDATE tbl_menu SET seq = seq + 1 WHERE seq >= 2 AND seq <= 6;
UPDATE tbl_menu SET seq = 2 WHERE id = 6;

The first statement is run the current sequence number to add one value, that means the table would like this:

ID NAME SEQ
1 Home 1
2 Blog 3
3 Download 4
4 Contact Us 5
5 About Us 6
6 Latest News 7

The next query is update the ID:6 sequence number to 2

ID NAME SEQ
1 Home 1
2 Blog 3
3 Download 4
4 Contact Us 5
5 About Us 6
6 Latest News 2

Finally you can run your display query like this

SELECT * FROM `tbl_menu ORDER BY seq;

ID NAME SEQ
1 Home 1
6 Latest News 2
2 Blog 3
3 Download 4
4 Contact Us 5
5 About Us 6