Board index   FAQ   Search  
Register  Login
Board index PHP PHP General

SELECT COUNT from multiple tables

General discussions related to php

Moderators: macek, egami, gesf

SELECT COUNT from multiple tables

Postby shamir524 » Tue Mar 26, 2013 8:47 am

Hello Everybody, i new here and really need some help,

i have this code which it shows me all the users i have but i created a new module to only show me users from a specific role (ei: admin, supervisor, etc) tthe roles are in another table in my mysql how can i show only user from specific role:

tableName1 is my users and tableName2 is my roles

// SETUP PAGING VARIABLE
// ------------------------------------------------------------
$tableName1="users";
$tableName2="users_in_roles";
$targetpage = "resellers.php";
$limit = GV_PAGE_SIZE;
$stages = 2;

// ------------------------------------------------------------
// GET DISPLAY QUERY STRING VALUE
// ------------------------------------------------------------
if(isset($_GET['display']) && !empty($_GET['display']) && is_numeric($_GET['display']) && $_GET['display'] >= 0 && $_GET['display'] <= 1000)
{
$display = strip_tags($_GET['display']);
$_SESSION['display'] = $display;
$limit = $_SESSION['display'];
$remember_limit = $limit;
}
else
{
$display = $limit;
}

// ------------------------------------------------------------
// GET A-Z QUERY STRING Value
// ------------------------------------------------------------
if(isset($_GET['az']) && !empty($_GET['az']) && strlen($_GET['az']) <= 2 && !is_numeric($_GET['az']))
{
$az_letter = $_GET['az'];

// remember requested number of records
if(isset($_SESSION['display']))
{
$limit = $_SESSION['display'];
}
}
else
{
$az_letter = '%';

// remember requested number of records
if(isset($_SESSION['display']))
{
$limit = $_SESSION['display'];
}
}

// DB QUERY: get total count
// ------------------------------------------------------------
$query = "SELECT COUNT(*) AS TotalCount FROM $tableName WHERE UserName LIKE '$az_letter%'";
$record_count = mysqli_fetch_array(mysqli_query($conn, $query));
$record_count = $record_count['TotalCount'];
shamir524
New php-forum User
New php-forum User
 
Posts: 1
Joined: Tue Mar 26, 2013 8:34 am

Re: SELECT COUNT from multiple tables

Postby seandisanti » Thu Mar 28, 2013 10:52 pm

you can't aggregate a wildcard like count(*), because you'd not be giving it any group by field. the way to do it would be like this...

SELECT field1, count(field2) as count FROM table where field3='role' GROUP BY field1

substitute in your own field and table names, joins etc, but that's how you get a good count.
seandisanti
php-forum Fan User
php-forum Fan User
 
Posts: 787
Joined: Mon Oct 01, 2012 12:32 pm


Return to PHP General

Who is online

Users browsing this forum: No registered users and 1 guest

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.