MySQL select from multiple identical tables and display results randomly with php

Question

Hello I would like to query multiple identical tables in my db which has different prefixes and than display the results randomly but somehow I need to track the origin of the item and I couldn't figure out how

I do the query like this because I don't have access to information_schema

$query = "SHOW TABLES FROM mydb WHERE RIGHT( tables_in_mydb, 5 ) = 'table'";
$res = mysql_query($query);
$num = mysql_num_rows($res);

while($row = mysql_fetch_row($res)) {

$numbers = explode('_', $row[0]);

  if($num > 0) {
    $q = "SELECT `this`, `that`, `something` FROM ".$numbers[0]."_idetinticaltables"; // :)
    $r = mysql_query($q);
    while($c = mysql_fetch_array($r)) {
       /*display the results randomly with an identifier where the come from*/
    }
  }
}

store the $numbers[0] somewhere, since that's what tells you which table the data came from. — Marc B, Dec 14 '12 at 14:56

score 0 · Answer 1 · answered Dec 14 '12 at 14:18

0

You could use ORDER BY RAND() to randomly sort it

answered Dec 14 '12 at 14:18

Moseleyi

2,585
1
24
46

its a good idea but it will randomise only table by table if I print the results out than it will look like this: table1 in random order, table2 in random order, tableX in random order, and I would like to randomise the complete result like: table4, table1, table3, table2 – Laci K Dec 14 '12 at 15:04
Well, you can't use ORDER BY on SHOW TABLES, so you would need to put the references to an array and then use shuffle($ar) on those names and use a loop to display also randomized results. How does that sound? – Moseleyi Dec 14 '12 at 15:12

score 0 · Answer 2 · edited Dec 14 '12 at 20:08

0

$aa=array()   
while($c = mysql_fetch_array($r)) 
{
   /*display the results randomly with an identifier where the come from*/
   $aa[]=$c;
}
echo $aa; // print "Array"

edited Dec 14 '12 at 20:08

hakre

193,403
52
435
836

answered Dec 14 '12 at 14:50

Premkumar S

263
3
8

myabe I'm wrong but if you echo $aa outside the loop than it will print only the first item of the array. – Laci K Dec 14 '12 at 14:58
@LaciK: Well, it just outputs `Array` and that's it. Not an answer, don't bother with it. – hakre Dec 14 '12 at 16:18

score 0 · Answer 3 · edited May 23 '17 at 11:47

The following might work:

Get the list of the tables you're interested in. You already do that.

Create a UNION of multiple SELECT statements. Each SELECT statement differs for the table being selected from and you add a column set to the name of the table (so you can identify it later):

(SELECT *, TABLENAME = 'first_name_of_table' FROM first_name_of_table ...)
UNION
(SELECT *, TABLENAME = 'second_name_of_table' FROM second_name_of_table ...)
UNION
...
ORDER BY RAND() LIMIT 10;

Because it is a UNION you can randomize the whole order then. See How can i optimize MySQL's ORDER BY RAND() function? because it is not that trivial to do well, the example above is only to have an ORDER BY and LIMIT clause placed there. With many entries in your tables, it will kill your server.

MySQL select from multiple identical tables and display results randomly with php

3 Answers3