Skip to main content
J
Jonathan_Fortis
Inspiring
July 5, 2013
Answered

display amount of records based on a column value PHP

  • July 5, 2013
  • 2 replies
  • 2888 views

i have a table (cfsCustomersImages)

containing the columns

imagesID

image

custID

imageWant

the value of column imageWant is default "N" if the user wants and image they have a form they can check to change the value to "Y"

what i need to show is a value of the total of "Y" values in the column

for example

if 6 "Y"'s are in the columns

6 images have been selected

does this make sense?

This topic has been closed for replies.
Correct answer bregent

thanks for your suggestions, I did already search "mysql column alias" but it didnt really help


>I did already search "mysql column alias" but it didnt really help

Really? The first search result shows you exactly how to do it. Of course, you still need to have a basic understanding of SQL and PHP.

Change your query to:

$query_rsCount = sprintf("SELECT COUNT(*) AS rowCount FROM cfsCustomers, cfsCustomersImages WHERE cfsCustomers.custID = cfsCustomersImages.custID AND custEmail = %s AND imageWANT = 'Y'", GetSQLValueString($colname_rsCustomer, "text"));

then use:

<?php echo $row_rsCount['rowCount']; ?>

2 replies

M
matthew stuart
Inspiring
July 10, 2013

If you just want a total amount of records that have been returned by your query, place your cursor on the page where you want the figure to appear, then go to your bindings panel and click on the +, then go to Display Record Count --> Display Total Records and select the recordset you want to return the count from.

Does this answer your question?

B
bregent
Participating Frequently
July 10, 2013

Matthew, the number of records returned by his second query will always be one,  so that won't work. If he wants to get the number of records from his first query where he's not filtering on the 'imageWANT' column, then that would be a good way to do it.

J
Jonathan_FortisAuthor
Inspiring
July 12, 2013

thanks for your suggestions, I did already search "mysql column alias" but it didnt really help

B
bregent
Participating Frequently
July 8, 2013

SELECT count(*) from cfsCustomersImages

WHERE imageWant = 'Y'

J
Jonathan_FortisAuthor
Inspiring
July 8, 2013

i have tried that. but needed to expand it to just show the records based on the user session but doesnt seem to be working

$colname_rsCustomer = "-1";

if (isset($_SESSION['MM_Username'])) {

  $colname_rsCustomer = $_SESSION['MM_Username'];

}

mysql_select_db($database_cfs, $cfs);

$query_rsCustomer = sprintf("SELECT * FROM cfsCustomers, cfsCustomersImages WHERE custEmail = %s AND cfsCustomersImages.custID = cfsCustomers.custID", GetSQLValueString($colname_rsCustomer, "text"));

$query_limit_rsCustomer = sprintf("%s LIMIT %d, %d", $query_rsCustomer, $startRow_rsCustomer, $maxRows_rsCustomer);

$rsCustomer = mysql_query($query_limit_rsCustomer, $cfs) or die(mysql_error());

$row_rsCustomer = mysql_fetch_assoc($rsCustomer);

mysql_select_db($database_cfs, $cfs);

$query_rsCount = sprintf("SELECT COUNT(*) FROM cfsCustomers, cfsCustomersImages WHERE cfsCustomers.custID = cfsCustomersImages.custID AND custEmail = %s AND imageWANT = 'Y'", GetSQLValueString($colname_rsCustomer, "text"));

$rsCount = mysql_query($query_rsCount, $cfs) or die(mysql_error());

$row_rsCount = mysql_fetch_assoc($rsCount);

$totalRows_rsCount = mysql_num_rows($rsCount);

and am echoing out <?php echo $row_rsCount['imageWant']; ?>

but dont think this is correct either

B
bregent
Participating Frequently
July 8, 2013

>and am echoing out <?php echo $row_rsCount['imageWant']; ?>

>but dont think this is correct either

Yes, it is not correct. There is no "imageWant" column in your query. The query only returns the count. Give the aggregate function a column alias and refer to that in your echo statement.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices