xtcommerce und MySQL 5.x

[MrX1980]

Hallo!

Hat jemand schon xtcommerce 3.0.4 sp1 mit MySQL 5.x richtig ans laufen bekommen?

Fehler: 1054 "Unknown column ..."

Ich wei?, dass es mit den "left Join" bzw. der Reihenfolge der Tabellen bei "from" zu tun hat. Allerdings kenne ich mit Joins nicht so aus. Bei wbb bzw. Portal 2.2 habe ich es hinbekommen. Nur hierbei klappt es noch nicht so richtig.

Hinweise dazu habe ich aus diesem Forum:

http://www.4homepages.de/forum/index.php?topic=10184.new

Jemand der ein "sp2" erstellen k?nnte diesbez?glich?

[MrX1980]

Von oscommerce ist heute eine MySQL 5.x taugliche Version rausgekommen.

Dann denke ich mal wird es auch bald von xtcommerce eine kommen, oder?

[mzanier]

f?r sponsoren ja.

[joterveen]

$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 ((( ".TABLE_PRODUCTS_DESCRIPTION." pd, ".TABLE_PRODUCTS." p )

left join ".TABLE_MANUFACTURERS." m on p.manufacturers_id = m.manufacturers_id, ".TABLE_PRODUCTS_TO_CATEGORIES." p2c )

left join ".TABLE_SPECIALS." s on p.products_id = s.products_id )

where p.products_status = '1'

and p.products_id = p2c.products_id

and pd.products_id = p2c.products_id

".$group_check."

".$fsk_lock."

and pd.language_id = '".(int) $_SESSION['languages_id']."'

and p2c.categories_id = '".$current_category_id."'".$sorting;

----------------

Beispiel:

Select COLUMNS From ((TABLES T1) left join TABLE T2 on T1.COLUMN = T2.COLUMN)

----------------

Wichtig sind die Klammern - Dann gehts auch mit MySQL 5.xxx

mfg

[reimemonster]

In welcher Datei muss man dass ?ndern?

[joep147]

Originally posted by reimemonster@May 10 2006, 01:52 PM

In welcher Datei muss man dass ?ndern?

Quoted post

mochte ich auch gerne wissen.

ich habe auch dieser Fehler:

1054 "Unknown column ..."

und ich habe MySQL 4.1.18-standard??

mfg

[joep147]

falsches Topic, entschuldigung.

mfg

[Samson]

for me works perfect using mysql5 and php5

this is the file that has to be changed:

\xtcommerce\includes\modules\default.php

Line 199

from ((".TABLE_PRODUCTS." p, ".TABLE_PRODUCTS_DESCRIPTION." pd, ".TABLE_MANUFACTURERS." m, ".TABLE_PRODUCTS_TO_CATEGORIES." p2c ) left join ".TABLE_SPECIALS." s on p.products_id = s.products_id )

Line 233

from ((".TABLE_PRODUCTS." p, ".TABLE_PRODUCTS_DESCRIPTION." pd, ".TABLE_MANUFACTURERS." m ) left join ".TABLE_SPECIALS." s on p.products_id = s.products_id )

Line 277

from ((".TABLE_PRODUCTS." p, ".TABLE_PRODUCTS_DESCRIPTION." pd, ".TABLE_MANUFACTURERS." m, ".TABLE_PRODUCTS_TO_CATEGORIES." p2c ) left join ".TABLE_SPECIALS." s on p.products_id = s.products_id )

Lin3 320

from (((".TABLE_PRODUCTS_DESCRIPTION." pd, ".TABLE_PRODUCTS." p ) left join ".TABLE_MANUFACTURERS." m on p.manufacturers_id = m.manufacturers_id, ".TABLE_PRODUCTS_TO_CATEGORIES." p2c )

[Giant5000]

Hi Samson,

great hint ... works fine ;-)

thanks a lot

Best regards Sven

[Johnny23]

Ich habe beide losungen versucht, aber denn bekomm ich ein 1064 - error

Kann jemand genau anweisen wass ich anderen muss?

Sehr viel Dank!

Johnny

[AM49549R]

ich hatte f?r den alten Shop V2 mein MSQL zur?ckstufen lassen auf 4.

Nun habe ich aber die 3.04 SP1 drauf ebenfalls unter MSQL 4, was ist der Unterschied zu MSQL 5??

