Mysql Fehler

Einklappen
X
 
  • Filter
  • Zeit
  • Anzeigen
Alles löschen
neue Beiträge

  • Mysql Fehler

    Hallo Community,
    habe folgendes Problem ich bekomme folgende Fehlermeldung

    Code:
    [COLOR=#000000][B]1064 - 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 '' at line 9
    
    select count(DISTINCT p.products_id) as total  from products_description pd                       join products_to_categories p2c                       join products p                        join (select p.products_id         from products p         join products_attributes pa on pa.products_id = p.products_id         join products_options_values pv on pv.products_options_values_id = pa.options_values_id         where pa.options_values_id in (4)         and pv.language_id = 2        [/B][/COLOR]
    der zuständige code ist folgender:
    Code:
      if (!empty($excluded_options))
        $excluded_options_condition = 'and po.products_options_id not in (' . $excluded_options . ')';
      // modify options filter join to connect to products_attributes instead of products
      $option_filter_join = str_replace('x.products_id = p.products_id', 'x.products_id = pa.products_id', $option_filter_join);
      // 1. get options and values for this catgory
      $filter_sql = 'select po.products_options_id options_id, 
                     po.products_options_name options_name,
                     pv.products_options_values_id options_values_id,
                     pv.products_options_values_name options_values_name,
                     count(pa.products_id) option_values_count
                     from '.TABLE_PRODUCTS_OPTIONS.' po
                     join '.TABLE_PRODUCTS_OPTIONS_VALUES_TO_PRODUCTS_OPTIONS.' pv2po on pv2po.products_options_id = po.products_options_id
                     join '.TABLE_PRODUCTS_OPTIONS_VALUES.' pv on pv.products_options_values_id = pv2po.products_options_values_id
                     join '.TABLE_PRODUCTS_ATTRIBUTES.' pa on pa.options_id = po.products_options_id and
                                                    pa.options_values_id = pv.products_options_values_id
                     join '.TABLE_PRODUCTS_TO_CATEGORIES.' p2c on p2c.products_id = pa.products_id
                   join '.TABLE_PRODUCTS.' p on p.products_id = pa.products_id
                     '. $option_filter_join .'
                     where p2c.categories_id = ' . $current_category_id . '
                     and po.language_id = '.(int) $_SESSION['languages_id'].'
                     and pv.language_id = '.(int) $_SESSION['languages_id'].'
                     and p.products_status = 1
                     '. $excluded_options_condition .'
                     group by 1, 2, 3, 4
                     order by 1, 4';
      // echo $filter_sql;
      $filter_query = xtDBquery($filter_sql);
    Ich weiss einfach nicht mehr weiter .
    MySQL Version: 5.5.9
    5.2.17 (Zend: 2.2.0)

    Wäre über jede Hilfestellung dankbar !!!

    lg eleven-seven

  • #2
    Wenn das in der Fehlermeldung die komplette Query ist – dann machst du wohl diese Klammer
    Code:
    join (select p.products_id         from products p   …
    nirgends mehr zu.
    I don't believe in rebirth. Actually, I never did in my whole lives.

    Kommentar


    • #3
      wenn ich nur wüsste wo ??

      habe mal den filter_sql per echo ausgeben lassen mit diesem Ergebnis:
      select po.products_options_id options_id, po.products_options_name options_name, pv.products_options_values_id options_values_id, pv.products_options_values_name options_values_name, count(pa.products_id) option_values_count from products_options po join products_options_values_to_products_options pv2po on pv2po.products_options_id = po.products_options_id join products_options_values pv on pv.products_options_values_id = pv2po.products_options_values_id join products_attributes pa on pa.options_id = po.products_options_id and pa.options_values_id = pv.products_options_values_id join products_to_categories p2c on p2c.products_id = pa.products_id join products p on p.products_id = pa.products_id join (select p.products_id from products p join products_attributes pa on pa.products_id = p.products_id join products_options_values pv on pv.products_options_values_id = pa.options_values_id where pa.options_values_id in (4) and pv.language_id = 2 group by 1 having count(p.products_id) = (select count(products_options_values_id) from products_options_values where language_id = 2 and products_options_values_id in (4))) x on x.products_id = pa.products_id where p2c.categories_id = 1 and po.language_id = 2 and pv.language_id = 2 and p.products_status = 1 group by 1, 2, 3, 4 order by 1, 4
      eleven-seven

      Kommentar


      • #4
        Hier nochmals die Orginaldatei.
        Der Fehler kommt wenn ich eine Option auswähle.

        eleven-seven
        Angehängte Dateien

        Kommentar


        • #5
          Es könnte helfen, wenn du die Query mal vernünftig formatierst. So guckt sich das doch niemand an.

          PHP-Code spielt an dieser Stelle jedenfalls keine Rolle. Du hast ja einen MySQL-Fehler.

          Kommentar


          • #6
            Sorry
            bin auch jetzt schon stück weiter, die Fehlermeldung bezog sich auf diesen Part hier.
            HTML-Code:
            // build filter sql to use in product queries and dropdown calculation as additional join part
                if (!empty($option_filter_ids)) {  
                  $option_filter_join = 'join (select p.products_id
                    from '.TABLE_PRODUCTS.' p
                    join '.TABLE_PRODUCTS_ATTRIBUTES.' pa on pa.products_id = p.products_id
                    join '.TABLE_PRODUCTS_OPTIONS_VALUES.' pv on pv.products_options_values_id = pa.options_values_id
                    where pa.options_values_id in (' . $option_filter_ids . ')
                    and pv.language_id = '.(int) $_SESSION['languages_id'].'
                    group by 1
                    having count(p.products_id) = (select count(products_options_values_id) 
                                                      from '.TABLE_PRODUCTS_OPTIONS_VALUES.'
                                                      where language_id = '.(int) $_SESSION['languages_id'].'
                                                 and products_options_values_id in (' . $option_filter_ids . '))) x on x.products_id = p.products_id';
                }
            ein echo dieses Querys ergibt :
            HTML-Code:
            join (select p.products_id         from products p         join products_attributes pa on pa.products_id = p.products_id         join products_options_values pv on pv.products_options_values_id  = pa.options_values_id         where pa.options_values_id in (3,5,1)         and pv.language_id = 2         group by 1         having count(p.products_id) = (select  count(products_options_values_id)                                            from products_options_values                                           where language_id = 2                                      and products_options_values_id in (3,5,1))) x on x.products_id  = p.products_idselect p.products_fsk18, p.products_shippingtime,  p.products_model, p.products_ean, pd.products_name,                       m.manufacturers_name, p.products_quantity,  p.products_image, p.products_weight, pd.products_short_description,                       pd.products_description, p.products_id,  p.manufacturers_id, p.products_price, p.products_vpe,                       p.products_vpe_status, p.products_vpe_value,  p.products_discount_allowed, p.products_tax_class_id                       from products_description pd                       join products_to_categories p2c                       join products p                        join (select p.products_id         from products p         join products_attributes pa on pa.products_id = p.products_id         join products_options_values pv on pv.products_options_values_id  = pa.options_values_id         where pa.options_values_id in (3,5,1)         and pv.language_id = 2         group by 1         having count(p.products_id) = (select  count(products_options_values_id)                                            from products_options_values                                           where language_id = 2                                      and products_options_values_id in (3,5,1))) x on x.products_id  = p.products_id                       left join manufacturers m on p.manufacturers_id =  m.manufacturers_id                       where p.products_status = '1'                       and p.products_id = p2c.products_id                       and pd.products_id = p2c.products_id                                                                                                  and pd.language_id = '2'                       and p2c.categories_id = '1' ORDER BY  p.products_price ASC
            gruß eleven-seven
            Zuletzt geändert von eleven-seven; 26.06.2012, 14:43.

            Kommentar


            • #7
              Nochmals, bitte formatieren!

              PHP-Code ist mittelmäßig uninteressant. Der Parser ist dafür da, den Code zu parsen. Das müssen nicht wir machen.

              Kommentar


              • #8
                HTML-Code:
                join (select p.products_id
                        from products p
                        join products_attributes pa on pa.products_id = p.products_id
                        join products_options_values pv on pv.products_options_values_id = pa.options_values_id
                        where pa.options_values_id in (3,5,1)
                        and pv.language_id = 2
                        group by 1
                        having count(p.products_id) = (select count(products_options_values_id) 
                                                          from products_options_values
                                                          where language_id = 2
                                                     and products_options_values_id in (3,5,1))) x on x.products_id = p.products_id1

                Kommentar


                • #9
                  Fehlermeldung ist unverändert?
                  Wo ist der Anfang der Query?

                  Kommentar


                  • #10
                    Hallo Tobiaz,
                    ja selbe Fehlermeldung!
                    Das wa die komplette ausgabe der $option_filter_join

                    gruß

                    Kommentar


                    • #11
                      Dein Join ist sehr komplex. Es wäre hilfreich, wenn du alle Informationen auf einmal präsentierst. Nochmals, der zusammenhang mit PHP ist ja erstmal irrelevant. Die Query muss erstmal eigenständig lauffähig sein.

                      Kommentar


                      • #12
                        So ich versuche es die relevanten querys hier darzustellen

                        $listing_sql
                        HTML-Code:
                        select p.products_fsk18, p.products_shippingtime, p.products_model, p.products_ean, pd.products_name,
                                              m.manufacturers_name, p.products_quantity, p.products_image, p.products_weight, pd.products_short_description,
                                              pd.products_description, p.products_id, p.manufacturers_id, p.products_price, p.products_vpe,
                                              p.products_vpe_status, p.products_vpe_value, p.products_discount_allowed, p.products_tax_class_id
                                              from products_description pd
                                              join products_to_categories p2c
                                              join products p 
                                              join (select p.products_id
                                from products p
                                join products_attributes pa on pa.products_id = p.products_id
                                join products_options_values pv on pv.products_options_values_id = pa.options_values_id
                                where pa.options_values_id in (3,5,1)
                                and pv.language_id = 2
                                group by 1
                                having count(p.products_id) = (select count(products_options_values_id) 
                                                                  from products_options_values
                                                                  where language_id = 2
                                                             and products_options_values_id in (3,5,1))) x on x.products_id = p.products_id
                                              left join manufacturers m on p.manufacturers_id = m.manufacturers_id
                                              where p.products_status = '1'
                                              and p.products_id = p2c.products_id
                                              and pd.products_id = p2c.products_id
                                              
                                                                           
                                              and pd.language_id = '2'
                                              and p2c.categories_id = '1' ORDER BY p.products_price ASC
                        $filter_sql

                        HTML-Code:
                        select po.products_options_id options_id, 
                                         po.products_options_name options_name,
                                         pv.products_options_values_id options_values_id,
                                         pv.products_options_values_name options_values_name,
                                         count(pa.products_id) option_values_count
                                         from products_options po
                                         join products_options_values_to_products_options pv2po on pv2po.products_options_id = po.products_options_id
                                         join products_options_values pv on pv.products_options_values_id = pv2po.products_options_values_id
                                         join products_attributes pa on pa.options_id = po.products_options_id and
                                                                        pa.options_values_id = pv.products_options_values_id
                                         join products_to_categories p2c on p2c.products_id = pa.products_id
                                       join products p on p.products_id = pa.products_id
                                         join (select p.products_id
                                from products p
                                join products_attributes pa on pa.products_id = p.products_id
                                join products_options_values pv on pv.products_options_values_id = pa.options_values_id
                                where pa.options_values_id in (3,5,1)
                                and pv.language_id = 2
                                group by 1
                                having count(p.products_id) = (select count(products_options_values_id) 
                                                                  from products_options_values
                                                                  where language_id = 2
                                                             and products_options_values_id in (3,5,1))) x on x.products_id = pa.products_id
                                         where p2c.categories_id = 1
                                         and po.language_id = 2
                                         and pv.language_id = 2
                                         and p.products_status = 1
                                         
                                         group by 1, 2, 3, 4
                                         order by 1, 4
                        $option_filter_join
                        HTML-Code:
                        join (select p.products_id
                                from products p
                                join products_attributes pa on pa.products_id = p.products_id
                                join products_options_values pv on pv.products_options_values_id = pa.options_values_id
                                where pa.options_values_id in (3,5,1)
                                and pv.language_id = 2
                                group by 1
                                having count(p.products_id) = (select count(products_options_values_id) 
                                                                  from products_options_values
                                                                  where language_id = 2
                                                             and products_options_values_id in (3,5,1))) x on x.products_id = pa.products_id
                        Ich hoffe das passt jetzt !

                        Gruß und danke schonmal

                        Kommentar


                        • #13
                          Also die ersten beiden Queries sind in meinen Augen (und auch nach Meinung meines SQL-Servers) fehlerfrei. Von dem dritten erwarten wir das wohl nicht.

                          Was passiert bei dir?

                          Kommentar


                          • #14
                            die ersten Zwei ohne Probleme beim dritten dann Fehler!

                            HTML-Code:
                            #1064 - 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 'join (select p.products_id         from products p         join products_attri' at line 1

                            Kommentar


                            • #15
                              Wie gesagt, das wundert doch nicht.

                              Ein Query, der mit JOIN anfängt ist ganz sicher kein qültiger Query.

                              Kommentar

                              Lädt...
                              X