Create new column in mySQL using values from other columns

Question

I have two columns in my database I'd like to combine to make a new third column. The first column is a company name and the second is the URL to the company website.

I'd like to make a new column that is the company name hyperlinked to the website:

<a href="http://companywebsite.com>Company Name</a>

Is there a simple way to go about doing this? I'm VERY new to mySQL and can't figure out how I'd even go about doing this.

Bonus points for coming up with a way where when I add a new entry with company name and URL it automatically generates a value for this new hyperlink column.

Thanks.

I would not add this column at all; it contains no new data and duplicates existing data - can't you build the URL as and when needed or in your client layer? — Alex K.
– Alex K., Commented Oct 9, 2014 at 13:19
Please post a schema and some sample data - it should be easy to construct the data you want from the columns that are already there, rather than storing it explicitly. — Neville Kuyt
– Neville Kuyt, Commented Oct 9, 2014 at 13:20
Would suggest the same as Alex K. If you need it on database level you could use a view. — mreiterer
– mreiterer, Commented Oct 9, 2014 at 13:23
@ Alex Unfortunately, I'm using a plugin to render the tables in a WP page and it doesn't allow for any real customization. I can just make a simple query and that's it, so it seems like having everything in the table is the only option. — jonmrich
– jonmrich, Commented Oct 9, 2014 at 13:23
@Neville Dumped a few lines into an SQL file: cl.ly/code/320B2t2S2c3G — jonmrich
– jonmrich, Commented Oct 9, 2014 at 13:25

Neville Kuyt · Accepted Answer · 2014-10-09 13:50:32Z

2

As Polvonjon write, use CONCAT:

SELECT brief_description, CONCAT('<a href="', link, '">', innovation_name, '</a>') FROM inno_db

By the way - inno_db is a very odd name for a table in the database; it's a particular type of storage engine for MySQL. Do you not think "companies" is a better name?

Creating a new column is a bad idea - you have to keep it updated, and you're duplicating data, which leads to bugs in the long term. Ideally, you use the query to populate your WP screen.

If you can't do that, as the comments recommend, you could create a view, from which you can just do a straight select:

create view WPPlugin
as

select brief_description, 
CONCAT('<a href="', link, '">', innovation_name, '</a>') 
FROM inno_db

in your plug in code, you then do select * from WPPlugin.

edited Oct 9, 2014 at 13:50

answered Oct 9, 2014 at 13:37

Neville Kuyt

29.6k2 gold badges40 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

jonmrich Over a year ago

This generates a SQL query result (I guess), but I need it to add this data to an existing column (hyperlinked_name). How do I do that? Tried adding UPDATE 'hyperlinked_name' but that didn't work.

jonmrich Over a year ago

I'm following your update and this is close, but I need one other column (description) to be included in this view that I use for the plugin. So it should be this newly created column and description (an existing column in the table). Can I create a view with both of these?

jonmrich Over a year ago

Wait...I see that it's pulling all the columns, so this is perfect...One problem...the new column has entries like this: <�a href="gweepi.com/">Gweepi Medical<�/a> How do I get rid of the � ?

Neville Kuyt Over a year ago

I've updated the answer. I can see you're new here, but if you include all these details in your question, you'll get better answers.

Neville Kuyt Over a year ago

Where do you see those characters - when you execute the query in SQL window, or on the web page? If the latter, read this: codex.wordpress.org/Function_Reference/esc_url

|

Uchqun Tursunbayev · Accepted Answer · 2014-10-09 13:22:43Z

0

Try use CONCAT function:

SELECT company, url, CONCAT('<a href="', url, '">', company, '</a>') FROM companies

But I will not advice to using this sample of getting anchor tag. Try to use VIEW element in your MVC app.

answered Oct 9, 2014 at 13:22

Uchqun Tursunbayev

527 bronze badges

Comments

Oxana Urdaneta · Accepted Answer · 2014-10-09 13:25:08Z

0

Assuming you have a table with three columns CREATE TABLE mytable ( company_name text , url text , hyperlink text )

Then you would need to UPDATE the value of the third column (hyperlink) based on what you need, something like:

UPDATE mytable
SET    hyperlink = '<a href="http://' || url || '>' || company_name || '</a>';

answered Oct 9, 2014 at 13:25

Oxana Urdaneta

11 bronze badge

1 Comment

jonmrich Over a year ago

This just resulted in 0 being added to the new column

Collectives™ on Stack Overflow

Create new column in mySQL using values from other columns

3 Answers 3

6 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related