Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Sign in to follow this  
Guest

osC mySQL

Recommended Posts

Guest

The purpose of this contribution is to provide general reports of the database contents to the store owner in both PDF and plain SQL formats. A script configuration facility offers various options for storing SQL scripts that can be used to instantiate reports with the single click of a button.

 

A tooltip facility is also present that allows instant information to an sql table structure.

 

The package includes an enhanced fpdf library along with a new class to process reports in PDF format. A PDF example is included with the archive.

 

Furthermore the contribution processes sql scripts and generates the sql output. Note with this first version only the select command has been tested.

 

http://www.oscommerce.com/community/contributions,4238

Edited by enigma1

Share this post


Link to post
Share on other sites
Guest

There are 2 items missing from this contribution

 

1. Modifications to the catalog\admin\includes\column_left.php

Just before the last closing ?> add the following:

 

//-MS- SQL Scripts Box added
 require(DIR_WS_BOXES . FILENAME_SQL_SCRIPTS);
//-MS- SQL Scripts Box added EOM

 

This will include the sql scripts on the left panel.

 

2. The button_new.gif image is missing from the catalog\admin\includes\languages\english\images\buttons folder.

 

There is incorrect numbering of the steps in the readme.htm

These minor changes will be added with the next version (1.01) once I'll confirm the contribution's operation.

Share this post


Link to post
Share on other sites
Guest

Released v1.01 (complete package) that includes the missing files and instructions.

Share this post


Link to post
Share on other sites

Hi,

 

Great contribution. Just one small thing. The javascript code to automatically fill in the execute query textarea box when you select a pre-defined query does not work in Firefox.

 

Thanks,

 

Sherif


Sherif Awad

Technical Support Specialist

Share this post


Link to post
Share on other sites

interessting contirbution, maybe we could post more exemple , or even predefine some general reports people would like , like list of costumers , list of orders , list of manufacturer

 

What about the security ? could this contribution be used to by some hackers to get info on the database , this is always what im a afraid of when i install contribtuion that give info on the database

 

best regards for your share


MS2

Share this post


Link to post
Share on other sites
Guest

for the FF jscript issue replace the comboToArea with this:

 

  function comboToArea(object, area_name) 
 {

for (var i = 0; i < object.options.length; i++) {
  if (object.options[i].selected) {
	object.selectedIndex = i;
	break;
  }
}
obj_content = object.options[object.selectedIndex].value;
mytextarea = document.getElementsByName(area_name); 
mytextarea[0].value = obj_content;
 }

 

It should work with FF and IE. Still the mouse relocation is unfortunately browser specific so I need to enhance the jscript for the tooltip. There is no problem with IE.

 

For more examples basically you could combine queries there're plenty in the forums here is a simple I use to concatanate customer's names and emails

select CONCAT(c.customers_lastname, ' ', c.customers_firstname) as full_name, customers_email_address as email from customers c order by c.customers_lastname

 

select products_name as name, products_description as details from products_description where language_id=1

 

select orders_id, title, text, value from orders_total where title='Total:'

I am sure overtime, more examples will emerge that could be useful for a store.

 

As of the security this module it's on the admin end. And your osc admin should always be protected. There other tools if someone enters your osc Admin will likely use (database or file managers).

Share this post


Link to post
Share on other sites
Guest

Here is the fix for the tooltip jscript code. It should work now on both FF and IE.

 

  function moveToMouseLoc(e)
 {
var ns4 = document.layers;
var ns6 = document.getElementById && !document.all;

if(ns4||ns6)
{
  x = e.pageX;
  y = e.pageY;
}
else
{
  x = event.x + document.body.scrollLeft;
  y = event.y + document.body.scrollTop;
}
toolTipSTYLE.left = x + offsetX;
toolTipSTYLE.top = y + offsetY;
return true;
 }

 

I will include both fixes with the next release.

Share this post


Link to post
Share on other sites
select products_name as name, products_description as details from products_description where language_id=1

 

if i got language id 4 and 5 how ot adjust

select products_name as name, products_description as details from products_description where language_id=1, products_description as details from products_description where language_id=4, products_description as details from products_description where language_id=5

 

doesnt work ...

 

I think really it make me earn a lot of time to check all the data in the database in easy way

I hope you'll put in the next release in the sql a list of the query members will add here :-)


MS2

Share this post


Link to post
Share on other sites
Guest
if i got language id 4 and 5 how ot adjust

select products_name as name, products_description as details from products_description where language_id=1, products_description as details from products_description where language_id=4, products_description as details from products_description where language_id=5

 

doesnt work ...

 

I think really it make me earn a lot of time to check all the data in the database in easy way

I hope you'll put in the next release in the sql a list of the query members will add here :-)

 

your query is incorrect. Place a single where clause and use "or" for the languages.

 

