MYSQL :: Random Data

Pick Random Data From DB
I have made an art website for my artist girlfriend that runs in mySQL.

I would like to place a random image and title of the image on the homepage that changes each day, kind of like a 'pic of the day' feature.

Can SQL do this? I suppose it could also instead of being random go from field to field each day, i.e. image 1 and title 1 on day one, image 2 day 2 etc etc until it reaches the end then starts again.

Random Data Generation
Im trying to upload a lot of data for testing into mysql db however, I cant figure out how to update same fields(primary - foreign)in different tables. I could copy one table to another but other fields would need updating at the same time. Random data generator I use does not upload same data into different fields.

Random Rows, Non-random Ordering
Normally to select random rows from a table I'd do something like:

SELECT field1, field2, RAND() AS random
FROM mytable
ORDER BY random
LIMIT 5
Unfortunately, this time I need 5 random rows sorted on a different field:

SELECT field1, field2, RAND() AS random
FROM mytable
ORDER BY field2
LIMIT 5
Is there a way that I can still sort by field2 and yet still select 5 rows at random? Could the former query be run as a subquery and then a second ORDER BY clause run on those results? Worst-case I can re-sort the results in my host language, but a pure SQL solution would be best I think.

Bonus points if the answer can always put a particular row first in the results.

Sql Random Pwd
i read somewhere that it is not safe to use a mysql randomly generated password . I have a table with many records and want to reset everyone's password to a random alpha-numeric password.
What is the best way to do this one ? something using a php random pwd and then update the table ? or there is something better than that ?

Random Row
I am trying to get one random row at a time. Right now I am using this SQL:

Code:

SELECT * FROM `trtrack` ORDER BY RAND( ) LIMIT 0 , 1

But it's really slow, some queries taking up to .5 to 1 second. Is there any other way? I use PHP.

Add A Random Value
in php, this would simply be rand(1,5)
in mysql, I tried

update `table` set column=(column+rand(1,5));

but that was not a correct guess.
how can I add a random value of 1 digit bt x and y?

Random Row Select?
if I have a table with 200 rows in it. I want to select 1 random row. Does anyone know how to do that in a select statement?

Random Keyword
I have a question about what query I should use to achieve the following...

I have a table called 'keywords' that has the following fields

id (auto_increment)
pagename
keyword

There can be multiple keywords for one pagename, but each keyword has it's own row. For example, this could be in the table:

id pagename keyword
11 test-news test
10 test-news news
9 test-news article
What I want to achieve is to select 1 random keyword for each unique pagename. So there can only be 1 keyword selected for a pagename, but it should happen for all unique pagename entries. What query would I use to achieve this?

Random Retrieving
data in myTable

(id) name
(1) Tom
(4) Jane
(8) John
I have data in myTable like the above.

Can I retrieve one of names in name column randomly?

Random Images
is there sombody who can helpme witht he following, i am ''pretty new'' to
php and am looking for a script to show images randomly at a 10/ 15 seconds
interval.

Random Query
I have been tasked with getting daily quotes out of a sql database, there needs to be 3 that are randomly selected. I have not found any documentation in regards to doing this.

How would I qurey a database and pull 3 quotes of the day out randomly. This should even occur when a user refreshes the page, is there a sql function that would do this? or do I need to do it via php, perl, etc...

Random Query
$querystring = $querystring." ORDER BY status, dateposted desc";

How can i make my query that its sorts first by status and then random for date posted.

Random Function?
I would like to know if there is an existing random function I can use to generate a random number so I can concatenate it to a string?

Select At Random?
I have a db that I'm using as a mailing list. In many (but not all) cases, there are several contacts per company. I rarely want to send my mail to more than 1 person at any one company, and I never want to send to more than 3.

I don't have any criteria that I can use to select which contact to select, so I want to do it essentially at random. So I want to contruct a SELECT query that says, "when there are >1 contact at company X, pick 1 (or 2) of them at random. When there is only 1 contact at a company, include that contact/company in the result as well."

I'm sure there's an easy way to do this, but having searched online and read a lot of the 1500-page manual, I haven't figured it out.

Inserting Random #
I have a db name image records. It has 3 columns recno_no, file_name and active_fl.

is there a way to insert 100 random #'s into the database table and its columns for testing?

Random Records
I'm trying to get a random record for use with my website. I've tried figuring out some premade code and it's led me nowhere..

Here's basically the rundown...

I have a database setup by my site's shopping cart system, and in it are 2 tables i need to read from, "specials" and "featured". I want to pull 4 random entries from "specials" and 1 from "featured" to have posted on my site's main page outside of the store.

I know how to connect to and select the database, I even know how to send queries, but the whole selecting a random entry is what's got me at a brick wall.

I'm not used to PHP and MySQL, i'm just now learning after 4 years of using Perl(and never using a database system, but creating my own database-like files).

Random Select
When I do a select with a limit I get always the same records. Usually this
is the desidered effect. But now I want to do a random select. So every time I do a select I should get a different set of results. What is the best way to achieve this effect?

Order By Random
Is there anyway to order records randomly? instead of order by id desc to order it randomly??

