Newbie SQL question

kensai

I was using ASP+Access and now I'm trying to convert my code to PHP+MySQL. The one problem I couldn't solve is using limit in subqueries. They doesn't work my Mysql Version 4.1 and I don't think even Mysql 5 supports it. I searched forums and the net, I've found a few workarounds on this one but they all are using joins and I don't know about it yet so I couldn't understand them 🙁. Can anyone please help me to convert this sql to Mysql version? I'd be very happy if you could help me.

Here's the code:

select id
from press_scanner
where id not in
(select id from press_scanner
order by date desc,id desc limit intLimit1)
and id in
(select id from press_scanner
order by date desc,id desc limit intLimit2)
order by tarih desc,id desc

Thank you!

nnichols

As long as intLimit1 and intLimit2 are defined this should work fine.

<?php

$intLIMIT1 = 20;
$intLIMIT2 = 30;

$sql = "SELECT id 
	FROM press_scanner 
	WHERE id NOT IN (SELECT id FROM press_scanner ORDER BY date DESC,id DESC LIMIT $intLIMIT1) 
	AND id IN (SELECT id from press_scanner ORDER BY date DESC,id DESC LIMIT $intLIMIT2) 
	ORDER BY tarih DESC,id DESC";

?>

Piranha

Can you please echo the sql row and post here to let us see exactly how the sql looks like? intLimit1 and intLimit2 have to be numbers and not a string, and they should start with $ in php.

nnichols

Having looked at your code again it looks like it is supposed to be some form of pagination. Assuming that I am correct you should rewrite your query to make the most of MySQL's LIMIT construct -

SELECT id 
FROM press_scanner 
ORDER BY date DESC,id DESC
LIMIT $first_record, $num_rows

kensai

nnichols wrote:
Having looked at your code again it looks like it is supposed to be some form of pagination. Assuming that I am correct you should rewrite your query to make the most of MySQL's LIMIT construct -
SELECT id 
FROM press_scanner 
ORDER BY date DESC,id DESC
LIMIT $first_record, $num_rows

You are quite correct, I was doing paging in my sql. I learned about Limit statement and everything went smoothly then 🙂

Thanks a lot for all the replies.

bradgrafelman

Don't forget to mark this thread resolved.