Now, I want to display 5 jobs within the Admin Category, but I want them pulled randomly from the database. ORDER BY RAND won't work because there are only 4 records. I need to display 5 records as it doesn't matter if the same record is displayed more than once. How do I do this?
select * from temp_table t left join temp_table t1 on t.f<> t1.f
ORDER BY RAND()
limit 5
As you said, you don't care if same record appears more than once.
__________________
Found a flower or bug and don't know what it is ? agrozoo.net galery
if you don't spot search button at once, there is search form: agrozoo.net galery search
temp_table? t.f? Where did those come from, Bubikol???
And if there are only 4 records in temp_table, that will *NOT* get more than 4 records.
One simple answer:
Code:
SELECT X.* FROM
( SELECT 1 AS dummy, * FROM table
UNION
SELECT 2 AS dummy, * FROM table
) AS X
WHERE X.jobcategory LIKE '%Admin%'
ORDER BY RAND() LIMIT 5
But it's not terribly efficient.
I would be strongly tempted to do this in PHP/JSP/ASP code, instead.
Read the 4 records into an array and then just pick a random array element 5 times.
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
I gather f is field and t is table to enlightened people (-:
The proof that you get what I state in that post, 4 records produces 5 (more actualy):
Code:
DROP TABLE IF EXISTS `temp_table`;
CREATE TABLE IF NOT EXISTS `temp_table` (
`latin` varchar(50) COLLATE utf8_slovenian_ci NOT NULL,
UNIQUE KEY `latin` (`latin`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_slovenian_ci;
-- Dumping data for table test.temp_table: 4 rows
DELETE FROM `temp_table`;
INSERT INTO `temp_table` (`latin`) VALUES
('Amanita battarrae'),
('Amanita ceciliae'),
('Amanita citrina f. alba'),
('Amanita crocea');
select * from temp_table t left join temp_table t1 on t.latin<> t1.latin
ORDER BY RAND()
limit 5
__________________
Found a flower or bug and don't know what it is ? agrozoo.net galery
if you don't spot search button at once, there is search form: agrozoo.net galery search
Last edited by BubikolRamios; 11-25-2012 at 11:14 PM..
I would be strongly tempted to do this in PHP/JSP/ASP code, instead.
Read the 4 records into an array and then just pick a random array element 5 times.
I agree - as more than the total number of records in the table is needed it will always be more efficient to just rread in all the entries and then select them without having to read the same record from the database more than once.
Only when the number of records required is significantly less than the number of entries in the table would 'sorting' the table in the database be more efficient and that too becomes inefficient when you have a large enough number of records and only need to retrieve a small number.
PHP code isn't necessary. You've asked a mysql question and been given a mysql answer without regard to the front end application (the two don't have to be used together in other words).
If you mean you'd like to use PHP to step through those results and point them to another page you can indeed do that.
Do you have phpmyadmin at all? If so you can run the query above and then click on the PHP button and it will generate necessary code.
If you don't have phpmyadmin then you can look at the mysql functions in the php manual and it will show you examples. look in the php manual under mysql_fetch_array for an example.
He would like to know how to do as Felgall suggested in post #5.
I don't use PHP, so I'm not going to attempt an answer. (It would be trivial in ASP, where the method GETROWS() converts the entire set of records to an array, automatically. <grin/>)
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
It would be trivial in ASP, where the method GETROWS() converts the entire set of records to an array, automatically. <grin/>)
In PHP mysqli_result::fetch_all also converts the entire set of records to an array, automatically (it used to be done using mysql_fetch_array in the now obsolete mysql_ interface) Presumably there's also a PDO call that does it as well for those using that in PHP.
Before you post, read our: 
