0

i have a question here.. i have 1 dynamic table.. and i want to ask u a question, is it possible to create a where clause based on my dynamic table.. let me explain it..

this is my table

tabel Attribute

_____________________
idAttribute | Name
_____________________
1           | Width
2           | Diameter
3           | AR
etc

Is it possible to create automatic / dynamic where clause just based on that table.Name value

width = ? AND Diameter = ? AND AR = ? AND etc

Any suggestion?

Let me more explain..

lets say i have a variabel $query that contain my condition...

$query = "SELECT * FROM xxx WHERE ".$where;

Nah.. in my $where variabel, if i write it normally, based on my table above it will be..

$where = "width = ? AND diameter = ? AND AR = ?";

My question is how to create $where dynamic based on my table above, so if my table above is

idAttribute | Name
__________________
1           | Width
2           | Diameter
3           | AR
4           | Weight

It will be automaticly like this

$where = "width = ? AND diameter = ? AND AR = ? AND weight = ?";

any suggestion?

Wawan Brutalx
  • 603
  • 6
  • 15
  • 27
  • 1
    It is not exactly clear what you are asking, can you please edit your post and try to clarify? – Taryn Nov 05 '12 at 16:18
  • Isn't that what queries do anyways? Lets you put any value into the `where` clause? If `PDO::prepare` is what you're looking for, then here's the manual http://php.net/manual/en/pdo.prepare.php – Teena Thomas Nov 05 '12 at 16:20

2 Answers2

1

Since you did not provide a lot of details, it is not exactly clear what you are asking. I made a few assumptions based on the one table that you detailed. If you are looking to query the data that is based on the name value, you can use something like this:

select i.name i_name,
  a.name a_name,
  ia.value
from item i
left join item_attribute ia
  on i.itemid = ia.itemid
left join attribute a
  on ia.idattribute = a.idattribute
where a.name = 'Width'
  and ia.value = 100

See SQL Fiddle with Demo

But it might be easier to query the data by first transforming it to a column, then searching, similar to this:

select *
from
(
  select i.name i_name,
    sum(case when a.name = 'Width' then ia.value end) width,
    sum(case when a.name = 'Diameter' then ia.value end) diameter,
    sum(case when a.name = 'AR' then ia.value end) ar,
    sum(case when a.name = 'Weight' then ia.value end) weight
  from item i
  left join item_attribute ia
    on i.itemid = ia.itemid
  left join attribute a
    on ia.idattribute = a.idattribute
  group by i.name
) src
where width = 100

See SQL Fiddle with Demo

Taryn
  • 242,637
  • 56
  • 362
  • 405
1

Perform the aggregation entirely in SQL:

$qry = $dbh->execute('
  SELECT GROUP_CONCAT(
    "`", REPLACE(Name, "`", "``"), "` = ?"
    ORDER BY ...
    SEPARATOR " AND "
  ) FROM Attribute
');
$where = $qry->fetchColumn();

Or part SQL, part PHP:

$qry = $dbh->execute('
  SELECT   CONCAT("`", REPLACE(Name, "`", "``",), "` = ?")
  FROM     Attribute
  ORDER BY ...
');
$where = implode(' AND ', $qry->fetchAll(PDO::FETCH_COLUMN, 0));

Or entirely in PHP:

mb_regex_encoding($charset); // charset of database connection

function foo($n) {return '`'.mb_ereg_replace('`','``',$n).'` = ?';}
$qry = $dbh->execute('SELECT Name FROM Attribute ORDER BY ...');
$where = implode(' AND ', array_map('foo', $qry->fetchAll(PDO::FETCH_COLUMN, 0)));

Beware that you'll probably wish to sort the order in which you fetch the columns (as shown above) so that you know the order in which parameters should be supplied to the arising statement; or else use named parameters instead of anonymous placeholders.

eggyal
  • 122,705
  • 18
  • 212
  • 237