PHP and Mysqli getting value of a count(distinct()) query

Question

I have a db table in mysql called gsm, with 2 columns, LAC and NAME.

So I am trying to count how many different LAC are stored in the DB and retrieve a php value to further use. I am using mysqli

I have:

$sql = "select count(distinct lac) from gsm ");

How do I store that query into a variable in php?

read the marked answer in this - http://stackoverflow.com/questions/4331353/retrieve-data-from-db-and-display-it-in-table-in-php-see-this-code-whats-wron — Alive to die - Anant, Apr 23 '15 at 16:21
@Fransisco Castillo, why you added an extra parenthesis in the end? — A l w a y s S u n n y, Apr 23 '15 at 16:23
@Fransisco Castillo you get the answer or should i add code here? — Hardy Mathew, Apr 23 '15 at 16:24
@hardikbhavsar if you can please add the code, that would be amazing! and thank you! — Francisco Castillo, Apr 23 '15 at 17:48
@FranciscoCastillo Any luck finding a solution to this? The below answer from Hardy Matthew isn't in mysqli unfortunately. — James Finn, Aug 25 '15 at 11:10

score 2 · Answer 1 · edited Sep 14 '15 at 20:40

//conection:
$link = mysqli_connect("www.mywebsite.com","user","password","dataname") or die("Error " . mysqli_error($link));

//consultation:
$query = "SELECT COUNT(DISTINCT alias) as visitors FROM Visits where time BETWEEN timestamp(DATE_SUB(NOW(), INTERVAL 1 HOUR)) AND timestamp(NOW())";

//execute the query.
$result = $link->query($query) or die("Error in the consult.." . mysqli_error($link));
$row = mysqli_fetch_array($result);

//display information:
// The text to draw
$text = $row['visitors'];

score 0 · Answer 2 · answered Aug 25 '15 at 11:55

In order to do this in mysqli, you need

$count = $DB->query("SELECT COUNT( DISTINCT(LAC)) FROM gsm");
$row = $count->fetch_row();
echo 'LAC appears '. $row[0] . ' times in total.';

So now you can use $row[0] for whatever else you want in php. It is the number of unique values of LAC in your database.

score -1 · Answer 3 · answered Apr 24 '15 at 13:13

you can check with the below code , however its not tested from my end but i m sure that it would work.

 # Init the MySQL Connection
    if (!( $db = mysql_connect('localhost', 'root', '') ))
        die ('Failed to connect to MySQL Database Server - #'.mysql_errno ().': '.mysql_error ());
    if (!mysql_select_db('ram'))
        die ('Connected to Server, but Failed to Connect to Database - #'.mysql_errno ().': '.mysql_error ());
    # Prepare the SELECT Query
    $sql = "select count(distinct lac) as LAC from gsm ";
    if (!( $selectRes = mysql_query($sql) )) {
        echo 'Retrieval of data from Database Failed - #' . mysql_errno() . ': ' . mysql_error();
    } else if (mysql_num_rows($selectRes) == 0) {

        echo 'No Any Record Returned';
    } else {
        while ($row = mysql_fetch_assoc($selectRes)) {
            echo $row['LAC'];
        }
    }

PHP and Mysqli getting value of a count(distinct()) query

3 Answers3

Linked