MySQL Count into PHP Variable

mysql count into PHP variable

Like this:

// Changed the query - there's no need for DISTINCT
// and aliased the count as "num"
$data = mysql_query('SELECT COUNT(`users_id`) AS num FROM `users_table`') or die(mysql_error());

// A COUNT query will always return 1 row
// (unless it fails, in which case we die above)
// Use fetch_assoc for a nice associative array - much easier to use
$row = mysql_fetch_assoc($data);

// Get the number of uses from the array
// 'num' is what we aliased the column as above
$numUsers = $row['num'];

Is it possible to get a sql count result assign to a php variable?

You can use mysql_fetch_row :

//code for connecting to database
$query = "SELECT COUNT(*) FROM `user_table` WHERE `username` = 'John12' OR `username` = 'Paula25'";
$result = mysql_query($query);
if(!$result) die($db->error);    

$row = mysql_fetch_row($result);

$count = $row[0];

if($count < 2) { 
  // do this if true
} else {
  // do this
}

As @mith mentioned in comment, if you just starting your project, better using mysqli than mysql. Read this : MySQL vs MySQLi when using PHP

How can I store the result of an SQL COUNT statement in a PHP variable

mysql_query returns a query resource id. In order to get values from it you need to use mysql_fetch_assoc on the resource id to fetch a row into an array.

$result = mysql_query("SELECT COUNT(*) FROM News");
$row = mysql_fetch_assoc($result);
$size = $row['COUNT(*)'];

select count(*) from table of mysql in php

You need to alias the aggregate using the as keyword in order to call it from mysql_fetch_assoc

$result=mysql_query("SELECT count(*) as total from Students");
$data=mysql_fetch_assoc($result);
echo $data['total'];

how to convert mysql count output into php variable

Try this:

$result = mysql_query("SELECT COUNT(classcode) as count FROM playerinfo WHERE classcode='class1'");

$result_array = mysql_fetch_assoc($result); 
$count = $result_array['count']

echo $count;

How to save the count() group by from mysql into a php variable

You want to alias the « COUNT(*) » to something else, like « cnt ». Then you can access the column by name after fetching, as demonstrated below :

$query = "
    SELECT 
        user_posted_to, 
        COUNT(*) as cnt
    FROM activity_log_table 
    WHERE 
        post_type = 'discussion' 
        AND activity_type = 'Like'
       AND activity_timestamp >= CURRENT_DATE - INTERVAL 7 DAY
        AND activity_type <= CURRENT_DATE
    GROUP BY user_posted_to 
    ORDER BY activity_timestamp DESC 
    LIMIT 25";

$result = mysqli_query($connection, $query);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "user_posted_to: " . $row["user_posted_to"]. " - Count: " . $row["cnt"] . "<br>";
    } 
} else {
    echo "0 results";
}

PHP SQL get SELECT COUNT(user) to variable

You should use mysql_result and get the first column of the result. Like this:

$result = mysql_query("SELECT COUNT(user) FROM game_paths WHERE user='$user'");
$count = mysql_result($result, 0);

You can also alias the column like this:

$result = mysql_query("SELECT COUNT(user) AS total FROM game_paths WHERE user='$user'");
$data = mysql_fetch_assoc($result);
$count = $data['total'];

Which might be better if you're going to select several columns at the same time, and also for readability.

How to put result of COUNT to a variable

You can use mysql_num_rows(). Something like this.

$sql=mysql_query("SELECT * FROM myTable WHERE town = 'Sydney'");
$count = mysql_num_rows($sql);
echo $count;

mysql_num_rows — Get number of rows in result

OR with COUNT() mysql function

$sql = mysql_query("SELECT COUNT(*) as count FROM myTable WHERE town = 'Sydney'");
while($rw=mysql_fetch_object($sql)){
    echo $count = $rw->count;
    }

Mysql with variables and count method

This problem is addressed in the MySQL manual section on User-Defined Variables. In particular, here:

As a general rule, other than in SET statements, you should never
assign a value to a user variable and read the value within the same
statement. For example, to increment a variable, this is okay:

SET @a = @a + 1;

For other statements, such as SELECT, you might get
the results you expect, but this is not guaranteed. In the following
statement, you might think that MySQL will evaluate @a first and then
do an assignment second:

SELECT @a, @a:=@a+1, ...;

However, the order of evaluation for
expressions involving user variables is undefined.

In other words, despite setting the value of @order_count "first" in your SELECT statement, the variable is clearly returning its original value (an empty string) when selected as the second column.

The manual continues (emphasis mine):

Another issue with assigning a value to a variable and reading the
value within the same non-SET statement is that the default result type of a variable is based on its type at the start of the statement.
The following example illustrates this:

mysql> SET @a='test'; mysql> SELECT @a,(@a:=20) FROM tbl_name;

For this SELECT statement, MySQL reports to the client that column one is
a string and converts all accesses of @a to strings, even though @a is
set to a number for the second row. After the SELECT statement
executes, @a is regarded as a number for the next statement.

To avoid problems with this behavior, either do not assign a value to
and read the value of the same variable within a single statement, or
else set the variable to 0, 0.0, or '' to define its type before you
use it.

In other words, since you declare the variable by assigning it the empty string, operations on the variable will be interpreted using string values, hence mysqli reporting the first column as the string '125068543' instead of an integer.

Now, you haven't told us what else you're doing with this value, but from the scenario you've presented, you can eliminate the expected results by setting (and referencing) your variable only once, like so:

SELECT @order_count := count(id) FROM wp_shop_orders AS o

---

Related Topics