Shuffle mysql record

Good evening.
I wanted to ask your advice on how to do this thing.
I have a table in MySQL.
From that table I have to select the records that have “user1” in the User column, and I have to put them IN RANDOM ORDER in another table.
All this should be done with php.
Thanks so much

You don’t have any real control over what order they’re actually stored in a database table, hence all the sorting you can do during data retrieval, indexing options and so on.

Don’t forget that properly random order of the first four positive non-zero integers is also 1, 2, 3, 4 - it doesn’t necessarily have to be 4,2,1,3 or whatever. Can you be a bit more specific on how you need them to be mixed up in the second table?

Is for a tournaments manager.
The players sign up, then i would that the player combinations are random

All database tables are stored in random order - it is how you sort the results when you are retrieving them that determine s the order the table has for that specific query.

If you want to retrieve records from a mySQL database in random order you can ORDER BY RAND() on the table that contains the data and get a different random order each time.

and when I randomized how do I pass to the other table in that order?

you can’t - the other table will not have an order on it either until you specify one when reading from it.

The only way you can store an order is if you have a field that contains something to identify the order - then you can order it by that field when you read it.

No need to save everything in the second table in that case - just have two fields for the “order” and the primary key of the original table.

so I could add to the first table a random column. with php they create many random numbers how many rows in the table, and I put in random column. Then I copy the lines in Table 2 with the order of the random column, right ?.

I think you may be misunderstanding what a database / tables are it terms of “the bits”

As a not really technically accurate explanation:

Imagine a drive is segmented into “sectors” each of 4096 bits. The OS “maps” to the various sectors so it doesn’t need to scan the entire drive to get to something.

Now the code writes to as much of the drive as it needs for any given amount of data. I you have 8 rows with ~512 bits worth of data, it may place that data all in the same sector.
If you have 8 rows with ~3072 bits worth of data, rather than “split” the data, it will use 8 sectors leaving ~1024 bits “unused” in each of those sectors.
If you then have 8 more rows with ~512 bits worth of data it could use the leftover unused space for those.

That is, the table data is saved with maximum computer efficiency in mind, not in a way that lives up to any human concept of orderliness or how things “should” be organised.

Not that you shouldn’t have more than one copy of a database, indeed you should always have at least one backup copy.

But it is completely unnecessary to create what you might consider a “scrambled” copy.

Don’t mislead yourself into thinking the database is as how a UI presents it.

I guess if you can retrieve them in random order, but need to make that random order repeatable, then you just create an incrementing field as you store them in the second table. That way you can continue to retrieve them from the second table in the random order you first got them in. Or you could add an “order” column in the first table, retrieve them in random order and put the incrementing number in that - then while you need to keep retrieving them in that same order, you just order by that column.

I already basically gave this solution suggesting that the second table only needs two fields - one to indicate the order and the second identifying the record in the original table that order applies to.

There is no need to duplicate all of the data from the original table - only the primary key field(s) need to be duplicated. To duplicate the rest would break normalisation and would mean that the data in the second table no longer represents the content of the first table after any changes to the original table are applied.

1 Like

but so it has to be done twice queries to view data in php

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.