I can't find anything about dynamically referencing one MySQL table entry to another. It may not be possible.
Essentially, I'd like to know if in MySQL you can do the equivalent to referencing the value of a certain Excel cell to another. For example, if in Excel I set Sheet 1 Cell A1 to some value like "MyVal". Then if I set Sheet 2 Cell A1 to "=Sheet1!A1" and Sheet 3 Cell A1 to "=Sheet2!A1" the value of Sheet 3 Cell A1 is "MyVal". If I go back to Sheet 1 Cell A1 and change the value to "MyNewVal" then the value is automatically updated on Sheet 2 Cell A1 and Sheet 3 Cell A1 to "MyNewVal".
My question is... in MySQL can I set the value of a certain entry in the first table to be dynamically linked to the value of a different entry in a second table such that when I query the first table (using existing PHP code) I get the value that's in the second table? I imagine that if it's possible then perhaps the value of the entry in the first table would look like a query that queries the second table for the correct value.
I understand how to write an UPDATE query in PHP to explicitly make the values the same but I don't want to change the existing php code. I want to link them in a relative/dynamic way. The short reason is that I don't want to change the PHP code since the same code is used on several of the sites I maintain and I want to keep the existing php code the same for cleaner maintenance/upgrading.
However, since the databases on the various sites are already different, it would be very clean to somehow dynamically link the appropriate entries in the different tables in the database itself.
Any help would be very appreciated. If this is possible, if you could just point me in the right direction, I'd be happy to do the research.
There are 2.5 ways to do this (basically two, but it feels like there's three):
From easiest to hardest...
Option 1:
If you need tableA to reflect tableB's value, don't store the value in tableA at all, just use tableB's value. Use either a join:
select a.*, b.col1
from tableA a
join tableB b on <some join condition>
or a subselect
select *, (select col1 from tableB where <some condition>) col1
from tableA
Option 2:
If you're happy with option 1, convert it to a view, which behaves like a table (except are restrictions on updating views that are joins):
create view myview as
select ... (one of the above selects)
Option 3:
Create a database trigger that fires when tableB's value is changed and copies the value over to the appropriate row/column in tableA
create trigger tableB_update
after update on tableB
for each row
update tableA set
tablea_col = new.col1
where id = new.tableA_id;
Note that new and old are special names given to the new and old rows so you can reference the values in the table being updated.
Choose the option that best suits your needs.