Reference values from other columns of same record

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Reference values from other columns of same record

Post by josh »

I have `cat` `title` and `filename`, right now `filename` is an integer with which #1 the record is (auto-encryment) I want the filename to be the `cat` + `title`
So something like concat(`cat`, `title`) ???
if cat was one cat1 and title was title1 i want filename to be cat1_title1
How can I do that in mysql?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

actually, if filename can be calculated/constructed from cat and title, it is a process attribute, meaning it doesn't belong in a normalized database anyway ;)

if you insist on storing it though, i believe you would have to use a stored procedure that get's triggered on insert and update.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

So what your saying is take the variable $filename with php and conver it to $cat and $title then instead of selecting where filename= i select where title= AND cat=

Right?
That's what I was going to do but I thought it might be better to let mysql do the work because if I ever decided to set the filename of a record to something differnet then $cat . $title then I would need to re-write my script
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Code: Select all

SELECT *
FROM foo
WHERE CONCAT(cat, title) = '$filename';
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

Hmm, I decided to just go:

Code: Select all

list($cat, $title) = split('_', $filename);
$result = mysql_query("select `data` from `table` where `cat` = '$cat' AND `title` = '$title'");
Works fine for my needs, If I need to store the filename I will use CONCAT
/me bookmarks this thread

Thank you for your help
Post Reply