Hi all,

I am trying to follow the category structure php code from this site:
http://www.knowledgesutra.com/forums...ub-categories/

Steps I have already done:

1. I've created the db ok and verified it...Made sure the correct tables were there
2. Created the main.php file Included the connect string
3. Created the sub.php file Included the connect string
4. uploaded them both

opened main.php and I am getting:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in main.php on line 20

FYI:

I used the The Final Script as stated verbatim on the above url with the only exception being that I changed the connect to db string to:

PHP Code:

<?php

$dbhost = 'localhost';
$dbuser = 'user';
$dbpass = 'pass';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('<br> Error connecting to mysql');

$dbname = 'navigation';
mysql_select_db($dbname);

?>

The above change was for no other reason than because it works with another php script I use. I go with what works.

Anyone have a suggestion?

Thanks again!
Brian

You have a bad SQL query somewhere. At first glance, the only thing I notice is that the author keeps using single quotes instead of backticks. For example, this:

SELECT * FROM 'categories' WHERE 'parentid' = 0 ORDER BY 'catid' ASC

should be

SELECT * FROM `categories` WHERE `parentid` = 0 ORDER BY `catid` ASC

Better yet, don't use backticks unless you actually need them:

SELECT * FROM categories WHERE parentid = 0 ORDER BY catid ASC

Overall, the code isn't so great. I keep seeing this:

PHP Code:

echo "$cat[catname]"; //completely pointless quotes 


and every select query is a "select *".

Thank you, I will have another look at that code to see what might be the culprit.

Originally Posted by Brian07002

Thank you, I will have another look at that code to see what might be the culprit.

still no luck, still have an error with the script.

delete

Ok,

At this point I created a new 'categories' db from scratch...

Here is what I have:

Database name: Navigation

Two tables in the Navigation db: Categories, Subcategories

In the Categories table, I have 4 columns:

catid (int 11)
parentid (int 11)
name (varchar 255)
description (text)

In the Subcategories table, I have 4 columns:

subcatid (int 11)
catid (int 11) ( I don't know if this is needed in the subcat table..Is it since it's already in the cat table? )
name (varchar 255)
description (text)

Question:

I this correct?

Isn't a subcategory a category with a parent? If so you can do without the second table.

I have progressed with this script, but now I am having a problem with creating a dynamic page for each subcategory. Ex: when I click on a subcategory, I want a new page to display and that page would be pre populated with a php script that is generated from another scripts admin page. I am going to show you what the php script that needs to go into each subcategory page below:

Note: This code will be slightly different in each subcategory page.

Example 1 might look like this:

PHP Code:

 $f = fopen('http://reunitemysite.com/scripts/amp/show.php?z=1&incl=1&ip='.getenv('REMOTE_ADDR').'&url='.urlencode(getenv('HTTP_HOST').getenv('REQUEST_URI')),'r');
echo stripslashes(fread($f,100000));
fclose($f); 


Example 2 might look like this:

PHP Code:

 $f = fopen('http://reunitemysite.com/scripts/amp/show.php?z=7&incl=1&ip='.getenv('REMOTE_ADDR').'&url='.urlencode(getenv('HTTP_HOST').getenv('REQUEST_URI')),'r');
echo stripslashes(fread($f,100000));
fclose($f); 


Note that the two examples look identical but there not -- the difference is very subtle, and that subtle difference is the number after the z= part in the url.

So the end result is that each subcategory page will be turned into a "zone" once that zone code is on each subcategory page. I want the pages to be dynamic in the sense of not having to create the subcategory pages one at a time' ex .html pages.

I have some code to show it's working up to the point where categories and subcategories display, but the subcategory pages haven't been completed yet. If you need db structure / sample code let me know I will post it.

Btw-, I am using one db table for the categories not two. I used the parent id to determine if it's a cat or subcat.It feels good to learn again...For a change

Thank you again!
Brian

anyone?

I think this has been mentioned in some of your other threads before already.
If you want to make the page dynamic, you need to create a template page, and get some key information to determine what you should output on the page.

In your case, if the whole content is as simple as the code snippets you posted above, you could do with a very simple soltuion, such as:

PHP Code:

// get the id value, i.e. as a GET variable
// possibly adding some validation
$id = $_GET['id'];

$f = fopen('http://reunitemysite.com/scripts/amp/show.php?z=' . $id . '&incl=1&ip='.getenv('REMOTE_ADDR').'&url='.urlencode(getenv('HTTP_HOST').getenv('REQUEST_URI')),'r'); 
echo stripslashes(fread($f,100000)); 
fclose($f); 


Originally Posted by lizciz

I think this has been mentioned in some of your other threads before already.
If you want to make the page dynamic, you need to create a template page, and get some key information to determine what you should output on the page.

In your case, if the whole content is as simple as the code snippets you posted above, you could do with a very simple soltuion, such as:

PHP Code:

// get the id value, i.e. as a GET variable
// possibly adding some validation
$id = $_GET['id'];

