2

I have a generic MySQL statement used for many tables that just retrieves all data for a single table:

$table = "profiles";//set static for example
$result = mysql_query("SELECT * FROM `$table`");

I would like the results to show the text from position in place of job_id. How do I do this if I still want my query to remain generic and there could be multiple fields that are references to other tables where I would want the related text not the id?

mysql> show columns from profiles;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| staff_id    | int(11)      | NO   | PRI | NULL    | auto_increment | 
| job_id      | int(11)      | NO   |     | NULL    |                | 
| name        | varchar(100) | NO   |     | NULL    |                | 
+-------------+--------------+------+-----+---------+----------------+

mysql> show columns from job_positions
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| job_id      | int(11)      | NO   | PRI | NULL    | auto_increment | 
| position    | varchar(100) | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

Is there some kind of relationship I can set on these fields and then issue a command with the query to return the related text instead of the id's?

Craig
  • 1,929
  • 3
  • 26
  • 36
  • Sounds like you want a **Good PHP ORM Library**: http://stackoverflow.com/questions/108699/good-php-orm-library – Ben Lee Mar 13 '12 at 00:12

1 Answers1

1

You can create a view:

create or replace view info as
select p.job_id as job_id, p.name as name, j.position as position
from profile as p, job_position as j where p.job_id=j.job_id

Then you can still using your query

$table = "info";
$result = mysql_query("SELECT * FROM `$table`");
PasteBT
  • 2,040
  • 14
  • 17
  • This looks promising however what about tables with no view? Is there a way to check for a view on a table and if so run the query where $table = $tablename."_view" otherwise $table = $tablename? – Craig Mar 13 '12 at 00:37
  • The point of the generic mysql statement is that $table is automatically assigned from the user action e.g. they click the profile button so it assigns 'profile' as the $table. I can't change it to 'people_view' as this will break other things in the program. Therefore I would need to change it right before running the SQL. hence if I called the view I created 'profile_view' I could use $table.'_view' however the other queries using this generic statement will fail as there is no view created for them and therefore no $table.'_view' – Craig Mar 13 '12 at 01:30