PHP/MySQL table question

[casha]

I have set up a random product display on my homepage using my own coding of PHP/MySQL. Right now, I have my code pulling a random product from the MAIN PRODUCT TABLE:

mysql_query( "SELECT name, price, thumbnail FROM s01_Products ORDER BY RAND() LIMIT 1" ) 

However, I would like to display only products from ONE SPECIFIC CATEGORY, rather than from the whole list of products. Is there a table to do so, and if so which one? Any other ideas?

Thank you.

[wcw]

Re: PHP/MySQL table question

You have to check the categoryxproduct to find the products in the category, then get the random product from the match.

Why not just use a module like Emporium Plus Tool Kit which has functions to do that built in without having to use an external program to get the items? Then insert that merchant page into your home page. For example, here's a test page that shows 10 random from a category and 5 from the entire store.
http://www.pinemporium.com/mm5/merch...&Store_Code=PE

[casha]

Re: PHP/MySQL table question

I checked the categoryxproduct and yes, the products are listed there next to the cat_id, however, you cannot actually display the product from that table. ???

[wcw]

Re: PHP/MySQL table question

Right, you have to do a query with a join of the tables.

[casha]

Re: PHP/MySQL table question

Ah...I just understood that after I walked away from the computer. Sorry, I've been sitting here all day. :)

Thank you for your suggestion!

[Siamese-Dream.Com]

Re: PHP/MySQL table question

Sorry, I've been sitting here all day.

With all due respect, you would be doing yourself a BIG favor by getting the emporium plus ToolKit. It would save you LOTS of time and frustration, and has lots of features that, I swear, will help increase sales and make your life easier.

And while you might be happy to do a lot of custom coding now, remember that there is going to be a major new release of Miva merchant (called PR 8) in about 6 weeks, and there is a possibility that you MIGHT have to re-do your coding to get your scripts working with the newest release.

Again, I am not trying to sound like a know it all, but if you are going to spend all day in front of the computer, you cold either try and wrestle with MySQL join commands and get ONE function done, or you can, in about the same time, implement five or six great features from the ToolKit / toolBelt modules.

Maybe I should mind my own business. Just that if I didn't mention this, I would feel like an "enabler."

[koala]

Re: PHP/MySQL table question

I'll join Mark in saying you might want to take a look at Tool Kit or Tool Belt. I don't even know how we got by without Tool Kit installed

[kayakbabe]

Re: PHP/MySQL table question

What you are doing seems risky.

To do what you are doing... I built a very simple storemorph page, that pulls a random product from a category using toolkit. Toolbelt can do it too. Whichever one you have will work.

Then in wordpress/php pages.... I simply call in the storemorph page.
I don't have to worry about authenticating to the db, or if my db moves or changing passwords and updating everywhere... I only have to update my store. It's way more secure this way.

Less risk to hacking attempts on your php pages. etc. With the cc info that is contained in your store, you should be really wary about setting up database connections to your store database from other sources besides miva merchant, which is certified now as pa-dss proven.

In my little storemorph page... I just leave out all the html open header and stuff.. it's just the product output.

The bonus is I can use this technique all over everywhere. And since I use css tags in the produt output.. the output can appear differently and match whichever site I am calling it into. I just need to put a little in the css file for the containing website.

[Brandon MUS]

Re: PHP/MySQL table question

I recommend the toolkit for something like this too, but the SQL is simple too

SELECT products.name
     , products.price
     , products.thumbnail
  FROM s01_Products products
INNER
  JOIN s01_CategoryXProduct cxp
    ON cxp.product_id = products.id
 WHERE products.active = 1
   AND cxp.cat_id = " . intval($YOUR_CAT_ID_HERE) . "
 ORDER
    BY RAND()
 LIMIT 1

[morditech.com]

Re: PHP/MySQL table question

Sounds to me like his homepage is a custom php page and not his storefront which means the toolkit probably won't help him.

[Siamese-Dream.Com]

Re: PHP/MySQL table question

Sounds to me like his homepage is a custom php page and not his storefront which means the toolkit probably won't help him.

Au contraire. KayakBabe's solution to make a store morph page that uses ToolKit, and call that store morph page from a php page, would work great.

[Brandon MUS]

Re: PHP/MySQL table question

I recommend the toolkit for something like this too, but the SQL is simple too

SELECT products.name
     , products.price
     , products.thumbnail
  FROM s01_Products products
INNER
  JOIN s01_CategoryXProduct cxp
    ON cxp.product_id = products.id
 WHERE products.active = 1
   AND cxp.cat_id = " . intval($YOUR_CAT_ID_HERE) . "
 ORDER
    BY RAND()
 LIMIT 1

It just dawned on me that you probably don't know your category id. Here's the query for the category code...

SELECT products.name
     , products.price
     , products.thumbnail
  FROM s01_Products products
INNER
  JOIN s01_CategoryXProduct cxp
    ON cxp.product_id = products.id
INNER
  JOIN s01_Categories categories
    ON categories.id = cxp.cat_id
 WHERE products.active = 1
   AND categories.code = '" . mysql_escape_string($YOUR_CAT_CODE_HERE) . "'
 ORDER
    BY RAND()
 LIMIT 1

[kayakbabe]

Re: PHP/MySQL table question

Morditech

It's really simple.. it will work from any php driven page.
and yes using a php include and you don't need toolkit at all. You can suck in any other webpage this way.. just be careful. I suggested a specially crafted storemorph page that doesn't have the html headers and body tags to save headaches and to be more universally useful. You can pull it into your php site, you can pull it into other pages within your miva store... etc. You only have to update in once place and wham it's updated everywhere. I do this with terms and policies so that I don't have to update my wordpress front end and also pages within my store. It works like a charm. (Note: if you did pull in a fully formatted html screen, you'd have all kinds of browser display mess).

The specific question was best answered by using toolkit to obtain the output in a storemorph screen.

As far as your php file it's a simple include statement.

If you've done any php or borrowed any php or played with wordpress templates... you've seen include statements..
http://php.net/manual/en/function.include.php

so you would do something like:

include("/mm5/merchant.mvc?screen=stufftodisplay");
include("http://mydomain.com/mm5/merchant.mvc?screen=stufftodisplay");

or if you use short seolinks it would be something like this
include("/stufftodisplay.html");
include("http://www.mydomain.com/stufftodisplay.html");

You'll totally avoid security issues with directly interfacing to your store database, becuase you aren't. You don't have to keep track of several db connection strings when you update your store's mysql password (you do update them regularly I hope).

[kayakbabe]

Re: PHP/MySQL table question

another bonus for using the php include method instead...
you don't have to know the mysql table prefix either.
If you have a mall setup .. your tables of each store have different prefixes.
They won't all be S01_tablename. In your php include all you need to know is the store code.

[Brandon MUS]

Re: PHP/MySQL table question

The problem with using the php include() method is that the server is making the request then and not the customer. This means that you can't access the basket or customer information. To get around this, you can pass the session_id as a GET request.

include("http://mydomain.com/mm5/merchant.mvc?screen=stufftodisplay&session_id=" . $session_id);