0

i have to tell you this is based on a very simple php theory but it is not simple as it appears.

this is the scenario

I have 10 tables known as(table_1,table_2,table_3 etc), currently i want get the result set of each of those tables inside a loop. so in order to fullfill my requirement when i use the below code it returns an error

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in

The Code

$table_count = mysql_query("SELECT TABLE_NAME FROM information_schema.tables WHERE 
table_schema = 'milepostdb' AND table_name LIKE 'table_%' ");

while($row = mysql_fetch_array($table_count)){
$table = $row["TABLE_NAME"];

$excute = mysql_query("CALL Dummy_2('$table')");
$result = mysql_fetch_assoc($excute);//line which triggers the error
var_dump($result);
}

But if i just hard code a table name and put it it works fine

$var = 'table_1';
$excute = mysql_query("CALL Dummy_2('$var')");
$result = mysql_fetch_assoc($excute);
var_dump($result);

or

$excute = mysql_query("CALL Dummy_2('table_1')");
$result = mysql_fetch_assoc($excute);
var_dump($result);

both the above codes works well.

Now can any body tell why does the variable that is passed through $table = $row["TABLE_NAME"]; returns an error when normal variable that is assigned works fine. whats the difference between the two approaches???

Prog_Rookie
  • 446
  • 7
  • 23
  • 2
    You forgot to call `mysql_query()`. – Barmar Jan 02 '15 at 08:33
  • sorry... thats not the case... thats a mistake while posting the question. i just edit that – Prog_Rookie Jan 02 '15 at 08:37
  • 1
    The error means that `mysql_query()` is returning `false`, which means you have a SQL error. Add `or die(mysql_error())` to the end of the `mysql_query()` line so you'll see the reason. – Barmar Jan 02 '15 at 08:41
  • 1
    What does `var_dump($table)` show? – Barmar Jan 02 '15 at 08:42
  • @barmar yes only when the table is hard coded. the second part of code in this question... array (size=8) 'ID' => string '1' (length=3) 'name' => string 'Test_E' (length=11) 'accountname' => string 'Din' (length=3) 'accountID' => string '1' (length=1) 'status' => string '2' (length=1) 'total' => string '206' (length=3) 'min(a.timestamp)' => string '2014-05-16 05:38:01' (length=19) 'max(a.timestamp)' => string '2014-12-31 07:55:35' (length=19) – Prog_Rookie Jan 02 '15 at 08:44
  • I don't understand what that is. I asked for `var_dump($table)`. `$table` should just be a string, not an array. – Barmar Jan 02 '15 at 08:54
  • sorry mixed it up!!!!!thats the $result.... it shows 'table_1', 'table_2' so on – Prog_Rookie Jan 02 '15 at 09:15
  • That looks OK. What is the error printed my `mysql_error()`? – Barmar Jan 02 '15 at 09:16
  • Commands out of sync; you can't run this command now – Prog_Rookie Jan 02 '15 at 09:21
  • OK, now we're getting somewhere. See my answer. – Barmar Jan 02 '15 at 09:25
  • thanks wait ill chk the answer and let u knw – Prog_Rookie Jan 02 '15 at 09:28

1 Answers1

1

You need to read all the results from the first query before you can start another query.

$tables = array();
$table_count = mysql_query("SELECT TABLE_NAME FROM information_schema.tables WHERE 
    table_schema = 'milepostdb' AND table_name LIKE 'table_%' ") or die(mysql_error());

while($row = mysql_fetch_array($table_count)){
    $tables[] = $row["TABLE_NAME"];
}
mysql_free_result($table_count);

foreach ($tables as $table) {
    $excute = mysql_query("CALL Dummy_2('$table')") or die(mysql_error());
    $result = mysql_fetch_assoc($excute);//line which triggers the error
    var_dump($result);
}
Barmar
  • 669,327
  • 51
  • 454
  • 560
  • OP called the mysql_query . $result = mysql_fetch_assoc(mysql_query($excute)); – Utkarsh Dixit Jan 02 '15 at 08:37
  • sorry thats a mistake.. ill edit that – Prog_Rookie Jan 02 '15 at 08:38
  • same error as i have mention above,, but adding more to the error "Commands out of sync; you can't run this command now"... the exact way it come is like this..... Array ( [TABLE_NAME] => table_1 ) Array ( [TABLE_NAME] => table_2 ) Commands out of sync; you can't run this command now – Prog_Rookie Jan 02 '15 at 09:40
  • which means first table is processed correctly... error occurs from the sceond table in the loop – Prog_Rookie Jan 02 '15 at 09:41
  • Try it with the `mysql_free_result` that I just added. – Barmar Jan 02 '15 at 09:46
  • thanks mate ill look into that.... all this help means alot – Prog_Rookie Jan 02 '15 at 09:47
  • same error... goes through the first table and throws an error there onwards – Prog_Rookie Jan 02 '15 at 10:11
  • I'm running out of ideas. The only other thing I can think of is to make two `mysql_connect()` calls, so you can perform the inner query on a different connection from the outer query. – Barmar Jan 02 '15 at 10:12