[74deluxe]

<div class='quotetop'>QUOTE(Johnny23 @ Jun 7 2006, 04:56 PM) Quoted post</div><div class='quotemain'>

Ich habe beide losungen versucht, aber denn bekomm ich ein 1064 - error

Kann jemand genau anweisen wass ich anderen muss?

Sehr viel Dank!

Johnny

[adlerweb]

In der Datei für Reviews scheint auch noch ein fehlerhaftes JOIN zu hängen

[gio]

Mir fällt ein Gebirge vom Herzen.. et funzt wieder!

[Deadblow]

Wie sieht es mit der products_new.php aus?

Dort kommt der Fehler auch vor, aber ich bin zu blöd die richtige Klammer-Setzung bei 2 left joins zu bewältigen.

Danke im Voraus.

[techway]

products_new.php für mysql5

<?php

/* -----------------------------------------------------------------------------------------

   $Id: products_new.php 1292 2005-10-07 16:10:55Z mz $   


   XT-Commerce - community made shopping

   http://www.xt-commerce.com


   Copyright (c) 2003 XT-Commerce 

   -----------------------------------------------------------------------------------------

   based on: 

   (c) 2000-2001 The Exchange Project  (earlier name of osCommerce)

   (c) 2002-2003 osCommerce(products_new.php,v 1.25 2003/05/27); www.oscommerce.com 

   (c) 2003     nextcommerce (products_new.php,v 1.16 2003/08/18); www.nextcommerce.org


   Released under the GNU General Public License 

   -----------------------------------------------------------------------------------------

   Third Party contributions:

   Enable_Disable_Categories 1.3            Autor: Mikel Williams | [email protected]


   Released under the GNU General Public License 

   ---------------------------------------------------------------------------------------*/


include ('includes/application_top.php');

// create smarty elements

$smarty = new Smarty;

// include boxes

require (DIR_FS_CATALOG.'templates/'.CURRENT_TEMPLATE.'/source/boxes.php');

// include needed function

require_once (DIR_FS_INC.'xtc_date_long.inc.php');

require_once (DIR_FS_INC.'xtc_get_vpe_name.inc.php');


$breadcrumb->add(NAVBAR_TITLE_PRODUCTS_NEW, xtc_href_link(FILENAME_PRODUCTS_NEW));


require (DIR_WS_INCLUDES.'header.php');


$products_new_array = array ();

$fsk_lock = '';

if ($_SESSION['customers_status']['customers_fsk18_display'] == '0') {

    $fsk_lock = ' and p.products_fsk18!=1';

}

if (GROUP_CHECK == 'true') {

    $group_check = " and p.group_permission_".$_SESSION['customers_status']['customers_status_id']."=1 ";

}

if (MAX_DISPLAY_NEW_PRODUCTS_DAYS != '0') {

    $date_new_products = date("Y.m.d", mktime(1, 1, 1, date(m), date(d) - MAX_DISPLAY_NEW_PRODUCTS_DAYS, date(Y)));

    $days = " and p.products_date_added > '".$date_new_products."' ";

}

$products_new_query_raw = "select distinct

                                    p.products_id,

                                    p.products_fsk18,

                                    pd.products_name,

                                    pd.products_short_description,

                                    p.products_image,

                                    p.products_price,

                                       p.products_vpe,

                                       p.products_vpe_status,

                                    p.products_vpe_value,                                                          

                                    p.products_tax_class_id,

                                    p.products_date_added,

                                    m.manufacturers_name

                                    from ".TABLE_PRODUCTS." p

                                    left join ".TABLE_MANUFACTURERS." m

                                    on p.manufacturers_id = m.manufacturers_id

                                    left join ".TABLE_PRODUCTS_DESCRIPTION." pd

                                    on p.products_id = pd.products_id,

                                    ".TABLE_CATEGORIES." c,

                                    ".TABLE_PRODUCTS_TO_CATEGORIES." p2c 

                                    WHERE pd.language_id = '".(int) $_SESSION['languages_id']."'

                                    and c.categories_status=1

                                    and p.products_id = p2c.products_id

                                    and c.categories_id = p2c.categories_id

                                    and products_status = '1'

                                    ".$group_check."

                                    ".$fsk_lock."                                    

                                    ".$days."

                                    order

                                    by

                                    p.products_date_added DESC ";



