[SOLVED] Need help with displaying Subcategories!!!!

[ycoleman]

Hello.  I am creating an online store website and I need help with displaying information for the products for this store.  I am connected to the database  I am having trouble with getting subcategories from the database to display on my page called "SubCategory.php" when I click on each link from my "category.php" page. My category page is fine but it's just my subcategory page that I am having problems with.  :-\

 

This is the code from my "Category.php" page:

 

<?

        $sql = "SELECT * FROM tblCategory";

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

 

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

  $CatID = $row['CatID'];

  $CategoryName = $row['CategoryName'];

  echo "<a href='SubCategory.php?CatID=" . $CatID . "'>" . $CategoryName ."[/url]

";

  }

?>

 

 

And this is the code for the "SubCategory.php" page:

 

<?

//assign the query string

$thisCatID = $_GET['CatID']

 

?>

 

 

<?

//get subcategories

$sql ="SELECT * FROM tblSubCategory";

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

$sql_2 ="SELECT * FROM tblSubCategory WHERE CatID = `".$thisCatID."`";

$result = @mysql_query($sql_2,$db) or die(mysql_error());

 

 

 

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

  $CatID = $row['CatID'];

  $SubCategoryName = $row['SubCategoryName'];

  $thisCatID =$row['CatID'];

 

    echo "<a href='SubCategory.php?CatID=" . $CatID . "'>" . $SubCategoryName ."[/url]

";

  }

 

 

?>

 

[monk.e.boy]

Please look up SQL injection. Someone will delete your database id you leave this:

 

<?
//assign the query string
$thisCatID = $_GET['CatID']

?>

 

 

read this:

http://www.webmaster-talk.com/php-forum/58129-sql-injection-problem-php-mysql-websites.html

 

<?

 

//get subcategories

// remove this:

//$sql ="SELECT * FROM tblSubCategory";

//$result = @mysql_query($sql,$db) or die(mysql_error());

 

 

$sql_2 ="SELECT * FROM tblSubCategory WHERE CatID = `".$thisCatID."`";

$result = @mysql_query($sql_2,$db) or die(mysql_error());

 

 

 

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

  $CatID = $row['CatID'];

  $SubCategoryName = $row['SubCategoryName'];

  $thisCatID =$row['CatID'];

 

    echo "<a href='SubCategory.php?CatID=" . $CatID . "'>" . $SubCategoryName ."[/url]

";

  }

 

?>

 

This code looks ok, what is your problem?

[ycoleman]

When I did that I get a result that says "Query is empty"

[ycoleman]

I can't pull information from the database like product info, price, pictures and etc; on my webage in the subcategory.php by using php. :'(

 

Here's the link to my page so far and when you click on each category you see the error message:

 

https://wiki.sl.iupui.edu/~ycoleman/Assignment4/home.php

[monk.e.boy]

echo out the sql query before you do the mysql query.

 

monk.e.boy

[ycoleman]

i need more info from that last reply.  Do you mean by this echo $result = ........................? ???

[per1os]

<?php

//get subcategories
// remove this:
//$sql ="SELECT * FROM tblSubCategory";
//$result = @mysql_query($sql,$db) or die(mysql_error());

// needed single quotes (') not `  and added the real_escape to help prevent sql injection
$sql_2 ="SELECT * FROM tblSubCategory WHERE CatID = '".mysql_real_escape_string($thisCatID)."'";
$result = @mysql_query($sql_2,$db) or die(mysql_error());



   while ($row = mysql_fetch_array($result)) {
   $CatID = $row['CatID'];
   $SubCategoryName = $row['SubCategoryName'];
   $thisCatID =$row['CatID'];
   
    echo "<a href='SubCategory.php?CatID=" . $CatID . "'>" . $SubCategoryName ."[/url]
";
   }

?>

 

--FrosT

[boo_lolly]

i'd recommend changing the categories table structure. make it look like this:

+----------------+-------------------------+------------+
|  parentcatid   |          name           |  subcatid  |
+----------------+-------------------------+------------+
|   0            |  Appliances             |            |
+----------------+-------------------------+------------+
|   1            |  Dishwashers            |     0      |
+----------------+-------------------------+------------+
|   2            |  Washing Machine        |     0      |
+----------------+-------------------------+------------+
|   3            |  Dryer                  |     0      |
+----------------+-------------------------+------------+
|   4            |  Kitchenware            |            |
+----------------+-------------------------+------------+
|   5            |  Plates                 |     4      |
+----------------+-------------------------+------------+
|   6            |  Silverware             |     4      |
+----------------+-------------------------+------------+
|   7            |  Napkins                |     4      |
+----------------+-------------------------+------------+
|   8            |  Kitchen Knives         |     4      |
+----------------+-------------------------+------------+
|   9            |  Gardening Equipment    |            |
+----------------+-------------------------+------------+
|   10           |  Potting Soil           |     9      |
+----------------+-------------------------+------------+
|   11           |  Tiller                 |     9      |
+----------------+-------------------------+------------+

 

set parentcatid to autoincrement. if your table looks like that, you can call upon each individual subcat, and you can list all the subcats if you need to as well. does that make sense? i think this is a much easier/efficient way of keeping up with categories. and you can have a separate items table that will check the parentcatid OR subcat id in the categories table. at least that's the way i did it when i built a small shopping cart cms for a client of mine.

[ycoleman]

I still get this stupid error 

"Notice: Undefined variable: thisCatID "

 

I thought that I declared this as a variable.

[per1os]

<?php
if (isset($_GET['catid'])) {
     $thisCatID = mysql_real_escape_string($_GET['catid']);
}else {
    //uh oh no catid!!! set it to 1 (i do not know why)
    $thisCatID = 1;
}
?>

 

 

[ycoleman]

Ok it's working so far  but when I click on each Category, the same Subcategory appears on each page and I have way more subcategories.