ORDER BY Random
Is it possible to pull results from a database using a SELECT... ORDER BY and pull them in a random order?

Random Pick
When an artist isn't picked, there is a random artist picked. Now I have it setup so it counts the rows, and picks a random number from 1-nr of rows. This number is then used as the id and gotten a mysql query from this id.

$query = mysql_query("SELECT * FROM `artists`");
$total = mysql_num_rows($query);
$random=rand(1,$total);
$right = mysql_query("SELECT * FROM `artists` WHERE `id`=$random");

Now, I want something a bit different. I also have ABC....Z, by the first letterof Artist Name. If the first letter is picked, but no artist, I want the random artist to be start with this letter. I wanted to use something like above but the id won't be in order. it could be like 1, 20, 32, etc etc.

Random Row Order
What do I need to put into a SQL statement for it to retrieve 100 Random Rows (containing certain criteria in the WHERE clause). Something like this (theoretically) is what i'm looking for:

SELECT *
FROM table
WHERE field="yes"
ORDER RANDOM
LIMIT 0, 25

Any Ideas?

Random Ports
mysql 5.0 was working fine in Debian "etch".

Then I installed awstats, which changed something somewhere and now the server starts on a random port. However, no clients can FIND it! How do I resolve this issue?

Generating Random
I have a database set up and all I need to do is make a field with just something random for each entry that is already in the database. Something just 5 numbers and/or letters is fine. How would I go about doing this?

Selecting Random
so i have a table with three columns: sentence, auto_id and createdate (datetime). I want to display random sentences (from sentence) up to 15 from each day. That's easy enough:
$query = "SELECT sentence FROM sentences WHERE createdate LIKE '$today%' ORDER BY RAND() limit 15";
but now I'm running into a problem and that is if a user submits a huuuge block of sentences into this db I end up displaying mostly that users stuff (and often only that users stuff) so I'd like to come up with a query that will use the information afforded it to select:only one or two randomly selected sentence(s) from each entry and return it randomly up to 15.
what does that query look like?

Select Random Row
I have read that

"select * from table_name order by rand()" will gives poor performance when there are thousands of rows in the table. Does the same holds true even if there is a where condition (returns only hundres of rows at most)?
For example:

"select * from table_name where col1=2 order by rand()". If I have thousands of rows in the table but the where condition limit it down to couple of hundred, will this query still gives bad performance?

Random Insert
Has anyone ever experience mysql randomly entering blank data into without an INSERT?
I have a login/sigup system that seems to insert blank data into a table w/o anyone actually signing up.

It only happens to 2 tables when in the process of the signup writes to 5 tables.

What really gets me is that the insert date is exactly the same every time this happens.

12-31-69 04:00:00 PM

What is also interesting is that the .cgi form does not allow any blank fields.

Random Record
I have a table that has several hundered rows with one column being "LastRated", a DATETIME field.
Currently I am pulling a random record from this table with:

SELECT * FROM tblImages ORDER BY Rand() LIMIT 1

Is there anyway I can re-work this query so that it is much more likely to pick on of the records with an older LastRated?Even if it were to only randomly pull a record from the 50 oldest LastRateds that would be fine.

Auto_Increment At Random
im using the MySQL Control Panel 0.9.4. I can do this with SQL so I assume MySQL can as well?:

I want to have a user fill out a form on my site, then when their information is inserted into the database I want it to automatically generate the User ID #. I went ahead and set this up and checked, â€œAuto_Incrementâ€. And it works. The only problem is that it increases the increment by 1 each time. I have a Length of 11 and want it to randomly mix up all the numbers. I want to avoid a member changing the 1 increment to access someone elseâ€™s account. Is there a way to have the number generate at random rather than by 1 increment at a time in MySQL?

Random Selections
I am trying to select a certain number of records out of a table. The catch is that I want the records to be chosen at random. Is there a way in mysql that would allow me to a random selection?

Checking For Random ID
Let's say that I generate a randon id using this method (if there is a better one, please post it):

$id = sha1(mt_rand(1, 500));

