SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast SmAsH_HaCkEr's Avatar
    Join Date
    Aug 2009
    Location
    Egypt
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    problem with array and mysql

    Hi,

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

    1 - problem with sub_categories

    the data base looks like :

    PHP Code:
    "`id`,`name`,`url`,`sub_cat`" 
    and the sub_cat column should have the id of the sub categories in this way :
    PHP Code:
    1,2,
    in the php file :

    PHP Code:
    $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 :

    PHP Code:

    $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>\n<br />\n<div class='text'>$atext</div>\n<br /><br />\n <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 :

    PHP Code:
    $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. #2
    SitePoint Guru
    Join Date
    Jan 2007
    Posts
    967
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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. #3
    Non-Member
    Join Date
    Apr 2011
    Location
    no fixed address
    Posts
    851
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by SmAsH_HaCkEr View Post
    and the sub_cat column should have the id of the sub categories in this way :
    PHP Code:
    1,2,
    you've got it backwards

    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Non-Member
    Join Date
    Apr 2011
    Location
    no fixed address
    Posts
    851
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    With the adjacency list model you need to use recursion to perform some tasks which results in a performance hit with large databases.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by webdev1958 View Post
    With the adjacency list model you need to use recursion to perform some tasks ...
    the operative part of that statement being only some tasks -- i.e. not all, the simpler tasks are accomplished extremely efficiently using self-joins
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast SmAsH_HaCkEr's Avatar
    Join Date
    Aug 2009
    Location
    Egypt
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    PHP Code:
    $text "welcome,"
    The result should be
    PHP Code:
    welcome 
    Any function except str_replace

    And thank you in advance

  8. #8
    Non-Member
    Join Date
    Apr 2011
    Location
    no fixed address
    Posts
    851
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    the operative part of that statement being only some tasks -- i.e. not all, the simpler tasks are accomplished extremely efficiently using self-joins
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •