updating an SQL database with PHP

Question

While building a web based location app, I've been having a hard time trying to UPDATE my SQL database on my server.

I've been able to run this PHP file to get the data from the SQL succeccfuly:

<?php
require("phpsqlajax_dbinfo.php");
function parseToXML($htmlStr) 
{ 
$xmlStr=str_replace('<','&lt;',$htmlStr); 
$xmlStr=str_replace('>','&gt;',$xmlStr); 
$xmlStr=str_replace('"','&quot;',$xmlStr); 
$xmlStr=str_replace("'",'&#39;',$xmlStr); 
$xmlStr=str_replace("&",'&amp;',$xmlStr); 
return $xmlStr; 
} 
// Opens a connection to a MySQL server
$connection=mysql_connect (localhost, $username, $password);
if (!$connection) {
  die('Not connected : ' . mysql_error());
}
// Set the active MySQL database
$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) {
  die ('Can\'t use db : ' . mysql_error());
}
// Select all the rows in the markers table
$query = "SELECT * FROM markers WHERE 1";
$result = mysql_query($query);
if (!$result) {
  die('Invalid query: ' . mysql_error());
}
header("Content-type: text/xml");
// Start XML file, echo parent node
echo '<markers>';
// Iterate through the rows, printing XML nodes for each
while ($row = @mysql_fetch_assoc($result)){
  // ADD TO XML DOCUMENT NODE
  echo '<marker ';
  echo 'name="' . parseToXML($row['name']) . '" ';
  echo 'address="' . parseToXML($row['address']) . '" ';
  echo 'lat="' . $row['lat'] . '" ';
  echo 'lng="' . $row['lng'] . '" ';
  echo 'type="' . $row['type'] . '" ';
  echo '/>';
}
// End XML file
echo '</markers>';
?>

but when I try to UPDATE the same SQL with this code, it's not working:

<?php
require("phpsqlajax_dbinfo.php");
// Opens a connection to a MySQL server
$connection=mysql_connect (localhost, $username, $password);
if (!$connection) {
  die('Not connected : ' . mysql_error());
}
// Set the active MySQL database
$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) {
  die ('Can\'t use db : ' . mysql_error());
}
// Select all the rows in the markers table
$query = "UPDATE markers SET lat="32" WHERE 1";
$result = mysql_query($query);
if (!$result) {
  die('Invalid query: ' . mysql_error());
}
?>

Can you please help me figuring out what I've done wrong ?

Thanks! Gil.

Please stop using **mysql_*** and start using **mysqli_** or **PDO** instead, as it will be removed in PHP 7. — Naruto, Oct 02 '15 at 11:08
I love it when they put `die('Invalid query: ' . mysql_error());` and their query failed, but "failed" to tell us the syntax error they get; *classic*. — Funk Forty Niner, Oct 02 '15 at 11:20
also note, you should stop using `@` to bypass any errors. PHP is weakly typed, which (among other things) means it will keep running even if it errs, which opens a security hole, particularly with your sql connections. In other words, if it errs, you should force the script the stop; don't hide your problems. Show them and fix them before you launch — nomistic, Oct 02 '15 at 11:20
If you can, you should [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really not hard](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Oct 02 '15 at 11:34

score 3 · Accepted Answer · answered Oct 02 '15 at 11:16

Take a look at this part of your code:

$query = "UPDATE markers SET lat="32" WHERE 1";

You opened the query string with double quotes, and inside, you quote the lat value with double quotes. Php considers your opening double quotes closed. To fix this: either use single quotes or escape the inner double quotes. See examples below:

$query = "UPDATE markers SET lat='32' WHERE 1"; //begin with double quotes and use single quotes inside

$query = 'UPDATE markers SET lat="32" WHERE 1';//begin with single quotes and use double quotes inside

$query = "UPDATE markers SET lat=\"32\" WHERE 1"; //begin with double quotes, but escape the inner double quotes

I'd advise you to go for the first option, as MySQL favours single quotes in its string.

Thanks a lot Naija, indeed my mistake was putting the double qoutes! — Gil A, Oct 02 '15 at 11:46
I really admire the knowledge you guys have. I'm more of a coding fan than proffesional. — Gil A, Oct 02 '15 at 11:56

updating an SQL database with PHP

1 Answers1