MYSQL :: Rows Disappearing At Random

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.

DB Rows Just "disappearing"
I've been getting complaints lately that entries to a database are just "disappearing", they are entered via PHP, and the system works fine. Only certain people have access to the PHP interface and even of those selected people only 1 or 2 people have the 'power' to delete a row.

But I've been told that after adding an entry and seeing that it is deffinitely there, they come back later to find it's gone and nobody has deleted it. It happens very rarely, there are thousands of database entries but this sort of disappearance has only happened a handful of times.

Can rows just go missing? How?

If it turns out that I can't find the source of the problem then I'll have to just try to retrieve these missing rows everytime one disappears. Is there a way to retrieve 'deleted' rows from MySQL? One has gone missing today so I'd be glad to find out how I could get it back.

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 For Displaying Rows From Database
I did query and get all of the rows in my table test . Each row in my database include fields like this : id , ask , a , b , c , d , answer . All are 7 fields . When display on a page i want it to be displayed in inorder , in random the with the field ID and in the row the 4 fields a,b,c,d are all displayed in an inorder too .

Telling this is so hard to imaging so i am trying to put an example here . This is a projest of mine , doing the work of a test with a question and 4 choies for ppl to choose the answer . Code:

Selecting Random Rows In A VERY Large Table?
I would like to select random rows in a very large mysql table but I don't want to use something like that:

"SELECT * FROM tablename
ORDER BY RAND() LIMIT 1000";
Because it's horribly slow when you have a large table like mine (6 million rows+).

Tables Disappearing
I'm very new to SQL, however, I have the service installed (on an imac os x 10.3.3) it's running fine, the grant tables are fine and passwords have been issued to the accounts, yadda yadda. Yesterday I create a database with a single table in it and added some information to that table. Today...that table is gone...no sign of it.

Occasionally Disappearing Zerofill?
I have a column declared as
`property_no` smallint(4) unsigned zerofill NOT NULL default &#55612;&#57200;',
. When I run this query, the zero fill on property_no disappears:

SELECT prop_id, `order`, p.property_no, p.address, p.tagline, p.thumbs
FROM Featured_prop
LEFT OUTER JOIN(
SELECT properties.property_id, properties.property_no as property_no, properties.address as address, properties.tagline as tagline, properties.thumbs as thumbs
FROM Featured_prop
LEFT OUTER JOIN properties ON properties.property_id = Featured_prop.prop_id
) AS p on p.property_id = Featured_prop.prop_id
ORDER BY `order`
If a query on the table is run without a join, the zerofill is preserved. I'm puzzled, how do I ensure it's preserved regardless of the type of query?
MySql version is 4.1.19.

Disappearing Database Files
Strange problem we have here and I'm looking for some insight on it.

On one of our MySQL machines, everything was working fine until we rebooted a few days ago. Immediately after the reboot, some database connections were failing with:

CODE"1016 - Can't open file: 'xyz.ibd' (errno: 1)"

Disappearing Tables In Windows 98
I think I have experienced this with 3 different computers. I'm not
sure, but I think all of them were using Windows 98 and MySQL version 3.x.

The problem is that some of the tables disappear from the database,
usually the tables that have most of data have disappeared so I think it
might be a bug in Windows 98 file handling.

Has anyone else experienced this or similar things?

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 Data
I keep getting random data put in to by database??? Any idea how to stop this?

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 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?

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?

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?