Problem with variables 1

[presfox]

I have a script wich is parsing info from an XML page and then storing it into the following variables:

$regplayers
$activeplayers
$deadplayers

Now if i want to get it into the DB:

$query =
"INSERT INTO stats
(
    regplayers, activeplayers, deadplayers
)
VALUES
(
    '$regplayers', '$deadplayers', '$activeplayers'
)";

$sql = mysql_query($query) or die("Invalid query: " . mysql_error());

I made the query to be printed and then it looks fine all stats are correct

but in my database it just adds them with all values at 0

what do i do wrong???

 

[sleipnir214]

Are these three columns numeric? If so, the single-quotes should not be there.



Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[presfox]

this is my table:

CREATE TABLE `stats` (
`id` int(11) NOT NULL auto_increment,
`regplayers` decimal(7,6) NOT NULL default '0.000000',
`activeplayers` int(6) NOT NULL default '0',
`deadplayers` int(6) NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `id_2` (`id`),
KEY `id` (`id`)
) TYPE=MyISAM AUTO_INCREMENT=19 ;

 

[presfox]

Invalid query: 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 '1.310, 130, 709 )' at l

this is what it says when i remove the '

$query =
"INSERT INTO stats
(
    regplayers, activeplayers, deadplayers
)
VALUES
(
   $regplayers, $deadplayers, $activeplayers
)";

$sql = mysql_query($query) or die("Invalid query: " . mysql_error());

 

[DRJ478]

The answer is in sleipnir's post.
Only strings need to be quoted in SQL statements. Numeric values are just passed as is:

$query =
"INSERT INTO stats
(
    regplayers, activeplayers, deadplayers
)
VALUES
(
    $regplayers, $deadplayers, $activeplayers
)";

 

[presfox]

you have the exact code as i tryed.

it doesnt work

see my post for the error i get

 

[sleipnir214]

I dunno. I created a table using your CREATE TABLE query.

I then successfully ran the insert query:

INSERT INTO stats
(
regplayers, activeplayers, deadplayers
)
VALUES
(
1.310, 130, 709
)

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[presfox]

yes when i try numbers it works fine

its the variables that dont work

full code:

<?
$username=&quot;&quot;;
$password=&quot;F&quot;;
$database=&quot;&quot;;

mysql_connect(localhost,$username,$password);

@mysql_select_db($database) or die( &quot;Unable to select database&quot;);

