Page 1 of 2 12 LastLast
Results 1 to 10 of 11
  1. #1
    Join Date
    Aug 2008
    Posts
    115
    Plugin Contributions
    0

    Default issue running custom query

    The following breaks when it hits the Execute() line:
    PHP Code:
    $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????

  2. #2
    Join Date
    Oct 2006
    Posts
    5,481
    Plugin Contributions
    11

    Default Re: issue running custom query

    PHP Code:
    $prodId_query "SELECT p.products_id FROM " TABLE_PRODUCTS " p WHERE p.products_image LIKE '" $prodImgName "'"
    I no longer provide installation support on forum for all my modules. However, if there are real bugs with the modules please feel free to contact me

  3. #3
    Join Date
    Aug 2008
    Posts
    115
    Plugin Contributions
    0

    Default Re: issue running custom query

    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.

    PHP Code:
    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.

  4. #4
    Join Date
    Jan 2004
    Posts
    65,335
    Blog Entries
    7
    Plugin Contributions
    229

    Default Re: issue running custom query

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

    Zen Cart - putting the dream of business ownership within reach of anyone!
    Donate to: DrByte directly or to the Zen Cart team as a whole

    Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
    Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.

  5. #5
    Join Date
    Aug 2008
    Posts
    115
    Plugin Contributions
    0

    Default Re: issue running custom query

    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?

  6. #6
    Join Date
    Aug 2008
    Posts
    115
    Plugin Contributions
    0

    Default Re: issue running custom query

    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?

  7. #7
    Join Date
    Jan 2004
    Posts
    65,335
    Blog Entries
    7
    Plugin Contributions
    229

    Default Re: issue running custom query

    Quote Originally Posted by stride-r View Post
    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.
    .

    Zen Cart - putting the dream of business ownership within reach of anyone!
    Donate to: DrByte directly or to the Zen Cart team as a whole

    Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
    Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.

  8. #8
    Join Date
    Aug 2008
    Posts
    115
    Plugin Contributions
    0

    Default Re: issue running custom query

    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:
    PHP Code:
      $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???

  9. #9
    Join Date
    Aug 2008
    Posts
    115
    Plugin Contributions
    0

    Default Re: issue running custom query

    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.

  10. #10
    Join Date
    Oct 2006
    Posts
    5,481
    Plugin Contributions
    11

    Default Re: issue running custom query

    $myProdIds is an object, not an array

    you have to do this

    while(!$myProdIds->EOF){

    //do whatever
    echo $myProdIds->fields['products_id'];
    $myProdIds->MoveNext();
    }
    I no longer provide installation support on forum for all my modules. However, if there are real bugs with the modules please feel free to contact me

 

 
Page 1 of 2 12 LastLast

Similar Threads

  1. v151 htmlspecialchars error when running query from developers_tool_kit
    By mc12345678 in forum General Questions
    Replies: 3
    Last Post: 23 Oct 2013, 06:11 PM
  2. Running custom admin scripts from PHP CLI?
    By mdegrandis in forum General Questions
    Replies: 4
    Last Post: 21 Jul 2011, 10:02 PM
  3. Specifying 'Product priced by attributes' by running phpmyadmin SQL query?
    By vandiermen in forum Setting Up Categories, Products, Attributes
    Replies: 1
    Last Post: 2 Aug 2009, 06:34 AM
  4. Custom Query
    By 0be1 in forum General Questions
    Replies: 1
    Last Post: 11 Sep 2008, 12:07 AM
  5. Custom Query
    By Prellyan in forum Setting Up Categories, Products, Attributes
    Replies: 0
    Last Post: 9 Nov 2006, 04:11 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
disjunctive-egg
Zen-Cart, Internet Selling Services, Klamath Falls, OR