$f = fopen('http://reunitemysite.com/scripts/amp/show.php?z=' . $id . '&incl=1&ip='.getenv('REMOTE_ADDR').'&url='.urlencode(getenv('HTTP_HOST').getenv('REQUEST_URI')),'r'); 
echo stripslashes(fread($f,100000)); 
fclose($f); 


Thank you, that is working great!

One more question. I am trying to open the links to my subcategories on the same page as the categories in a div section. Currently I have one sql query to display the categories using a where clause (where parentid=0) will display the categories. Now how could I use that same query so the where clause will be != (not equal) to diplay the subcategories? That's how I was thinking it could be accomplished, I think I need two where clauses in this case cause the parentid=0 is for the cats and parentid=(anything else) is for subcats

Thanks again
Brian

I don't understand the question, really. You have something like this?

select * from catageories where parentid = 0

And, you just want to change it into this?

select * from catageories where parentid != 0

So, what's the problem?

Originally Posted by lizciz

I don't understand the question, really. You have something like this?

select * from catageories where parentid = 0

And, you just want to change it into this?

select * from catageories where parentid != 0

So, what's the problem?

Sorry, I basically repeated myself on that message, I was experiencing some excruciating back pain last night / early this morning...Feeling a bit better. Ok, let me show you some code:

<html>
<head><style type="text/css" media="screen"></style></head>

<?php

// Connect to the categories database
include('connect.php');

// Query to select the categories from the database
$query = "SELECT * FROM categories WHERE `parentid` = 0 ORDER BY `catid` ASC";
$result = mysql_query($query);

// Here I am using columns (reference page: http://www.qualitycodes.com/tutorial.php?articleid=33&title=Displaying-Records-in-Multiple-Columns-Using-PHP )to display the *Categories* in a nice layout //


$cols = 5;		// Here we define the number of columns
echo "<table>";	       // The container table with $cols columns

do {
echo "<tr>";
for($i=1;$i<=$cols;$i++) {	// All the rows will have $cols columns even if the records are less than $cols

$row=mysql_fetch_array($result);

if($row) {

$catid = $row['catid'];
$catname = $row['name'];

// $img = $row['banner'];

?>

<td>
<table>
<tr valign="top">
<td> <!-- <img src="<?php echo $img ?>" /> --> </td> <!-- columns can have both text and images -->
<td>


<?php 

// The following url display's the Category Name, and then is linked to the catid row in the categories database
echo "<a href='subcategories.php?catid=".$catid."'>".$catname."</a> ";?> <?php echo "<p>"; ?>

</td>
<td width="50">&nbsp;</td>	<!-- Create gap between columns -->

</tr>
</table>
</td>

<?php

}

else {
echo "<td>&nbsp;</td>";	//If there are no more records at the end, add a blank column
}
}
} 

while($row);
echo "</table>";

?>
</html>

That code will *ALMOST* work as I want, however, I don't want to use a subcategories.php page to load the subcatgories, instead, I want the subcategories themselves to show in a column layout just like the categories dispaly does, on the SAME PAGE. I stressed the SAME PAGE cause that is the main objective not to load on a new page. So the categories would display in a column down the left side of the page, and the subs to the right but neatly in columns.

Thank you Lizciz, and sorry for the bad post before.
Brian

I'm not sure i got it yet, but here's a shot (again, a very simple example).

You'll need just 1 page basically

// Select the categories, you'll want to show them every time, right?
// Store them in an array (or output them directly, like you do above,
// i prefer to do ALL "administrative" actions first, like quering the DB,
// and THEN output it).

// Check if a category has been chosen (i.e. with a GET variable,
// like 'cat_id'). You can i.e. use the isset() function

// if a category is set, fetch all sub categories from the database,
// store in an array

// check if also a subcategory has been set (i.e. 'sub_cat_id')
// if set, fetch the data from that sub category.


// Now for the actual output
// Show your categories, like above...
// if a category is set, output them aswell, otherwise... well, don't :P
// if a sub category is set, show it's actual data

Now, all the subcategory links should go to their own page, just with an added "?cat_id=xxx". And, the sub category links also go to the same address, with another added "&sub_cat_id=yyy".

I think my problem is how do I check if the subcat has been set if I have not created a table for the subcats? How would that be done with the select query? Well, i know the !=0 but thats giving me the problem and I made two queries using query2

To select the categories, you do as in your code above

SELECT id FROM categories WHERE `parentid` = 0 ORDER BY `catid` ASC

If a category has been selected, you want to select its' sub categories

SELECT id FROM categories WHERE `parentid` = [CATEGORY ID HERE] ORDER BY `catid` ASC

If a sub category has been selected, you want to selected the data from that sub category. Now, I don't know how exactly your database looks, but something like this perhaps?

SELECT data FROM categories WHERE `parentid` = [CATEGORY_ID_HERE] AND `id` = [SUB_CATEGORY_ID_HERE] ORDER BY `catid` ASC

