[RESOLVED] Failing sprintf()'ed query

[spookztar]

Hi,

I have a query that suddenly fails when applying sprintf() to it

It looks like this:

$sqlcatquery = sprintf(mysql_query("SELECT categoryname, cart_id FROM categories WHERE BINARY categoryname='%s' AND cart_id=%d"),$category, $cart) OR die(mysql_error());
$row = mysql_fetch_array($sqlcatquery);

• and produces:

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 '%d' at line 1

applying singlequotes to "%d" still produces failure (it's also an integer, so it shouldn't be correct) produces:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/arioch/public_html/formprocessor.php on line 257

• and the result set is empty.

Changing categoryname to "%d" (BINARY produces integer, correct?)

Gives me:

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 '%d AND cart_id='%d'' at line 1

How do I get around this?

[laserlight]

You should apply sprintf() to the query, not to the result of executing the query.

[spookztar]

Thanx for responding, laserlight:

The thing still fails.

Doing:

$sqlcatquery = sprintf("SELECT categoryname, cart_id FROM categories WHERE BINARY categoryname=%d AND cart_id=%d",$category, $cart) OR die(mysql_error());
         mysql_query($sqlcatquery) OR die(mysql_error());

Gives me once again: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/arioch/public_html/formprocessor.php on line 259

The resultset is empty once again.

[laserlight]

Try this:

$query = sprintf("SELECT categoryname, cart_id
    FROM categories
    WHERE BINARY categoryname='%s' AND cart_id=%d",
    $category, $cart);
$result = mysql_query($query) OR die(mysql_error());
$row = mysql_fetch_array($result);

If you still have a problem, then it must lie either with your database connection, your database selection, or your query itself.

[spookztar]

The error was here, and quite stupid:

$test = mysql_query($sqlcatcheck) OR die("<h3 class='warning'>ERROR: Querying database failed.</h3>$add_catform");
$row = mysql_fetch_array($sqlcatcheck) OR die(mysql_error());

• should be

$row = mysql_fetch_array($test) OR die(mysql_error());

That's what you get for programming 10 hours straight :glare:

[spookztar]

Well, I''m not quite out of the woods yet...

I'm at work right now, so I can't test this, but I'm fairly sure I'm right. Just need confirmation.

In this part:

WHERE BINARY categoryname='%s' AND cart_id=%d", $category, $cart);

• the $categoryname variable that holds the value pulled from the "categoryname" field in the database constantly produces a "{". I figure that this is because of the changing of the datatype on categoryname into BINARY when performing the query. What's the proper way of accomodating this when using sprintf()? Is it as simple as just using %b? (without singlequotes)

[spookztar]

Tried it now, no, still doesn't work. Code works fine without sprintf()

$category = mysql_real_escape_string(strip_tags(trim($_POST['product_category'])));

$sqlcatcheck = sprintf("SELECT categoryname, cart_id FROM categories WHERE BINARY categoryname=%b AND cart_id=%d",$category, $cart);

$query = mysql_query($sqlcatcheck) OR die("<h3 class='warning'>ERROR: Querying database failed.</h3>$add_catform");

$row = mysql_fetch_array($query) OR die(mysql_error());
$categorytest = mysql_real_escape_string($row[0]);

$categorytest constantly ends up containing "{" (the content of the "category" field in the first row in the D😎

How do I get sprintf() to function properly with a search using the BINARY datatype for an existing exact match on a binary level in the DB?