Mysql Fehler

[eleven-seven]

Hallo Community,
habe folgendes Problem ich bekomme folgende Fehlermeldung

[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:

  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

[wahsaga]

Wenn das in der Fehlermeldung die komplette Query ist – dann machst du wohl diese Klammer

join (select p.products_id         from products p   …

nirgends mehr zu.

[eleven-seven]

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

[eleven-seven]

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

eleven-seven

[TobiaZ]

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.

[eleven-seven]

Sorry
bin auch jetzt schon stück weiter, die Fehlermeldung bezog sich auf diesen Part hier.

// 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 :

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

[TobiaZ]

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.

[eleven-seven]

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

[TobiaZ]

Fehlermeldung ist unverändert?
Wo ist der Anfang der Query?

[eleven-seven]

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

gruß

[TobiaZ]

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.

[eleven-seven]

So ich versuche es die relevanten querys hier darzustellen

$listing_sql

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

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

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

[TobiaZ]

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?

[eleven-seven]

die ersten Zwei ohne Probleme beim dritten dann Fehler!

#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

[TobiaZ]

Wie gesagt, das wundert doch nicht.

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