How to refference a value from the same row in mysql?

Question

I am retrieving a long text blurb for a row. Example: a description for a book. Is there a way that I can reference other values of the same row in the longer text as a form of variable. The idea here being that I don't have to comb through the entire long text output and change every instance of the value where referenced.

Using the same as before: a description which is a column retrieved from the same row as say the title and author of the same book: "$TITLE is a book written by $AUTHOR etc. etc." If for example the same row had a title column and author column with the value of "Huckleberry Fin" and "Mark Twain" and a description column with the value of "$TITLE is a book written by $AUTHOR etc. etc."" .. When the description was retrieved, the description would read "Huckleberry Fin is a book written by Mark Twain etc. etc." Re-Using the same example: If the above row's title were updated to "The Adventures of Huckleberry Finn" without updating the description, which would be a laborious and tedious process that I would like to avoid, when the description is retrieved it would read: "The Adventures of Huckleberry Finn is a book written by Mark Twain"

Maybe this doesn't exist and I would have to make my own engine for it web server side, but it seems like the best option for efficiency would be to have this done in the database and not the web server. Maybe I don't know enough about MySQL to know that this could be achieved using something like a view for instance. Anyway, it seems like this would be a very common thing to have done on the server and I can't imagine that this cannot be done... but then again, maybe I am missing something really simple.

You don't mean where {description column} like '%param1%' and {description column} like '%param2%'. I have no idea if the syntax is right, but it would do what I think you are wanting in Ms SQL — LJ01, Feb 20 '18 at 05:09
I don't know whether I just didn't try hard enough to understand the earlier version but this version is clear. — philipxy, Feb 21 '18 at 21:39

score 1 · Accepted Answer · answered Feb 20 '18 at 06:25

1

You could use REPLACE:

SELECT title, REPLACE(description, '$TITLE', title) as description FROM books

But it's going to be ureadable if you need to replace multiple placeholders using nested REPLACE expressions:

SELECT title,
    REPLACE(
        REPLACE(description, '$TITLE', title),
        '$MAINCHARACTER',
        main_character
    ) as description
FROM books

I would rather do it in application language. For example with PHP you could use:

$description = str_replace(
    ['$TITLE', '$MAINCHARACTER'],
    [$title, $mainCharacter],
    $description
);

Adding more placeholders wouldn't make it unreadable.

[..] but it seems like the best option for efficiency would be to have this done in the database and not the web server.

I don't see any reason, why any language should do that less efficient than MySQL.

Anyway, it seems like this would be a very common thing to have done on the server

I doubt that. You will find a lot of comments like "Why don't you do that in application language?". At least MySQL has a very limited support for string manipulation.

answered Feb 20 '18 at 06:25

Paul Spiegel

30,925
5
44
53

I like what you are driving at and it might very be that I have to make all of the string replacements in the application. I would just like to exploit the language to the fullest before re-writing the wheel if possible and since I don't have the most experience with MySql, I figured I would ask before committing to 12 hours of work in a direction that might possibly be solved in 10 minutes of work. It just seems like a common enough operation that one might be inclined to think that maybe this was solved in the database layer. If it's not a feature of MySql, then someone should suggest it. – Rusty Weber Feb 20 '18 at 09:53
P.S. The main reason I was hoping that this was solved in the database is because if I retrieve the description using php I know to edit the values and I can write that in PHP 1 time, but if my co-workers retrieve the description using python or perl, they also have to wash the data like I did and that implementation has to be written multiple times. Seems MUCH cleaner just to retrieve a description and not have to do any post processing don't you think? – Rusty Weber Feb 20 '18 at 10:08
Well you can write a feature request. We are waiting for things like CECK constraint since many years. But maybe they will find your request more important. So good luck! – Paul Spiegel Feb 20 '18 at 15:16
Maybe I should just examine another database technology. Is there any database that allows this? – Rusty Weber Feb 20 '18 at 22:54
Basically.. the answer I've gotten is that.. NO, MySql can't do that and you have to do that in your php code. Which is fine. – Rusty Weber Mar 07 '18 at 17:53

score 0 · Answer 2 · answered Feb 20 '18 at 06:31

Are you trying to call different data within the same row? You can use mysql_fetch_row.

If yes, you can add a column of index, like

=============================

index |title |character

1 | Huckleberry Fin | Tom Sawer

2 | ABC | John

==============================

And say you have your table name called "books", Then you do

$handle = mysql_query('SELECT * FROM books WHERE index=1');
$row = mysql_fetch_row($handle);

so $row[0]=1, $row[1]=Huckleberry Fin, and $row[2]=Tom Sawer

You can take a look at this manual.

And you may want to learn how to use escape functions like mysql_escape_string()here.

P.S. Sorry for the ugly table drawn LOL

Hi. If you don't know what they want, dont' guess, wait until you have enough reputation to ask for clarification via a comment. Guessing just genrates junk misdirected unhelpful answers. If a post is unclear then it doesn't represent an answerable question. PS Please read the edit help about code blocks (for code & tables) & see the formatted version of your post under the edit box. — philipxy, Feb 20 '18 at 06:44

How to refference a value from the same row in mysql?

2 Answers2