Select ALL, but count only certain titles

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Select ALL, but count only certain titles

Post by simonmlewis »

Can someone tell me what on earth I am doing wrong here please?

Code: Select all

include "dbconn.php";
 
if ($update == "delete") {
$query = mysql_query ("delete FROM stocknotification where title = $title");
    echo "<div class='admincompletedbox'>
        <b>That comment was deleted.</b></div>";
    }
 
  echo "<table width='715' cellpadding='1' cellspacing='0' class='table' bordercolor='#FFFFFF' border='1' style='border-collapse: collapse;'>";
 
$resultcat = mysql_query ("SELECT DISTINCT title FROM stocknotification ORDER BY title");
if (mysql_num_rows($resultcat)==0) 
{ 
echo "<tr><td><div class='sectionhead'>No one has yet posted a comment..</div></td></tr>"; 
}
else 
  {
  while ($rowcat = mysql_fetch_object($resultcat))
    {
      echo "<tr><td colspan='2'><div class='sectionadminhead'>$rowcat->title</div></td></tr>";
      
      $result = mysql_query ("SELECT * COUNT (title) as num_of FROM stocknotification WHERE title = '$rowcat->title'"); 
      echo "<tr><td>";
echo $row['num_of'] ." have registered for this product."; 
      echo"</td><td>
      
      
      </td>
</tr>";
    }
    mysql_free_result($result);
  }
    mysql_free_result($resultcat);
    mysql_close($sqlconn);
    
echo "</table>";
Firstly I am getting this error:
Warning: mysql_free_result(): 5 is not a valid MySQL result resource in /usr/local/psa/home/vhosts/shop.co.uk/httpdocs/support/includes/notifications.inc on line 53
The line in question is

Code: Select all

    mysql_close($sqlconn);
The database has two row of entries. One for one Title and one for a different Title.
It's basically doing a count of everything producing the different titles, but showing "2" for both.

S.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Select ALL, but count only certain titles

Post by simonmlewis »

Am now trying this too, and get even more errors, mainly about free_result, but I am not getting a count at all.

Code: Select all

<?php
$id = $_POST['id'];
$update = $_POST['update'];
$title = $_POST['title'];
$cookieemail = $_COOKIE['email'];
$cookietype = $_COOKIE['type'];
 
if(isset($_REQUEST['websitesort']))
{
    $websitesort = $_REQUEST['websitesort'];
    $_SESSION['websitesort']=$websitesort;
}
else
{
    $websitesort = $_SESSION['websitesort'];
}
if ($websitesort == "clear") { $websitesort = NULL;}
 
if ($cookietype == "admin") {
include "dbconn.php";
 
if ($update == "delete") 
{
$query = mysql_query ("delete FROM stocknotification where title = $title");
    echo "<div class='admincompletedbox'>
        <b>That comment was deleted.</b></div>";
}
 
echo "<table width='715' cellpadding='1' cellspacing='0' class='table' bordercolor='#FFFFFF' border='1' style='border-collapse: collapse;'>";
 
$resultcat = mysql_query ("SELECT DISTINCT title FROM stocknotification ORDER BY title");
  while ($rowcat = mysql_fetch_object($resultcat))
    {
     echo "<tr><td colspan='2'><div class='sectionadminhead'>$rowcat->title</div></td></tr>";
     
        $result = mysql_query ("SELECT *, COUNT (title) as num_of FROM stocknotification WHERE title = '$rowcat->title'"); 
        while ($row = mysql_fetch_object($result))
        {
        echo "<tr><td>" . $row['num_of'] ." have registered for this product.</td><td>        
        <form method='post' action='index.php?page=notifications&head=notifications'>
        <input type='hidden' name='url' value='$row->url'>
        <input type='hidden' name='title' value='$row->title'>
        <input type='hidden' name='email' value='$row->email'>
        <input type='hidden' name='update' value='notify'>
        <input type='submit' value='Notify Customers'>
        </form>
        </td></tr>";
        }
        mysql_free_result($result);
     }
    mysql_free_result($resultcat);
    mysql_close($sqlconn);
echo "</table>";
    }
    
    else 
    {
    echo "<meta http-equiv='Refresh' content='0 ;URL=index.php?page=a_login&menu=a_admin&head=please login'>";
    }
    ?>
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Select ALL, but count only certain titles

Post by califdon »

Nearly always, when you get the error "not a valid MySQL result resource", it means that your query failed, so any subsequent reference to its results causes this error. For this reason, you should always include error reporting in your code until you are confident that the database queries are working properly. Instead of:

Code: Select all

$result=mysql_query("SELECT * FROM foo WHERE bar=0");
use:

Code: Select all

$sql="SELECT * FROM foo WHERE bar=0";
$result=mysql_query($sql) or die("Query: $sql <br>" . mysql_error());
You don't need all those mysql_free_result() and mysql_close() functions. Those were necessary in early versions of PHP, but I think the consensus is that they are completely unnecessary with PHP4 and higher versions.
Post Reply