PHP + Mysql complicated SQL SELECT query

Question

I was thought how to make a complicated (as I think) SELECT query from table with 3 kind of comments (first - positive comments, second - negative comments and third - neutral comments). Using PHP I would like to SELECT and diplay first negative comments, and right after negative comments diplay all other type of comments. How to diplay them with one SELECT query together with LIMIT that I use to separate for pagination?

Example of table:

id - unique id

type - (value 1-positive, 2-negative, 3-neutral)

text - value

I was thought first SELECT * FROM comments WHERE type='2' ORDER BY id LIMIT 0,100

while(){

...

}

Right after that second

SELECT * FROM commetns WHERE type!='2' ORDER BY id LIMIT 0,100

while(){

...

}

But how use LIMIT for pagination if there is two different SELECT queries?

Wouldn't just ordering by type work for you?

Mathijs Segers
– Mathijs Segers

2015-06-26 11:35:12 +00:00
Commented Jun 26, 2015 at 11:35 — Mathijs Segers
– Mathijs Segers, Commented Jun 26, 2015 at 11:35

rurouni88 · Accepted Answer · 2015-06-26 11:40:13Z

2

Use a combination of UNION and LIMIT. However, you need to determine the bind variables, and specify the number of rows you want to display.

(SELECT * FROM comments WHERE type = '2' LIMIT ?)
UNION
(SELECT * FROM comments WHERE type != '2' LIMIT ?);

SQLFiddle: http://sqlfiddle.com/#!9/6d682/1

edited Jun 26, 2015 at 11:40

answered Jun 26, 2015 at 11:32

rurouni88

1,1735 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Kickstart · Accepted Answer · 2015-06-26 12:05:20Z

1

Use an IF statement in the ORDER BY clause to change the type 2 to sort first:-

SELECT *
FROM comments
ORDER BY IF(type = 2, 0, type)
LIMIT 1, 20

This will give you all the negative (type 2) comments first, followed by the other comments (whether positive or neutral). You would probably want to add an extra column to the sort just for consistency of display.

edited Jun 26, 2015 at 12:05

answered Jun 26, 2015 at 11:38

Kickstart

21.5k2 gold badges26 silver badges33 bronze badges

5 Comments

Andrey Over a year ago

what if I have already ORDER BY id DESC? Your variant works just as I need it. How to ORDER BY so that first go type=2 comments, then follow all other ORDER BY id DESC comments?

Kickstart Over a year ago

just have you order clause as ORDER BY IF(type = 2, 0, type), id DESC . This way they will be ordered by the type (negative first), and within type by descending id.

Andrey Over a year ago

tried here sqlfiddle.com/#!2/b1b96/7 . It doesn't ORDER BY id DESC after type=2. On the example need id to go like: 4, 1, 3, 2. But with this ORDER BY usage it shows 4, 1, 2, 3

Kickstart Over a year ago

Ah, see what you want now. You want any type other than 2 to be ordered by id, rather than ordered by id within type. Try ORDER BY IF(type = 2, 0, 1), id DESC

Andrey Over a year ago

perfect! now it works as I needed. great thanks to you. I'm so happy thanks to you =))

Mohammad · Accepted Answer · 2015-06-26 11:40:44Z

1

I didn't get your case exactly, but I think you may use OR operator to get what you want:

SELECT * from comments WHERE type=2 OR type=-2 ORDER BY type DESC

answered Jun 26, 2015 at 11:40

Mohammad

3,5576 gold badges50 silver badges77 bronze badges

1 Comment

Mathijs Segers Over a year ago

WHERE type in (2, -2) would be easier. not that it would matter for the DBMS, will result in the same.

Proxytype · Accepted Answer · 2015-06-26 11:44:37Z

0

you can use union to merge group of tables, but you must have the same columns in all the tables, for example:

select commetns,'nagtive' as type from nagtive_tbl limit 10
union
select  commetns,'positive' as type from positive_tbl limit 10
union
select  commetns,'neutral' as type from neutral_tbl limit 10

this query return table with all the comments from different tables each row contain is type so you can filter it while you building the lists on the client.

answered Jun 26, 2015 at 11:44

Proxytype

7227 silver badges18 bronze badges

Comments

Mathijs Segers · Accepted Answer · 2015-06-26 11:46:37Z

0

I must be missing context, otherwise you would just be fine using:

SELECT * from comments ORDER BY type ASC LIMIT 0,10

So by ordering the type, you'll first get all the items with type 1, followed by 2 and 3.

Just using the limit will chop them in the pieces you want and it will still respect the order.

edited Jun 26, 2015 at 11:46

answered Jun 26, 2015 at 11:36

Mathijs Segers

6,2489 gold badges54 silver badges78 bronze badges

Collectives™ on Stack Overflow

PHP + Mysql complicated SQL SELECT query

5 Answers 5

Comments

5 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

5 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related