$buffer = '';
//Put the file you want to read from below
$handle = fopen (&quot;[URL unfurl="true"]http://war1.world-of-war.com/xmlshare/serverinfo.xml&quot;,&quot;r&quot;);[/URL]
while (!feof($handle)) {
//Adjust the number to be as small as possible. It specifies how many bytes you want to read in one line in the text file you're reading from. If you are reading from a large file then increase the value.
$buffer .= fgets($handle,1024);
}
fclose($handle);

$start = strpos($buffer,'<regPlayers>');
$end = strpos($buffer,'</regPlayers>');
$length = $end-$start;
$regplayers = substr($buffer, $start, $length);

$buffer = '';
//Put the file you want to read from below
$handle = fopen (&quot;[URL unfurl="true"]http://war1.world-of-war.com/xmlshare/serverinfo.xml&quot;,&quot;r&quot;);[/URL]
while (!feof($handle)) {
//Adjust the number to be as small as possible. It specifies how many bytes you want to read in one line in the text file you're reading from. If you are reading from a large file then increase the value.
$buffer .= fgets($handle,1024);
}
fclose($handle);

$start = strpos($buffer,'<activePlayers>');
$end = strpos($buffer,'</activePlayers>');
$length = $end-$start;
$activeplayers = substr($buffer, $start, $length);

$buffer = '';
//Put the file you want to read from below
$handle = fopen (&quot;[URL unfurl="true"]http://war1.world-of-war.com/xmlshare/serverinfo.xml&quot;,&quot;r&quot;);[/URL]
while (!feof($handle)) {
//Adjust the number to be as small as possible. It specifies how many bytes you want to read in one line in the text file you're reading from. If you are reading from a large file then increase the value.
$buffer .= fgets($handle,1024);
}
fclose($handle);

$start = strpos($buffer,'<deadPlayers>');
$end = strpos($buffer,'</deadPlayers>');
$length = $end-$start;
$deadplayers = substr($buffer, $start, $length);

$buffer = '';
//Put the file you want to read from below
$handle = fopen (&quot;[URL unfurl="true"]http://war1.world-of-war.com/xmlshare/serverinfo.xml&quot;,&quot;r&quot;);[/URL]
while (!feof($handle)) {
//Adjust the number to be as small as possible. It specifies how many bytes you want to read in one line in the text file you're reading from. If you are reading from a large file then increase the value.
$buffer .= fgets($handle,1024);
}
fclose($handle);

$start = strpos($buffer,'<gameTime>');
$end = strpos($buffer,'</gameTime>');
$length = $end-$start;
$gametime = substr($buffer, $start, $length);



print &quot;REGPLAYERS= $regplayers<br />ACTIVEPLAYERS= $activeplayers <br />DEADPLAYERS= $deadplayers <br />GAMETIME= $gametime<br />&quot;;



$query =
&quot;INSERT INTO stats
(
    id, regplayers, activeplayers, deadplayers
)
VALUES
(
   '', $regplayers, $deadplayers, $activeplayers
)&quot;;

$sql = mysql_query($query) or die(&quot;Invalid query: &quot; . mysql_error()); 








mysql_close();



?>

 

[sleipnir214]

If you output the query, then copy-and-paste it into your preferred MySQL management app, does the query work?

If you output the query to a text file and examine the file with a hex-capable text editor, are there any weird characters in the query?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[presfox]

if i print it and after that use it it works fine

and their are no weird chars

 

[presfox]

SQL-query : [Edit] [Create PHP Code]
INSERT INTO stats( id, regplayers, activeplayers, deadplayers )
VALUES (
'', 1.311, 130, 710
)

yep running fine as i expected

output can be found at

http://www.wowstats.netclans.com/update.php

 

[DRJ478]

It sounds odd.
It may be worth a try to rewrite your SQL to use the SET syntax:

INSERT INTO stats SET regplayers=$regplayers, activeplayers=$activeplayers, deadplayers=$deadplayer

Try that and see if it works.
Some 'comfort' code that might help you extract the values a bit more efficiently:

function getValue($tagName,$subject) {
	$pattern = &quot;/<$tagName>(.+)<\/$tagName>/&quot;;
	preg_match($pattern,$subject,$resultArr);
	return($resultArr[1]);
}

Just pass the name of the tag and the buffer. It will extract the value between the tags.

 

[sleipnir214]

presfox:
I have it. DRJ478's code suggestion was the key.

strpos() returns an explicit string. preg_match() sets the return array to an array of mixed type.

PHP is not typecasting the values inserted into the query when it passes the query directly to MySQL.

With the inclusion of DRJ478's code and some improvements to efficiency by the script's not unnecessarily fetching the data multiple times, here is code that works on my machine:

<?php
$username=&quot;test&quot;;
$password=&quot;test&quot;;
$database=&quot;test&quot;;

$value_points = array
(

	'regPlayers',
	'activePlayers',
	'deadPlayers',
	'gameTime'
);

$values = array();


function getValue ($tagName,$subject)
{
	$pattern = '/<' . $tagName . '>(.+)<\/' . $tagName . '>/';
	preg_match ($pattern, $subject, $resultArr);
	return ($resultArr[1]);
}

mysql_connect('localhost',$username, $password);

mysql_select_db($database) or die( &quot;Unable to select database&quot;);

$buffer = '';
$handle = fopen (&quot;[URL unfurl="true"]http://war1.world-of-war.com/xmlshare/serverinfo.xml&quot;,&quot;r&quot;);[/URL]

while (!feof($handle))
{
	$buffer .= fgets($handle,1024);
}
fclose($handle);

foreach ($value_points as $value_point)
{
	$values[$value_point] = getValue($value_point, $buffer);
}

$query =
&quot;INSERT INTO stats
(
	regplayers, deadplayers, activeplayers
)
VALUES
(
	&quot; . $values['regPlayers'] . &quot;, &quot; . $values['deadPlayers'] . &quot;, &quot; . $values['activePlayers'] . &quot;
)&quot;;

$sql = mysql_query($query) or die(&quot;Invalid query: &quot; . mysql_error()); 

?>

The minimum necessary change to your code is to explicitly force PHP to typecast in the line:

'', $regplayers, $deadplayers, $activeplayers

By changing it to read:

'', " . (float)$regplayers . ", " . (int)$deadplayers . ", " . (int)$activeplayers . "


Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!