Jump to content

Queries equal same amount of rows but should not


mbrown
Go to solution Solved by Psycho,

Recommended Posts



$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 by mbrown
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 ?

Link to comment
Share on other sites

<?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?

Link to comment
Share on other sites

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");
?>
Link to comment
Share on other sites

  • Solution

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 by Psycho
Link to comment
Share on other sites

 

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.