what could be wrong with this query statement

[thepreacher]

Ok please find below the code you requested

Code

Language: javascript (GeSHi-highlighted)
		
var transcode = document.fxratescr.transcurcode.value;
		var recvcode = document.fxratescr.recevcurcode.value;
		
			getRequestobject();
				objRequest.onreadystatechange = handleHttpResponse2;
				var url = 'fetchfxData.php?param1=fxrate';
				objRequest.open("GET", url+'&param2='+transcode+'&param3='+recvcode, true);
				objRequest.send(null);
			
						function handleHttpResponse2() {
			if (objRequest.readyState == 4) {
		        if (objRequest.status == 200) {
					//var results2 = eval("(" + objRequest.responseText + ")");
					var results2 = objRequest.responseText;
					//alert(results2.messages.message[0].dateset);
		            alert(results2);
		            objRequest=null;
					document.fxratescr.dateset.value = results2.messages.message[0].dateset;
					document.fxratescr.transcurcode.value = results2.messages.message[0].transcurcode;
					document.fxratescr.recevcurcode.value = results2.messages.message[0].recevcurcode;
					document.fxratescr.midrate.value = results2.messages.message[0].midrate;
					document.fxratescr.periodtype.value = results2.messages.message[0].periodtype;

and the PHP is

Code

Language: php (GeSHi-highlighted)
$param1 = $_GET["param1"]; //tablename
$param2 = $_GET["param2"];
$param3 = $_GET["param3"];
 
	$query = "SELECT * FROM fxrate WHERE transcurcode = '$param2' AND recevcurcode = '$param3'";
  $json = '{"messages": {';
  if ($count > 0) 
	{	
		$json .= '"message":[ ';
		while ($row = mysql_fetch_array($result)) 
		{
		$json .= '{';
		$json .= '"dateset": "' . htmlspecialchars($row['dateset']) . '",
					"transcurcode": "' . htmlspecialchars($row['transcurcode']) . '",
					"recevcurcode": "' . htmlspecialchars($row['recevcurcode']) . '",
					"midrate": "' . htmlspecialchars($row['midrate']) . '",
					"periodtype": "' . htmlspecialchars($row['periodtype']) . '",
					"fromdate": "' . htmlspecialchars($row['fromdate']) . '",
					"todate": "' . htmlspecialchars($row['todate']) . '",
				},';	
	    }
        $json=substr($json,0,-1); //VGR REM get rid of the last comma ;-)))
		$json .= ']';
	}
	$json .= '}}';
	echo $json;

thanks

[VGR]

well, I don't know if $count is set, but I can tell you there is something BAD happening to $param1, $param2 and $param3 between the point where you set them to $_GET equivalents, and the point qhere you build the $query, because this proves it works just fine :

http://www.fecj.org/extra/test_preacher.php?param1=tablename&param2=pa_2_value&param3=par_3_value

that's the test code :

Code:

<?php
//
// test_preacher.php (ert)
//
//VGR01042007 Creation
//
$param1 = $_GET['param1']; //tablename
$param2 = $_GET['param2'];
$param3 = $_GET['param3']; //VGR REM fixed idiotic double quotes
 
$query = "SELECT * FROM fxrate WHERE transcurcode = '$param2' AND recevcurcode = '$param3';"; //VGR REM added trailing semicolon (SQL terminator)
echo "q='$query'<br>";
echo "done.";
?>

[VGR]

BTW, your comment :

$param1 = $_GET['param1']; //tablename

makes me wonder whether $param1 should replace the litteral 'fxrate" in the query, or not. Up to you.

[mishkad]

I'm quite sure that query for HTML issues - like  add or strip slashes, double quotes and the nastiest as mentioned htmlspecialchars() - so eventually when U output syntax to screen and run it at console or what ever your MySQL tool is everything works fine but will constantly fails at real run - and may drive you crazy sometimes ;-).

M.D.

[VGR]

yes, but as I provided a very basic code for testing, he can see if th ebehaviour is different on his computer (server) than on mine.
usually the problems arise from magic_quoting or urlencoding. I doubt he passes any non-ASCII-7bits characters in the param1, param2 and param3 (those being mysql settings like tablenames, and row names ;-) but as far as magic_quoting occurs... he hs to test.
moreover, he uses  a GET method call, so magic quoting shouldn't be a problem theoretically.
if the sample code I provided does what it has to do on your server, just add the mysql_query($query) call afterwards... AND IT SHOULD WORK.

if not, come back with more feedback.

[thepreacher]

Thanks VGR, Rob and all. VGR your code worked and am now a happy man. BTW VGR can you  please elaborate on why you changed the " in the

Code

Language: php (GeSHi-highlighted)
$param3 = $_GET["param3"];

to

Code

Language: php (GeSHi-highlighted)
$param3 = $_GET['param3']; //VGR REM fixed idiotic double quotes 
 

also is it conventional the addition of the trailing semicolon to the quesry statement :

Code

Language: php (GeSHi-highlighted)
$query = "SELECT * FROM fxrate WHERE transcurcode = '$param2' AND recevcurcode = '$param3';"; //VGR REM added trailing semicolon (SQL terminator)
echo "q='$query'<br>";

Thanks once again

[VGR]

fast answer :

1) the actual string litteral delimitor in SQL AND in PHP is the single quote.
example 'this is a test phrase'

PHP admits two other ways (and perhaps more, see "types and constants" and "expressions" in PHP manual, top sections at www.php.net/manual/en/ )
"this is a $variable phrase" which will produce the same result if echoed if you state ***beforehand*** that 

Code:

$variable='test';

(note the single quotes)

other possibility (my favourite, also to be found at the forementioned web pages)
echo<<<MARKER
this is a $variable phrase<br>
and a very<br>
long string that can span an entire script<br><hr>
that's the way I transform a HTML static page to a PHP dynamic one because <br>
replacing variables by their $values is easy even for array, matrix or session/get/post {$_SESSION['somevar']}<br>
the onyl bad case are the constants that can not be "expanded"
you can mix 'all' kind of "quotes" or escaped stuff\"toto"youpie\" in there.
MARKER;

MARKER can be almost any string you like ; usually rather shorter : EOS (end-of-string), EOR (repeat), BB, EOT (table) those are just mnemonics)

So my comment about "idiotic" means that :
- single quotes are fast. they don't enable "variable expansion" (or "replacement with values")
- double quotes do allow such expansion, and are thus slower.
- using double quotes with a litteral (ie, no variables to expand) is idiotic.
ok ?

single quotes ar ethe rule, double are the exception.

2) single quote is THE string delimitor in SQL, and the ONLYone. SQL is a standardized ISO language. You've no choice.