2 Replies Latest reply on Nov 3, 2008 12:35 PM by Newsgroup_User

    innerjoin problem.

    SyKeSnr1

      Hello,
      For my website I use "dynamic" menu's which are stored in a SQL table. When a certain product is shown on the detail page the menu according to the product categorie (dutch for category) is shown.

      The products themselves and all their information are stored in the 'product' table, the menu's for the different product categories are stored in the 'index' table.

      On the detail page a get is used to show the right product information. I wanted to do the same thing for the menu and came up with this SQL:

      SELECT product_2.*
      FROM (product INNER JOIN index ON product.categorie = index.categorie) INNER JOIN product AS product_2 ON index.index = product_2.categorie
      WHERE product.slug = colname2

      Table fields used:
      product.categorie
      product.slug (this is the name of the product which is used in the GET to show the right product)

      index.categorie (this variable is the same as the product table counterpart. When a product category "hat" exists, there is also a "hat" row in the index table)
      index.index (this is where the HTML of the actual menu is stored)

      When I show it like this the following error pops up:

      You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'index ON product.categorie = index.categorie) INNER JOIN product AS product_2 ON' at line 1

      I kind of copied and renamed the variables which worked for an older website of mine (didnt write it myself, dont have that much knowledge of SQL/PHP), this code works when used to show comparable products, instead of the "categorie" variable an "id" variable is used which is a short number.

      I cant figure out what's going wrong, maybe u guyz can tell me. If you need any additional information please let me know.

      Thanks!



        • 1. Re: innerjoin problem.
          SyKeSnr1 Level 1
          Can anyone tell me if this has to do with faulty SQL, or is it simply not possible to do an innerjoin with two non-number variables or something? I cant figure out why this isnt working..
          • 2. Re: innerjoin problem.
            Level 7
            SyKeSnr1 posted in macromedia.dreamweaver:

            > Can anyone tell me if this has to do with faulty SQL, or is it
            > simply not possible to do an innerjoin with two non-number
            > variables or something? I cant figure out why this isnt working..

            I'm not much good with JOINs for some reason.

            If you don't get an answer in here, then, if you have access to a
            Usenet NewsServer, there are newsgroups that are devoted to databases
            and SQL

            comp.databases.mysql
            alt.php.sql

            and probably more. You'll need to mention which database you're using.


            --
            Mark A. Boyd
            Keep-On-Learnin' :)