running a query with tables listed in an array

Question

I need to run a mysql query that selects records from several tables. the names of the tables are received via post and are stored in an array. What I have done is this that does not work:

//--> Check if anything is posted from the client
if(isset($_POST['code'])){

    $emps = array();

    foreach(($_POST['code']) as $c) {
        $emps[] = $c;
    }

    @$res = mysql_query("select code,fname,faname from (".implode(',',$emps).")") where emp_code='11330' ;

    while($r = mysql_fetch_array($res)){
        //do something...
    }
}

Well for starters, remove the error suppressor for the query and see why it's not working. — Phix, Apr 01 '13 at 08:35
What are you trying to achieve? Passing table names in post/get is a terrible idea.. — Jay Bhatt, Apr 01 '13 at 08:35
dont use @ operator to ignore error its always bad you must solve them — NullPoiиteя, Apr 01 '13 at 08:36
For security reasons filter, the posted data like this: `$allowedTables = array(/*list of the allowed table names*/); $emps = array_intersect($_POST['code'],$allowedTables);` What you want is a bit complicated. Do you need `UNION` or `JOIN` the results ? — Kovge, Apr 01 '13 at 08:39
[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://www.brightmeup.info/article.php?a_id=2). — NullPoiиteя, Apr 01 '13 at 08:39

score 1 · Accepted Answer · answered Apr 01 '13 at 08:38

1

Replace

mysql_query("select code,fname,faname from (".implode(',',$emps).")") where emp_code='11330' ;

with

mysql_query("select code,fname,faname from (".implode(',',$emps).") where emp_code='11330'") ;

answered Apr 01 '13 at 08:38

Deepanshu Goyal

2,738
3
34
61

1

I expect this error: #1052 - Column 'emp_code' in where clause is ambiguous – Apr 01 '13 at 08:44
you need to write it like this then tablename.emp_code and moreover I guess you should be using joins in it – Deepanshu Goyal Apr 01 '13 at 08:47
1

I guess you know Joins : just go through it, http://stackoverflow.com/questions/12890071/select-from-multiple-tables-mysql – Deepanshu Goyal Apr 01 '13 at 08:57

score 0 · Answer 2 · answered Apr 01 '13 at 08:59

0

try this

 for($i=0;$i<count($emps);$i++)
  {
      $query=$query."select code,fname,faname from ".$emps[$i]." where 
         ".$emps[$i].".emp_code='11330' UNION " ;
  }

     @$res=mysql_query($query);

answered Apr 01 '13 at 08:59

thumber nirmal

1,639
3
16
27

running a query with tables listed in an array

2 Answers2