Php And Mysql Problem Need a little help

[saga 0]

hi..i'm new in php and mysql... here is my problem....$sql = "SELECT COUNT(*) FROM messages WHERE _parent={$row['_id']}";after sending the query above i dont know how to fetch the actual rows counted..i tried using the mysql_fetch_array() and mysql_fetch_row() function but it didnt work...actually the only function i know that fetches results from sql queries is mysql_fetch_array()...so what function or code i need to have the result from the query which uses COUNT?

[rvalkass 5]

Just setting that text string as a variable won't do anything. Put this code after the $sql variable:

mysql_query($sql);

If you want to count the number of rows then use the mysql_num_rows() function. If its fields you're after then use the mysql_num_fields() function.

Hope this helps you.

[saga 0]

Just setting that text string as a variable won't do anything. Put this code after the $sql variable:

mysql_query($sql);

If you want to count the number of rows then use the mysql_num_rows() function. If its fields you're after then use the mysql_num_fields() function.

 

Hope this helps you.

191780[/snapback]

of course i query first the $sql variable with mysql_query($sql)..

 

anyway just a follow up question..

 

what function parameters shoudl i pass to mysql_num_rows() to be able to get the row count of let say for example WHERE sex = 'male'.. something like that..

 

what i realy need is not all the row count of a single table but certain rows that contains certain data...

[saga 0]

for the meantime i am using ths code to be able to know the number of rows which has a certain data...

while($row = mysql_fetch_array($result))        $count++;

but this kind of approach will take a lot of processing and space in the server.. becuase if the table contains a thousand rows then it will load to memory all those information which is not necesary..

i still need an alternative to this problem.. if there is any.. thnks..

[Avalon 1]

Your problem is your SELECT statement. To SELECT the rows, you need to remove the COUNT() parameter like this.

$sql = "SELECT * FROM messages WHERE _parent={$row['_id']}";

Then to return the actual rows and the count of the rows you need statements something like this, assuming you have 3 fields in each row.

<table><?php $result = mysql_query($sql); $numrows = mysql_num_rows($result); while($row = mysql_fetch_array($result)){echo "<tr><td>$row[field1]</td><td>$row[field2]</td><td>$row[field3]</td></tr>";} ?></table>

The variable '$numrows' will return the count of rows returned by your SELECT statement.

Hope that helps.

[Spectre 0]

The mysql_num_rows() function returns the number of rows returned from the last query executed, as has been mentioned. Any easy approach would simply be:

mysql_num_rows(mysql_query('SELECT blah FROM blah WHERE sex = "male"'));

When modifying data in a table, the mysql_affected_rows() function returns the number of rows affected by that query (eg. mysql_query('DELETE FROM blah LIMIT 2'); mysql_affected_rows() = 2).

[vitrus 0]

I agree with Spectre allthough he made 1 mistake...mysql_num_rows() returns the result of the "specified" result (no not query) ..so once you executes your query you can use $numerofrows = mysql_num_rows($result); yo make the variable $numerofrows contain an Integer with the rowcount...

[gogoily 0]

Use this:$sql = "SELECT COUNT(*) FROM messages WHERE _parent={$row['_id']}";$query=mysql_query($sql);$amount=mysql_fetch_row($query);then $amount[0] is the result you want.

[magiccode9 0]

hi, saga

 

overall I have read whole thread, and have this summary if you wish to

 

1.) only wanted the no. of records(rows) with a sub-set of all records.

you can do this as following, let's say we have a table (table1) with 3 fields:

id, role, groups, then:

k:\mysql\bin>mysql<enter>mysql> show tables;+--------------------+| Tables_in_test_db2 |+--------------------+| table1             |+--------------------+1 row in set (0.00 sec)mysql> select * from table1;+----+------+--------+| id | role | groups |+----+------+--------+|  1 |    0 |      0 ||  2 |    1 |      0 ||  3 |    5 |      0 |+----+------+--------+3 rows in set (0.00 sec)mysql> select count(*) from table1 where role=0;+----------+| count(*) |+----------+|        1 |+----------+1 row in set (0.00 sec)mysql> select count(*) from table1 where groups=0;+----------+| count(*) |+----------+|        3 |+----------+1 row in set (0.00 sec)

as you can see the result that return a value(1, 3 above) with a field named (count(*)). then, you can use :

a.) $row = mysql_fetch_array($result)

b.) $numrow = $row[0];

in fact, you can use $row = mysql_fetch_row($result) also, but that required you modify the sql statement, you can have a reference to mysql mannual

 

2.) as other forum members stated, you use:

$roleid = 0;

$sql = "SELECT * FROM messages WHERE role=$roleid";

$result = mysql_query($sql);

$rownum = mysql_num_rows($result);

$row = mysql_fetch_row($result) ==> for get row

 

- hope this help

- Eric