PHP - How Do I Rewrite This Code To Count Mysql Rows?

Hi

I'm not at all good at PHP and haven't really played with it for some years, so bare with me if code examples are old

I'm looking for a simple solution to count rows (MySQL), without using multiple queries.

The below code is quite simple, counting rows where 'Department' is 'Sales':

$sql ="SELECT * FROM DB WHERE Department ='Sales'";
$result=mysqli_query($con,$sql);
$rowcount=mysqli_num_rows($result);

But what If I need to count the rows for the different departments?:

$sql ="SELECT * FROM DB WHERE Department ='Sales'";
$result=mysqli_query($con,$sql);
$rowcount=mysqli_num_rows($result);

$sql ="SELECT * FROM DB WHERE Department ='Support'";
$result=mysqli_query($con,$sql);
$rowcount=mysqli_num_rows($result);

$sql ="SELECT * FROM DB WHERE Department ='Online'";
$result=mysqli_query($con,$sql);
$rowcount=mysqli_num_rows($result);

There must be a better / simpler way to this with a single query?

Hope for someone to show me the light

PS. Once this is resolved, I'll probably need further help, to accomplish my final result.

It's late and I'm not thinking straight. I'm posting this question. Hopefully I get a reply in the morning.

I have two tables.

TABLE 1 - PRODUCTS

TABLE 2 - LIKES

I am a User who has posted these products. I want to find out ALL the Likes I have received for all my products. Here is my code.

$find_products = $db->prepare("SELECT product_id FROM products WHERE user_id = :user_id");
$find_products->bindParam(':user_id', $my_user_id);
$find_products->execute();
$result_products = $find_products->fetchAll(PDO::FETCH_ASSOC);
if(count($result_products) > 0) {
  foreach($result_products as $row) {
    $product_id = $row['product_id'];

    $find_likes = $db->prepare("SELECT like_id FROM product_likes WHERE product_id = :product_id");
    $find_likes->bindParam(':product_id', $product_id);
    $find_likes->execute();
    $result_likes = $find_likes->fetchAll(PDO::FETCH_ASSOC);
    if(count($result_likes) > 0) {
      $get_likes  = 0;
      foreach($result_likes as $row) {
        $get_likes++;
      }
    }
  }
}

The issue with the above code is that It only shows the Likes if I echo inside the foreach loop. And it'll show combined Likes from each of my products. But I want to actually combine ALL the Likes from ALL the products and be able show them as a single number, outside of the foreach loop.

How do I do that?

 $result = mysql_query("SELECT *  FROM `friend_system` WHERE `friend_system_accepter` = '$myusername' ");
echo mysql_num_rows($result);

$result = mysql_query("SELECT COUNT(friend_system_accepter)  FROM `friend_system` WHERE `friend_system_accepter` = '$myusername' ");
echo mysql_num_rows($result);

Folks,

Having trouble here. Forgot how you use the COUNT. Not interested in the num_rows due to it being slow compared to COUNT.

I have users table like this:

id|username|sponsor_username

0|barand|requinix

1|phpsane|alison

2|mickey_mouse|requinix

Now, I want to check if a sponsor username exists or not. Such as does the entry "requinix" exists or not in one of the rows in the "sponsor_username" column.

In our example it does twice and so the variable $sponsor_username_count should hold the value "2". Else hold "0".

I get error:

Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?' at line 1 

My code:

	$stmt_1 = mysqli_prepare($conn,"SELECT COUNT(sponsor_username) from users = ?"); 
            mysqli_stmt_bind_param($stmt_1,'s',$sponsor_username_count); 
            mysqli_stmt_execute($stmt_1); 
            
            //Show error if 'users' tbl was not successfully queried". 
            if (!$stmt_1) 
            { 
                echo "ERROR 1: Sorry! Our system is currently experiencing a problem loading this page!"; 
                exit(); 
            } 
            else 
            { 
                mysqli_stmt_bind_result($stmt_1,$sponsor_username_count);     
                mysqli_stmt_fetch($stmt_1); 
                mysqli_stmt_close($stmt_1); 
                
                //Check if Sponsor Username in Url ($_GET) is already registered or not. If not then show "Invalid Url" or Link message. 
                if ($sponsor_username_count < 1) 
                { 
                    echo "<b>Invalid Url or Link!<br> Invalid Sponsor: \"$sponsor_username\"!</b><?php "; 
                    exit(); 
                } 
            } 
	

<?php


require('pdf/fpdf/fpdf.php');
include('core/connect.php');


//Create new pdf file
$pdf=new FPDF();


$pdf->Open();


//Disable automatic page break
$pdf->SetAutoPageBreak(false);


//Add first page
$pdf->AddPage();


//Heading
$pdf->SetFillColor(255,255,255);
$pdf->SetFont('Arial','BU',12);
$pdf->SetY(25);
$pdf->SetX(25);
$pdf->Cell(25,6,'Top Site:',9,0,'L',1);


$result_topten = mysqli_query($con, 


"SELECT `site_id` AS `site`, COUNT(*) AS `count`
    FROM `faults`
    GROUP BY `site`
    ORDER BY count(*) DESC;");
  
while($row = mysqli_fetch_array($result_topten))
{


$site   = $row['site'];
$num_faults   = $row['count'];


}
$pdf->SetFillColor(192,192,192);
$pdf->SetFont('Arial','B',10);
$pdf->SetY(35);
$pdf->SetX(25);
$pdf->Cell(50,6,'Site',1,0,'C',1);
$pdf->Cell(50,6,'Number of faults',1,0,'C',1);


$pdf->SetFillColor(232,232,232);
$pdf->SetFont('Arial','',10);
$pdf->SetY(45);
$pdf->SetX(25);
$pdf->Cell(50,6,$site,1,0,'C',1);
$pdf->Cell(50,6,$num_faults,1,0,'C',1);


$i = $i++;


//Send file
$pdf->Output();
?>

<----OUTPUT OMITTED---->

$result=mysqli_query($con, 


"SELECT
 COUNT(IF(level='P1',1, NULL)) 'P1', 
 COUNT(IF(level='P2',1, NULL)) 'P2', 
 COUNT(IF(level='P3',1, NULL)) 'P3' 
 FROM faults");


//initialize counter
$i = 0;


//Set maximum rows per page
$max = 25;


while($row = mysqli_fetch_array($result))
{
//If the current row is the last one, create new page and print column title
if ($i == $max)
{
$pdf->AddPage();


//print column titles for the current page
$pdf->SetY($y_axis);
$pdf->SetX(25);
$pdf->Cell(50,6,'Total P1s',1,0,'L',1);
$pdf->Cell(50,6,'Total P2s',1,0,'L',1);
$pdf->Cell(50,6,'Total P3s',1,0,'L',1);


//Go to next row
$y_axis = $y_axis + $row_height;


//Set $i variable to 0 (first row)
$i = 0;
}


//This bit definitely ain't right
$p1 = $row['level'];
$p2 = $row['level'];
$p3 = $row['level'];




$pdf->SetY($y_axis);
$pdf->SetX(25);
$pdf->Cell(50,6,$p1,1,0,'L',1);
$pdf->Cell(50,6,$p2,1,0,'L',1);
$pdf->Cell(50,6,$p3,1,0,'R',1);


//Go to next row
$y_axis = $y_axis + $row_height;
$i = $i + 1;
}


//Send file
$pdf->Output();


?>