$products_new_split = new splitPageResults($products_new_query_raw, $_GET['page'], MAX_DISPLAY_PRODUCTS_NEW, 'p.products_id');


if (($products_new_split->number_of_rows > 0)) {

    $smarty->assign('NAVIGATION_BAR', '

           <table border="0" width="100%" cellspacing="0" cellpadding="2">

                  <tr>

                    <td class="smallText">'.$products_new_split->display_count(TEXT_DISPLAY_NUMBER_OF_PRODUCTS_NEW).'</td>

                    <td align="right" class="smallText">'.TEXT_RESULT_PAGE.' '.$products_new_split->display_links(MAX_DISPLAY_PAGE_LINKS, xtc_get_all_get_params(array ('page', 'info', 'x', 'y'))).'</td>

                  </tr>

                </table>


           ');


}


$module_content = '';

if ($products_new_split->number_of_rows > 0) {

    $products_new_query = xtc_db_query($products_new_split->sql_query);

    while ($products_new = xtc_db_fetch_array($products_new_query)) {

        $products_price = $xtPrice->xtcGetPrice($products_new['products_id'], $format = true, 1, $products_new['products_tax_class_id'], $products_new['products_price'], 1);

        $vpePrice = '';

        if ($products_new['products_vpe_status'] == 1 && $products_new['products_vpe_value'] != 0.0)

            $vpePrice = $xtPrice->xtcFormat($products_price['plain'] * (1 / $products_new['products_vpe_value']), true).TXT_PER.xtc_get_vpe_name($products_new['products_vpe']);

        $buy_now = '';

        if ($_SESSION['customers_status']['customers_fsk18'] == '1') {

            if ($products_new['products_fsk18'] == '0')

                $buy_now = '<a href="'.xtc_href_link(basename($PHP_SELF), xtc_get_all_get_params(array ('action')).'action=buy_now&BUYproducts_id='.$products_new['products_id'], 'NONSSL').'">'.xtc_image_button('button_buy_now.gif', TEXT_BUY.$products_new['products_name'].TEXT_NOW).'</a>';

        } else {

            $buy_now = '<a href="'.xtc_href_link(basename($PHP_SELF), xtc_get_all_get_params(array ('action')).'action=buy_now&BUYproducts_id='.$products_new['products_id'], 'NONSSL').'">'.xtc_image_button('button_buy_now.gif', TEXT_BUY.$products_new['products_name'].TEXT_NOW).'</a>';

        }

        if ($products_new['products_image'] != '') {

            $products_image = DIR_WS_THUMBNAIL_IMAGES.$products_new['products_image'];

        } else {

            $products_image = '';

        }

        if ($_SESSION['customers_status']['customers_status_show_price'] != 0) {

            $tax_rate = $xtPrice->TAX[$products_new['products_tax_class_id']];

            // price incl tax

            if ($tax_rate > 0 && $_SESSION['customers_status']['customers_status_show_price_tax'] != 0) {

                $tax_info = sprintf(TAX_INFO_INCL, $tax_rate.' %');

            } 

            // excl tax + tax at checkout

            if ($tax_rate > 0 && $_SESSION['customers_status']['customers_status_show_price_tax'] == 0 && $_SESSION['customers_status']['customers_status_add_tax_ot'] == 1) {

                $tax_info = sprintf(TAX_INFO_ADD, $tax_rate.' %');

            }

            // excl tax

            if ($tax_rate > 0 && $_SESSION['customers_status']['customers_status_show_price_tax'] == 0 && $_SESSION['customers_status']['customers_status_add_tax_ot'] == 0) {

                $tax_info = sprintf(TAX_INFO_EXCL, $tax_rate.' %');

            }

        }

        $ship_info="";

        if (SHOW_SHIPPING=='true') {

        $ship_info=' '.SHIPPING_EXCL.'<a href="javascript:newWin=void(window.open(\''.xtc_href_link(FILENAME_POPUP_CONTENT, 'coID='.SHIPPING_INFOS).'\', \'popup\', \'toolbar=0, width=640, height=600\'))"> '.SHIPPING_COSTS.'</a>';

        }

        $module_content[] = array ('PRODUCTS_NAME' => $products_new['products_name'],'PRODUCTS_SHIPPING_LINK' => $ship_info,'PRODUCTS_TAX_INFO' => $tax_info, 'PRODUCTS_DESCRIPTION' => $products_new['products_short_description'], 'PRODUCTS_PRICE' => $products_price['formated'], 'PRODUCTS_VPE' => $vpePrice, 'PRODUCTS_LINK' => xtc_href_link(FILENAME_PRODUCT_INFO, xtc_product_link($products_new['products_id'], $products_new['products_name'])), 'PRODUCTS_IMAGE' => $products_image, 'BUTTON_BUY_NOW' => $buy_now);


    }

} else {


    $smarty->assign('ERROR', TEXT_NO_NEW_PRODUCTS);


}


$smarty->assign('language', $_SESSION['language']);

$smarty->caching = 0;

$smarty->assign('module_content', $module_content);

$main_content = $smarty->fetch(CURRENT_TEMPLATE.'/module/new_products_overview.html');

$smarty->assign('main_content', $main_content);


$smarty->assign('language', $_SESSION['language']);

$smarty->caching = 0;

if (!defined(RM))

    $smarty->load_filter('output', 'note');

$smarty->display(CURRENT_TEMPLATE.'/index.html');

include ('includes/application_bottom.php');

?>

[/php]

product_reviews_info.php für mysql5:

[PHP]
<?php

/* -----------------------------------------------------------------------------------------
$Id: product_reviews_info.php 1238 2005-09-24 10:51:19Z mz $

XT-Commerce - community made shopping
http://www.xt-commerce.com

Copyright (c) 2003 XT-Commerce
-----------------------------------------------------------------------------------------
based on:
(c) 2000-2001 The Exchange Project (earlier name of osCommerce)
(c) 2002-2003 osCommerce(product_reviews_info.php,v 1.47 2003/02/13); www.oscommerce.com
(c) 2003 nextcommerce (product_reviews_info.php,v 1.12 2003/08/17); www.nextcommerce.org

Released under the GNU General Public License
---------------------------------------------------------------------------------------*/

include ('includes/application_top.php');
// create smarty elements
$smarty = new Smarty;
// include boxes
require (DIR_FS_CATALOG.'templates/'.CURRENT_TEMPLATE.'/source/boxes.php');
// include needed functions
require_once (DIR_FS_INC.'xtc_break_string.inc.php');
require_once (DIR_FS_INC.'xtc_date_long.inc.php');


// lets retrieve all $HTTP_GET_VARS keys and values..
$get_params = xtc_get_all_get_params(array ('reviews_id'));
$get_params = substr($get_params, 0, -1); //remove trailing &

$reviews_query = "select rd.reviews_text, r.reviews_rating, r.reviews_id, r.products_id, r.customers_name, r.date_added, r.last_modified, r.reviews_read, p.products_id, pd.products_name, p.products_image from ".TABLE_REVIEWS." r left join ".TABLE_PRODUCTS." p on (r.products_id = p.products_id) left join ".TABLE_PRODUCTS_DESCRIPTION." pd on (p.products_id = pd.products_id and pd.language_id = '".(int) $_SESSION['languages_id']."'), ".TABLE_REVIEWS_DESCRIPTION." rd where r.reviews_id = '".(int) $_GET['reviews_id']."' and r.reviews_id = rd.reviews_id and p.products_status = '1'";
$reviews_query = xtc_db_query($reviews_query);

if (!xtc_db_num_rows($reviews_query))
xtc_redirect(xtc_href_link(FILENAME_REVIEWS));
$reviews = xtc_db_fetch_array($reviews_query);

$breadcrumb->add(NAVBAR_TITLE_PRODUCT_REVIEWS, xtc_href_link(FILENAME_PRODUCT_REVIEWS, $get_params));

xtc_db_query("update ".TABLE_REVIEWS." set reviews_read = reviews_read+1 where reviews_id = '".$reviews['reviews_id']."'");

$reviews_text = xtc_break_string(htmlspecialchars($reviews['reviews_text']), 60, '-<br />');

require (DIR_WS_INCLUDES.'header.php');

$smarty->assign('PRODUCTS_NAME', $reviews['products_name']);
$smarty->assign('AUTHOR', $reviews['customers_name']);
$smarty->assign('DATE', xtc_date_long($reviews['date_added']));
$smarty->assign('REVIEWS_TEXT', nl2br($reviews_text));
$smarty->assign('RATING', xtc_image('templates/'.CURRENT_TEMPLATE.'/img/stars_'.$reviews['reviews_rating'].'.gif', sprintf(TEXT_OF_5_STARS, $reviews['reviews_rating'])));
$smarty->assign('PRODUCTS_LINK', xtc_href_link(FILENAME_PRODUCT_INFO, xtc_product_link($reviews['products_id'], $reviews['products_name'])));
$smarty->assign('BUTTON_BACK', '<a href="'.xtc_href_link(FILENAME_PRODUCT_REVIEWS, $get_params).'">'.xtc_image_button('button_back.gif', IMAGE_BUTTON_BACK).'</a>');
$smarty->assign('BUTTON_BUY_NOW', '<a href="'.xtc_href_link(FILENAME_DEFAULT, 'action=buy_now&BUYproducts_id='.$reviews['products_id']).'">'.xtc_image_button('button_in_cart.gif', IMAGE_BUTTON_IN_CART));
$smarty->assign('IMAGE', '<a href="javascript:popupImageWindow(\''.xtc_href_link(FILENAME_POPUP_IMAGE, 'pID='.$reviews['products_id']).'\')">'.xtc_image(DIR_WS_THUMBNAIL_IMAGES.$reviews['products_image'], $reviews['products_name'], '', '', 'align="center" hspace="5" vspace="5"').'<br /></a>');

$smarty->assign('language', $_SESSION['language']);

// set cache ID
if (!CacheCheck()) {
$smarty->caching = 0;
$main_content = $smarty->fetch(CURRENT_TEMPLATE.'/module/product_reviews_info.html');
} else {
$smarty->caching = 1;
$smarty->cache_lifetime = CACHE_LIFETIME;
$smarty->cache_modified_check = CACHE_CHECK;
$cache_id = $_SESSION['language'].$reviews['reviews_id'];
$main_content = $smarty->fetch(CURRENT_TEMPLATE.'/module/product_reviews_info.html', $cache_id);
}

$smarty->assign('language', $_SESSION['language']);
$smarty->assign('main_content', $main_content);
$smarty->caching = 0;
if (!defined(RM))
$smarty->load_filter('output', 'note');
$smarty->display(CURRENT_TEMPLATE.'/index.html');
?>

[Deadblow]

Danke Dir !

[xtc_contrib]

Hi all,

Thanks a lot for this really great help with mysql 5.

As i'm here, i want to tell to everybody that i'm french and i'm translating xtcommerce in french and if anybody need the french translation, i will send it. I haven't finished all the translation but in few time it will be done.

Thanks again for your help and to google to help me to understand what you said. ;-)

