Reputation: 187
I want a MySQL field to be able to reference the same field in another row and return that field's value when loaded into PHP.
For instance, if row with id = 1
has value "bar"
in column foo
, the row with id = 3
should have value "[1]"
(or something similar; the 1 is pointing to the row with id = 1
) in column foo
and MySQL should replace that value with "bar"
when returning the array.
I'm not talking about an UPDATE
query. I am trying to build a SELECT query that will make the appropriate replacement. I want [1]
to be the permanent value of that row, and reflect whatever foo
in the referenced row should happen to be.
Upvotes: 1
Views: 2875
Reputation: 165069
You could try something like this but without knowing more, your mileage may vary.
Also, simply use 1
, not [1]
as the reference value
SELECT a.id, b.foo
FROM `foo_table` a
INNER JOIN `foo_table` b
ON CAST(a.foo AS UNSIGNED) = b.id
WHERE a.id = 3 -- or whatever
I'd be more inclined to make your table design more specific. For example, try this structure
CREATE TABLE `foo_table` (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
foo VARCHAR(255),
parent_id INT NULL,
FOREIGN KEY (parent_id) REFERENCES `foo_table` (id)
ON DELETE CASCADE
) ENGINE=INNODB;
Then your query can be
SELECT a.id, COALESCE(b.foo, a.foo) AS foo
FROM foo_table a
LEFT JOIN foo_table b
ON a.parent_id IS NOT NULL
AND a.parent_id = b.id;
Upvotes: 1