Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Report Query Help - Should be an easy one


B!!!

Recommended Posts

Posted

I've designed a report to list the items in my store that have the standard placeholder image that we use when we don't have photography yet. It's fairly simplistic and works fine. However, is there a way to have it search for more than just that one filename? Or null entries?

 

  $imagefile = "ics.jpg";
 $products_query_raw = strtolower(
"select p.products_id, p.products_image, p.products_model, pd.products_name, m.manufacturers_name".
" from ".TABLE_PRODUCTS." p, ".TABLE_MANUFACTURERS." m, ".TABLE_PRODUCTS_DESCRIPTION." pd".
" where p.products_id = pd.products_id ".
"   and p.manufacturers_id = m.manufacturers_id ".
"   and pd.language_id = '".$languages_id."'".
"   and p.products_image = '".$imagefile."'".
" group by pd.products_id".
" order by p.products_model ASC");

 

Ideally, I'd like to stick an "or" or two in there for the Product Image, but am not sure how to accomplish that. Any help would be appreciated?

Posted
I've designed a report to list the items in my store that have the standard placeholder image that we use when we don't have photography yet. It's fairly simplistic and works fine. However, is there a way to have it search for more than just that one filename? Or null entries?

 

  $imagefile = "ics.jpg";
 $products_query_raw = strtolower(
"select p.products_id, p.products_image, p.products_model, pd.products_name, m.manufacturers_name".
" from ".TABLE_PRODUCTS." p, ".TABLE_MANUFACTURERS." m, ".TABLE_PRODUCTS_DESCRIPTION." pd".
" where p.products_id = pd.products_id ".
"   and p.manufacturers_id = m.manufacturers_id ".
"   and pd.language_id = '".$languages_id."'".
"   and p.products_image = '".$imagefile."'".
" group by pd.products_id".
" order by p.products_model ASC");

 

Ideally, I'd like to stick an "or" or two in there for the Product Image, but am not sure how to accomplish that. Any help would be appreciated?

Sure it is. You can either create more of those variables, e.g. $imagefile2 = ... or create an array of filenames to use.

 

Then you need to add an 'or p.products_image = '.".$imagefile2."'",' to the query.

Posted

Thanks for the quick reply! I actually tried that exact same thing that you describe with the "or" statement, and things went really wonky by having 2000+ pages of report, where all but the first few pages were blank. I'll give it another shot though. How do you check for empty or null fields?

Posted

Ok, I tried that "or" statement again and it worked fine this time. Now, what about that null or blank field?

Posted

All right, I figured out the problem of finding the null values, but now it's doing that goofy thing again that I described above. Also, it's taking a long time for the report to generate, where as before it was very fast. See anything wrong with this?

 

  $imagefile1 = 'ics.jpg';
 $products_query_raw = strtolower(
"select p.products_id, p.products_image, p.products_model, pd.products_name, m.manufacturers_name".
" from ".TABLE_PRODUCTS." p, ".TABLE_MANUFACTURERS." m, ".TABLE_PRODUCTS_DESCRIPTION." pd".
" where p.products_id = pd.products_id ".
"   and p.manufacturers_id = m.manufacturers_id ".
"   and pd.language_id = '".$languages_id."'".
"   and p.products_image is null ".
"   or p.products_image = '".$imagefile1."'".
" group by pd.products_id".
" order by p.products_model ASC");

 

I end up with a report that says "Displaying 1 to 30 (of 197757 products)" - we only have just over 1500 products. There's almost 6600 pages. And all the manufacturers and model numbers are the same. Something is definitely not right.

 

If I use with of the last two statements alone with an AND instead of an OR, it works just fine (but without all the data, of course), so unless I'm missing something really obvious, the OR is hosing things up.

 

Any suggestions?

Posted

After doing some more checking, the problem seems to happen only when the OR statement is true.

Posted

$imagefile1 = 'ics.jpg';
 $products_query_raw = strtolower(
"select p.products_id, p.products_image, p.products_model, pd.products_name, m.manufacturers_name".
" from ".TABLE_PRODUCTS." p, ".TABLE_MANUFACTURERS." m, ".TABLE_PRODUCTS_DESCRIPTION." pd".
" where p.products_id = pd.products_id ".
"   and p.manufacturers_id = m.manufacturers_id ".
"   and pd.language_id = '".$languages_id."'".
"   and ( p.products_image is null ".
"   or p.products_image = '".$imagefile1."' )".
" order by p.products_model ASC");

 

you need to put braces around the image check and you don't need the group statement.

With the language check you will only return one row for each products_id

 

Coincidently, I've written a similar report for a contribution where I've uploaded the code on the oscommerce.nl website for people to try/test before letting it out to the masses on this forum. Perhaps it is time to share that piece of code ...

 

Carine

KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Posted
All right, I figured out the problem of finding the null values, but now it's doing that goofy thing again that I described above. Also, it's taking a long time for the report to generate, where as before it was very fast. See anything wrong with this?

 

  $imagefile1 = 'ics.jpg';
 $products_query_raw = strtolower(
"select p.products_id, p.products_image, p.products_model, pd.products_name, m.manufacturers_name".
" from ".TABLE_PRODUCTS." p, ".TABLE_MANUFACTURERS." m, ".TABLE_PRODUCTS_DESCRIPTION." pd".
" where p.products_id = pd.products_id ".
"   and p.manufacturers_id = m.manufacturers_id ".
"   and pd.language_id = '".$languages_id."'".
"   and p.products_image is null ".
"   or p.products_image = '".$imagefile1."'".
" group by pd.products_id".
" order by p.products_model ASC");

 

I end up with a report that says "Displaying 1 to 30 (of 197757 products)" - we only have just over 1500 products. There's almost 6600 pages. And all the manufacturers and model numbers are the same. Something is definitely not right.

 

If I use with of the last two statements alone with an AND instead of an OR, it works just fine (but without all the data, of course), so unless I'm missing something really obvious, the OR is hosing things up.

 

Any suggestions?

what are you trying to achieve with the strtolower function there?

Posted

KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Posted
what are you trying to achieve with the strtolower function there?

 

The original code was in the contributions section. It was a Low Stock Report by Fabius, but it seemed simple enough that I could modify it for my own needs. I'm not a php programmer so I don't know what the strtolower function is for or why it was used originally.

 

Thanks, bruyndoncx. I will give your suggestion a try first about the braces a try first.

 

B

Posted

Bruyndoncx, thanks for the help! The brackets worked perfectly, and now my report is working just like it should!

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...