If it is crucial to make sure that this does not exist in the database (let's say I need a million of these in my database, and not a single one can be the same as another), how would I check that?

Do I just retrieve all the ids from the table and compare them to the newly generated one? What is the impact on resources/speed?

Random Order
Suppose I have a table with 10 records in it.
I can select them with either ascending or desending order. Is it possible that I can select them in random order, so sequences is different each time?

Random Output
is there any way to do a mysql RANDOM output? Like e.g. if I got a table and I want to have every row displayed of the table, but by random (changing whenever you look at it), but every entry being displayed once

Random Selection
Basically I have this:

(category INT,type INT,item TEXT)

I want to be able to select (say) 20 distinct random records with category=x and type=y.
How can I do that ?

Random Rows
I understand the preferred way to select random rows is as follows:
SELECT * FROM tbl_name ORDER BY RAND();
my problem is, I also need "order by" to sort the results in alphabetical order. how can I do both?
in other words, let's say I have 200 rows. I want 30 randomly selected, but I want those 30 alphabetised.

Random Index
Is there a way to display search results in random order?

Random Number
I have a list of email addresses in a text file.
I want to create a table and have them added to a new table with 3 columns/fields - ID, Email, and UID

ID - Integer - auto increment
Email - varchar 40
UID - Unique random value 15 characters (varchar)

How do I get the UID added such that it is random and unique.
Can MySQL do this?

Random Records
Im told that ORDER by rand() isnt a good idea to use on large tables, so Im interested to know how else this could be done. I need to display 20 random records (there are conditions in the WHERE clause as well).

Random Join
What is the best way to randomly join 2 tables?

table "users" contains a list of unique User IDs.

table other_user_data contains a list of User IDs (and other fields) that do not match the user IDs in the first table. There can be multiple rows per user ID in other_user_data.I would like that every time i run the query, the users in the users table should be matched with any other user in the second table and return users.user_id and multiple rows from other_user_data that "match".

Random Topic
www.habbomedia.ca/topic. I made that using php and html but what i want to do is add theinto different tables, one english and one chinese, etc... and they are acessed by a php file example english.php, that reads only the english table's words and displays a random on upon each refresh.... is this possible?

Rows Disappearing At Random
I've just confirmed that rows in my MySQL database are disappearing at random. It seems that the larger my tables get, the more often rows disappear. I am running MySQL 5.0.27-standard-log. How can I prevent this?

How To Get 5 Random Returns From Mysql?
I have a table that stores username and image_id. Like -

user_name | image_id

user_1 | image_1
user_2 | image_2
user_3 | image_33
user_2 | image_56 etc.

I need to get 5 random image_ids, each from a different user. I was considering using rand(), but google told me that this will slow down the sql server, bigger the database.

PHP

SELECT DISTINCT user_name, image_id FROM tablename ORDER BY RAND() LIMIT 5

Generate Random String
I'm writing a sequence of insert statements which I want to execute via the phpmyadmin interface to insert records into a table.

The table contains only two columns: name and uid. The first column is obvious. The second column is just a random string of 32 characters.

My insert statement are of the form ...

INSERT INTO user_table (name, uid) VALUES('Some Body', &#55616;&#56537;dfdfkdsfjdfddf33dfkdfk');
INSERT INTO user_table (name, uid) VALUES('Another Body', 'ewrkdk32832wrekldfnejdsfdsf');

I want to know if there is an easier way write the insert statement so that the statement will generate the random 32 character string for me.

Such as something of the form....

INSERT INTO user_table (name, uid) VALUES('Some Body', RANDOM(32));

Or is there a way to tell phpmyadmin to populate the "uid" column of existing records with a random string if that column contains NULL.

Generating A Random 'Tip Of The Day'
I've recently been playing around creating a Google gadget with a kind of 'Tip of the Day' theme.

At the moment it gets a different random tip from the database every time the page is refreshed. What I want to do is set it so it will show the same tip for 24 hours and then change, so it will take longer to run out of original tips

I'm just wondering the best way to do this. Should I write a daily cron job to fetch a random tip and send it to a different table, or to generate the page dynamically? Or is there a better way to do it using MySQL? Have a date field in the tips database and allocate each tip to an upcoming day? Or do it with PHP somehow? Or even cookies, if it comes to that?

Random Password Generator?
I have 3 tables as follows:

Lesson:

lesson_id | lesson_name |
-------------------------|
1 | Lesson One |
2 | Lesson Two |
3 | Lesson Three |
4 | Lesson Four |
--------------------------

student_lesson:

lesson_id | student_id |
----------------------|
1 | 44 |
1 | 45 |
1 | 46 |
1 | 47 |
----------------------|

class_lesson:

lesson_id | class_id |
-------------------|
1 | 901 |
2 | 902 |
3 | 903 |
4 | 904 |
-------------------|

The following query:

SELECT crs_lesson.lesson_id, lesson_name,
count(crs_student_lesson.student_id) AS student_count,
count(crs_class_lesson.class_id) AS class_count
FROM crs_lesson
LEFT JOIN crs_student_lesson USING (lesson_id)
LEFT JOIN crs_class_lesson USING (lesson_id)
GROUP BY crs_lesson.lesson_id

Produces the following result:

lesson_id | lesson_name | student_count | class_count |
------------------------------------------------------|
1 | Lesson One | 4 | 4 |
2 | Lesson Two | 0 | 0 |
3 | Lesson Three | 0 | 0 |
4 | Lesson Four | 0 | 0 |
------------------------------------------------------|

This shows that the second count is always set to the same value as the first count even though the values should be different.

Saving Random Query
I want to select about 10 rows randomly from my table every day. I know how to randomly select rows, but is it possible to save that select so that I get the same 10 randomly selected rows again?

Random Empty Records
I run a WAMP webserver and host an online entry form for a local running race. I'm getting random empty records inserted into the database. For every 50 or so legitimate entries, I get a blank record or two. A few people have said they signed up, but their infomation is not in the database. At first I thought it was user error, but now I'm not so sure. Has anyone else experienced a problem with random blank records inserted in thier database? Any guesses?

Random Data

I keep getting random data put in to by database??? Any idea how to stop this?