Somename Somename - 2 months ago 11
MySQL Question

Shorten the code without a prepared statement

I want to check if the number is present in the column

col
and run a
mysqli_num_rows
. So far its working with the following code:

//$conn = connection to mysql.

$row1sql = "SELECT * from chktable where col='1'";
$row2sql = "SELECT * from chktable where col='2'";
$row3sql = "SELECT * from chktable where col='3'";
$row4sql = "SELECT * from chktable where col='4'";

$query1sql = $conn->query($row1sql);
$query2sql = $conn->query($row2sql);
$query3sql = $conn->query($row3sql);
$query4sql = $conn->query($row4sql);

$num1 = mysqli_num_rows($query1sql);
$num2 = mysqli_num_rows($query2sql);
$num3 = mysqli_num_rows($query3sql);
$num4 = mysqli_num_rows($query4sql);


I edit the other code based on the
$num
.

How can I shorten this code? Can I use a
for
statement? How do I use it if its applicable? Or any other way to shorten this code?

Thanks.

Answer

This will get the col value and number of rows of it, only for col values 1,2,3,4

$query = $conn->query("SELECT col, count(*) as num FROM chktable GROUP BY col WHERE col IN (1,2,3,4);");
while($row = $query->fetch_object())
    echo "Col: ".$row->col.", Num:".$row->num;

you can do whatever check you want inside the while loop