count() is definately not counting right. Why so?

burstroo

Could someone help me out with this? I'm trying to debug this snippet to find out why count() is counting off. In this particular snippet it should be echoing 8 as the number of rows found in the database, and it is saying there is only two. I know it probably has to be with the way i'm choosing to go about it so maybe if that's the case someone can show me an alternative route to getting done what I need to get done. Thanks in advance.

 $blog_id = $b['id'];
    $Words = mysql_query("SELECT `word` FROM `tags` WHERE `blog_id`='$blog_id'");
	 $count = count(mysql_fetch_array($Words));
     echo($count);
      $i = 0; 
      while($singleWord = mysql_fetch_array($Words)) {
       $i++;
        $singleClass_start = "<font size='2' style='font-weight:bold;color:#2EA7EC'>";
		$singleClass_end   = "</font>";
       if ($i < $count) {
        $MAINDATA .= $singleClass_start."".$singleWord['word'].", ".$singleClass_end;
       } else {
	    $MAINDATA .= $singleClass_start."".$singleWord['word']."".$singleClass_end;
       } 
      }

MarkR

That's not how count() works. In PHP, count() will count the number of elements in an array- but mysql_fetch_array fetches a row as an array.

So in this context, it will give you the number of columns in the query, which will be 1 (or 2 if it's also associative, which I think it will be in this case).

You should instead use SELECT COUNT(*) FROM tags WHERE blog_id=whatever in SQL.

Then just fetch the single value.

Also you might want to ensure that $blog_id is an integer to protect your application from SQL injection attacks.

Mark

burstroo

Alright thanks for the input. I actually end up went ahead and end up using mysql_num_rows - it works the way I want, but is there any complaint of using it this way? Any past known issues with it? Also, making sure blog_id is an integer. You mean in the db, correct? May sound like a simpleton question, but I want to make sure that I am understanding correctly. If so, I have already made sure of this. 🙂 Thanks again, and thanks in advance for the reply.

    // TAGS 
    $blog_id = $b['id'];
    $Words = mysql_query("SELECT `word` FROM `tags` WHERE `blog_id`='$blog_id'");
    $count = mysql_num_rows($Words);
     echo($count);
      $i = 0; 
      while($singleWord = mysql_fetch_array($Words)) {
       $i++;
        $singleClass_start = "<font size='2' style='font-weight:bold;color:#2EA7EC'>";
		$singleClass_end   = "</font>";
       if ($i < $count) {
        $MAINDATA .= $singleClass_start."".$singleWord['word'].", ".$singleClass_end;
       } else {
	    $MAINDATA .= $singleClass_start."".$singleWord['word']."".$singleClass_end;
       } 
      }

MarkR

burstroo wrote:
Alright thanks for the input. I actually end up went ahead and end up using mysql_num_rows - it works the way I want, but is there any complaint of using it this way?

Yes, it pointlessly reads information from the database; this may create more of an overhead than necessary.

Say there were a million rows in "tags" table, it would definitely take a lot longer, also it could exhaust memory trying to select them all.

Also, making sure blog_id is an integer. You mean in the db, correct?

No, I mean the variable $blog_id from the query string. Otherwise someone could inject SQL into it.

Mark

burstroo

Alright I feel really ignorant when I ask for too much help, but could you please show me what you would do (Even if it is an off snippet, so you don't feel I'm asking for too much help) to implement count(*) into my current queries. I want to keep away from putting up another redundant query if not needed. Also, do you have a link to somewhere I can read up on preventing from SQL injection? Thanks in advance.

bradgrafelman

Simple code sample:

$query = 'SELECT COUNT(*) FROM myTable';
$exec = mysql_query($query) or die('Error: ' . mysql_error());

$numRows = mysql_result($exec, 0);

As for the SQL injection, either search the board or Google for keywords such as "prevent sql injection php".

Basically, the concept is that you don't want to pass ANY data directly into a SQL query string that could have been altered by the remote user. This includes GET/POST data, as well as some elements in the $_SERVER superglobal.

The suggestion mentioned above was to ensure that $blog_id is an integer; to do this, I would use a function such as [man]intval/man. Also, since blog_id is a numeric column in SQL, there should be no quotes around its values (e.g. "WHERE blog_id='$blogid'" shouldn't have the quotes).

ClarkF1

In this example though, COUNT shouldn't be used because the data from the database is actually being used so if you just use COUNT you won't be able to output the word later on