Dealing with deleted records in mysql. It breaks my PHP loops...

[ArmyofHarmony]

Hi guys,

I'm building a site at the moment that has a mysql table containing rows of data with 3 columns: id, name, url. The ID field is set to auto-increment in this case.

On my webpage I'm trying to pull out 5 random entries from the database, and print some text to a page. I've built some code to generate 5 random numbers between 1 and the maximum amount of rows in the table. But there's a problem here, if someone deletes one of the records...

Let's say I have 3 rows:

1 armyofharmony www.armyofharmony.com
2 overclockers www.overclockers.co.uk
3 bbc www.bbc.co.uk

If I delete record 2 (overclockers) and try and pick random entries from here, the table will look like this:

1 armyofharmony www.armyofharmony.com
3 bbc www.bbc.co.uk

If my code then picks the number "2" and I query the database for sites with ID == 2 it won't work.

I figure I need to do my selection based on row number, instead of unique ID. From what i've googled this might not be the best approach.

Can anyone help me with this?

What's annoying is that I think I'm only suffering here because I'm using random IDs. If I just had to continually loop through 5 times I wouldn't need to care about the ID.

I'm picking the ID in advance using PHP so that the process is quicker than selecting it using mySQL.

Cheers

 

[moogle]

Count the rows then pick a random number from the rows?

 

[ArmyofHarmony]

Hi Moogle

I'm not sure you can run a MYSQL select based on row number... that's the bit I'm getting stuck on.

Basically I've counted the rows and am picking random numbers from the rows... but if my IDs for each record do not correspond to the rows, it won't work!

Cheers

 

[ArmyofHarmony]

I think if I first run a query to create an array with all the IDs available, then select 5 IDs at random from there it might work... Just googling around to see if it's possible

 

[moogle]

Does SELECT 1 FROM table work?

As in just a number of the row?

Don't have php server setup here or a table to mess around with

Actually can't you select all rows then pick one using $row[number here] instead of using the name of the field?

 

[Thrash]

This could be the type of thing you are looking for:

select id from theTable ORDER BY RAND() limit 0,5

Plucks 5 random records from the table.

 

[ArmyofHarmony]

Hi Thrash

The reason I've avoided that approach is that I've heard it can take a long time to retrieve results. From what I've read online, it's better to pick the actual record you want before doing the query - it can massively increase speed

Cheers

 

[Thrash]

Should have read your whole post, sorry missed you bit at the end. If not a large data set then I would have thought it would still be the best approach though.

 

[whitecrook]

when a record deletes, just flag it as deleted, when you pick a deleted record just pick another random record or just take the next one.

 

[SMB30]

when a record deletes, just flag it as deleted, when you pick a deleted record just pick another random record or just take the next one.

Or alternatively:

SELECT ID FROM TABLE WHERE isDeleted != 1 ORDER BY RAND() limit 0,5

 

[fez]

Its quite hard sometimes to find info on queries using mysql as there are a lot of considerations to take into account.

If you are running a huge database then using RAND() will not be a good idea but if its only a few hundred rows max you won't have any issues. Mysql is very fast for the average user and it's easy to get worried about performance once you start reading about it. Truth is, unless your application is a beast with huge connected datasets you won't even notice the difference between using rand() in the query and abstracting that process to your code.

If it is going to become very large you will need to think about alternatives such as storing a reference of deleted rows that you can use to filter a set of random record ids generated on the server before you fetch from mysql.