How to disable checkbox if the data already exist in referred table in database PHP?

Question

I am currently trying to figure out why my loop is not working.

Expected behavior:
If a user has already voted (their user_id is in the vote table), disable their checkbox.

Result:
Only the first option, person_1, is disabled.

But if I replace user_id (3) with user_id (2) in the vote table, it will disable the checkbox for person_1, person_2

<?php
$result_userdet = $mysqli->query("SELECT * FROM `user`") 
    or die($mysqli->error);
$vote = $mysqli->query("SELECT * FROM `vote`") 
    or die($mysqli->error);

while ($row = $result_userdet->fetch_assoc()) {
    if (mysqli_num_rows($vote) == 0) { ?>
    <br>
    <input type="checkbox" name="user_id[]" value="<?php echo $row['user_id'];?>" > <?php echo $row['user_name'];?></option>
    <?php 
    } else {
        $result = $vote->fetch_assoc();

        if ($row['user_id'] == $result['user_id']) { ?>
            <br>
            <input type="checkbox" name="user_id[]" value="<?php echo $row['user_id'];?>" disabled> <?php echo $row['user_name'];?></option>
            <?php
        }

        if ($row['user_id'] != $result['user_id']) { ?>
            <br>
            <input type="checkbox" name="user_id[]" value="<?php echo $row['user_id'];?>" > <?php echo $row['user_name'];?></option>  
            <?php
        }
    }
}
?>

It's quite confusing but I think you wanted to make a join query, user and vote tables. There's no connection between them in this way. — Vladan, Sep 07 '20 at 17:42
It is a very bad idea to use `die($mysqli->error);` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) — Dharman, Sep 07 '20 at 17:43
Sorry for my bad english.What I want is to display all user id in user table except those who in the vote table. @Vladan. — Muqri, Sep 07 '20 at 17:50

score 0 · Accepted Answer · edited Sep 07 '20 at 18:18

You're fetching a row from the vote table in each iteration of the user loop:

$result = $vote->fetch_assoc();

This means, assuming results ordered by user_id in ascending order, this:

1st iteration:

$row = ['user_id' => '1', 'user_name' => 'person_1'];
$result = ['user_id' => '1', 'verbal' => 'country'];
$row['user_id'] === $result['user_id']; // '1' === '1' is true => checkbox disabled

2nd iteration: - now we've fetched the second (and last) row of votes with user_id = 3.

$row = ['user_id' => '2', 'user_name' => 'person_2'];
$result = ['user_id' => '3', 'verbal' => 'verbal'];
$row['user_id'] === $result['user_id']; // '2' === '3' is false => checkbox enabled

3rd iteration:

$row = ['user_id' => '3', 'user_name' => 'person_3'];
$result = NULL; // no more votes
$row['user_id'] === $result['user_id']; // '3' === NULL is false => checkbox enabled

There are three possible solutions:

Fetch all your votes first, then look for the respective users. With many users, you'll run out of memory, not really scalable.

$usersVoted = [];
while ($voteRow = $vote->fetch_assoc()) {
    $usersVoted[] = $voteRow['user_id'];
}

// ... then, later:
if (in_array($row['user_id'], $usersVoted)) {
    // has voted, disable the checkbox
}

Query SELECT COUNT(*) FROM `vote` WHERE `user_id` = {$row['user_id']} for each user - this will send a query to the database for each user.

// You should break this up and handle errors properly, this is just an example:
$userHasVoted = $mysqli->query("SELECT COUNT(*) FROM `vote` WHERE `user_id` = {$row['user_id']}")->fetch_row()[0] === '1';

Fetch all users and their vote rows in one SQL statement - easiest and fastest:

$users = $mysqli->query("SELECT `user`.*, `vote`.`verbal` FROM `user` LEFT JOIN `vote` ON `user`.`user_id` = `vote`.`user_id`");

while ($row = $result_userdet->fetch_assoc()) {
    ?>
    <input 
        type="checkbox"
        name="user_id[]"
        value="<?=$row['user_id']?>" 
        <?=($row['verbal'] !== NULL ? 'disabled' : '')?>
    > <?=$row['user_name']?><br />
    <?php
}

If the first two options are not recommended then I would advise to remove them and keep only one in your answer. — Dharman, Sep 07 '20 at 18:18
I like to see all possible solutions on StackOverflow - the lazy, the ugly and the good ones. Someone may think of using the first two not knowing of the excessive memory and/or bandwidth requirements. — ericek111, Sep 07 '20 at 18:24
Thank very much @ericek111, its works!!. Also thank you explaining each of the iteration. I am still learning about PHP so what is the symbol ( ` ) between the sql statement for? — Muqri, Sep 07 '20 at 18:44
The backtick symbol prevents MySQL from parsing the column names as keywords. It's best to avoid using protected words as identifiers, but by putting them in backticks (\`), you can be sure they aren't misrepresented in any way even in future versions (I experienced breakage in older code running on newer MySQLs because of that.) More on that here: https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql Also, if the answer solves your question, kindly mark it as the solution. (And the fellow downvoter could've at least left a comment.) — ericek111, Sep 07 '20 at 18:53

How to disable checkbox if the data already exist in referred table in database PHP?

1 Answers1