Perhaps this is just me, but I don't know if having the categories and sub categories in the same table like that is the best solution.

I *think*, that in a more general solutions, one would do something like this:

Table: categories
Fields: id (int), name (varchar), description (text), etc...

Table: sub_cats
Fields: parent_id (int), sub_id (int)


Explanation:

ALL categories (including sub categories) are stored in the categories table.
The sub_cats table just links one category to another within the categories table.
Example:

categoies
 id   name
---------------------------------
  1   Fruit
  2   Apple
  3   Programming languages
  4   Banana
  5   PHP
  6   Orange
  7   C++

sub_cats
 parent_id   sub_id
-------------------------------
 1           1       // link Fruit to itself, meaning it is a "top level parent"
 3           3       // link Programming languages to itself, meaning it is a "top level parent"
 1           2       // this will make Fruit a parent to Apple
 1           4       // this will make Fruit a parent to Banana
 1           6       // this will make Fruit a parent to Orange
 3           5       // this will make Programming languages a parent to PHP
 3           7       // this will make Programming languages a parent to C++

This means that any category can be linked to any other category. It will i.e. enable sub categories, to have sub categories of their own, which in case can have sub categories etc. To mark a category as "top level" (has no parent), i chose to link the category to itself (although it could probably be done in some other way).


Perhaps someone else would like to comment on this suggestion?


In order to actually fetch data from the tables, you'd have to use some joining. If you havn't dealt with that before, this approach probably sounds a whole lot more complicated :P

But, to select all parent categories, you'll want to find all categories that are linked to themselves in the sub_cats table.

select id,name,description
from categories
join sub_cats on categories.id = sub_cats.parent_id
where sub_cats.parent_id = sub_cats.sub_id

This will return the Fruit and Programming languages rows.

And to find the sub category of any category, lets say Fruit (with id 1), find the ones with the specified parent_id in the sub_cats table.

select id,name,description
from categories
join sub_cats on categories.id = sub_cats.parent_id
where sub_cats.parent_id = 1

This will return the Apple, Banana and Orange rows.


Wow, this was a long post, I'll just stop writing now :P

I was told that using one table and using the parentid was better and less stress on the db, but I still have alot more to learn about that.

Lizciz,

Could you post some working code? I just cannot wrap my head around all the code. I've been trying to work from post #16, with out any luck.

I'm going to assume that your table looks like this:

categories: id, name, parentid, data

where id is the unique key for each category,
name is its actual name,
parent id refers to another category in the same table as its parent (or is zero)
and data - in it's simplest form - would be just your 'z' variable in the small code from your post #8 (or i.e. NULL for parent categories).

PHP Code:

// connect to DB
// This is just a copy/paste from your first post, so I'll leave it out

$categories = array();
$sub_cats = array();

$cat_id = NULL;
$sub_cat_id = NULL;
$data = NULL;

// fetch all parent categories
$query = "select id,name from categories where parentid = 0";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) {
    $categories[] = $row;
}


// check if a category has been selected, and if so, select its sub categories
if (isset($_GET['cat_id'])) {
    $cat_id = $_GET['cat_id']; // you should also validate the id. i.e. check that it is numeric and run it through mysql_real_escape_string()
    $query = "select id,name from categories where parentid=$cat_id";
    $result = mysql_query($query);
    while ($row = mysql_fetch_array($result)) {
        $sub_cats[] = $row;
    }

    // check if a subcategory has also been selected, and if so, select its data
    if (isset($_GET['sub_cat_id'])) {
        $sub_cat_id = $_GET['sub_cat_id']; // you should also validate the id. i.e. check that it is numeric and run it through mysql_real_escape_string()
        $query = "select data from categories where id=$sub_cat_id and parentid=$cat_id";
        $result = mysql_query($query);
        $row = mysql_fetch_array($result);
        $data = $row['data'];
    }
}

// now, all relevant data has been selected. For simplicity, I'll just display it all
var_dump($categories, $sub_cats, $data);


// You'll want to create category links as
foreach ($categories as $c) {
    echo '<a href="this_page.php?cat_id=' . $c['id'] . '">' . $c['name'] . '</a>';
}

// ... sub categoires as ...
if (!is_null($cat_id) && !empty($sub_cats)) {
    foreach ($sub_cats as $c) {
        echo '<a href="this_page.php?cat_id=' . $cat_id . '&sub_cat_id=' . $c['id'] . '">' . $c['name'] . '</a>';
    }
}

// ... and data as your previous code. So something like
if (!is_null($sub_cat_id) && !is_null($data)) {
    $f = fopen('http://reunitemysite.com/scripts/amp/show.php?z=' . $data . '&incl=1&ip='.getenv('REMOTE_ADDR').'&url='.urlencode(getenv('HTTP_HOST').getenv('REQUEST_URI')),'r'); 
    echo stripslashes(fread($f,100000)); 
    fclose($f);
} 


That should do it. Please note though:

*** Untested code! ***