Jquery - finding range between two unique id's in mysql

Question

Another question which has me perplexed:

I have a table which enables users to enter as many rows as they like based on their userid and unique id (auto incremental).

I need to be able to get this information from mysql and place the previously entered information into the fields on the web application (they may need to be edited before confirming that they're correct).

I store the total number of records for that user so far in one variable, and the total number of records for all users in another variable.

The question is: how do I get the range of ids for the records the user has already enterered.

Example: User 1 has 2 records in the database and there is 7 in total (5 by another user). How would I get the unique IDs of the 2 records that already exist?

Thanks for any suggestions you may have!

Answer 1

I'm not entirely sure what you mean, so this may or may not be helpful.

This SQL should give you the record ids:

SELECT id FROM tableofuserrows WHERE userid = [User Id]

You can then fetch this from the database with PHP, e.g.

$q = mysql_query('SELECT id FROM tableofuserrows WHERE userid = ' . (int) $_GET['userid']) or die(mysql_error());
$result = array();
while ($row = mysql_fetch_assoc($q)) {
    $result[] = $row['id'];
}
mysql_free_result($q);

echo json_encode($result);

So if you wanted to fetch these IDs from the browser using jQuery:

 $.getJSON("http://url", { userid: 3 }, //set userid properly 
    function(data){
        $.each(data, function(i,id){
            //do something with the recordid
            alert(id);
        });
    }
 );

Answer 2

Do you have to do this dynamically using jquery or can you load the fields in the web form with the rest of the page using php ?

Either way, you're going to need to query the database table for all rows where userid = a certain user. Once you get these results, you'll need to create a page you can call and get results from using jquery if you're going that route.

Someone just posted what I'm saying with code examples :-)

Answer 3

I decided to use MIN(id) in the select statement, counting how many rows there are and then populating the form fields accordingly, starting with the min value and adding the counted rows. Works well ;)