Need Assistance with Implementing Code Using Variables Inside of MySQL Database

[Illustrator76]

I apologize if my title is confusing, my problem is fairly simple but hard to explain for the title. I now have my development server all set up and running locally for testing, design, etc... Everything is working perfectly fine, but I have one problem that I do not know how to solve. I have hyperlinks in my database that will be displayed in my HTML. I personally prefer using absolute rather than relative links, so this going to cause a problem when moving my database content over from my development server to the live server.

 

From everything that I understand, using PHP in a database is a huge no-no, so I am searching for another solution to my problem. Basically, I want to be able to have links that look like: <some variable>/folder/content.php which on my development site would output: http://developmentsiteaddress.com/folder/content.php, but once I moved my database over to my live site I can change the text/value of <some variable> and it would output: http://www.mysite.com/folder/content.php.

 

If it comes down to it and there is no other choice than to use relative links in order for things to sync properly, then I guess I will have to deal with it, but I really prefer to use absolute links.

 

Any help given with this would be great.

Edited April 14, 2015 by Illustrator76

[joel24]

do you store them as absolute links?

 

store them as relative and then you can play with them easily after retrieving...

i.e. create a method which creates an absolute URL from a relative link based on the current domain.

function getAbsoluteUrl($relativePath)
{
    return $_SERVER['HTTP_HOST'].$relativePath;
}

//and then when you want the absolute URL call that method.

[Illustrator76]

 

do you store them as absolute links?

 

store them as relative and then you can play with them easily after retrieving...

i.e. create a method which creates an absolute URL from a relative link based on the current domain.

function getAbsoluteUrl($relativePath)
{
    return $_SERVER['HTTP_HOST'].$relativePath;
}

//and then when you want the absolute URL call that method.

 

Yes, I code them as absolute links. I probably didn't explain it correctly. This is data that is hard coded into certain tables in my database by me. I am using this for my portfolio, so I have links to all of my portfolio images hard coded into my database that will display on certain pages. So inside of my database "paintings" table for example I have <a href="http://developmentsiteaddress.com/folder/painting1.php"> and I'd like to have developmentsiteaddress.com somehow be a "variable" inside of my MySQL database that when I transfer all of the files over to my live server I can change the "variable" of developmentsiteaddress.com to be www.mysite.com instead.

Edited April 14, 2015 by Illustrator76

[joel24]

why don't you do a mysql replace to remove the absolute URL and just use the file path so you can implement a more dynamic solution as per my original reply?

 

i..e (ensure to remove the entire URL... i.e. if it's http://www.developmentsiteaddress.com then use that value.)

UPDATE `table_name`
SET `pathColumnName` = replace(pathColumnName, 'http://developmentsiteaddress.com', '')

Edited April 14, 2015 by joel24

[Illustrator76]

Thanks. As you suggested I have removed the absolute links and made them relative links/file paths. I am still a bit confused (still learning) as to how your function/method works. I copied and pasted that into my PHP code, but my links are still relative links/file paths. Is that function supposed to automatically recognize the file paths and change them into absolute links? I am just a bit unsure on how the code is supposed to work.

 

Thanks.

[joel24]

When you have the URL, you need to call that method.

i.e.

<?php echo "<a href='" . getAbsoluteUrl($row['relativePath']) . "'>Here is a link</a> ?>

You'll need to replace $row['relativePath'] with the variable you're currently using to display the URL / path.

[Illustrator76]

Thanks. The problem is that the code you wrote would have to go directly in my database table, and that doesn't work because the HTML literally displays the code and doesn't run the function, because the PHP echo command won't work if it's stored in a database. The only way I understand to get PHP working in a database is to use something called eval(), and I have universally heard that using PHP in a database is a bad idea, so I want to stay away from that.

 

So basically this is the exact code that is in my database: <a href="/folder/painting1.php">, <a href="/folder/painting-name.php">, etc... the "a" tag, the href, basically that entire line of code is in my database. If I had it set up where this part was hard coded in to my HTML: <a href=""> then I could put what you suggested in there, but with that entire "a" tag being in the database, that code doesn't work.

[joel24]

running php from your database? as you've said, it's bad idea and indicated a need to restructure your code.

 

you need to run a php script to use the eval function... so obviously you have a php script somewhere?

 

php is a server side coding language which is able to connect to your database, retrieve, insert and update information and then send data to the user (usually html).

 

I'd suggest doing a few tutorials, or simply leaving the links as relative...

 

Could you post your script which is currently showing the website to the user?

[Illustrator76]

Yeah, I may just have to leave the links as relative. I can't hard code any part of the links because of the where and how I am displaying them, and as we both agree, running PHP from my database is a bad idea. I have my code structured a certain way for a reason, and since it took me quite a while to make things like I have, I don't want to go messing with it just to get absolute links.

 

Thanks again for your help!