PHP Count rows in table - obvious choices aren't working

Denno · Dec 12, 2010

[SOLVED] PHP Count rows in table - obvious choices aren't working

I want to count how many rows are in my table so that I can hide a registration form on my website when a certain number is reached.

I originally tried

Code:

SELECT * FROM teams

To select every row, from the database, and then I would use mysql_num_rows. This would result in a blank page appearing when testing.
I then changed that to be

Code:

SELECT COUNT (*) FROM teams

But this didn't work either.

I can run other queries on the database fine, however this counting one isn't playing nice.

My PHP is below. I really hope someone can help me find a problem with it

I have wamp set up and working, has been working very well for quite some time, so I don't think the problem lies there..

Here is my php file:

PHP:

<?php 
require_once "../scripts/connect_to_mysql.php";

//Query the database to count how many records there are
$sqlCommand = "SELECT COUNT (*) FROM pages"; 
$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error());
$count = mysql_num_rows($query);
mysqli_free_result($query);

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>

<script type="text/javascript">
function validate_form(){
	valid = true;
	if(document.form.pageTitle.value == ""){
		alert("Please enter the page title.");
		valid = false;
	}else if(document.form.linkLabel.value == ""){
		alert("Please enter the link label");
		valid = false;
	}else if(document.form.pageBody.value == ""){
		alert("Please enter a page body");
		valid = false;
	}
	return valid;
}
</script>

</head>

<body>
<table width="100%" border="0" cellspacing="1" cellpadding="1">
  <tr>
    <td>Creating a New Page</td>
    <td><a href="index.php">Admin Home</a></td>
    <td><a href="../index.php">View Live Website</a></td>
  </tr>
</table>

<?php 
echo $count;
echo $footer;
if($count > 5){
	echo "Sorry, there are too many biatch!";	
}else{
	?>
    
<p>Be sure to fill in all fields, as they're all required.</p>
<form id="form" name="form" method="post" action="page_new_parse.php" onsubmit="return validate_form();">
<table width="100%" border="0" cellspacing="1" cellpadding="3">
  <tr>
    <td width="300" align="right">Page Full Title</td>
    <td><label for="pageTitle"></label>
      <input name="pageTitle" type="text" id="pageTitle" size="150" /></td>
  </tr>
  <tr>
    <td align="right">Link Label</td>
    <td><label for="linkLabel"></label>
      <input name="linkLabel" type="text" id="linkLabel" size="30" /></td>
  </tr>
  <tr>
    <td align="right" valign="top">Page Body</td>
    <td><label for="pageBody"></label>
      <textarea name="pageBody" id="pageBody" cols="150" rows="8"></textarea></td>
  </tr>
  <tr>
    <td align="right">&nbsp;</td>
    <td><input type="submit" name="submitPage" id="submitPage" value="Create Page Now" /></td>
  </tr>
</table>
</form>
<?php
}
?>
</body>
</html>

Denno · Dec 22, 2010

Just incase anyone was wondering, I found the answer using another forum.

I was mixing mysql and mysqli, which is a no no. I've changed to just be using all mysql commands, and now it works fine.

Denno

dqhendricks · Jan 15, 2011

A few problems.

First, he is right, you are using mysql functions with mysqli resources. Instead try mysqli_num_rows().

Second, COUNT(*) will return one row who's value simply contains the row count. This means that counting the resulting rows with mysqli_num_rows() will return a value of 1.

Instead maybe try this.

Code:

$sqlCommand = "SELECT COUNT (*) FROM pages WHERE 1";  
$query = mysqli_query($myConnection, $sqlCommand) or die (mysqli_error()); 
$row = mysqli_fetch_row($query);
$count = $row[0];
mysqli_free_result($query);

Hope this helps.

- dqhendricks
http://www.spotlesswebdesign.com/

Denno · Jan 15, 2011

thanks for your reply, but as you will read in the post just before yours, I fixed this problem a month ago.

Denno

PHP Count rows in table - obvious choices aren't working

Denno

New Member

Denno

New Member

dqhendricks

New Member

Denno

New Member