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?
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):
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 citrina f. alba'),
select * from temp_table t left join temp_table t1 on t.latin<> t1.latin
ORDER BY RAND()
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.
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.