UPDATE SET problem - PHP Server Side Scripting forum at WebmasterWorld - WebmasterWorld

Forum Moderators: coopster

Message Too Old, No Replies

UPDATE SET problem

Phobia1

1:18 pm on Mar 8, 2009 (gmt 0)

10+ Year Member

Hi Guys
Could anyone help me with this please. I simply want to put the data read from XML into my database. Why does it not work?
Best
F
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<?php
require("./resources/globals.php");
//This is a PHP (4/5) script example on how eurofxref-daily.xml can be parsed

//Read eurofxref-daily.xml file in memory
$XMLContent= file("http://www.example.com/stats/eurofxref/eurofxref-daily.xml");
//the file is updated daily between 14:15 and 15:00 CET

foreach ($XMLContent as $line) {
if (ereg("currency='([[:alpha:]]+)'",$line,$currencyCode))
{
if (ereg("rate='([[:graph:]]+)'",$line,$rate))
{
//Output the value of 1 EUR for a currency code
//echo '1 € = '.$rate[1].' '.$currencyCode[1].'<br />';
$rate= ''.$rate[1].'<br />';
$currencyCode=''.$currencyCode[1].'<br />';

//set the update string
$updateStmt="Update currency SET rate=$rate WHERE code=$currencyCode";

/////////////////////////////////////////
// Connect to the Database

$con = mysql_connect($localhost,$userName,$password);
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("currency", $con);

echo $updateStmt; //displays correct data or each code

mysql_query($updateStmt);mysql_close($con);//no update to database

///////////////////////////////////////////

}
}
}

php ?>
<body>
</body>
</html>

[edited by: eelixduppy at 7:24 pm (utc) on Mar. 8, 2009]
[edit reason] exemplified [/edit]

eelixduppy

6:22 pm on Mar 8, 2009 (gmt 0)

Change this line:


mysql_query($updateStmt);

To something like this:


mysql_query($updateStmt) or die(mysql_error());

and let me know if you are getting any errors from your query.

Phobia1

11:55 am on Mar 9, 2009 (gmt 0)

10+ Year Member

Hi thanks for the reply.
Yes I get an error now.
Update currency SET rate=1.2658
WHERE code=USD
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 '> WHERE code=USD
' at line 1

Phobia1

1:22 pm on Mar 9, 2009 (gmt 0)

10+ Year Member

