DB query question

I would like to query the db with an onclick using JQUERY/AJAX, then return the results without a page refresh. The JQUERY part is not where the problem lies.

I'm a newb when it comes to PHP. I've been looking over the product info main_template_vars.php file to see if I can figure out how to query the db. I'm not having any luck so far, there is a lot of stuff in there I probably don't need that is throwing me off.

Here's what I need to happen, user clicks button, button onclick submits Product ID to AJAX script. AJAX script then calls the file product_info_query.php(this would be where the DB query lives). The php file would then query the DB and return results(Product name, Product Image, Product description, product price) for that particular product ID. What's the trick to coding this query into a php file for the zen cart DB?

Maybe I'm missing something, but doesn't Zen Cart already do this?

No it doesn't, unless I've missed something, I'm building a custom interface that will create itself somewhat dynamically. The only thing left is the DB query. The plan is to return product data without a page refresh so you never leave the page.

So you want to have a bunch of buttons that contain only product id info, and load the one product clicked? You can do that, I suppose, but Zen Cart loads all of the product basic info, not just the id, automatically for the product listing page. Unless you have a huge number of them, or they aren't actually defined as to where the button points, you could apply your javascript to showing only the called-for set of already-loaded products.

I only have a few products so I created my own index page. Right now it is hard coded, but I want to make it easier to update by making it dynamic. For me the easiest way is via Javascript because I understand it. PHP not so much. I've spent a couple of days analyzing the code. I have done a basic proof of concept with a standard db query:

$db = mysql_select_db(DB) or  die('Could not connect to database !<br />Please contact the site\'s administrator.');

$query = mysql_query(" SELECT * FROM products_description WHERE products_id='".$_POST['value']."' ");

With a few other bits for formatting it returned basic results. However what I want to do is rework a copy of the main_template_vars.php file used with the product info page so it will execute on a $_POST. This would be easy enough if I could actually figure out how zen cart is querying the DB. It looks as though Zen cart would normally execute this page as it loaded the header for the product info page using the $_GET command with the product ID

$sql = "select count(*) as total
          from " . TABLE_PRODUCTS . " p, " .
                   TABLE_PRODUCTS_DESCRIPTION . " pd
          where    p.products_status = '1'
          and      p.products_id = '" . (int)$_GET['products_id'] . "'
          and      pd.products_id = p.products_id
          and      pd.language_id = '" . (int)$_SESSION['languages_id'] . "'";


  $res = $db->Execute($sql);

I understand this section for the most part, but where are the $res and $db variable being defined in zen cart, where is the mysql connect, select and query that define those variables? There seem to be multiple files needed the execute upon loading the product info page that I must be missing in order to get this script running. I need to figure out if there is a way to execute it as a stand alone file when the $_GET is replaced by a $POST variable?

Apparently your unique requirements are demanding that you write your own custom PHP-based ajax handler.
The $db object is built by calling application_top.php as part of normal bootstrapping. Anything you do outside of that means you'll need to write your own complete framework instead of using the Zen Cart core code framework which spawns from index.php and application_top. If you skip calling application_top then you also bypass all the security protections and other safeties built into the system.
There are numerous existing addons which have built their own ajax handlers. You might consider one of those instead of writing your own from the ground up.

It rather sounds like you want to reinvent the wheel when all you really need is whitewalls and a different tread pattern. I think you will spend a huge amount more time getting your custom page to run completely right than if you tweaked the existing pages.

I think you guys have mis-read what I was trying to do. I am not trying to re-invent the wheel. All I am trying to do is make a call to query the DB, that in theory seems like it should be pretty simple. To do that I was attempting to figure out the process in which zen cart does this, but there are so many files that are used to for one page I've been trying to wrap my head around what is what. In the end I want to use the existing function and security of the cart, yet still have a streamlined page.

I only have a few products so I created my own index page. If you modified the existing product listing page (which is an index page), you would have all of the correct fundamental Zen Cart handling, to which you could just add your desired javascript display functionality. How did you make your custom index page, anyway?

I turned off the header, footer and sideboxes in the template files, I turned everything off that displays on the index page, basically giving me a blank canvas. I designed a new index page using the define_main_page.php file. I removed all links to anything I didn't need such as search, notifications, etc. So now I have my index page with my products and other info in a few divs. The add to cart buttons are currently javascript based forms. It works well with the only real issue I have is that when I decide to add new products, or change products, which won't be often, I'll have to add/edit them manually on my define main page file and the backend admin section.

I like this format better because the customer see's the product, clicks on it and a lightbox window is called which pops up with the description, photo and add to cart button. Some attributes for said product are passed silently, ie. if it includes attributes(like color) that should not be user selectable, along with user selectable attributes(like size), all with one button click, and the customer never leaves the main page. The customer is quickly guided from the add to cart through checkout in only about 4-5 page clicks. If I could combine the checkout process to one page I'd probably do that too, cutting down pages clicks even less.

Maybe I am trying to reinvent the wheel, I look at it more like turning a whitewall into a low profile, but I needed a basic streamlined cart and zen cart fit the bill for the most part; However my lack of php knowledge made it easier to do everything in javascript on the define_main_page file rather than tweak the php. So here I am, maybe I just leave it as is, or I figure out another way to do it utilizing the product_info.php page somehow.

I suppose it's irrelevant to you that your site will be completely unusable by anyone who comes to it with javascript disabled. I see plenty of sites like that, even with no notification that essential functionality will be missing without js.

There have been discussions about a "quick look" or "quick view" feature for the product listing page (which can be the home page if you have only one category, or set that category to open on the home page). Rbarbour had the most comprehensive thread, I think, and he started with a CSS-based solution, but decided it was too complex for others to use as a mod, and wound up developing a js-based solution originated by another member. If the code is still available, you could use that and turn off all of the extra info on the basic product listing; the full product info including attributes would then come up when the product name/whatever was chosen.

Every javascript link contains a fallback to a basic html page. The quick view looks promising but the code has not been released yet. I am thinking of other solutions, but they are going to require much more php tweaking of the template files than I had originally wanted to make, so I see many forum searches and posts in my future.

Every javascript link contains a fallback to a basic html page. Good for you:) So many sites do not bother with that. Stock Zen Cart, where it uses js, does provide <noscript> alternatives.