select products_name as name, products_description as details from products_description where language_id=1 or language_id=4 or language_id=5

Share this post


Link to post
Share on other sites
your query is incorrect. Place a single where clause and use "or" for the languages.

 

your solution works thanks , but it mae one new line for each language and the same product , i wanted to have for instance the name in several language and the description in the 3 languages ,and if it s not possibile then the name is the defaut langauge and then the descritpion in the 3 languages

 

Thanks in advance :P


MS2

Share this post


Link to post
Share on other sites
Guest

use the if statement and if the language id is set to say 1 you display the name otherwise you do not. But the number of columns that return will be the same for each row.

 

So

select if(language_id=1, products_name, language_id) as name, products_description from products_description where language_id=1 or language_id=4 or language_id=5

Share this post


Link to post
Share on other sites
use the if statement and if the language id is set to say 1 you display the name otherwise you do not. But the number of columns that return will be the same for each row.

 

So

select if(language_id=1, products_name, language_id) as name, products_description from products_description where language_id=1 or language_id=4 or language_id=5

 

sorry this code doenst work it gives

 

name Adidas Y-3 Shoes products_description

 

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

 

name 4

products_description

 

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

 

name 5

 

os instaed of name it gives the number id of the language and no porduct description


MS2

Share this post


Link to post
Share on other sites
Guest

this is because I set the language id so you will see how it works you could replace it with ''

 

As of the description seems you have no description for that entry.

Share this post


Link to post
Share on other sites
Guest

i am getting this error in admin and nothing is showing up on site. i am thinking it might be a connect problem. We changed servers and it worked fine then last week it just blew up.

 

1064 - You have an error in your SQL syntax near 'MAX_DISPLAY_NEW_PRODUCTS' at line 1

 

select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from products p left join specials s on p.products_id = s.products_id where products_status = '1' and TO_DAYS(NOW()) - TO_DAYS(p.products_date_added) < 'NEW_PRODUCT_DAYS' order by p.products_date_added desc limit MAX_DISPLAY_NEW_PRODUCTS

 

[TEP STOP]

 

 

I am also getting an error message saying lanuage isn ot defined and corruency is not set and this error above is in most of the admin boxes.

 

Any one know what happened? I am thinking a connection to the db but it is not saying that on the main page. If it were not connecting it would send an error message saying it failed to connect to database server.

 

Thanks

Share this post


Link to post
Share on other sites
Guest
i am getting this error in admin and nothing is showing up on site. i am thinking it might be a connect problem. We changed servers and it worked fine then last week it just blew up.

 

1064 - You have an error in your SQL syntax near 'MAX_DISPLAY_NEW_PRODUCTS' at line 1

 

select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from products p left join specials s on p.products_id = s.products_id where products_status = '1' and TO_DAYS(NOW()) - TO_DAYS(p.products_date_added) < 'NEW_PRODUCT_DAYS' order by p.products_date_added desc limit MAX_DISPLAY_NEW_PRODUCTS

 

[TEP STOP]

 

 

I am also getting an error message saying lanuage isn ot defined and corruency is not set and this error above is in most of the admin boxes.

 

Any one know what happened? I am thinking a connection to the db but it is not saying that on the main page. If it were not connecting it would send an error message saying it failed to connect to database server.

 

Thanks

 

Did you install the osC mySQL contribution and you got that error? or you simply got it? If that's not related with the contribution try the general forum. Otherwise post the contribution details problem.

Share this post


Link to post
Share on other sites

Hi,

 

Great contribution.

Only one thing is not working for me:

 

When i select a query and the option SQL it works fine

When i select PDF i get this error:

 

Warning: Division by zero in /var/www/vhosts/emerchandise.nl/httpdocs/demo/shopadmin/includes/classes/pdf_mysql_report.php on line 418

 

Warning: Cannot modify header information - headers already sent by (output started at /var/www/vhosts/emerchandise.nl/httpdocs/demo/shopadmin/includes/classes/pdf_mysql_report.php:418) in /var/www/vhosts/emerchandise.nl/httpdocs/demo/shopadmin/pdf/fpdf/fpdf.php on line 1071

FPDF error: Some data has already been output to browser, can't send PDF file

 

Regards,

Alex

Share this post


Link to post
Share on other sites
Guest

Ok I know where the error is happening from your report but I don't know why is happening. Can you tell me a couple of things:

 

1. The sql query you inserted in the edit box to generate the PDF; or the table name you selected to generate the PDF.

2. What happens when you select a table like countries and then you click the pdf button on the right side. Do you still see the same error?

3. Also try this simple query in the edit box and generate a PDF to see if the error is still there.

select products_id from products

 

There is a limit based on the width of the paper type. If you have lets say 50 columns the width of each one will likely be too short and may cause this problem with the code because it adjusts the column width based on the number of columns.

