Query does not return data

[Johnnyh]

Hi.
Really hope someone here can solve this puzzle...

I'm running Zencart v1.5.5a on the domain www.artos.dk and are trying to build a Facebook-shop with the products from one of the categories. To do this a PHP-feed has been copied to the root www.artos.dk/zencart and was supposed to return data with all the products from the shop, but it is returning an empty feed.

This is what the support for the Facebook-app (StoreYa) is answering:

The feed (http://artos.dk/zencart/storeya_feed.php) is empty which is why your store cannot be imported.
We recommend that you contact your webmaster for why the following query does not return data:

$prdqry= mysql_query("select ".DB_PREFIX."products.products_id,products_price,products_price_sorter,products_ image,products_name,products_description,products_url from ".DB_PREFIX."products,".DB_PREFIX."products_description where ".DB_PREFIX."products.products_status='1' and ".DB_PREFIX."products.products_id=".DB_PREFIX."products_description.products _id and ".DB_PREFIX."products_description.language_id='".$language_id."' order by ".DB_PREFIX."products.products_id ");

Language is the following:

$lang=mysql_fetch_assoc(mysql_query("select languages_id from ".DB_PREFIX."languages where code in (select configuration_value from ".DB_PREFIX."configuration where configuration_key='DEFAULT_LANGUAGE')"));
$language_id=$lang['languages_id'];

I have no idea why this is happening and are hoping for your help?

[RodG]

Really hope someone here can solve this puzzle...

$prdqry= mysql_query("...........

Language is the following
$lang=mysql_fetch_assoc(mysql_query(.............................

I have no idea why this is happening and are hoping for your help?

At a guess, the problem is that this code is using 'mysql_query' which was deprecated in php 5.5 and removed in PHP 7.

You should *probably* be using the 'mysqli' (note the 'i') routines instead.

Just a thought.

Cheers
RodG

[Johnnyh]

Thank you. I will ask StoreYa...

[lat9]

I'll also point out that none of the PHP constants, e.g. DB_PREFIX, DEFAULT_LANGUAGE, are being resolved either.

What is the context in which the containing script runs? If it's an externally-accessible script, e.g. www.example.com/myscript.php, then you'll need to include the statement

require 'includes/application_top.php';

at the very top of that script. Once that's done, the constants will resolve; next step will be to follow RodG's advice and get an update that uses mysqli_ type functions.

[RodG]

I'll also point out that none of the PHP constants, e.g. DB_PREFIX, DEFAULT_LANGUAGE, are being resolved either.

Oh my. I really managed to overlook the obvious on this one didn't I. I agree, the OP needs to take care of this 1st... It could be all that is needed.

Cheers
RodG

[carlwhat]

i agree with the previous posts, however...

if you are looking to build a feed from your ZC store, i would look into this module:

https://www.zen-cart.com/downloads.php?do=file&id=1375

i'm sure you could use that module as a base and modify as necessary. especially if you are looking at coding something from scratch.

good luck.

[frank18]

That line

require 'includes/application_top.php';

is already inserted at the very top of the script

[carlwhat]

ok, i take it all back. as the page does NOT return an error (as far as i can tell), and @frank18 says the script is included in the top, i can safely say that the initiation script is included. storeya does seem to have integrated a number of carts at least according to their website.

i would modify the storeya_feed.php script to actually see what the sql statement is:

$prdqry= mysql_query("select ".DB_PREFIX."products.products_id,products_price,products_price_sorter,products_ image,products_name,products_description,products_url from ".DB_PREFIX."products,".DB_PREFIX."products_description where ".DB_PREFIX."products.products_status='1' and ".DB_PREFIX."products.products_id=".DB_PREFIX."products_description.products _id and ".DB_PREFIX."products_description.language_id='".$language_id."' order by ".DB_PREFIX."products.products_id ");

// add these 2 lines:

echo "select " . DB_PREFIX . "products.products_id,products_price,products_price_sorter,products_ image,products_name,products_description,products_url from " . DB_PREFIX . "products," . DB_PREFIX . "products_description where " . DB_PREFIX . "products.products_status='1' and " . DB_PREFIX . "products.products_id=" . DB_PREFIX . "products_description.products _id and " . DB_PREFIX . "products_description.language_id='" . $language_id . "' order by " . DB_PREFIX . "products.products_id ";

die(__FILE__ . ':' . __LINE__); 

then go back here:

http://artos.dk/zencart/storeya_feed.php

and post the sql statement here.

good luck!

[Johnnyh]

Thank you for your suggestions. Now the connection was succesfully established but no products were exported.

I added the lines carlwhat suggested and got this back:

http://artos.dk/zencart/ Catalog Feed generated by StoreYa.com 2.1 06/30/2017 09:1726 select zen_products.products_id,products_price,products_price_sorter,products_ image,products_name,products_description,products_url from zen_products,zen_products_description where zen_products.products_status='1' and zen_products.products_id=zen_products_description.products _id and zen_products_description.language_id='' order by zen_products.products_id /var/www/artos.dk/public_html/zencart/storeya_feed.php:134

Fulls script is:

<?php
require('includes/application_top.php');
header('Content-Type: application/rss+xml;charset=UTF-8');
ob_end_clean();

function encodeIfNeeded($text) {
if('utf-8' != strtolower(CHARSET)) {
$text = iconv(CHARSET, 'UTF-8', $text);
}
return $text;
}

function replace_problem_characters($text) {
$formattags = array("&");
$replacevals = array("&#38;");
$text = str_replace($formattags, $replacevals, $text);
$in[] = '@&(amp|#038);@i'; $out[] = '&';
$in[] = '@&(#036);@i'; $out[] = '$';
$in[] = '@&(quot);@i'; $out[] = '"';
$in[] = '@&(#039);@i'; $out[] = '\'';
$in[] = '@&(nbsp|#160);@i'; $out[] = ' ';
$in[] = '@&(hellip|#8230);@i'; $out[] = '...';
$in[] = '@&(copy|#169);@i'; $out[] = '(c)';
$in[] = '@&(trade|#129);@i'; $out[] = '(tm)';
$in[] = '@&(lt|#60);@i'; $out[] = '<';
$in[] = '@&(gt|#62);@i'; $out[] = '>';
$in[] = '@&(laquo);@i'; $out[] = '«';
$in[] = '@&(raquo);@i'; $out[] = '»';
$in[] = '@&(deg);@i'; $out[] = '°';
$in[] = '@&(mdash);@i'; $out[] = '—';
$in[] = '@&(reg);@i'; $out[] = '®';
$in[] = '@&(–);@i'; $out[] = '-';
$text = preg_replace($in, $out, $text);
return $text;
}

function strip_html_tags($str) {
// $document should contain an HTML document.
// This will remove HTML tags, javascript sections
// and white space. It will also convert some
// common HTML entities to their text equivalent.

$search = array ("'<script[^>]*?>.*?</script>'si", // Strip out javascript
"'<[/!]*?[^<>]*?>'si", // Strip out HTML tags
//"'([rn])[s]+'", // Strip out white space
"'&(quot|#34);'i", // Replace HTML entities
// "'&(amp|#38);'i",
"'&(lt|#60);'i",
"'&(gt|#62);'i",
"'&(nbsp|#160);'i",
"'&(iexcl|#161);'i",
"'&(cent|#162);'i",
"'&(pound|#163);'i",
"'&(copy|#169);'i",
"'&#(d+);'e"); // evaluate as php

$replace = array ("",
"",
//"\1",
"\"",
"&",
"<",
">",
" ",
chr(160),
chr(161),
chr(162),
chr(163),
chr(169),
"chr(\1)");

return preg_replace($search, $replace, $str);
}


echo '<?xml version="1.0" encoding="UTF-8"?>' . "\n";
?>
<rss xmlns:g="http://base.google.com/ns/1.0" version="2.0">
<channel>
<?php
echo '<title>Catalog Feed</title>'. "\n";

$store=mysqli_fetch_assoc(mysqli_query("select * from ".DB_PREFIX."configuration where configuration_key='STORE_NAME'"));

echo '<link>'.HTTP_SERVER.DIR_WS_CATALOG.'</link>'. "\n";
echo '<description>Catalog Feed generated by StoreYa.com 2.1</description>'. "\n";

// * NOTICE OF LICENSE
// *
// * This source file is the property of StoreYa Feed LTD. and a part of its patent pending technology.
// * Using this file is allowed only for the purpose of importing web stores onto Facebook using the service of StoreYa Feed LTD.
// * Do NOT copy/edit/change this file or use it to any service other than the above.

$admin=mysqli_fetch_assoc(mysqli_query("select * from ".DB_PREFIX."admin limit 0,1"));
echo '<lastBuildDate>'.date('m/d/Y H:is').'</lastBuildDate>'. "\n";
//echo '<copyright>Copyright '.date('Y').', '.$_SERVER['HTTP_HOST'].'</copyright>'. "\n";

if($_GET['language_id']=='')
{
$lang=mysqli_fetch_assoc(mysqli_query("select languages_id from ".DB_PREFIX."languages where code in (select configuration_value from ".DB_PREFIX."configuration where configuration_key='DEFAULT_LANGUAGE')"));
$language_id=$lang['languages_id'];
}
else
$language_id=$_GET['language_id'];


$prdqry= mysqli_query("select ".DB_PREFIX."products.products_id,products_price,products_price_sorter,products_ image,products_name,products_description,products_url from ".DB_PREFIX."products,".DB_PREFIX."products_description where ".DB_PREFIX."products.products_status='1' and ".DB_PREFIX."products.products_id=".DB_PREFIX."products_description.products _id and ".DB_PREFIX."products_description.language_id='".$language_id."' order by ".DB_PREFIX."products.products_id ");
while($row=mysqli_fetch_assoc($prdqry))
{
echo '<item>'. "\n";
echo ' <title>'.strip_html_tags(replace_problem_characters(encodeIfNeeded($row['products_name']))).'</title>'. "\n";
echo ' <link>'. htmlentities(HTTP_SERVER.DIR_WS_CATALOG.'index.php?main_page=product_info&produc ts_id='.$row['products_id']).'</link>'. "\n";
echo ' <description>'. htmlspecialchars(strip_html_tags(replace_problem_characters(encodeIfNeeded($row['products_description'])))).'</description>'. "\n";
echo ' <g:image_link>'.HTTP_SERVER.DIR_WS_CATALOG.'images/'.$row['products_image'].'</g:image_link>'. "\n";
echo ' <g:id>'.$row['products_id'].'</g:id>'. "\n";
echo ' <grice>'.$row['products_price'].'</grice>'. "\n";
echo ' <g:sale_price>'.$row['products_price_sorter'].'</g:sale_price>'. "\n";


$catqry=mysqli_query("select categories_name from ".DB_PREFIX."categories_description,".DB_PREFIX."products_to_categories where language_id='".$language_id."' and ".DB_PREFIX."products_to_categories.categories_id=".DB_PREFIX."categories_descri ption.categories_id and ".DB_PREFIX."products_to_categories.products_id='".$row['products_id']."'");
while($catrow=mysqli_fetch_assoc($catqry))
{
echo ' <groduct_type>'.strip_html_tags(replace_problem_characters(encodeIfNeeded($catrow['categories_name']))).'</groduct_type>'. "\n";
}
echo '</item>'. "\n";
}
echo '</channel>'. "\n";
echo '</rss>';

[mc12345678]

Umm. A large majority of mysqli_ related functions require "authentication" in order to work. Meaning, the database credentials need to be sent.

See the php manual for mysqli_ functions such as query: http://php.net/manual/en/mysqli.query.php

This supplier with their copyrighted software should probably get more with the times considering the original script supported only mysql_ code which was deprecated in php 5.5 which is considered below the php version that is currently "supported".

To see how to use the function within ZC, take a look at the query_factory code for the Execute function found in the includes/classes/db/mysql folder though the above code could be made even more robust for ZC ("requires" knowledge of the code not just the database interface) by using ZC functions to access the database and to ensure referencing the tables established for the store, etc...

Effectively would expect that the first parameter of the query to be: $db->link. The second parameter to be the query. Otherwise as $db-> hedges on using a ZC function, would need to gather the database access information that is read in when loading application_top.php.