HI again
I have changed the code a little to fix at least one problem that was found with mysql_select_db. Still however have problems with mysql_query($update
Best F
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<?php
require("./resources/globals.php");
//This is a PHP (4/5) script example on how eurofxref-daily.xml can be parsed

//Read eurofxref-daily.xml file in memory
$XMLContent= file("http://www.example.com/stats/eurofxref/eurofxref-daily.xml");
//the file is updated daily between 14:15 and 15:00 CET

foreach ($XMLContent as $line) {
if (ereg("currency='([[:alpha:]]+)'",$line,$currencyCode))
{
if (ereg("rate='([[:graph:]]+)'",$line,$rate))
{
//Output the value of 1 EUR for a currency code
//echo '1 € = '.$rate.' '.$currencyCode[1].'<br />';
$rate= ''.$rate[1].'<br />';
$currencyCode=''.$currencyCode[1].'<br />';

//set the update string
$updateStmt="Update currency SET rate=$rate WHERE code=$currencyCode";

/////////////////////////////////////////
// Connect to the Database

$con = mysql_connect($localhost,$Username,$Password);

if (!$con)
{

die('Could not connect: ' . mysql_error());
}

$db_selected = mysql_select_db($dbname, $con);
if (!$db_selected)
{
die ("Can\'t use this database : " . mysql_error());
}

echo $updateStmt; //displays correct data or each code

mysql_query($updateStmt) or die(mysql_error());
mysql_close($con);//no update to database

///////////////////////////////////////////

}
}
}

php ?>
<body>
</body>
</html>

[1][edited by: eelixduppy at 3:44 pm (utc) on Mar. 9, 2009]
[edit reason] exemplified [/edit]

sonjay

1:30 pm on Mar 9, 2009 (gmt 0)

10+ Year Member

$currencyCode is a string.... You need to wrap it in single quotes:

WHERE code='$currencyCode'

Phobia1

2:15 pm on Mar 9, 2009 (gmt 0)

10+ Year Member

Hi and thanks for the reply. The problem I think is with the ' in mysql_query .I still can't get it to work.

UPDATE currency SET rate=1.2565
WHERE code='USD
'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 '> WHERE code='USD
'' at line 1

Phobia1

9:18 am on Mar 10, 2009 (gmt 0)

10+ Year Member

Hi Guys, I still cant get this to work and would appreciate a big kick from a guru please.
The problem is with the mysql_update statement, I think.

$updateStmt=("UPDATE currency SET rate='$rate' WHERE code='$currencyCode'"); If I omit the WHERE and make
$updateStmt=("UPDATE currency SET rate='$rate', code='$currencyCode'"); data is written could it be that the code parameter is always in uppercase as in USD? Here is the latest code.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
<?php
require("./resources/globals.php");
//This is a PHP (4/5) script example on how eurofxref-daily.xml can be parsed

//Read eurofxref-daily.xml file in memory
$XMLContent= file("http://www.example.com/stats/eurofxref/eurofxref-daily.xml");
//the file is updated daily between 14:15 and 15:00 CET

foreach ($XMLContent as $line) {
if (ereg("currency='([[:alpha:]]+)'",$line,$currencyCode))
{
if (ereg("rate='([[:graph:]]+)'",$line,$rate))
{
//Output the value of 1 EUR for a currency code
//echo '1 € = '.$rate.' '.$currencyCode[1].'<br />';
$rate= ''.$rate[1].'<br />';
$currencyCode=''.$currencyCode[1].'<br />';

$updateStmt=("UPDATE currency SET rate='$rate' WHERE code='$currencyCode'");
//$updateStmt=("UPDATE currency SET rate='$rate',code='$currencyCode'");

///////////////////////////////////////// DATABASE////////////////////////////////
// Connects to your Database
mysql_connect($localhost, $userName, $password) or die(mysql_error());
mysql_select_db($userName) or die(mysql_error());

mysql_query($updateStmt)or die(mysql_error());
}
}
}

$data = mysql_query("SELECT * FROM currency") or die(mysql_error());
Print "<table border cellpadding=3>";

while($info = mysql_fetch_array( $data ))
{
Print "<tr>";
Print "<th>Key:</th> <td>".$info['code'] . "</td> ";
Print "<th>Ref:</th> <td>".$info['rate'] . "</td></tr> ";

}
Print "</table>";

mysql_close();
///////////////////////////////////////// DATABASE////////////////////////////////

///////////////////////////////////////////
php ?>
<body>
</body>
</html>

[1][edited by: eelixduppy at 4:45 pm (utc) on Mar. 10, 2009]
[edit reason] example.com [/edit]

Habtom

9:57 am on Mar 10, 2009 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

This line is not correct:
$updateStmt=("UPDATE currency SET rate='$rate' WHERE code='$currencyCode'");

Try:

$updateStmt="UPDATE currency SET rate='$rate' WHERE code='$currencyCode'";

After your replace the line, if you don't see the result you wanted, place an echo and exit statement right after the above line, and see what is going into the UPDATE statement.

$updateStmt="UPDATE currency SET rate='$rate' WHERE code='$currencyCode'";
echo $updateStmt;
exit();

Phobia1

10:12 am on Mar 10, 2009 (gmt 0)

10+ Year Member

Hi and thanks to all that helped. The code is now working, I git confused by relying on the echo $updateStmt and it lie to me...:-) I tried INSERT INTO and noticed that USD<b> was inserted....errrrrrr.
Thanks again
best from Bulgaria
F