Sorry for the complex question, I hope you can help.
I have data in a MySQL database which I want to load into a grid, there can be quite a lot of data, normally that would be no issue, however in order to display the data to the end user, I need to lookup the IDs in the main table which means joining 4 or 5 tables, with a lot of rows MySQL is going to find this heavy work.
So I was wondering if there is a better way to break this down into smaller queries, I considered the array engine, but the SQL engine is more efficient selecting only the data it needs on the page, whereas I don’t want to pass over a 50,000 row array.
Is there a way to lookup the values for each column using an SQL select?
// host id – this needs to be translated into a name
$grid->host_id = “select host_name from hosts where id = ?” // ? – host_id – primary key
// destination can be used direct from this table
// current_status doesn’t actually exist in this table, fetch from another table
$grid->current_status = “select current_status from status where service_id = ?” // ? = service_id, this isn’t the primary key, but if queries can only be done on a single ? then might be able to work around this