How to save URL's to a MYSQL database from an array

Question

What is the best way to save a URL or symbols into a MYSQL database.

I'v seen this being used "{$htmlOutputArray[1]}" and then else where this "$htmlOutputArray[1]" and some other places do what i'v done below... but which is the best?

So far I have: (example code)

$html = "034251\nhttp://stackoverflow.com/questions/ask"
$htmlOutputArray = explode("\n", "$html");

$htmlOutput = $htmlOutputArray[0];
$postIDOutput = $htmlOutputArray[1];
$con = mysql_connect('localhost', 'user', 'pass') or die('Could not connect: ' . mysql_error());
#echo 'Connected successfully';
mysql_select_db("dbName", $con);
mysql_query("UPDATE tableName SET PostidINT='$postIDOutput', URLofPostTXT='$htmlOutput' WHERE id='$unID'");
mysql_close($con);

Answer 1

First of all you should look into the dangers of SQL injection and how you can prevent it.

Here's both a way you can do this and the safer version.

mysql_select_db("dbName", $con);

$sql = sprintf("UPDATE tableName SET PostidINT=%d, URLofPostTXT='%s' WHERE id=%d",
     mysql_real_escape_string($htmlOutputArray[1]), 
     mysql_real_escape_string($htmlOutputArray[0]), 
     mysql_real_escape_string($unID));

mysql_query($sql);

mysql_close($con);

What mysql_real_escape_string() is doing is preventing unsafe characters from being entered into your database.

What sprintf() is doing is formatting your string, so for example, only numbers will be entered in the PostidINT and id variables.

Answer 2

I'd vote for prepared statements (and mysqli):

$connection = new mysqli("localhost", "user", "pass", "db");
$statement = $connection->prepare("UPDATE tableName SET PostidINT=?, URLofPostTXT=? WHERE id=?");
$statement->bind_param("i", $postIDOutput);
$statement->bind_param("s", $htmlOutput);
$statement->bind_param("i", $unID);
$statement->execute();