[webrise]

thx .... hat alles funktioniert. Mein 2er Shop läuft jetzt auch mit mySql5.

Leider funktioniert der Code für die products_new.php nicht. Es erscheint folgende Fehlermeldung:

[B]Warning[/B]:  main(/../inc/xtc_get_vpe_name.inc.php) [[URL="http://www.elmadera.de/function.main"]function.main[/URL]]: failed to open stream: No such file or directory in [B]/../products_new.php[/B] on line [B]30[/B]


[B]Fatal error[/B]:  main() [[URL="http://www.elmadera.de/function.require"]function.require[/URL]]: Failed opening required '/../inc/xtc_get_vpe_name.inc.php' (include_path='.:/usr/share/pear/php4') in [B]/../products_new.php[/B] on line [B]30[/B]

Bei mir existiert auch die Datei xtc_get_vpe_name.inc.php nicht. Ist der Code für die 3er Version?

P.S. Übrigens die advanced_search_result.php muss auch angepasst werden.

Zeile 133:

$from_str = "from ((" . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd ) left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id), " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c";

jetzt funkt die products_new.php - hier der Code:

Zeile 56:

from ((" . TABLE_PRODUCTS . " p,

" . TABLE_CATEGORIES . " c,

" . TABLE_PRODUCTS_TO_CATEGORIES . " p2c)

left join " . TABLE_MANUFACTURERS . " m

on p.manufacturers_id = m.manufacturers_id)