Jump to content
xt:Commerce Community Forum
Sign in to follow this  
MrX1980

xtcommerce und MySQL 5.x

Recommended Posts

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?

Share this post


Link to post
Share on other sites

$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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 )

Share this post


Link to post
Share on other sites

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??

Share this post


Link to post
Share on other sites

<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

Share this post


Link to post
Share on other sites

products_new.php für mysql5 :cool:


<?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');
?>

Share this post


Link to post
Share on other sites

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. ;-)

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites
Sign in to follow this  

×
×
  • Create New...