Announcement

Collapse
No announcement yet.

PHP/MySQL table question

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    PHP/MySQL table question

    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:

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

    #2
    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
    Last edited by wcw; 11-21-10, 05:20 PM.
    Bill Weiland - Emporium Plus http://www.emporiumplus.com/store.mvc
    Online Documentation http://www.emporiumplus.com/tk3/v3/doc.htm
    Question http://www.emporiumplus.com/mivamodu...vc?Screen=SPTS
    Facebook http://www.facebook.com/EmporiumPlus
    Twitter http://twitter.com/emporiumplus

    Comment


      #3
      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. ???

      Comment


        #4
        Re: PHP/MySQL table question

        Right, you have to do a query with a join of the tables.
        Bill Weiland - Emporium Plus http://www.emporiumplus.com/store.mvc
        Online Documentation http://www.emporiumplus.com/tk3/v3/doc.htm
        Question http://www.emporiumplus.com/mivamodu...vc?Screen=SPTS
        Facebook http://www.facebook.com/EmporiumPlus
        Twitter http://twitter.com/emporiumplus

        Comment


          #5
          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!

          Comment


            #6
            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."
            Mark Romero
            ~~~~~~~~

            Comment


              #7
              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
              Dylan Buchfink
              The Mattress & Sleep Company
              http://www.tmasc.ca/

              Comment


                #8
                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.
                Last edited by kayakbabe; 11-22-10, 03:20 PM.

                Comment


                  #9
                  Re: PHP/MySQL table question

                  I recommend the toolkit for something like this too, but the SQL is simple too
                  Code:
                  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
                  Last edited by Brandon MUS; 11-23-10, 08:15 AM.

                  Comment


                    #10
                    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.

                    Comment


                      #11
                      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.
                      Mark Romero
                      ~~~~~~~~

                      Comment


                        #12
                        Re: PHP/MySQL table question

                        Originally posted by Brandon MUS View Post
                        I recommend the toolkit for something like this too, but the SQL is simple too
                        Code:
                        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...

                        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
                        Last edited by Brandon MUS; 11-24-10, 06:54 AM.

                        Comment


                          #13
                          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).
                          Last edited by kayakbabe; 11-24-10, 05:08 PM.

                          Comment


                            #14
                            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.

                            Comment


                              #15
                              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);

                              Comment

                              Working...
                              X