what could be wrong with this query statement

[thepreacher]

"SELECT * FROM FXRATE" works as expected. But

$query = "SELECT * FROM fxrate WHERE transcurcode = '$param2' AND recevcurcode = '$param3'";

failes to return anything. If i run the query  replacing the variable names with actual data it works.

thanks.

[rdivilbiss]

You are using PHP?

[thepreacher]

yes its php

[rdivilbiss]

Sorry, duh.

I only use parameterized SQL statements not inline SQL, so I confused myself. (also known as PREPARED statements in the PHP/MySQL world.)

"SELECT * FROM fxrate WHERE transcurcode = '" . $param2 . "' AND recevcurcode = '". $param3 ."'";

Try that.

And assuming
$query = "SELECT * FROM fxrate WHERE transcurcode = '" . $param2 . "' AND recevcurcode = '". $param3 ."'";
$result =  $query;

if (!$result) {
     $message  = "My Invalid query: " . mysql_error() . "<br>\n";
     $message .= "My Whole query: " . $query . "P2=" .$param2. "  P3=". $param3 . "<br>\n";
     die($message);
}

To see that the query is built correctly and that the parameters have the correct data or any data.

[thepreacher]

thanks Rob. can you link me to any good read on MYSQL?PHP prepered statement?

[VGR]

in fact, prepared statements are a benefit when you've repeated execution of the same statements. It's also an obligation when using Oracle, because Oracle is inefficient. Prepared statements have an overhead attached to them. Inline statements are faster to execute the first time. Prepared statements have NO MEANING when the RDBMS has a query cache ;-)

MySql has a wonderful query cache, so don't use prepared statements and you'llreach maximum efficiency :D

it's as simple as that IMHO

your query failed probably because you enclosed it in single quotes. You've to understand PHP expands variables only between double quotes or in an echo<<<MARKER...MARKER; statement (the most convenient type)

the best sugegstion I can do to you is to echo the query just the line before execution, after preparing it.
if you don't want to echo it, then echo it in an HTML comment.

for instance :

Code:

      $query="update $locName set parent=$papa2, contenu=CONCAT('(recovered)<br>',contenu) where id=$courant";
      $result=mysql_query($query,$linkID2) or die ("bad query FIX messages in '$query' . ".mysql_error());

if the query doesn't FAIL but does not return any dataset either, you've to understand WHY :

Code:

      $query="update $locName set parent=$papa2, contenu=CONCAT('(recovered)<br>',contenu) where id=$courant";
echo "q='$query'<br>";
      $result=mysql_query($query,$linkID2) or die ("bad query FIX messages $query . ".mysql_error());

or

Code:

      $query="update $locName set parent=$papa2, contenu=CONCAT('(recovered)<br>',contenu) where id=$courant";
echo "
<!--q=$query-->
";
      $result=mysql_query($query,$linkID2) or die ("bad query FIX messages $query . ".mysql_error());

(new lines will be put in the HTML source so that you may find the html comment right on start of a line)

regards

[VGR]

ok, re-reading your original posting, I have two explanations why your - correctly written AFAIK - query didn't work as expected :
- (least probable) variables don't hold the value you think they do
- (most probable) variables do contain non-printable characters like EOLN and consorts [thus it's the same reason as above]

try to output strlen($variable)."'".htmlspecialchars($variable)."'" and check out precisely
usually, the variable appears as 2 characters longer than it should : you've a \r\n embedded in it, that screws up your query.
classical issue.

[thepreacher]

I just outputed the strlen and the variable it self and its ok. :(

[VGR]

oookayyy

use one of the two "echo query" methods exposed above, copy-paste the query between the single quotes AS IS and paste/execute it in mysql console (type "mysql" in so-called "DOS" console, in \mysql_install\bin\ if you didn't set the PATH ))

it should FAIL (add a trailing semicolon, of course) but in case it does work check :
- results
- warnings (do "show warnings")

analyze and come back here with feedback

[thepreacher]

VGR regarding the echo query you are refering to should i replace the variables. If so how do they compare to those in my original post?

cheers and enjoy yourv weekend

[VGR]

no. Leave your variables. The purpose of the "echo" statements(³) is to see&check if the query is ok with the variables expanded ;-)

just do :

Code:

  $query = "SELECT * FROM fxrate WHERE transcurcode = '$param2' AND recevcurcode = '$param3'";
echo "q='$query'<br>";
  $result=mysql_query($query[,$connid]) or die ("bad query '$query' : ".mysql_error());

(³) there are other possibilities : database logging, textfile logging, logging to a specially crafted DIV, SPAN or window via DOM/AJAX, etc

[thepreacher]

below is what i got

Code

Language: php (GeSHi-highlighted)
q='SELECT * FROM fxrate WHERE transcurcode = '' AND recevcurcode = '''<br>

meaning the variables are empty. This is strange because when i echo "strlen($variable)."'".htmlspecialchars($variable)."'", as oyu suggested earlier i get (3'GBP') for param2 and (3'GHC') for param3 which is correct. :(

thanks for your help so far.

[rdivilbiss]

Prepared statements have NO MEANING when the RDBMS has a query cache ;-)

But to the contrary...they have a very real meaning in a web environment, where one should never run in-line SQL statements which have any data which is not hard coded, regardless of whether is has been filtered prior or not.

The parameters of the prepared statement are not treated as SQL.  When you build and execute inline SQL, as is being done here, the variables passed to the SQL are injection vectors.

[VGR]

yes, that's the only purpose they're good at on a production system :D

I hate prepared statements. They are old Oracle time stuff.

[VGR]

thepreacher, show me the entire block of code where you populate the two variables (displace the statements if appropriate) and issue the query. there must be ***some problem somewhere *** ;-)