Display all unique ID's even on 'limit' in MYSQL

Question

Hi,

I guess this is strange situation.

I have a results table which contain 100k records, basically this table consists of transactions with particular ID's ( not PK).

I am displaying the results limiting the records to 3000. I am using Javascript tool box to display a filter on top of table heading so that users can select / filter the results they want. But since I am limiting the rows to 3000, not all ID's are being captured in the filter combobox.

Now, My question is , is there anyway to display all the IDS for that ID column but still limit the results ? either through SQL query or any other method feasible?

I would appreciate if anyone has any suggestions for other possible ways.

Answer 1

I think the only way to do that, is to retrieve the IDS separately from the actual results (so in a separate query). That way you can limit the resultset, while still getting all the possible IDs for filtering/selecting purposes.

<?php
   $query1  = "SELECT name, id FROM items LIMIT 3000"; // get your items
   $result1 = mysql_query($query1);

   $query2  = "SELECT id FROM items"; // get all the IDs
   $result2 = mysql_query($query2);
?>

I cannot guarantee correctness of the above sample, but it should point you in the right direction.

Answer 2

Do a separate fetch for just the primary keys, you need all of them so you'll just have to fetch them. Then fetch all the data you need for the first 3000 records. You can loop through the results of the first query to get all the IDs you need into whatever structure you want.