Choose a random row from query

[transparencia]

The following query returns 10 results. How can I randomly choose one of that results?

 

 $request = db_query("
      SELECT att.ID_ATTACH, att.ID_MSG, att.filename, mem.ID_MEMBER,
          m.ID_TOPIC, m.subject, t.ID_BOARD  
      FROM smf_attachments AS att
         INNER JOIN smf_messages AS m ON (m.ID_MSG = att.ID_MSG)
         INNER JOIN smf_topics AS t ON (t.ID_TOPIC = m.ID_TOPIC)
         LEFT JOIN smf_members AS mem ON (mem.ID_MEMBER = m.ID_MEMBER)
         LEFT JOIN smf_boards AS b ON (b.ID_BOARD = t.ID_BOARD)
      WHERE att.attachmentType = 0
          AND t.ID_BOARD IN (65,66)
      ORDER BY att.ID_ATTACH DESC
      LIMIT 10");

[JAY6390]

Why not just use ORDER BY RAND() LIMIT 1

[transparencia]

Why not just use ORDER BY RAND() LIMIT 1

 

That would return any of the results. I want to return a random row from the last 10 rows inserted in that table.

[sasa]

 $request = db_query("SELECT * FROM
      (SELECT att.ID_ATTACH, att.ID_MSG, att.filename, mem.ID_MEMBER,
          m.ID_TOPIC, m.subject, t.ID_BOARD  
      FROM smf_attachments AS att
         INNER JOIN smf_messages AS m ON (m.ID_MSG = att.ID_MSG)
         INNER JOIN smf_topics AS t ON (t.ID_TOPIC = m.ID_TOPIC)
         LEFT JOIN smf_members AS mem ON (mem.ID_MEMBER = m.ID_MEMBER)
         LEFT JOIN smf_boards AS b ON (b.ID_BOARD = t.ID_BOARD)
      WHERE att.attachmentType = 0
          AND t.ID_BOARD IN (65,66)
      ORDER BY att.ID_ATTACH DESC
      LIMIT 10) 
      ORDER BY RAND() LIMIT 1");

[transparencia]

 $request = db_query("SELECT att.ID_ATTACH, att.ID_MSG, att.filename, mem.ID_MEMBER,
          m.ID_TOPIC, m.subject, t.ID_BOARD  
      FROM smf_attachments AS att
         INNER JOIN smf_messages AS m ON (m.ID_MSG = att.ID_MSG)
         INNER JOIN smf_topics AS t ON (t.ID_TOPIC = m.ID_TOPIC)
         LEFT JOIN smf_members AS mem ON (mem.ID_MEMBER = m.ID_MEMBER)
         LEFT JOIN smf_boards AS b ON (b.ID_BOARD = t.ID_BOARD)
      WHERE att.attachmentType = 0
          AND t.ID_BOARD IN (65,66)
              ORDER BY RAND() LIMIT 1");

 

This code doesn't work. I'm sorry if the problem isn't PHP specific but I actually thought it could be solved using only PHP.

 

What I want to do is choose a random result from the last 10 rows of a table. Any further help?

[Psycho]

The code doesn't work "how"? Are you getting errors, if so what are they? Did the original query work? If so, I don't see why sasa's query wouldn't work for what you want. He basically used your query as a sub query to get the last 10 records, then the outer query would select one of those records.

 

yes, of course you could do this through PHP, but why? Learn to use the power of databases instead of rebuilding the wheel. But, if you must know, if you wante to do it in PHP you could just dump the results into a multi-dimensional array and then randomize the array. But, using a query is really the way to go.

 

EDIT: I just realized the code you posted in your reply is NOT the same code sasa posted. Did you TRY the code he posted or did you not realize it was modified?

[Buddski]

As to not run into some error later I would suggest using sasa's code but you must add an alias to the derived table or MySQL will have a cry about it..

 

$request = db_query("SELECT * FROM
      (SELECT att.ID_ATTACH, att.ID_MSG, att.filename, mem.ID_MEMBER,
          m.ID_TOPIC, m.subject, t.ID_BOARD 
      FROM smf_attachments AS att
         INNER JOIN smf_messages AS m ON (m.ID_MSG = att.ID_MSG)
         INNER JOIN smf_topics AS t ON (t.ID_TOPIC = m.ID_TOPIC)
         LEFT JOIN smf_members AS mem ON (mem.ID_MEMBER = m.ID_MEMBER)
         LEFT JOIN smf_boards AS b ON (b.ID_BOARD = t.ID_BOARD)
      WHERE att.attachmentType = 0
          AND t.ID_BOARD IN (65,66)
      ORDER BY att.ID_ATTACH DESC
      LIMIT 10) as `table_thing`
      ORDER BY RAND() LIMIT 1");

[transparencia]

The code doesn't work "how"?

Are you getting errors, if so what are they?

Did the original query work?

Did you TRY the code he posted or did you not realize it was modified?[/b]

 

The code runs forever until a time out. No errors are returned. The original code, the code I posted works and returns the last 10 rows in order of a column.

 

I tried the code he posted and then my ignorance led me to believe that he had mistaken himself so I posted the code I thought he actually wanted to post (   ).

 

The thing is it still doesn't work, even with Buddski suggestion.

[Psycho]

Kind of hard to debug without your database. I've done a simple test within my own database and the logic provided above should work. I have also reviewed Buddski's posted query and compared it to your original query and see no reason it should not work.

 

The only thing I can think of is that the database structure is not normalized and/or the original query is not as efficient as it could be. As I stated above you could do it in PHP if you can't figure out a workable solution using a query.

[transparencia]

Kind of hard to debug without your database. I've done a simple test within my own database and the logic provided above should work. I have also reviewed Buddski's posted query and compared it to your original query and see no reason it should not work.

 

The only thing I can think of is that the database structure is not normalized and/or the original query is not as efficient as it could be. As I stated above you could do it in PHP if you can't figure out a workable solution using a query.

 

No problem, I came up with this code:

$i=0;
  while ($row =  mysql_fetch_row($request))
 { $product[$i] = $row;
 	$i++;
}
$j = rand ( 0  , 10  );
print_r($product[$j]);		

 

Do you think it will slow down the website much?

[Psycho]

Probably not. But that code should be modified. You are creating a random number from 0 to 10 (i.e. 11 possible options) whereas you only have 10 records from your query. Besides, why hard code the limit for the random number? If you were to ever change your query to a different number of records you would have to remember to change that too. Better to write the code so it self-adjusts:

 

while ($row =  mysql_fetch_row($request))
{
    $products[] = $row;
}
$randomProduct = $products[array_rand($products)];