URL variables in PHP and SQL

Report · Apr 18, 2010

Have what is likely a pretty basic problem, but those are always the hardest ones, where the solution's staring you in the face....

I have a query in a php mysql database that works perfectly if i have it with a hardcoded query like "category=11". But I'm trying to make it so that a passed url variable defines the category. I've implemented the url variable and confirmed the value's being passed (by having an echo statement to show if it worked or not), and then i set up a sprintf statement to take that value and insert it into the SQL statement. Only problem, it comes back saying the query is empty.

I suspect the problem has something to do with variable types string vs integer or something like that, but i can't figure it out. I think my biggest problem is not knowing enough about the debugging tools to be able to test things. I'm used to dev environments where there's an "immediate" window so I can watch data as it's being manipulated. any suggestions on how to track this issue down? here's the code. I stripped it down so it's just a white page with the query. Here's the code, with the most relevant parts highlighted in bold red.

------------------------------------------------------------------------------------------------------ code ------------------------------------------------------------------

<?php require_once('Connections/connection1.php'); ?>

<?php echo $_GET['CT']; ?>

<?php

if (!function_exists("GetSQLValueString")) {

function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

{

if (PHP_VERSION < 6) {

$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

}

$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

switch ($theType) {

case "text":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "long":

case "int":

$theValue = ($theValue != "") ? intval($theValue) : "NULL";

break;

case "double":

$theValue = ($theValue != "") ? doubleval($theValue) : "NULL";

break;

case "date":

$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

break;

case "defined":

$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

break;

}

return $theValue;

}

$trr=$CT; <--- added this so i could switch between hard coded value and url variable easily.

mysql_select_db($database_connection1, $connection1);

$query_R1 = sprintf("SELECT CatalogItems.ID, CatalogItems.NM, CatalogItems.DS, CatalogItems.FS, CatalogItems.TH, CatalogItems.P FROM CatalogItems WHERE CatalogItems.Category1 = %s OR CatalogItems.Category2 = %s OR CatalogItems.Category3 = %s",$trr);

$R1 = mysql_query($query_R1, $connection1) or die(mysql_error());

$row_R1 = mysql_fetch_assoc($R1);

$totalRows_R1 = mysql_num_rows($R1);

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<head>

<title>Untitled Document</title>

</head>

<body>

<?php echo $row_R1['ID']; ?>

</body>

</html>

<?php

mysql_free_result($R1);

?>

------------------------------------------------------------------------------------------------------------------

Report · Apr 18, 2010

First, my standard disclaimer: I don't do php.

....but, it seems to me that if you are going to use more paceholders in your sprintf than arguments, you need to use a placeholder number. I could be wrong, but refer to the php documentation to be sure.

Report · Apr 21, 2010

no sorry, wasn't that. For reference if anyone else has a similar issue, i solved it myself - it was a type definition problem. I had a %s (string) for the variable when it needed to be %d (integer). Also, since i used the same variable 3 times, I had it wrong...

I had (syntax is wrong, just for illustration) :

sprintf "some text %d more text %d more text %d", $X

and it needed to be

sprintf "some text %d more text %d more text %d", $X, $X, $X

Report · Apr 21, 2010

>no sorry, wasn't that.

Sure it was. You said it yourself - "Also, since i used the same variable 3 times, I had it wrong.." In other words, you had more placeholders than arguments.

Report · Apr 21, 2010

not quite. Simply changing the number of arguments didn't fix the problem. Had to repeat the same variable 3 times. I tried what you suggested first, and it didn't work. The big problem was the type mismatch, the number of arguments was secondary. I ended up finding the solution buried in a php reference manual. Ended up being extremely simple. D'oh!

thanks for your help though.

Report · Apr 21, 2010

>Simply changing the number of arguments didn't fix the problem.

>Had to repeat the same variable 3 times.

How is that different? You fixed it by changing the number of arguments, right? The variable you are using 3 times are the arguments for sprintf. You were only using your variable as an argument once. You need to either use it as many times to match the number of placeholders (as you did to fix it). Or you use a different format using a position identifier:

sprintf ("some text %1$d more text %1$d more text %1$d", $X)

>The big problem was the type mismatch

Yes, understood.