Problem with array and mysql

PHP
1

Hi,

While I’m trying to program my own script i faced some problems :

1 - problem with sub_categories

the data base looks like :

"`id`,`name`,`url`,`sub_cat`"

and the sub_cat column should have the id of the sub categories in this way :

1,2,5

in the php file :


$url = $_GET['url'];
$query = mysql_query("SELECT `id`,`name`,`url`,`sub_cat` FROM `cats` WHERE `url` = '$url' LIMIT 1");
list($id,$name,$url,$sub_cat) = mysql_fetch_array($query);


if($sub_cat != "none") {
$sql = mysql_query("SELECT `url` FROM `cats` WHERE `id` IN ($sub_cat)");
if(mysql_num_rows($sql) > 0) {
while(list($urls) = mysql_fetch_array($sql)) {
$OR = "OR `cat` = '$urls' ";
}
}
}

and i should start a new query to get the articles which had the same $urls :



$query = mysql_query("SELECT `id`,`name`,`text`,`cat` FROM `articles` WHERE `cat` = '$url' $OR ORDER BY `id` DESC LIMIT 20");
while(list($aid,$aname,$atext,$acat) = mysql_fetch_array($query)) {

echo "<h1>$aname</h1>\
<br />\
<div class='text'>$atext</div>\
<br /><br />\
 <h3>$acat</h3>";

}

but when i open main category it doesn’t show all topics in the main category and in the sub ones

i tried to make OR query like this one :

$OR = "OR `cat` IN ('$urls') ";

but it didn’t work either so how can i show all articles in the main and the sub cats ??

And Thank You In Advance

2

MYSQL doesn’t do this. The correct way would be to have another table containing sub categories and a third that connects the subcategories to the other table

Example

BOOKS

id
title

Subcategory

id
name

sub category connector
(this table binds the other two togethor)

book_id
subcategory_id

3

MYSQL doesn’t do this. The correct way would be to have another table containing sub categories and a third that connects the subcategories to the other table

With the nested set model you can store all your categories, as many levels of sub categories as you like, and then just assign a category id to a product in the products table.

4

you’ve got it backwards :slight_smile:

instead of a sub_cat column with multiple ids in a single value (which is a serious no-no in database design), you should ahve a parent_cat column with just the id of the parent

like this – Categories and Subcategories

:slight_smile:

5

With the adjacency list model you need to use recursion to perform some tasks which results in a performance hit with large databases.

6

the operative part of that statement being only some tasks – i.e. not all, the simpler tasks are accomplished extremely efficiently using self-joins

7

i guess i’ll write it from the begining
I think vb script doing it like the way i tried to do it or maybe i got it wrong.
Well Thank you all very much and i will try the lesson above
Forgive me as i’m writing from the mobile
And i have another question

Is there any function to remove the last letter from any text
For example


$text = "welcome,";

The result should be


welcome

Any function except str_replace

And thank you in advance

8

You can use whichever model you choose. But with the nested set model you don’t need recursion at all to perform any tasks and the nested set model is a fairly simple way to store and manipulate hierarchal data in a database imo.

But each to their own :slight_smile: