MYSQL question showing random row from database

rcep · 03-03-2008, 09:12 AM

MYSQL question showing random row from database

i am trying to mess around with tags on my site
i would like to show random row content from the available rows in my mysql table
i want to run something like the cloud of tags
how can i do this ?

Regards

Daniel · 03-03-2008, 10:18 AM

Either ORDER BY RAND() (Slightly slower)
Or use SELECT COUNT(*) to get the number of rows then use rand($start, $count); to get a random row number, then use select to grab that row or rows.

rcep · 03-03-2008, 10:39 AM

thx the count(*) was what am searching for
anyway is it a good idea to place like 30 random links to 30 different topic at the bottom of each page ?

monaco · 03-03-2008, 10:52 AM

Originally Posted by rcep

thx the count(*) was what am searching for
????: NamePros.com http://www.namepros.com/programming/439768-mysql-question-showing-random-row-database.html
anyway is it a good idea to place like 30 random links to 30 different topic at the bottom of each page ?

I guess this depends on what your goal is in doing this. Why 30 random topics? Why not 30 topics that are somehow related?

rcep · 03-03-2008, 10:58 AM

i want it for 2 perposes
1) in a directory i want to give the chance for all pages in directory to get index faster ( because files not getting indexed
2) add random topic in my site

how can i make the related thing is it by % like% ?

monaco · 03-03-2008, 11:38 AM

Originally Posted by rcep

i want it for 2 perposes
????: NamePros.com http://www.namepros.com/showthread.php?t=439768
1) in a directory i want to give the chance for all pages in directory to get index faster ( because files not getting indexed
2) add random topic in my site

how can i make the related thing is it by % like% ?

Related would be tricky...you'd actually need to have something like a keyword-based index to get that done (in the simplest way).

I would do it like this...

* A table containing keywords (A)
* A table containing a list of pages (B)
* A table relating (A,B) with unique constraint (A,B) and frequency of occurrence.

To load the "related" links, do a keyword inventory on the page and take as many keywords as you need to fill 30 spots, and match up highest occurrence in page with occurrence in the relation table above.

Sorry if all that sounded vague, I'm kinda creating the idea in my head as I type, LOL.

rcep · 03-03-2008, 12:00 PM

thanks better than nothing

please guys more details if possible

xrvel · 03-04-2008, 02:40 AM

Hi, Ryan.

Usually, i push available row IDs to an array.
And choose random IDs from the array (which means 30 IDs in your example).
Next, i take the data.

Here's the code

PHP Code:

  // Here is our storage array

????: NamePros.com http://www.namepros.com/showthread.php?t=439768
$ids = array();

 
// Get available IDs and store it in our array

$q = "SELECT product_id FROM products WHERE product_price > 100 LIMIT 0,100";

$q = mysql_query($q);

 
while ($r = mysql_fetch_row($q)) {

   $ids[] = $r[0];// push new ID

}

 
// We will store chosen IDs here

$chosen_ids = array();

 
// Get 30 random rows

$random_n = 30;

 
while (count($chosen_ids) < $random_n) {// while we want more random data

   $index = rand(0, count($ids));// get random index

????: NamePros.com http://www.namepros.com/showthread.php?t=439768
   $chosen_ids[] = $ids[$index];// push random element

   unset($ids[$index]);// remove chosen element

}

 
// We have chosen ids,

// lets grab the data with single execution

 
// We will make OR clause such as

//    WHERE id=3 OR id=8 OR id=9

$or_clause = '';

foreach ($chosen_ids as $id) {

   if ($or_clause == '') {

      $or_clause .= ' product_id = ' . $id;

   } else {

      $or_clause .= ' OR product_id = ' . $id;

   }

}

// OR clause completed

 
// Let's read the random rows

$q = " SELECT * FROM products WHERE $or_clause ORDER BY product_id ASC";

$q = mysql_query($q);

while ($r = mysql_fetch_array($q)) {

   // here's your random data

   print_r($r);

}

Regards,
Kurniawan.

03-03-2008, 09:12 AM	THREAD STARTER #1 (permalink)
rcep Senior Member Join Date: Jul 2005 Location: lebanon Posts: 2,458 Follow @ryansdistrict	MYSQL question showing random row from database MYSQL question showing random row from database i am trying to mess around with tags on my site i would like to show random row content from the available rows in my mysql table i want to run something like the cloud of tags how can i do this ? Regards __________________ Download free MP3 _____________ Get unique visitors 4 NPs (over 800 satisfied) << Check the Holidays offer _____ Email me for fast response ryan [at] ryansdistrict.com ________ My 9000NPs R 4 SALE cheap- Contact me!

