Php And Mysql Problem - Need a little help

saga

Oct 4 2005, 03:57 AM

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

Oct 4 2005, 05:49 AM

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

CODE

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

Oct 4 2005, 06:09 AM

QUOTE(rvalkass @ Oct 4 2005, 01:49 PM)

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

CODE

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.

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

Oct 4 2005, 08:55 AM

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

CODE

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

Oct 4 2005, 01:26 PM

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

CODE

$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.

CODE

<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

Oct 4 2005, 02:02 PM

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:

CODE

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

Oct 8 2005, 02:54 PM

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

Oct 30 2005, 09:15 AM

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

Nov 2 2005, 04:53 AM

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:

CODE

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

ADD REPLY / Got an Opinion!	a humble request :-)	RAPID SEARCH!	Free Hosting	[X]
Express your Opinions, Thoughts or Contribute more info. to help others. Ask your Doubts & Queries to get answers, So that "Together We can help others!"	Register FREE for AD-FREE forum, Create your own topics, Ask Questions, track topics, setup subscriptions & notifications and Get a Free Website w/ Email and FTP.		500MB Space No Ads, CPanel, FTP, PHP, MySQL, EMails - 100% FREE