Share this post


Link to post
Share on other sites
this is because I set the language id so you will see how it works you could replace it with ''

 

As of the description seems you have no description for that entry.

 

True i got no description for a lot of my product

 

im sorry i dont understand how the query has to be change :

 

select if(language_id=1, products_name, language_id) as name, products_description from products_description where language_id=1 or language_id=4 or language_id=5

 

 

Cannot modify header information - headers already sent by (output started

 

sometimes it s because there are so empty ligne containing space after the last ?>


MS2

Share this post


Link to post
Share on other sites
Guest
True i got no description for a lot of my product

 

im sorry i dont understand how the query has to be change :

 

select if(language_id=1, products_name, language_id) as name, products_description from products_description where language_id=1 or language_id=4 or language_id=5

Cannot modify header information - headers already sent by (output started

 

sometimes it s because there are so empty ligne containing space after the last ?>

 

the query will be like this

select if(language_id=1, products_name, '') as name, products_description from products_description where language_id=1 or language_id=4 or language_id=5

Share this post


Link to post
Share on other sites

select if(language_id=1, products_name, '') as name, products_description from products_description where language_id=1 or language_id=4 or language_id=5

 

I really got no luck , i got 3 language and so 3 translation of the name product , here here the result of the code u gave me

 

name

products_description

 

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

 

name Adidas Y-3 - Vest - Blue

products_description

 

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

 

name

products_description

 

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

 

name

products_description

 

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

 

name Adidas Y-3 - Vest - Green

products_description

 

 

 

while in fact i'd like to have:

 

name en Adidas Y-3 - Vest - Blue

name fr Adidas Y-3 - Veste - bleue

name de Adidas Y-3 - Vest - blaue

 

products_description en english descritpion

products_description fr french descritpion

products_description de german descritpion

 

:(


MS2

Share this post


Link to post
Share on other sites
Guest

then use the original query and append the language details

 

select concat(l.code, ' - ', pd.products_name) as name, pd.products_description as details from products_description pd left join languages l on (l.languages_id=pd.language_id) where pd.language_id=1 or pd.language_id=4 or pd.language_id=5

Edited by enigma1

Share this post


Link to post
Share on other sites
then use the original query and append the language details

 

it s very close to what i was trying to do , exept i make a new paragraph separated by the horizontal line for each language , while i would like to have for the same product the 3 names and the 3 description like this :

 

 

name fr- product1_french_name

name en- product1_english_name

name de- product1_german_name

 

details fr- product1_french_details

details en- product1_english_details

details de product1_german_details

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

 

name fr- product2_french_name

name en- product2_english_name

name de- product2_german_name

 

details fr- product2_french_details

details en- product2_english_details

details de product2_german_details

 

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

 

I know you could tell me " go learn more mysl then come back" and you would be right ;-) im abusing your kindness , but it don't know if it s a solution that could be done by the sql query , or it s more something the contrib doesnt do because the report is not made this way

 

Any solution near to this one would be so welcome :blush:


MS2

Share this post


Link to post
Share on other sites
Guest

that would require a different layout on the pdf level. Or a template system to drive the pdf report. For instance the way the layout of the sql query is much closer to what you want than the pdf report.

 

Of course in your case you could customize the pdf_mysql_report.php class file to change the layout.

 

Perhaps you could also use a single column and concatanate everything there. Like for example if I wanted to have the products name and description on the same column I could do

 

select concat(products_name, ' - ', products_description, ' - - - ', products_viewed) as everything from products_description where language_id = 1

 

and you could append more fields and add separators in the query to generate the effect you want

Share this post


Link to post
Share on other sites
Ok I know where the error is happening from your report but I don't know why is happening. Can you tell me a couple of things:

 

1. The sql query you inserted in the edit box to generate the PDF; or the table name you selected to generate the PDF.

It happens with every query also the ones from the readme file.

 

2. What happens when you select a table like countries and then you click the pdf button on the right side. Do you still see the same error?

When I create a PDF from the tables .... it works fine.

 

3. Also try this simple query in the edit box and generate a PDF to see if the error is still there.

Tried it... same error...

 

I can give you access to the admin section if necessary...

 

Alex

Share this post


Link to post
Share on other sites
Guest
It happens with every query also the ones from the readme file.

have you implement the changes in the database.php file as of the later documentation of v1.01? Because it seems the numfields is causing the problem and that is set from the database wrapper function I included in the file. If you have deployed those changes do this as a test.

 

open your catalog\admin\includes\classes\pdf_mysql_report.php locate this code:

 

$this->numFields = tep_db_num_fields($this->results);

 

add below:

if($this->numFields <= 0 ) 
 die('Invalid Fields Number returned');

 

and see if you get this error printed.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×