Page 1 of 1

Select ALL, but count only certain titles

Posted: Wed Sep 23, 2009 3:44 am
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.

Re: Select ALL, but count only certain titles

Posted: Wed Sep 23, 2009 4:03 am
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'>";
    }
    ?>

Re: Select ALL, but count only certain titles

Posted: Wed Sep 23, 2009 1:16 pm
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.