Help - trying to return 2 mysql_query commands

Dev Articles Community Forums Sponsor:

Be the architects of evolution and help create the mobile internet future. It’s your move---enter to win here!

September 10th, 2004, 09:38 AM

tonyoevans

Registered User

Join Date: Sep 2004

Posts: 2

Time spent in forums: < 1 sec
Reputation Power: 0

Help - trying to return 2 mysql_query commands

Can anyone help, I have a PHP script that returns a list of products categorys and also the number products within those categories.

The trouble I have is that it displays the first category and number of products and thats it.

The error it comes back with is "mysql_fetch_array(): 8 is not a valid MySQL result resource" however when I remove the following two lines it displays all the categories (but obviously not the number of products within them)

$countResult = mysql_query("SELECT count(*) AS num FROM products WHERE cat_id=$cat_id",$conn);
$num = mysql_result($countResult,0,"num");

I have tested the SQL and it works

The script is:-

function cat_list($conn)
{
global $db_name;
global $db_prod_table;
global $db_cat_table;

$sql = "SELECT * FROM $db_cat_table ORDER BY cat_name";
$result = mysql_query($sql,$conn) or die (mysql_error());

$cat_list ="<table width='800' border='0' cellspacing='2' cellpadding='4'>
<tr>
<td width='50' bgcolor='#000099'>ID</td>
<td width='450' bgcolor='#000099'><b>Category Name</b></td>
<td width='200' bgcolor='#000099'><b>Number of products</td>
</td width='50'></td>
</td width='50'></td>
</tr>";

while ($row = mysql_fetch_array($result))
{
extract($row);

// remove the following 2 lines and it displays all the categories
$countResult = mysql_query("SELECT count(*) AS num FROM products WHERE cat_id=$cat_id",$conn);
$num = mysql_result($countResult,0,"num");

$cat_list .=
"<tr>
<td width='50' height='20'>$cat_id</td>
<td width='450'>$cat_name</td>
<td width='200' align='center'>$num</td>
<td width='50' align='center'>
<b>a href=confirm_delete.php?cat_id=$cat_id&catProducts=$num>Delete</a></b>
</td>
<td width='50' bgcolor='#669900' align='center'>
<b><a href=modify_cat.php?cat_id=$cat_id>Edit</a></b>
</td>
</tr>";
}

$cat_list .="</table>";
return $cat_list;
}

Can anyone help

September 12th, 2004, 05:24 PM

zigote

Contributing User

Join Date: Aug 2002

Location: Atlanta GA

Posts: 73

Time spent in forums: 1 h 55 m 11 sec
Reputation Power: 6

Hi tonyoevans

I put this together, maybe this will help you understand a little more. I'm sure there is a better way...
I also didn't know your database schema, so I added a simple structure here.

PHP Code:


		
			
<?php

/* My Schema

 CREATE TABLE `books` (

  `bookId` int(11) NOT NULL auto_increment,

  `bookTitle` varchar(100) NOT NULL default '',

  `catId` int(11) default NULL,

  PRIMARY KEY  (`bookId`),

  UNIQUE KEY `bookId` (`bookId`)

) TYPE=MyISAM



CREATE TABLE `categories` (

  `pk_categoryId` int(11) NOT NULL auto_increment,

  `catName` varchar(100) NOT NULL default '',

  `catParentId` int(11) default NULL,

  PRIMARY KEY  (`pk_categoryId`)

) TYPE=MyISAM */



$dbServer = "localhost";

$dbUser = "user";

$dbPass = "pass";

$dbName = "test";



$db_prod_table = "books";

$db_cat_table = "categories";



$conn = mysql_connect("$dbServer", "$dbUser", "$dbPass")

    or die("Impossible to connect :" . mysql_error());

mysql_select_db("$dbName") or die(mysql_error());



function cat_list($conn)

{

    global $db_prod_table;

    global $db_cat_table;

    // Select our table and query

    $sql = "SELECT * FROM $db_cat_table ORDER BY catName";

    $result = mysql_query($sql, $conn) or die (mysql_error());

    

    // Loop threw our data

    while ($listCats = mysql_fetch_array($result))

    {

        $pk_categoryId = $listCats["pk_categoryId"];

        $catName = $listCats["catName"];

        

        // Count Total Products in each Category

        $totalProducts =

        mysql_result(mysql_query("select count(*) from $db_prod_table WHERE catId = $pk_categoryId "), 0, 0);



        // Display our results

        echo "  <tr>\n";

        echo "      <td width='50' height='20'>$pk_categoryId</td>\n";

        echo "      <td width='400'>$catName</td>\n";

        echo "      <td width='200'>$totalProducts</td>\n";

        echo "      <td width='100'>Edit - Delete</td>\n";

        echo "  </tr>\n";

    }

}

?>

<table width='800' border='0' cellspacing='2' cellpadding='4'>

    <tr> 

        <td width='50' bgcolor='#EFEFEF'><b>ID</b></td>

        <td width='400' bgcolor='#EFEFEF'><b>Category Name</b></td>

        <td width='200' bgcolor='#EFEFEF'><b>Number of products</b></td>

        <td width='100' bgcolor='#EFEFEF'><b>Action</b></td>

    </tr>

    <?php

        // Add Function to display our categories & total products

        cat_list($conn);

    ?>

</table>

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: