I'm facing a slight problem. I'm working on an application at the moment that requires the use of dynamic table names in MySQL.
Basically, I have a process of selecting albums from a database based on a number of factors (Genre, Play Length, Release Date etc) -- There is a section of this process that allows the user to create a bunch of custom filters..
The custom filters return a count to the user of all the albums within that selection criteria. The ID's of those albums are then stored in a table with a randomly generated hash/serial number (e.g. albumSelect_20880f9c05d68a)
I did it this way because I didn't want to store a huge comma separated list in a field (Really silly) -- And I didn't fancy sending an array of values to a hidden field in my HTML as this would just increase data throughput (Could be thousands of rows at a time)
In CodeIgniter, I'm using Query Bindings to generate my SQL queries, like so:
select * from artists where artistName = ? AND albumTitle = ?
The query is then automatically escaped when I parameterize the query
$query = $this->db->query($sql,array("Singer","Album"));
Now comes the tricky part
If I write my query to look something like this:
$sql = "select albumid from albums where albumid in (select albumid from albumSelect_?)";
$this->db->query($sql,array('20880f9c05d68a'));
The resulting query becomes:
select `albumid` from `albums` where `albumid` in (select `albumid` from `albumSelect_'20880f9c05d68a'`)
And quite rightly so, but obviously the query is then invalid..
Edit: More Info
The query could be part of a bigger query, depending on what criteria the user selects. e.g.
$sql = "select albumid from albums where albumid in(select albumid from tags where tag = ?) AND albumid in(select albumid from albumSelect_?)";
I was just wondering if there was a way to get this working, OR if anyone could suggest a better alternative.. Concatenation of the table name is obviously not an option.
Thanks in advance!
Dave