PHP - How To Count Rows From Query?

I have a mysql table that holds data for cars - each car has it's own ID, name, and year. Some cars are repeated.

Example: (ID - year - car name)
0001 - 2009 - Honda Civic
0002 - 2008 - Toyota Prius
0003 - 2009 - Honda Civic
0004 - 2008 - Toyota Prius
0005 - 2008 - Toyota Prius
0006 - 2007 - Honda Civic

How would I count how many of the same type of cars I have in that table and then display the quantity next to the car name? Like for the above example it would be:

2009 Honda Civic: 2
2007 Honda Civic: 1
2008 Toyota Prius: 3

Thanks in advance for any help  :D

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?

Edited August 17, 2019 by imgrooot

month   a0   a1   a2   a3   a4   a5   a6   a7   a8   a9   a10   a11   a12   a13   a14   a15   a16   a17   a18   a19   a20   a21   a22   a23   a24   a25   a26   a27   a28   a29   a30   a31
         1   puudu   01   02   03   04   05   06   07   08   09   10   11   12   13   14   15   16   17   18   19   [20]   [21]   22   23   24   25   [26]   27   28   29   30   31
         2   puudu   01   02   03   04   05   06   07   08   09   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28           
         3   puudu   01   02   03   04   05   06   07   08   09   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31
         4   puudu   01   02   03   04   05   06   07   08   09   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   
         5   puudu   01   02   03   04   05   06   07   08   09   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31
         6   puudu   01   02   03   04   05   06   07   08   09   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   
         7   puudu   01   02   03   04   05   06   07   08   09   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31
         8   puudu   01   02   03   04   05   06   07   08   09   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31
         9   puudu   01   02   03   04   05   06   07   08   09   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   
         10   puudu   01   02   03   04   05   06   07   08   09   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31
         11   puudu   01   02   03   04   05   06   07   08   09   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   
         12   puudu   01   02   03   04   05   06   07   08   09   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31


;
i need help .. with calculating how many days are between example:  january = 1  ; day =23 to feb =2; day 23
in php ... how can i do that

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

This displays the total rows in the table.

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

This displays '1', which is incorrect.   I want to echo out the number of rows where 'friend_system_accepter' = $myusername   Thanks

I have a html table displaying data from mysql database. I can count rows properly with mysql_num_rows but is there a way to echo which row number it is. What I want to do is count the rows ordered by cities and echo row number. Thanks for any help.

The following works, but I don't want to have to write 50 query statements for 50 states.  How can I just write one query (using a variable I presume) and then echo the variable count each time for each state?  I'm just trying to list the total number of rows for each state, that exist in the db. 

<?         
$result = mysql_query("SELECT * FROM mytable WHERE source = 'alabama'") 
or die(mysql_error());  
$num_rows = mysql_num_rows($result);

?>
 
href="/state/alabama/">alabama</a> (<?php echo("$num_rows");?>) <br>
 
 <?
$result = mysql_query("SELECT * FROM mytable WHERE source = 'alaska'") 
or die(mysql_error());  
$num_rows = mysql_num_rows($result);
?>
href="/state/alaska/">alaska</a>  (<?php echo("$num_rows");?>) <br>   

 
The above would give output such as:
alabama (122)
alaska (212)

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(); 
                } 
            } 
	

Basically, this part of the code is checking the "scanned" table in my mysql database to get the last scanned date for the item at hand.  It then takes that date and selects all the vendor items in the odbc table "ARVEND" that are greater than the last purchase date.  || This is where I am running into a problem.  When it loops through each vendor item it selects from the "associated" mysql table to see if the vendor item has been scanned.  If it does not find anything I am trying to get the $checkpurdate variable to add 1 to itself. |

In the second script you will see the elseif($checkpurdate>0) .... Here I am saying if there are any vendor items that have not been scanned, highlight the row in yellow.

But, this part of my code $checkpurdate = $checkpurdate + 1; does not seem to be counting correctly.  Sometimes it counts '4' for an item when there are only two vendor items available.

All of this may sound confusing since you may not know what I am trying to do.  But, the main problem I am having is the counting part is returning wrong numbers. $checkpurdate = $checkpurdate + 1;


Code: [Select]
/* $checkpurdate  */

 $query3  = "SELECT date FROM scanned WHERE `item` = '$ITEM' ORDER BY date DESC LIMIT 1";
$result3 = mysql_query($query3);
$checklastdate = "";
while($row3 = mysql_fetch_array($result3, MYSQL_ASSOC))
{$checklastdate= $row3['date'];
$checklastdate=date("m/d/y", strtotime($checklastdate));   }

//checkpurdate loops
$checkassoc="0";
$checkpurdate="0";
$sqlc="SELECT RECNO5 FROM ARVEND WHERE PURDATE1 > '$checklastdate' AND ITEM = '$ITEM'";
$rsc=odbc_exec($conn,$sqlc);
if (!$rsc)
  {exit("Error in SQL");}
 
while (odbc_fetch_row($rsc))
  {
  $checkrecno5=odbc_result($rsc,"RECNO5");
$checkassosquery  = "associated WHERE `VENDORID` = '$checkrecno5' AND ITEM = '$ITEM'";
$checkassoc = get_rows($checkassosquery);
 
  if($checkassoc>0)
  {
  // do nothing
  }
  else
  {
  $checkpurdate = $checkpurdate + 1;
  }
 
 
 }
 
Code: [Select]
  elseif($checkpurdate>0)
{  echo"<TR class=\"NV\"> <TD>";
}

Hello there,

Having a nightmare here.  It feels like what I need to do is really easy but I just can't get it to work.  I have a table called "groups2" that holds a unique id and the name of an activity.

