MYSQL question showing random row from database

[rcep]

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]

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]

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]

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]

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]

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]

thanks better than nothing :P

please guys more details if possible

 

[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

// 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.