Page 1 of 1

Reference values from other columns of same record

Posted: Fri Dec 17, 2004 4:59 pm
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?

Posted: Fri Dec 17, 2004 6:27 pm
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.

Posted: Fri Dec 17, 2004 7:04 pm
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

Posted: Fri Dec 17, 2004 8:16 pm
by timvw

Code: Select all

SELECT *
FROM foo
WHERE CONCAT(cat, title) = '$filename';

Posted: Fri Dec 17, 2004 10:24 pm
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