DB query question

[skylab001]

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?

[RescoCCC]

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

[skylab001]

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.

[gjh42]

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.

[skylab001]

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?

[DrByte]

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.