Problem when outputting MySQL number of rows

[DigitalSky]

I'm trying to output the total number of MySQL rows of two of my database tables. One table (members) has 5 rows, the other table (teams) has 2 rows. My code looks like this...

$query = mysql_query("SELECT * FROM members,teams");
$totalParticipants = mysql_num_rows($query);

When I echo the variable $totalParticipants it shows up as '10' instead of 7. It looks like what it's doing is multiplying one tables rows by the other (5x2) and getting 10. I can't figure out why it's doing this. Anyone got any ideas?

[SteveWh]

I believe that when two tables are in the FROM clause in that format, it creates a cross-join, which creates a result where every record of the first table is paired with every record of the second, i.e. every possible combination. Total rows = 10.

One solution would be to run a separate SELECT * on each table, save the counts to variables, and add them. But there might be a more concise method.

This calculates and returns the number of rows in the result set without actually creating a result table. That might be a start toward the simpler method:

SELECT COUNT(*) FROM table;

[rocknbil]

Right . . . put another way . . . .

first result -> returns first row in table 1, both in table two. (2)
second result -> returns first row in table 1, both in table two. (4)

.. and so on. What you want here is a join. Before you go there though,

I'm trying to output the total number of MySQL rows

Is this really all you want in this query? If it is, select * is inefficient on two counts. First, if you don't need all values in a recordset, you should always do

select field1, field2 from table

and second, if all you need is a count, selecting the actual rows is a huge overhead and unnecessary. It's also a bad habit to get into for counting results, for example, if you start to set pagination, mysql_num_rows will always be equal to the number of items per page. mySQL has a function for counting total rows: count(fieldname) or count(*).

HOWEVER - if you want a count AND will be outputting row data, your approach is probably fine. Get just a count:

$query = "select count(*) from members, teams where members.team_id=teams.team_id";

$results = mysql_query($query);
$row = mysql_fetch_array($results);
$totalParticipants = $results[0];

- mysql joins require the table prefix to avoid ambiguities (i.e., an id field in both tables, which are you referring to?)
- note mysql_fetch_array returns *both* the indexed and associative arrays.

But if you'll be outputting results,

$query = "select * from members, teams where members.team_id=teams.team_id";

$results = mysql_query($query);
$totalParticipants = $mysql_num_rows($results);

echo "<p>Total participants: $totalParticipants</p>";

while ($row = mysql_fetch_array($results)) {
echo "<p>" . $row[0] . " " . $row[1] . " " . $row[2] . "</p>";
}

if you select specific fields, you don't have to use the indexed array. Still have to prefix the fields.

$query = "select members.fname,members.lname,teams.teamname from members, teams where members.team_id=teams.team_id";

echo "<p>" . $row['fname'] . " " . $row['lname'] . " " . $row['teamname'] . "</p>";