Trying to count number of records in table and use result as a loop.

in mysql I use
SELECT count( lingo ) FROM `mainpage` WHERE lingo = 'en'
I get the correct result of 35

How do I use php to get same result and put result into a variable so
that i can loop 35 times.

I have tried
$sqlcount = "SELECT count( lingo ) FROM `mainpage` WHERE lingo = \'en\'";
$resultcount = mysql_query ($sqlcount);
echo $resultcount;
just to check that the number has been generated.
But nothing is displayed. Is this the correct way to do this.
Thanks in advance

Bill

Query-ing mysql from PHP returned a "resource".
To get the actual result, use mysql_fetch_array (or similar functions).

So:
$sqlcount = "SELECT count( lingo ) FROM `mainpage` WHERE lingo = \'en\'";
$resultRES = mysql_query ($sqlcount);
$resultcount = mysql_fetch_array($resultRES);
mysql_free($resultRES);
echo $resultcount[0];

Hendri Kurniawan

Bill wrote:
> Trying to count number of records in table and use result as a loop.
>
> in mysql I use
> SELECT count( lingo ) FROM `mainpage` WHERE lingo = 'en'
> I get the correct result of 35
>
> How do I use php to get same result and put result into a variable so
> that i can loop 35 times.
>
> I have tried
> $sqlcount = "SELECT count( lingo ) FROM `mainpage` WHERE lingo = \'en\'";
> $resultcount = mysql_query ($sqlcount);
> echo $resultcount;
> just to check that the number has been generated.
> But nothing is displayed. Is this the correct way to do this.
> Thanks in advance
>
> Bill

Hendri Kurniawan wrote:
> Query-ing mysql from PHP returned a "resource".
> To get the actual result, use mysql_fetch_array (or similar functions).
>
> So:
> $sqlcount = "SELECT count( lingo ) FROM `mainpage` WHERE lingo = \'en\'";
> $resultRES = mysql_query ($sqlcount);
> $resultcount = mysql_fetch_array($resultRES);
> mysql_free($resultRES);
> echo $resultcount[0];
>
> Hendri Kurniawan
>
> Bill wrote:
>> Trying to count number of records in table and use result as a loop.
>>
>> in mysql I use
>> SELECT count( lingo ) FROM `mainpage` WHERE lingo = 'en'
>> I get the correct result of 35
>>
>> How do I use php to get same result and put result into a variable so
>> that i can loop 35 times.
>>
>> I have tried
>> $sqlcount = "SELECT count( lingo ) FROM `mainpage` WHERE lingo = \'en\'";
>> $resultcount = mysql_query ($sqlcount);
>> echo $resultcount;
>> just to check that the number has been generated.
>> But nothing is displayed. Is this the correct way to do this.
>> Thanks in advance
>>
>> Bill
Hi Hendri,
I tried what you said and got the following error. The sql is only going
to give one answer and I can see why you put the argument
$resultcount[0]. Any other ideas on how to get the count. Thanks
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL
result resource

hi Hendri,

If I take the where clause out it works and returns all the records.
Tried numerious different combinations to try and put the where clause
in but still returns an error. Works ok using a sql statement in mysql.
Any other way to count records using where clause.
Thanks again for you time
bill

Bill wrote:
> Hendri Kurniawan wrote:
>> Query-ing mysql from PHP returned a "resource".
>> To get the actual result, use mysql_fetch_array (or similar functions).
>>
>> So:
>> $sqlcount = "SELECT count( lingo ) FROM `mainpage` WHERE lingo = \'en\'";
>> $resultRES = mysql_query ($sqlcount);
>> $resultcount = mysql_fetch_array($resultRES);
>> mysql_free($resultRES);
>> echo $resultcount[0];
>>
>> Hendri Kurniawan
>>
>> Bill wrote:
>>> Trying to count number of records in table and use result as a loop.
>>>
>>> in mysql I use
>>> SELECT count( lingo ) FROM `mainpage` WHERE lingo = 'en'
>>> I get the correct result of 35
>>>
>>> How do I use php to get same result and put result into a variable so
>>> that i can loop 35 times.
>>>
>>> I have tried
>>> $sqlcount = "SELECT count( lingo ) FROM `mainpage` WHERE lingo =
>>> \'en\'";
>>> $resultcount = mysql_query ($sqlcount);
>>> echo $resultcount;
>>> just to check that the number has been generated.
>>> But nothing is displayed. Is this the correct way to do this.
>>> Thanks in advance
>>>
>>> Bill
> Hi Hendri,
> I tried what you said and got the following error. The sql is only going
> to give one answer and I can see why you put the argument
> $resultcount[0]. Any other ideas on how to get the count. Thanks
> Warning: mysql_fetch_array(): supplied argument is not a valid MySQL
> result resource

