CODEWITHSUNDEEP
phpsql
vdegenne
vdegenne

Reputation: 13270

Get the single value of a sql result set

Consider this SQL query

$sql = "select count(*) as count from table where value='this'";

This query is going to return only one value in the field count.

Is there a way to get that value without using a fetch_array and pointing the first value of the returned array instead?

Upvotes: 4

Views: 11159

Answers (3)

FMolivierH
FMolivierH

Reputation: 99

As a sql beginner I too wasted a lot of time trying to use these one-dimensional outcomes of sql queries directly. I thought that since the result is not an array I could skip these while-loops with those fetch_arrays.

However, only now I realise that although the outcome may be the integer value of 8, as long as you do not convert it to the value it is, it is still considered as just a 'query-result' instead of an integer.

Jonathan's code helped me realise this. For myself, I used a slightly different code that helped me out. Here is what worked for me:

$con=mysql_connect("host", "user", "password");

if(!$con)
    {die('could not connect: '.mysql_error());
    }
$db_selected=mysql_select_db("database", $con);

$sql = "the sql query that yiels one value, for example a SELECT COUNT query";

$outcome_considered_as_a_query-result-set = mysql_query($sql,$con);

$outcome_considered_as_the_one_element_of_the_result_set = mysql_result($outcome_considered_as_a_query-result-set,0);

echo $outcome_considered_as_the_one_element_of_the_result_set;

mysql_close($con);

Upvotes: 1

Anze Jarni
Anze Jarni

Reputation: 1157

Use: mysql_num_rows

$count = mysql_num_rows(mysql_query($sql);

Just don't use count in this case. Select everything and this will count it.

Upvotes: 0

Sampson
Sampson

Reputation: 268344

mysql_result() will allow you to pull the value directly from the result set without having to use a fetch method. Here's a very simple example of how you can use it to get your single value:

mysql_select_db( "mysql", mysql_connect( "localhost", "root", "" ) );
$r = mysql_query( "SELECT count(name) as total FROM `help_keyword`" );
echo $r ? mysql_result( $r, 0 ) : mysql_error() ; // Outputs 450

There are three parameters for the mysql_result() function; first is the result set itself, second is the row index and third is the field index. I'm only using the first two parameters since the third one will default to 0, the first field returned.

Upvotes: 2

Related Questions