CODEWITHSUNDEEP
phpmysqlselect
Andrey
Andrey

Reputation: 3

PHP + Mysql complicated SQL SELECT query

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?

Upvotes: 0

Views: 111

Answers (5)

Kickstart
Kickstart

Reputation: 21513

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.

Upvotes: 1

Mathijs Segers
Mathijs Segers

Reputation: 6238

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.

Upvotes: 0

Proxytype
Proxytype

Reputation: 722

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.

Upvotes: 0

Mohammad
Mohammad

Reputation: 3547

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

Upvotes: 1

rurouni88
rurouni88

Reputation: 1173

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

Upvotes: 2

Related Questions