Hello there,
can someone tell me what is the problem with my php script? becouse of this script my website go down and server block after i restart the apache in 2 or 3 minutes with this script my website go down again... hope someone can tell me what is the mistake with this

PHP Code:

{php}

 $db2table  = 'tags';

 $limit          = 40;

 $result = mysql_query("SELECT * FROM " . $db2table . " ORDER BY RAND() LIMIT $limit");

 while ($web_tag = mysql_fetch_assoc($result)) {
  $tag_id        = $web_tag['TID'];
  $tag_name      = $web_tag['Tag'];
 $unwanted_array = array(
  '#192;'=>'a',
  '#193;'=>'a',
  '#194;'=>'a',
  '#195;'=>'a',
  '#196;'=>'a',
  '#197;'=>'a',
  '#198;'=>'a',
  '#199;'=>'c',
  '#200;'=>'e',
  '#201;'=>'e',
  '#202;'=>'e',
  '#203;'=>'e',
  '#204;'=>'i',
  '#205;'=>'i',
  '#206;'=>'i',
  '#207;'=>'i',
  '#209;'=>'n',
  '#210;'=>'q',
  '#211;'=>'q',
  '#212;'=>'q',
  '#213;'=>'q',
  '#214;'=>'q',
  '#216;'=>'o',
  '#217;'=>'u',
  '#218;'=>'u',
  '#219;'=>'u',
  '#221;'=>'y',
  '#222;'=>'p',
  '#223;'=>'b',
  '#224;'=>'a',
  '#225;'=>'a',
  '#226;'=>'a',
  '#227;'=>'a',
  '#228;'=>'a',
  '#229;'=>'a',
  '#230;'=>'ae',
  '#231;'=>'c',
  '#232;'=>'e',
  '#233;'=>'e',
  '#234;'=>'e',
  '#235;'=>'e',
  '#236;'=>'i',
  '#237;'=>'i',
  '#238;'=>'i',
  '#239;'=>'i',
  '#240;'=>'o',
  '#241;'=>'n',
  '#242;'=>'o',
  '#243;'=>'o',
  '#244;'=>'o',
  '#245;'=>'o',
  '#246;'=>'o',
  '#248;'=>'o',
  '#249;'=>'u',
  '#250;'=>'u',
  '#251;'=>'u',
  '#252;'=>'y',
  '#253;'=>'y',
  '#254;'=>'p',
  '#255;'=>'y',
  '#34;'=>'-',
  '#39;'=>'-',
  '#60;'=>'-',
  '#62;'=>'-',
  ' '=>'-',
  ','=>'-',
  '('=>'-',
  ')'=>'-',
  '<'=>'-',
  '>'=>'-',
  '_'=>'-',
  '/'=>'-',
  '?'=>'-',
  '&'=>'-',
  '"'=>'-',
  '!'=>'-',
  ':'=>'-',
  ';'=>'-',
  '+'=>'-',
  '%'=>'-',
  '$'=>'-',
  '^'=>'-',
  '*'=>'-',
  '{'=>'-',
  '}'=>'-',
  '['=>'-',
  ']'=>'-',
  '#'=>'-',
  '.'=>'-',
  '\\'=>'-',
  '\|'=>'-',
  '  '=>'-',
  '@'=>'-',
  '¼'=>'-',
  '\''=>'-' );

  $tag_name2     = strtr( $tag_name, $unwanted_array ); 
  $tag_name2     = str_replace(' ', '-', $tag_name);
  $tag_name2     = str_replace(' ', '-', $tag_name2);
  $tag_name2     = str_replace('  ', '-', $tag_name2);
  $tag_name2     = str_replace('  ', '-', $tag_name2);
  $tag_name2     = str_replace('--', '-', $tag_name2);
  $tag_name2     = str_replace('--', '-', $tag_name2);
  $tag_name2     = str_replace('--', '-', $tag_name2);
  $tag_name2     = str_replace('--', '-', $tag_name2);
  $tag_name2     = strtolower($tag_name2);

  $tag_color     = $web_tag['Color'];


  $tags = '<a href="' . $tag_name2 . '-' . $tag_id . '.html" title="' . $tag_name . '"><font style="color: ' . $tag_color . ';">' . $tag_name . '</font></a> 
';
  echo $tags;
 }
 mysql_close();
{/php} 


this php script i use on .tpl file

I'm guessing it has something to do with your query. ORDER BY RAND() is a bad idea on a large table. From my understanding, when you do this mysql has to execute rand() for every row on the table.

and is another way to i can make this ? thank you

I'm not aware of a perfect solution to this problem. I did some quick research and it seems that the better solutions get the more complicated they are:
http://www.google.com/search?hl=en&s...nd+alternative

One thing you could, that would require two queries and still wouldn't be a perfect solution, would be to get the number of rows in the table, choose a random number between zero and the number of rows minus 40, and select 40 rows starting from that number.

PHP Code:

$count; //assuming you've already retrieved the number of rows

$start = rand(0, $count - $limit);
$result = mysql_query("SELECT * FROM $db2table LIMIT $start, $limit"); 


Clearly this isn't random, but depending on what you're doing it may be close enough. Performance wise it should be much better than what you're doing now.

If your table has sequential numeric ids and there are no holes (ie no rows have been deleted) you can just generate random ids in php and select those ex:

SELECT * FROM table WHERE id IN(4, 2, 9, 11, 3, 7);

but that is a big assumption.

Edit:
It just occurred to me that if there are only a small number of holes in the table it would be possible to account for them by generating a larger list of ids and then limiting the result set to 40. There is still a chance that you won't get enough rows depending on how many and how big the holes are.

hello there,
i try this command

PHP Code:

 $result = mysql_query("SELECT * FROM " . $db2table . " where TID=(" . rand(1,100000) . ", " . rand(1,100000))); 


but is not working, can you please tell me what i mistake ? i want to add more rand(1,100000)

There is a problem with your syntax. The query should be of the form:

SELECT * FROM table WHERE TID IN(...)

There are a few problems with your approach:
1. Use a loop to generate the query.
2. Your current method does not account for duplicates
3. You should only use this method if you are certain there are no gaps in the TID values

PHP Code:

$limit; //how many rows you want
$count; //total number of rows

$rand_ids = array();
while(count($rand_ids) <= $limit)
{
     $id = rand(1, $count);
     if(!in_array($id, $rand_ids))
          $rand_ids[] = $id;
}

$rand_ids = implode(',', $rand_ids);

$result = mysql_query("SELECT * FROM $db2table WHERE TID IN($rand_ids)");