I need to count the number of columns that have a specific value (1) in a specific row (250). The value of the row is variable according to the query.
I tried the code below, but it didn't work.
$total_r = mysqli_query($con, "SELECT * FROM registers WHERE ID = '$ID' AND 1 IN (column1, column2, column3, column4)";
$total = mysqli_num_rows($total_r);
echo $total;
I need the result as a number, lets say "2 columns" or something like that.
In MySQL Booleans become 0 or 1 in numerical context. So you could add expressions checking for a column to be equal to 1.
SELECT (column1 = 1)
+ (column2 = 1)
+ (column3 = 1)
+ (column4 = 1)
FROM registers
WHERE id = ?;
Check with below query
SELECT COUNT(*) TOT FROM registers WHERE ID = '$ID' AND column1='1' AND column2='1' AND column3='1' AND column4='1'
What finally did was a general query and then fetch and save every column value into a variable. Then i just added them, because the value of each column could be 0 or 1. Here is the code:
$query = mysqli_query($con, "SELECT * FROM registers WHERE ID = '$ID'");
$result = mysqli_fetch_array($query);
$column1 = $result['column1'];
$column2 = $result['column2'];
$column3 = $result['column3'];
$total = $column1 + $column2 + $column3;
If the column has a value = 0, will not be considered into the count.
What about:
$query = mysqli_query($con, "SELECT * FROM registers WHERE ID = '$ID'"); $result = mysqli_fetch_array($query); $columns_with_value_1 = array_count_values($result)[1];
SELECT ( (column1 = 1) + (column2 = 1) + …) AS total FROM registers WHERE ID = '$ID'(minus the possible SQL injection part where$IDgets inserted, of course.)NULL?