Bill wrote:
> hi Hendri,
>
> If I take the where clause out it works and returns all the records.
> Tried numerious different combinations to try and put the where clause
> in but still returns an error. Works ok using a sql statement in
> mysql.
> Any other way to count records using where clause.
> Thanks again for you time

I'd say it's this bit:
"... = \'en\'"

Only escape single quotes if they are in a single quoted string.
You'd either use:
"... = 'en'"
Or:
'... = \'en\''

Grtz,
--
Rik Wasmus

BTW, for future reference, if you use this syntax (in development) errors
are a lot easier to spot:

$query = 'SELECT ... ';
$result = mysql_query($query) or die("The following query returned
errors:\n{$query}\nError reported:\n".mysql_error());

Saves tons of debugging time.
--
Rik Wasmus

Thanks rik thats what was wrong

$sqlcount = "SELECT name, text FROM mainpage where lingo = 'en'";
$resultRES = mysql_query ($sqlcount);
$resultcount = mysql_num_rows($resultRES);

echo $resultcount;

the above worked fine
cheers

Rik wrote:
> Bill wrote:
>> hi Hendri,
>>
>> If I take the where clause out it works and returns all the records.
>> Tried numerious different combinations to try and put the where clause
>> in but still returns an error. Works ok using a sql statement in
>> mysql.
>> Any other way to count records using where clause.
>> Thanks again for you time
>
> I'd say it's this bit:
> "... = \'en\'"
>
> Only escape single quotes if they are in a single quoted string.
> You'd either use:
> "... = 'en'"
> Or:
> '... = \'en\''
>
> Grtz,

> If I take the where clause out it works and returns all the records.
> Tried numerious different combinations to try and put the where clause in
> but still returns an error. Works ok using a sql statement in mysql.
> Any other way to count records using where clause.
> Thanks again for you time
> bill

In the code you supplied you have escaped the single quotes in the where
clause. as you have double quotes set around the sql query you do not need
to escape them and is most likely the cause of your problem.

I think there is a better method :
<?php
$sqlcount = "SELECT COUNT(name) AS c FROM mainpage where lingo =
'en'";
$result = mysql_query ($sqlcount);

while ($row = mysql_fetch_object($result))
$resultcount = $row->c;

echo $resultcount;
?>

http://www.mastervb.net/phpbooks/
http://www.mastervb.net

On Jan 5, 11:08 pm, Bill <n... [at] noreturn.f9.co.uk> wrote:
> Thanks rik thats what was wrong
>
> $sqlcount = "SELECT name, text FROM mainpage where lingo = 'en'";
> $resultRES = mysql_query ($sqlcount);
> $resultcount = mysql_num_rows($resultRES);
>
> echo $resultcount;
>
> the above worked fine
> cheers
>
> Rik wrote:
> > Bill wrote:
> >> hi Hendri,
>
> >> If I take the where clause out it works and returns all the records.
> >> Tried numerious different combinations to try and put the where clause
> >> in but still returns an error. Works ok using a sql statement in
> >> mysql.
> >> Any other way to count records using where clause.
> >> Thanks again for you time
>
> > I'd say it's this bit:
> > "... = \'en\'"
>
> > Only escape single quotes if they are in a single quoted string.
> > You'd either use:
> > "... = 'en'"
> > Or:
> > '... = \'en\''
>
> > Grtz,