PHP variables in WHERE clause, how to?

Question

I have following PHP script. I want to count and print comments for each article.

The id for each article can be "recalled" by this: <?php echo $listing['Listing']['listing_id'];?> (this return the contentid number)

Now, I have this script:

<?php
          $db =& JFactory::getDBO();
          $query = "SELECT COUNT(comments) AS totalcount WHERE contentid = ????? ";
          $db->setQuery($query);
          $count = $db->loadResult();
echo ($count); ?>

I tried to add in WHERE clause this:

"... WHERE contentid = {$listing['Listing']['listing_id']}"

but $count returns "0" zero. How can I add this variable in the WHERE clause?

Thanks in advance!

Did you confirm that the query actually succeeded? Have you output the query string you generated and tried running it manually? — Marc B
– Marc B, Commented Apr 13, 2012 at 18:06

Halcyon · Accepted Answer · 2012-04-13 18:08:42Z

2

In the case of an integer:

$query = "SELECT
    COUNT(comments) AS totalcount
WHERE
    contentid = " . ((int) $listing['Listing']['listing_id']);

In the case of a string:

$query = "SELECT
    COUNT(comments) AS totalcount
WHERE
    contentid = " . mysql_real_escape_string($listing['Listing']['listing_id']);

The biggest thing to be weary of is SQL injection. This makes your queries safe. The explicit cast to int will ensure an int value is passed, even if the value is erroneous, at least you wont be open to any attack.

answered Apr 13, 2012 at 18:08

Halcyon

57.7k10 gold badges93 silver badges131 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DavidF Over a year ago

@Frist van Campen Simply PERFECT! You rock! Thank you!

sarwar026 · Accepted Answer · 2012-04-13 18:19:13Z

2

Use sprintf and escape the string.

$query = sprintf("SELECT COUNT(comments) AS totalcount WHERE contentid = '%s'",mysql_real_escape_string($listing['Listing']['listing_id']));

edited Apr 13, 2012 at 18:19

sarwar026

3,8413 gold badges29 silver badges39 bronze badges

answered Apr 13, 2012 at 18:06

PRNDL Development Studios

3,3102 gold badges37 silver badges51 bronze badges

Comments

Kenny Thompson · Accepted Answer · 2012-04-13 18:10:56Z

1

try

$query = "SELECT COUNT(comments) AS totalcount WHERE contentid = '".mysql_real_escape_string($listing['Listing']['listing_id'])."'";

or

$query = "SELECT COUNT(comments) AS totalcount WHERE contentid = ".mysql_real_escape_string($listing['Listing']['listing_id']);

depending on the data type.

edited Apr 13, 2012 at 18:10

answered Apr 13, 2012 at 18:03

Kenny Thompson

1,49212 silver badges28 bronze badges

4 Comments

mikevoermans Over a year ago

Quoting the string... agreed.

Kenny Thompson Over a year ago

Yes, I suggest mysql_real_escape_string($foo) on variables to prevent injection.

PRNDL Development Studios Over a year ago

Do not use this. appending user-supplied variables is terrible practice.

Kenny Thompson Over a year ago

I don't imagine that an ID would be user supplied, but if it is wrap it with mysql_real_escape_string()

Collectives™ on Stack Overflow

PHP variables in WHERE clause, how to?

3 Answers 3

1 Comment

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related