I already have a query that finds the users selected groups using a while loop but I also want to show how many other members are in that group by counting the number of times the activity comes up or the id comes up.  I don't know whether I need 2 while loops nested or what but I get the error

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource on line 32 which is highlighted.

Can anyone help.  I am no expert at php and still learning so some advice or example code would be great.


$result = mysql_query("SELECT * FROM groups2 WHERE L_ID = ". $_SESSION['member_ID'] .";");
$data = mysql_query("SELECT COUNT('name') AS num FROM groups2 WHERE name = " . $row['name'] . "");
$count = mysql_fetch_assoc($data);
$numbers = $count['num'];
while ( $row = mysql_fetch_assoc($result) ) {
echo("<tr>
<td><font face='Arial, Helvetica, sans-serif' size='3'><strong>" . $row["name"] . "</strong></font></td>
</tr><tr>
<td><font face='Arial, Helvetica, sans-serif' size='1' color='#0000FF'><strong>Members (" . $numbers . ")</strong></font></td>
</tr><tr>
<td><hr width=95%><br></td>
</tr>");
}

Hello i am working on an application where i want to count from a selected date to another date and query all entries my database right now is composed of the following
sales_id
barcode_id
student_id
type of lunch
date

i have created the count per day, but now dont know how to go about to query the results and the count for the month.
Code: [Select]
$query = 'SELECT COUNT(*) FROM `sales` WHERE `tlunch` = 1 AND DATE(date) = CURDATE()';
$result = mysql_query($query) or die('Sorry, we could not count the number of results: ' . mysql_error());
$free = mysql_result($result, 0);

has any one done something similar

The code below is part of some validation code that I have, it is supposed to count the number of entries in the table 'test_selections' where 'userid' equals the user that is logged in and where the 'transfer_in' date is a date within the current month. I am getting an error saying "Error running query Unknown column '10' in 'where clause' ". Does anybody know why this could be? I am hoping that it is something to do with the snippet of code below so I don't need to post the whole lot!!

Any help would be much appreciated.

Code: [Select]
$current_month = date("m");
$query = "SELECT COUNT(`userid`) as `transfers_count`
          FROM `test_selections`
          WHERE `userid` = '{$_SESSION['userid']}' AND Month(`transfer_in`) = `$current_month`
          GROUP BY `userid`";

Hi !

I am trying to translate my mysqli count query that works perfectly into prepared statements.

Unfortunately, after playing around and using my knowledge of PS, I have come up with this script which fails to execute and returns a http 500 error.

I may have missed something very silly, I require some guidance on fixing the error.

 

 

<?php


$conn = mysqli_connect("xxxx", "xxxx", "xxxx", "xxx");

$sel_query = "SELECT 
    S1, B1
    COUNT(IF(S1 = ?, 1, NULL)) 'Accepted',
    COUNT(IF(S1 = ?, 1, NULL)) 'Rejected',
    COUNT(IF(S1 = ?, 1, NULL)) 'Under_Review' FROM Enrol";
    
    $stmt = $conn->prepare($sel_query); 
    
	$Accepted="Accepted";
	$Rejected="Rejected";
	$Under_Review="Under Review";

	
	$stmt->bind_param("sss",$Accepted, $Rejected, $Under_Review);
	$stmt->execute();
	$result = $stmt->get_result(); // get the mysqli result
	  
	if($result->num_rows === 0) exit('No records found!');
	while($row = $result->fetch_assoc()) {  ?>
  
   
<tr>
        
        <td><?php echo $row["Accepted"]; ?></td>
        <td><?php echo $row["Rejected"]; ?></td>
        <td><?php echo $row["Under_Review"]; ?></td>
</tr>




</table>

 

 

Edited June 24, 2020 by PythonHelp

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=312690.0

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=330871.0

I have a database lets say punch_log

The data in the table looks like:
---------------------------------------------------------------------------
|punch_log_id |  user_id | punch_id |   punch_time           |
---------------------------------------------------------------------------
|   10010         |  21         |   1       | 2010-11-10 15:04:59|
|   10011         |  21         |       2       | 2010-11-10 15:50:05|
|   10010         |  21         |   1       | 2010-11-11 15:04:59|
|   10011         |  21         |       2       | 2010-11-11 15:50:05|
|   10010         |  21         |   1       | 2010-11-12 15:04:59|
|   10011         |  21         |       2       | 2010-11-12 15:50:05|
|   10010         |  21         |   1       | 2010-11-13 15:04:59|
|   10011         |  21         |       2       | 2010-11-13 15:50:05|
|   10010         |  21         |   1       | 2010-11-14 15:04:59|
|   10011         |  21         |       2       | 2010-11-14 15:50:05|
|   10010         |  21         |   1       | 2010-11-14 15:50:59|  <-- this is why i need this.
|   10011         |  21         |       2       | 2010-11-14 15:55:05|  <-- this is why i need this.
----------------------------------------------------------------------------

Im currently using :

$kust  = $_POST['AKuu'];
$kuni3 = $_POST['LKuu'];
$valitudtootaja   = $_POST['TNimi'];


mysql_select_db($database, $con);
$query2 = "SELECT *, SUM(punch_id) FROM punch_log WHERE user_id = '".$valitudtootaja."' AND punch_time   BETWEEN '$kust' AND '$kuni3' AND punch_id ='1' ";   
$result2 = mysql_query($query2) or die(mysql_error());
while($row = mysql_fetch_array($result2)){
   $X = $row['SUM(punch_id)'];

When using the current query im getting

Workers days at work = 6
it should be 5 but thats why i need some solution to group dates and then sum them.

Does anybody have any ideas?