count question

[durangod]

Hi i am counting total number of customers on first load so i can post the total. That is all this part does.

so my query is

PHP Code:


//get total customers
$query = "SELECT COUNT(*) FROM users";
$res =  mysqli_query($mycon,$query);
$cntr = mysqli_fetch_array($res); 


When i do the print_r on $cntr it shows

PHP Code:

Array
(
    [0] => 4
    [COUNT(*)] => 4
) 


I know i can do it this way as well.

PHP Code:


$query = "SELECT COUNT(*) AS tnum FROM users"; 


then the result will be

PHP Code:

Array
(
    [0] => 4
    [tnum] => 4
) 


my questions are;

1. cant i just do it without the tnum option and just always display [0], either way it looks like [0] will always be a reliable correct value?

2. i am curious why there are two elements, i never realized that it showed this way [COUNT(*)] => 4 and its kind of strange seeing that format. Im just curious why either way there are two elements a [0] and the other one.

Thanks..

[Fou-Lu]

There are two items since you have used fetch_array. Fetch array by default will always return MYSQLI_BOTH which is the same as MYSQLI_NUM | MYSQLI_ASSOC unless you have specified otherwise. Therefore you get both numerical offset and associative offset.
fetch_row is reliable. It will always be 0 corresponding to the first property in the returned resultset record.

[felgall]

If you are intending to do a second query to actually retrieve data from all those rows then counting them first is unnecessary as you can easily obtain the count of the number of rows that are returned from a query without having to run a second query to get the count.

[durangod]

@Fou-Lu thanks how about that i never used mysqli_fetch_row before i have always relied on fetch_array or fetch_object.

And it also looks like i dont even have to worry about extra elements on the result other than [0] because from what i gather from the docs it defaults to [0] and thats all i need as i am not repeating this in a loop so it will always be just [0] result on this. So just doing this will always get me the correct value.

PHP Code:


//get total customers 
$query = "SELECT COUNT(*) FROM users"; 
$res =  mysqli_query($mycon,$query); 
$cntr = mysqli_fetch_row($res);  
$totcust = $cntr[0]; 


But that does bring up a question, i have seen at times when someone in the past has used MYSQLI_NUM | MYSQLI_ASSOC for example.
mysqli_fetch_array($resultval,MYSQLI_ASSOC)

So should i be replacing those with the normal fetch of just ($resultval) as that second parameter is not required anymore i dont think.



@felgall thanks, yes but i need to do this seperate because my other querys are in such a fashion that none of them pull all the rows, they are all limited by sorting vars and limiter status vars.

[Indor]

Try this,
Select count(*) from Table where Employ Name="ABCD";
i hope this will help you out

[Old Pedant]

Originally Posted by Indor

Try this,
Select count(*) from Table where Employ Name="ABCD";
i hope this will help you out

** WRONG ANSWER **

Aside from the fact that it is irrelevant to what he is asking, that code is illegal and will cause a SQL error.

[felgall]

Originally Posted by durangod

And it also looks like i dont even have to worry about extra elements on the result other than [0] because from what i gather from the docs it defaults to [0] and thats all i need as i am not repeating this in a loop so it will always be just [0] result on this.

Yes - because your query is only retrieving a single value per row. If you were retrieving a second field from the table in the same query the it would be in [1].

Even if you were returning multiple rows the count would be in [0] for every row when processing inside the loop.

[Indor]

Originally Posted by Indor

Try this,
Select count(*) from Table where Employ Name="ABCD";
i hope this will help you out

what worng in this???