Counting the rows in mysql database - the obvious choices aren't working!

[denno020]

I wish to have a page that will display a form if there aren't already enough registrations in the database. The following it an outline of how it will be:

<?php
require_once "../scripts/connect_to_mysql.php";                        //this works fine


// Count how many records in the database
(1) $sqlCommand = "SELECT * FROM teams"; 
(2) $sqlCommand = "SELECT COUNT (*) FROM teams";
(3) $sqlCommand = "SELECT COUNT (id) FROM teams";
$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error());
(1) $numRegistrations = mysql_num_rows($query);						//Count how many rows are returned from the query above
(2) $numRegistrations = $query
(3) $numRegistrations = mysql_num_rows($query);

mysqli_free_result($query);
?>
(Some HTML code, like DOCTYPE, head, start of body tags)
<?php
if($numRegistrations > 35){
	echo "Sorry, registrations for this event is at it's maximum.";	
}else{
?>

<form blah blah blah>

</form>

<?php
}
?>

(end of body and html tag)

 

You'll see above there is (1), (2), (3). These are the main 3 that I've been trying, and they match up the $sqlCommand to the $numRegistrations.

 

What would be the problem with this code? I use wamp server to test, have been using it for ages. Chrome browser to test in.

If I remove the database query, the rest of the page will load.

With the query in there, only the HTML code up until the database query is parsed, so therefore nothing is display on the page.

 

Please help.

 

Thanks

Denno

[litebearer]

is this what you want?

$result = mysql_query("SELECT * FROM tablename");

$num_rows = mysql_num_rows($result);



// Display the results

echo $num_rows;

[denno020]

yeah mate, that's what I want. That functionality, however it just refuses to work. I've got that listed in my code as (1).

Also, be aware that I don't have all 3 of those options in there at the time of testing, I've only put them in to illustrate the different combinations I've used.

 

Thanks

[litebearer]

you are mixing mysql and mysqli

[OOP]

This should work

 

// Count how many records in the database
$sqlCommand = "SELECT * FROM teams";
$query = mysql_query ( $sqlCommand ) or die ( mysql_error () );
$numRegistrations = mysql_num_rows ( $query );
echo $numRegistrations;

[denno020]

you are mixing mysql and mysqli

 

Ohhhhkk. So because I use mysqli_query(), the mysql_num_rows doesn't work?

 

I have tried to connect to my database using mysql before, however it didn't work. But I shall try again right now and see how I go, using the code provided by OOP.

 

Will post back with a result.

 

Thanks

[denno020]

I feel like a douche bag!

 

The mixing of the mysqli and mysql is exactly what the problem was!

I was having troubles with trying to run mysql queries before because I was using mysqli to connect to the database.

 

Problem is now fixed, and it works like a charm.

 

Thanks heaps!

[Pikachu2000]

If all you need is a count, a SELECT COUNT(`pk_id_field_here`) query is more efficient than using a mysqli_num_rows() query.

[denno020]

If all you need is a count, a SELECT COUNT(`pk_id_field_here`) query is more efficient than using a mysqli_num_rows() query.

 

I'm not really in need of a more efficient way, I don't think. I guess I could still implement the SELECT COUNT.

After trying it before, without success, I just wanted to use the first thing that I could get working, which was the other method lol.

 

If I wanted to use the SELECT COUNT query though, I would just assign $numRegistrations = $query?

(where $query = mysql_query($sqlCommand, $myConnection) or die (mysqli_error())

 

Is there any other steps or is the returned query the exact answer I'm after?

 

Tah

Denno

[Pikachu2000]

Assuming you settled on the mysql_ extension . . .

 

$query = "SELECT COUNT(`pk_id_field`)";
$result = mysql_query($query);
$array = mysql_fetch_row($result);
echo $array[0];   // $array[0] contains the number returned by COUNT

[denno020]

Assuming you settled on the mysql_ extension . . .

 

$query = "SELECT COUNT(`pk_id_field`)";
$result = mysql_query($query);
$array = mysql_fetch_row($result);
echo $array[0];   // $array[0] contains the number returned by COUNT

 

Awesome, thankyou very much. I will change to that code .

 

Thanks to everyone for their advice too, it all helped me to understand the problem.

 

Denno