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?