issue running custom query

[stride-r]

The following breaks when it hits the Execute() line:

$prodId_query = "SELECT p.products_id FROM " . TABLE_PRODUCTS . " WHERE p.products_image LIKE '" . $prodImgName . "'";

$thisProdId = $db->Execute($prodId_query); 

The error message is:
"Unknown column 'p.products_id' in 'field list'"

It's late, and I'm sure its probably simple, but what am i missing????

[yellow1912]

$prodId_query = "SELECT p.products_id FROM " . TABLE_PRODUCTS . " p WHERE p.products_image LIKE '" . $prodImgName . "'"; 

[stride-r]

Thanks for the suggestion but I'm still stuck. No error message this time so i'm assuming that it's not just the query syntax. Instead, now the script fails with no output beyond the line where the $db->Execute occurs.

for ($c=0;$c<= count($imageFiles);$c++){

  $prodImgName = str_replace("_LRG","",$imageFilePaths[$c]);
  $prodImgName = str_replace("images/large/","",$prodImgName);
  
  $prodId_query = "SELECT p.products_id FROM " . TABLE_PRODUCTS . " p WHERE p.products_image LIKE '" . $prodImgName . "' AND p.products_status = '1'";

  $thisProdId = $db->Execute($prodId_query);
  echo $thisProdId. "<br />";
} 

Since it looks like you'll need more background info on what i'm trying to do, here goes:

I have created a new custom define page. in it, i'm using php to pull up a list of all images that exist in the "images/large" directory and would like to display them as a list of textual links.

Got it working except for i'm now trying to grab/match the product Id for those products whose image has a large version. Of course, the large images directory contains many images for which there directly are no products (as some prods have multiple images and the naming convention for those will not match up - and some images may exist where a product has been deleted or not yet created ...but that's okay) I only need to find the prod id for those images that have a valid matching database entry.

[DrByte]

in your "for" statement, you're using $imageFiles, but in your first str_replace, you're using $imageFilePaths ... is that intentional?

[stride-r]

Thanks, Dr. Byte, for your input into my issue.

Yes, the $prodImgName is a temp variable holder for the actual file name ....which i've obtained by stripping away the extraneous chars from the larger version file name.

I've checked the accuracy of this by echo-ing out the results of the multiple str_replace and all file names come out correct.

Any other thoughts?

[stride-r]

Sorry, i misread your question at first.

Yes, it wasn't really a mistake although i could/should be consistent? But anyway the $imageFilePaths and $imageFiles are two equal length arrays - one contains the filenames with their full paths and the other contains a human-readable version of the same (I stipped off the file suffix and replaced underscores with spaces, thus a file in $imageFilePaths might be:
"/images/large/file_one_LRG.jpg"
...while its counterpart in $imageFiles would simply be:
"file one"

I've checked the accuracy of all this by echo-ing out the results of the multiple str_replace and all file names come out correct.

I've also tested this by replacing the loop variable with a fixed integer (just in case my arrays might not be equal length) but that's not the issue (and I've checked and my arrays ARE equal).

Any other thoughts?

[DrByte]

Got it working except for i'm now trying to grab/match the product Id for those products whose image has a large version. Of course, the large images directory contains many images for which there directly are no products (as some prods have multiple images and the naming convention for those will not match up - and some images may exist where a product has been deleted or not yet created ...but that's okay) I only need to find the prod id for those images that have a valid matching database entry.

You'll probably have to work your logic backwards. Prepare an array of images and product IDs. Then do your matching from the other angle.

Either that or you'll have to find a way to do a progressively-less-restrictive lookup, since the "LIKE" clause can't match on less than what you give it ... and as you know, the additional-image files are longer than the original product image base filename, and are of varying lengths.

[stride-r]

Okay, thanks. I think I get what you're saying. I also realize that making a new db query for each pass through the loop wasn't the most sane way to accomplish this either.

However, i'm still having an issue getting the right query results.

I've taken a step backwards and am now trying to get just an array of prod ids. Sounds simple enough ...but it's not working for me. Here's what I have:

  $prodIds_query = "SELECT p.products_id FROM " . TABLE_PRODUCTS . " p WHERE p.products_status = '1'";
  
  $myProdIds = $db->Execute($prodIds_query);
  
  echo "Array count: " . count($myProdIds) . "<br /><br />";

  foreach ($myProdIds as $idsValues){
     echo "Array values: " . $idsValues . "<br />";
  } 

The result of running the above is this seemingly (to me anyway ) odd reply:
Array count: 1

Array values:
Array values: Resource id #303
Array values: 0
Array values:
Array values: Array


Its retrieving a recordset of 1 ....yet I can view the 13 products currently loaded into my localhost demo/test Zen Cart store. Furthermore, if I copy/paste this same query into MySQL Query Browser, it displays the correct 13 records.

Any thoughts on what I'm doing wrong in trying to get at it from within Zen Cart itself???

[stride-r]

Also, I ran a print_r($myProdIds) on the above mentioned array and it spits out this:

queryFactoryResult Object ( [is_cached] => [resource] => Resource id #303 [cursor] => 0 [EOF] => [fields] => Array ( [products_id] => 1 ) )

Still not sure what it means.