Queries equal same amount of rows but should not

[mbrown]

$enabledCount=$db_con->prepare(
                                                "SELECT * FROM users where enabled=1;");
                                                $enabledCount->execute();
                                                $row=$enabledCount->fetchAll(PDO::FETCH_ASSOC);
                                                $usercount=count($row);




                                                $votecount=$db_con->prepare(
                                                "SELECT * FROM votes where motions_id=:motionid");
                                                $votecount->bindParam(':motionid',$motionid);
                                                $votecount->execute();
                                                $voterow=$votecount->fetch(PDO::FETCH_ASSOC);
                                                $votecount=count($voterow);

My code is above. The first one should return 5 but the second one should only return one. What am I missing?

Edited March 6, 2017 by mbrown

[mac_gyver]

you probably have a type conversion problem or a null value that's matching all values.

 

a) how do you know the second query is (apparently) matching 5 rows? you have only shown the code getting the result from the queries. perhaps there's a logic error or a mis-display of the results?

 

b) what exactly does using var_dump($motionid); show and what ARE the motions_id values in the incorrect result set?

 

c) how did the rows of data get inserted into the votes table and what is the column definition for the motions_id column?

[Jacques1]

You're using fetch() for the second query, which returns a single row. When you apply count() to that row, you're counting columns, not rows.

 

What's the point of that counting stuff anyway? You expect one row, you fetch one row, yet for some reason you check for more than one row. What's the real problem here?

[mbrown]

you probably have a type conversion problem or a null value that's matching all values.

 

a) how do you know the second query is (apparently) matching 5 rows? you have only shown the code getting the result from the queries. perhaps there's a logic error or a mis-display of the results?

 

b) what exactly does using var_dump($motionid); show and what ARE the motions_id values in the incorrect result set?

 

c) how did the rows of data get inserted into the votes table and what is the column definition for the motions_id column?

I have printed out the count from both. The rows got inserted when a person votes on a motion. Based on Jacques1 reply it could be because I am using an incorrect function for what I want.

 

You're using fetch() for the second query, which returns a single row. When you apply count() to that row, you're counting columns, not rows.

 

What's the point of that counting stuff anyway? You expect one row, you fetch one row, yet for some reason you check for more than one row. What's the real problem here?

Thank you for this information. That being said, I will do some research to identify what I need to do to count rows not columns. The second query may not always be one. It could be anything between 0 and 5 depending on how many people have voted on it. I need this to determine when we can set the motion_disposition column to PASSED in the motions table.

[Jacques1]

If you only want the row count, then it's extremely inefficient to load the entire table into PHP. Let MySQL do the counting:

<?php

$userCount = $db_con->query('SELECT COUNT(*) FROM users WHERE enabled')->fetchColumn();

$voteCountStmt = $db_con->prepare('SELECT COUNT(*) FROM votes WHERE motions_id = :motionid');
$voteCountStmt->execute(['motionid' => $motionid]);
$voteCount = $voteCountStmt->fetchColumn();

If you want both the count and the actual data, then use fetchAll() in both cases. When you use fetch(), you only get a single row, regardless of how big the result set actually is.

[mbrown]

If you only want the row count, then it's extremely inefficient to load the entire table into PHP. Let MySQL do the counting:

<?php

$userCount = $db_con->query('SELECT COUNT(*) FROM users WHERE enabled')->fetchColumn();

$voteCountStmt = $db_con->prepare('SELECT COUNT(*) FROM votes WHERE motions_id = :motionid');
$voteCountStmt->execute(['motionid' => $motionid]);
$voteCount = $voteCountStmt->fetchColumn();

If you want both the count and the actual data, then use fetchAll() in both cases. When you use fetch(), you only get a single row, regardless of how big the result set actually is.

 

Thanks Jacques1!  then I could do the conditional if $voteCount == $usercount ?

[mbrown]

<?php

$userCount = $db_con->query('SELECT COUNT(*) FROM users WHERE enabled=1')->fetchColumn();
$userCount -> execute();


$voteCountStmt = $db_con->prepare('SELECT COUNT(*) FROM votes WHERE motions_id = :motionid');
$voteCountStmt->execute(['motionid' => $motionid]);
$voteCount = $voteCountStmt->fetchColumn();

if ($voteCount == $ $userCount)
{
     //Update Final Disposition to PASSED
}

else
{
    exit;
}

So something like the above?

[mbrown]

I see I could do it something like this too

 

<?php
/* Delete all rows from the FRUIT table */
$del = $dbh->prepare('DELETE FROM fruit');
$del->execute();

/* Return number of rows that were deleted */
print("Return number of rows that were deleted:\n");
$count = $del->rowCount();
print("Deleted $count rows.\n");
?>

[Psycho]

I'm making some assumptions here, but I would think that a vote should indicate a vote for or against the issue. But, your logic seems to imply that votes are only to indicate a "for" vote and all users must for for in order for it to pass. That seems like odd logic. Even if a unanimous decision is required, you should have some allowance for when someone doesn't cast a vote.

 

Plus, you can easily determine 1) How many voters there are, 2) How many users have votes, and 3) The results of the vote with a single query. You don't state whether the votes table has an identifier for the user who casts a vote - but it should.

 

Query

SELECT COUNT(u.user_id) as users,
       COUNT(v.vote_id) as votes,
       (COUNT(u.user_id)=COUNT(v.vote_id)) as passed
FROM votes v
RIGHT JOIN users u
  ON  v.user_id = u.user_id
  AND v.motion_id = :motion_id

Results would look something like this (3 of 5 users have voted)

users | votes | passed
  5       3        0

Or this (5 of 5 users have voted)

users | votes | passed
  5       5        1

Edited March 6, 2017 by Psycho

[mbrown]

 

I'm making some assumptions here, but I would think that a vote should indicate a vote for or against the issue. But, your logic seems to imply that votes are only to indicate a "for" vote and all users must for for in order for it to pass. That seems like odd logic. Even if a unanimous decision is required, you should have some allowance for when someone doesn't cast a vote.

 

Plus, you can easily determine 1) How many voters there are, 2) How many users have votes, and 3) The results of the vote with a single query. You don't state whether the votes table has an identifier for the user who casts a vote - but it should.

 

Query

SELECT COUNT(u.user_id) as users,
       COUNT(v.vote_id) as votes,
       (COUNT(u.user_id)=COUNT(v.vote_id)) as passed
FROM votes v
RIGHT JOIN users u
  ON  v.user_id = u.user_id
  AND v.motion_id = :motion_id

Results would look something like this (3 of 5 users have voted)

users | votes | passed
  5       3        0

Or this (5 of 5 users have voted)

users | votes | passed
  5       5        1

 

Thanks pyscho. Again  I have only given part of the code.  I did not think about it. I can easily update the SQL to do things like that. 

 

Thanks again