mdg583
11-27-2008, 01:49 AM
Hello. Some people may not like this question, as I have been advised not to use the * selector with mysql select statements, but my whole system of doing things really depends on it.
Basically, here is my problem:
I have a basic database abstraction class (which does more than just database abstraction, like adding security restrictions to all database queries).
Note: I don't know if this is called an abstraction layer or an adapter, or what. It is a bit like zend db. I don't write any query language when using it - just php classes and functions.
Up till now I have a function to select 1 row of 1 table based on the value of some of any of its fields. Which table is used and what conditions are used is based on function parameters. This table also added security restrictions, etc.
For more complicated (join) queries I had another class which basically had pre-defined join relationships to query items by their join relationships.
Now I want to add simple join relationships to my first function, if at all possible. I would like it to query one main table, possibly join other tables, and then return all the fields of all tables.
I think I've pretty much got everything worked out except for one thing: how do I join multiple (arbitrary) tables together and then differentiate which resultant fields came from which table? I'd only join any given table once, but some tables may share field names with other tables.
Here is a possible example of a query:
Table structure:
events
--------------------------------------------------
| event_id | title | date | invitation_id |
--------------------------------------------------
invitations
----------------------------
| invitation_id | title |
----------------------------
Logic
$query = "SELECT events.*, invitations.* FROM events LEFT JOIN invitations ON (events.invitation_id = invitations.invitation_id) WHERE events.date = $date"
mysql_select_db($database, $connection);
$data = mysql_query($query, $connection) or die(mysql_error());
$row = mysql_fetch_assoc($data);
/*
* At this point I'd want to separate $row into $row_event and $row_invitation. The issue is, for instance, that both events and invitations have a field named 'title'. And even if they didn't I don't know which fields belong to which tables in the first place. Although I do know the primary key field for each table - I store that in a php file for reference.
*/
Really, what I need is something like the following:
$query = "SELECT events.* AS result_events.*, invitations.* AS result_invitations.* FROM events LEFT JOIN invitations ON (events.invitation_id = invitations.invitation_id) WHERE events.date = $date"
and then access as $row['result_events.title'], for instance.
I've come against a lot of issues, but found ways to deal with them, but now I am stuck on this issue.
Does anyone have any suggestions? Both 'title's get returned, but how do you differentiate between them?
Matthew
Basically, here is my problem:
I have a basic database abstraction class (which does more than just database abstraction, like adding security restrictions to all database queries).
Note: I don't know if this is called an abstraction layer or an adapter, or what. It is a bit like zend db. I don't write any query language when using it - just php classes and functions.
Up till now I have a function to select 1 row of 1 table based on the value of some of any of its fields. Which table is used and what conditions are used is based on function parameters. This table also added security restrictions, etc.
For more complicated (join) queries I had another class which basically had pre-defined join relationships to query items by their join relationships.
Now I want to add simple join relationships to my first function, if at all possible. I would like it to query one main table, possibly join other tables, and then return all the fields of all tables.
I think I've pretty much got everything worked out except for one thing: how do I join multiple (arbitrary) tables together and then differentiate which resultant fields came from which table? I'd only join any given table once, but some tables may share field names with other tables.
Here is a possible example of a query:
Table structure:
events
--------------------------------------------------
| event_id | title | date | invitation_id |
--------------------------------------------------
invitations
----------------------------
| invitation_id | title |
----------------------------
Logic
$query = "SELECT events.*, invitations.* FROM events LEFT JOIN invitations ON (events.invitation_id = invitations.invitation_id) WHERE events.date = $date"
mysql_select_db($database, $connection);
$data = mysql_query($query, $connection) or die(mysql_error());
$row = mysql_fetch_assoc($data);
/*
* At this point I'd want to separate $row into $row_event and $row_invitation. The issue is, for instance, that both events and invitations have a field named 'title'. And even if they didn't I don't know which fields belong to which tables in the first place. Although I do know the primary key field for each table - I store that in a php file for reference.
*/
Really, what I need is something like the following:
$query = "SELECT events.* AS result_events.*, invitations.* AS result_invitations.* FROM events LEFT JOIN invitations ON (events.invitation_id = invitations.invitation_id) WHERE events.date = $date"
and then access as $row['result_events.title'], for instance.
I've come against a lot of issues, but found ways to deal with them, but now I am stuck on this issue.
Does anyone have any suggestions? Both 'title's get returned, but how do you differentiate between them?
Matthew