MYSQL question showing random row from database

rcep · 03-03-2008, 08: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, 09: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, 09: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, 09:52 AM

Quote:

Originally Posted by rcep

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 ?

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, 09: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, 10:38 AM

Quote:

Originally Posted by rcep

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

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, 11:00 AM

thanks better than nothing :P

please guys more details if possible

xrvel · 03-04-2008, 01: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

$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

   $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, 08:12 AM	#1 (permalink)
rcep Senior Member Join Date: Jul 2005 Location: lebanon Posts: 2,279 7,614.65 NP$ (Donate)	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 __________________ Most beautiful 100 girls for the year Get unique visitors 4 NPs (over 200 satisfied NPiers) Dating & Love tips, tricks & advice Who is online: 4050 Guests, 43 Users

03-03-2008, 09:18 AM	#2 (permalink)
Daniel Danltn.com Join Date: May 2007 Location: Danltn.com / Nottingham, UK Posts: 1,201 13.51 NP$ (Donate)	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. __________________ VERY CHEAP ADVANCED* BULK DOMAIN-STATISTICS LOOKUP** Danltn's Blog - Daniel Neville's Site Spoof your links! Submit your blog NOW! Putting bloggers on the map!

03-03-2008, 09:39 AM	#3 (permalink)
rcep Senior Member Join Date: Jul 2005 Location: lebanon Posts: 2,279 7,614.65 NP$ (Donate)	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 ? __________________ Most beautiful 100 girls for the year Get unique visitors 4 NPs (over 200 satisfied NPiers) Dating & Love tips, tricks & advice Who is online: 4050 Guests, 43 Users

03-03-2008, 09:58 AM	#5 (permalink)
rcep Senior Member Join Date: Jul 2005 Location: lebanon Posts: 2,279 7,614.65 NP$ (Donate)	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% ? __________________ Most beautiful 100 girls for the year Get unique visitors 4 NPs (over 200 satisfied NPiers) Dating & Love tips, tricks & advice Who is online: 4050 Guests, 43 Users

03-03-2008, 11:00 AM	#7 (permalink)
rcep Senior Member Join Date: Jul 2005 Location: lebanon Posts: 2,279 7,614.65 NP$ (Donate)	thanks better than nothing :P please guys more details if possible __________________ Most beautiful 100 girls for the year Get unique visitors 4 NPs (over 200 satisfied NPiers) Dating & Love tips, tricks & advice Who is online: 4050 Guests, 43 Users

03-04-2008, 01:40 AM	#8 (permalink)
xrvel i love automation Join Date: Nov 2007 Posts: 1,409 987.78 NP$ (Donate)	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 $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 $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 \| Various online tools \| Free Proxy List \| Hidden Proxy Partai Golkar \| Noomle works : Las Vegas Airfare Last edited by xrvel; 03-04-2008 at 01:48 AM.

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