I posted before about a different problem and one of you commented
that another aspect of my code would not work anyway. Well, I thought
I had fixed it. Boy was I wrong.

I have a field in my table where once something is submitted it
doesn't post until approved. 0 for not approved, 1 for approved. I am
trying to count the number of non-approved items in my table to report
it on the admin page. I get:

Directory - You have 1 entries waiting for approval.
Address Book - You have 1 entries waiting for approval.

I know I have a lot more than 1 waiting for approval.

function adminApprovalNeeded ($connect, $db_table8)
{
$sql = "SELECT * FROM $db_table8 WHERE approved != '1'";
$result = mysql_query ($sql, $connect) or die ('Query failed:
' .mysql_error());
$approved = mysql_num_rows($result);
echo "<STRONG>Directory</STRONG> - You have $approved entries waiting
for approval.";
}

..oO(Norman Peelman)

> Is the field 'approved' a TEXT field or a numeric field (INT for
>example)?
>
>If its a numeric field then:
>
>SELECT * FROM $db_table8 WHERE approved = 0

Better:

SELECT COUNT(*) FROM $db_table8 WHERE approved = 0

If you just want to get the number of records, it's not necessary to
transfer all data from the DB to the script, just to throw it away after
calling mysql_num_rows().

Micha

Its an integer set at 1 space. I changed code to:

function adminApprovalNeededDir ($connect, $db_table8)
{
$sql = "SELECT COUNT(*) FROM $db_table8 WHERE approved = 0 ";
$result = mysql_query ($sql, $connect) or die ('Query failed:
' .mysql_error());
$approved = mysql_num_rows($result);
echo "<STRONG>Directory</STRONG> - You have $approved entries waiting
for approval.";
}

I'm still getting same result.

up2trouble wrote:
>
> Its an integer set at 1 space. I changed code to:
>
> function adminApprovalNeededDir ($connect, $db_table8)
> {
> $sql = "SELECT COUNT(*) FROM $db_table8 WHERE approved = 0 ";
> $result = mysql_query ($sql, $connect) or die ('Query failed:
> ' .mysql_error());
> $approved = mysql_num_rows($result);
> echo "<STRONG>Directory</STRONG> - You have $approved entries waiting
> for approval.";
> }
>
> I'm still getting same result.
>

You'll need to post your table definition here and just one more
thing are you sure $db_table8 refers to the correct table? There is
nothing in that query that would limit your results. Can you verify
through the MYSQL shell/command line?

Norm

?PHP
//
************************************************** ******************************************
//CONNECTION VARIABLES
//
************************************************** ******************************************

$db_server = "localhost";
$db_user = "";
$db_pass = "";
$db_name = "";
$db_table = "blog_category";
$db_table1 = "blog_entry";
$db_table2 = "quotes";
$db_table3 = "ab_category";
$db_table4 = "ab_person";
$db_table5 = "menu_heading";
$db_table6 = "menu_item";
$db_table7 = "dir_category";
$db_table8 = "dir_links";

$connect = mysql_connect($db_server, $db_user, $db_pass) OR DIE ('I
can not connect to server because: ' .mysql_errno(). " -
" .mysql_error());
mysql_select_db($db_name, $connect) OR DIE ('I can not connect to
server because: ' .mysql_errno(). " - " .mysql_error());


//
************************************************** ******************************************
//
************************************************** ******************************************
?>

<?PHP include("../includes/connect.txt");?>


<?PHP

function listCategory($connect, $db_table7)
{
echo "<TABLE CELLSPACING='2' CELLPADDING='2' BORDER='1'>";
echo "<TR><TH ALIGN='center' VALIGN='top'>ID</TH> <TH ALIGN='center'
VALIGN='top'>Name</TH>";
echo "<TH ALIGN='center' VALIGN='top'>Parent</TH></TR>";
$sql = "SELECT * FROM $db_table7 ORDER BY parent ASC";
$result = mysql_query ($sql, $connect) or die ('Query failed:
' .mysql_error());
while ($row = mysql_fetch_array($result))
{
$id = $row["id"];
$name = $row["name"];
$parent = $row["parent"];
echo "<TR><TD><STRONG>$id</STRONG></TD><TD>$name</TD>";
echo "<TD>$parent</TD></TR>";
}
echo "</TABLE>";
}

function addCategoryDir ($connect, $db_table7)
{
$name = $_POST['name'];
$parent = $_POST['parent'];
$sql = "INSERT INTO $db_table7 (name, parent) VALUES ('$name',
'$parent')";
mysql_query ($sql) or die(mysql_error());
}

function deleteCategoryDir ($connect, $db_table7)
{
$name = $_POST['name'];
$sql = "DELETE FROM $db_table7 WHERE '$name' = name";
mysql_query ($sql, $connect) or die ('Query failed:
' .mysql_error());
}

function addLink ($connect, $db_table8)
{
$name = $_POST['name'];
$url = $_POST['url'];
$description = $_POST['description'];
$category = $_POST['category'];
$sql = "INSERT INTO $db_table8 (name, url, description, category)
VALUES ('$name', '$url', '$description', '$category')";
mysql_query ($sql, $connect) or die ('Query failed:
' .mysql_error());
echo "Your link was added and the Administrator was notified that your
entry is awaiting approval.";
$sql2 = "SELECT COUNT(*) FROM $db_table8 WHERE approved = 0 ";
$result = mysql_query ($sql2, $connect) or die ('Query failed:
' .mysql_error());
$approved = mysql_num_rows($result);
$to = "lynettesmith@gmail.com";
$from = "lynnesmith.net";
$subject = "Directory link added";
$message = "Directory link added. You have $approved entries waiting
for approval.";
mail($to, $subject, $message, "From: $from");
}

function adminApprovalNeededDir ($connect, $db_table8)
{
$sql = "SELECT COUNT(*) FROM $db_table8 WHERE approved = 0 ";
$result = mysql_query ($sql, $connect) or die ('Query failed:
' .mysql_error());
$approved = mysql_num_rows($result);
echo "<STRONG>Directory</STRONG> - You have $approved entries waiting
for approval.";
}

<HTML>
<HEAD>
<?PHP include("../includes/headsection.txt")?>
</HEAD>
<BODY>
<?PHP include("../includes/connect.txt");?>
<DIV ID="wrapper">
<DIV ID="header">
</DIV>
<DIV ID="subheader">
<?PHP include("../includes/navigation.txt")?>
</DIV>
<DIV ID="rcol">
<?PHP include("../includes/navigationa.txt")?>
</DIV>
<DIV ID="content">
<H2 ALIGN="center">Website Administration Area</H2>
<?PHP include("../directory/dir_displaywaiting.php"); ?>
<BR>
<?PHP include("../addressbook/ab_displaywaiting.php"); ?>
</DIV>
<DIV ID="footer">
<?PHP include("../includes/footer.txt")?>
</DIV>
</DIV>
</BODY>
</HTML>

<?PHP include("../directory/dir_functions.php"); ?>
<?PHP
adminApprovalNeededDir ($connect, $db_table8);
?>

I always use a gui and have no idea how to even get to the command line

up2trouble wrote:
>
Code snipped
>


Ok, which GUI? PHP/MySQLAdmin? Can you reproduce the problem there?
Can you post your CREATE TABLE definition?

Other than that, I can't see anything directly wrong with your code.
That is probably the most basic of queries.

Norm

up2trouble wrote:
> php my admin for gui
>
> ab_person
> Field Type Null Default
> ID int(10) No
> business varchar(50) No &nbsp;
> firstName varchar(20) No &nbsp;
> lastName varchar(20) No &nbsp;
> dob varchar(5) No &nbsp
> category varchar(20) No &nbsp;
> address1 varchar(50) No &nbsp;
> address2 varchar(50) No &nbsp;
> city varchar(25) No &nbsp;
> state char(2) No
> zipcode varchar(5) No &nbsp
> telephone varchar(10) No &nbsp;
> cellphone varchar(10) No &nbsp;
> email1 varchar(75) No &nbsp;
> email2 varchar(75) No &nbsp;
> aimid varchar(20) No &nbsp;
> url varchar(150) No &nbsp;
> notes mediumtext No
> approved tinyint(1) No 0
>
>
> Indexes:
> Keyname Type Cardinality Field
> PRIMARY PRIMARY 20 ID
>
>
> Space usage:
> Type Usage
> Data 2,176 B
> Index 2,048 B
> Total 4,224 B
> Row Statistics:
> Statements Value
> Format dynamic
> Rows 20
> Row length ø 108
> Row size ø 211 B
> Next Autoindex 27
> Creation Jul 20, 2007 at 11:12 AM
> Last update Jul 20, 2007 at 01:00 PM
>
>

Try your query in phpmyadmin and see what the results are. Also try
just a "SELECT * FROM dir_links" to see what your table actually holds.
And the schema you posted is for $db_table4 (ab_person), not $db_table8
(dir_links) which is where you say your problem is.

Norm