03-03-2008, 10:18 AM	#2 (permalink)
Daniel Danltn.com Join Date: May 2007 Location: Danltn.com / Nottingham, UK Posts: 1,201	Either ORDER BY RAND() (Slightly slower) Or use SELECT COUNT() to get the number of rows then use rand($start, $count); to get a random row number, then use select to grab that row or rows. __________________ :lala: VERY CHEAP ADVANCED* BULK DOMAIN-STATISTICS LOOKUP** :kickass: Danltn's Blog - Daniel Neville's Site Spoof your links! Submit your blog NOW! Putting bloggers on the map!

03-03-2008, 10:39 AM	THREAD STARTER #3 (permalink)
rcep Senior Member Join Date: Jul 2005 Location: lebanon Posts: 2,458 Follow @ryansdistrict	thx the count() was what am searching for anyway is it a good idea to place like 30 random links to 30 different topic at the bottom of each page ? __________________ Download free MP3 _____________ Get unique visitors 4 NPs (over 800 satisfied) << Check the Holidays offer _____ Email me for fast response ryan [at] ryansdistrict.com ________ My 9000NPs R 4 SALE cheap- Contact me!*

03-03-2008, 10:58 AM	THREAD STARTER #5 (permalink)
rcep Senior Member Join Date: Jul 2005 Location: lebanon Posts: 2,458 Follow @ryansdistrict	i want it for 2 perposes 1) in a directory i want to give the chance for all pages in directory to get index faster ( because files not getting indexed 2) add random topic in my site how can i make the related thing is it by % like% ? __________________ Download free MP3 _____________ Get unique visitors 4 NPs (over 800 satisfied) << Check the Holidays offer _____ Email me for fast response ryan [at] ryansdistrict.com ________ My 9000NPs R 4 SALE cheap- Contact me!

03-03-2008, 12:00 PM	THREAD STARTER #7 (permalink)
rcep Senior Member Join Date: Jul 2005 Location: lebanon Posts: 2,458 Follow @ryansdistrict	thanks better than nothing please guys more details if possible __________________ Download free MP3 _____________ Get unique visitors 4 NPs (over 800 satisfied) << Check the Holidays offer _____ Email me for fast response ryan [at] ryansdistrict.com ________ My 9000NPs R 4 SALE cheap- Contact me!

03-04-2008, 02:40 AM	#8 (permalink)
xrvel i love automation Join Date: Nov 2007 Location: xrvel.com Posts: 1,620 Follow @xrvel	Hi, Ryan. Usually, i push available row IDs to an array. And choose random IDs from the array (which means 30 IDs in your example). Next, i take the data. Here's the code PHP Code: // Here is our storage array ????: NamePros.com http://www.namepros.com/showthread.php?t=439768 $ids = array(); // Get available IDs and store it in our array $q = "SELECT product_id FROM products WHERE product_price > 100 LIMIT 0,100"; $q = mysql_query($q); while ($r = mysql_fetch_row($q)) { $ids[] = $r[0];// push new ID } // We will store chosen IDs here $chosen_ids = array(); // Get 30 random rows $random_n = 30; while (count($chosen_ids) < $random_n) {// while we want more random data $index = rand(0, count($ids));// get random index ????: NamePros.com http://www.namepros.com/showthread.php?t=439768 $chosen_ids[] = $ids[$index];// push random element unset($ids[$index]);// remove chosen element } // We have chosen ids, // lets grab the data with single execution // We will make OR clause such as // WHERE id=3 OR id=8 OR id=9 $or_clause = ''; foreach ($chosen_ids as $id) { if ($or_clause == '') { $or_clause .= ' product_id = ' . $id; } else { $or_clause .= ' OR product_id = ' . $id; } } // OR clause completed // Let's read the random rows $q = " SELECT * FROM products WHERE $or_clause ORDER BY product_id ASC"; $q = mysql_query($q); while ($r = mysql_fetch_array($q)) { // here's your random data print_r($r); } Regards, Kurniawan. __________________ Xrvel \| Free Wordpress Plugins \| Desain \| Bodoh \| WP License Last edited by xrvel; 03-04-2008 at 02:48 AM.

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)