Inserting a Web link in database

[morrism35]

I want to insert a web link into my pageLink column of my artist_table database so that when someone searches for the artist a link to their webpage comes up, but I keep getting this error message.

 

Error: INSERT INTO artist_table (pageLink) VALUES ('alexander oneal')
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'http://www.kennethkleiner.com/morris/web250/Artist/alexanderoneal.php>alexander' at line 2

 

 

$sql = "INSERT INTO artist_table (pageLink) VALUES

('<a href='http://www.kennethkleiner.com/morris/web250/Artist/alexanderoneal.php'>alexander oneal</a>')";

 

[mac_gyver]

string data that may (in this case does) contain sql special characters (characters that have meaning in the sql syntax), such as NULL (ASCII 0), new-line, carriage-return, \, ', ", and Control-Z, must be escaped (or use prepared queries) so that the sql special characters don't break the sql syntax.

 

for the mysql database, the escape character is a \. if you are manually typing the sql query statement in your code, you can just add the \ before each of the the single-quotes that are inside the sql query statement. if this is not static data that's been typed in your code, you should either use the escape_string function/method for the php database api you are using or use prepared queries, which separate the sql syntax from the data values being used in the query.

 

 

 

[mikesta707]

Another possible solution would be to, instead of storing the entire HTML line of code, you could just store the URL. IE instead of what you wrote, you would do:

$sql = "INSERT INTO artist_table (pageLink) VALUES
('http://www.kennethkl...xanderoneal.php')";

Edit: Hmm, there seems to be some glitch that removes the rest of the text from a post after the first code bbcode block. Anyways:

 

This has several advantages. For example,  you could use this pageLink to build the HTML in PHP, which would allow you to customize the HTML more easily. You could add CSS classes, or change the link text much more easily than if you stored the raw HTML. For example:

 

 

//get the row data however, assume it is stored in an associative array called $row
$pageLink = $row['pageLink'];
$artistName = $row['name'];//use the artist name for the link text, or use whatever else you want
 
$link_html = "<a href='$pageLink' class='whatever classes you want'>$artistName</a>";
 
//now you can do whatever you want with the link html, for example simply echo it
echo $link_html

 

You could even add a column to your table so that the user can specify what the link text is (instead of using the artist name), and you could also add other attributes, like javascript events (onClick, onMouseOver, etc.)

Edited December 7, 2015 by mikesta707