Results 1 to 13 of 13
  1. #1
    ABW Ambassador writerguy's Avatar
    Join Date
    January 17th, 2005
    Location
    Springfield, Missouri, USA
    Posts
    3,248
    Help needed with "SELECT" command
    I've got a MySQL database with 2 tables in it. One table is named happymothers, the other table gotobaby. I have a SELECT command setting up category navigation for one table in my template:


    $result = mysql_query("select DISTINCT Custom2 from happymother ORDER by Custom2 ASC") or die (mysql_error());
    while ($row = mysql_fetch_array($result))
    {
    $catname = urlencode($row[Custom2]);
    echo "$catbullet <a href=\"category.php?Custom2=$catname\">$row[Custom2]</a><br>";
    }


    That code uses the "happymother" table and displays all the items by category in a nav sidebar.

    How can I structure my "SELECT" command so that it ALSO displays all the items by category from the "gotobaby" table in the SAME nav sidebar?

    Both tables have the category items in the "Custom2" column.

    As I understand it, I need the "DISTINCT" command in their. But when I try to structure a SELECT call using both tables, I can't figure out where and how to 1) use DISTINCT, and, 2) exactly how to structure the two tables together?

    Thanks.
    Last edited by writerguy; July 26th, 2008 at 11:49 PM. Reason: Edited to correct code typo
    Generate more fake news.

  2. #2
    Believe knight01's Avatar
    Join Date
    August 14th, 2006
    Location
    Dayton, Ohio
    Posts
    1,815
    Try something like this...
    SELECT DISTINCT FROM happymother LEFT JOIN gotobaby ON happymothers.Custom2 ORDER by Custom2 ASC
    I"m still struggling with joins but I think this is pretty close, at least until Snib or Patrick come along with a correct solution.
    Someday starts today
    Military Discounts

  3. #3
    Full Member
    Join Date
    October 22nd, 2006
    Posts
    200
    UNION works.

    "UNION" only selects distinct records "UNION ALL" is required to select all the records


    Code:
    $sql="(select  Custom2 from happymother)
    UNION
    (select  Custom2 from gotobaby)
     ORDER by Custom2 ASC";

    Regards Bob

  4. #4
    ABW Ambassador writerguy's Avatar
    Join Date
    January 17th, 2005
    Location
    Springfield, Missouri, USA
    Posts
    3,248
    Whoo-Hoo! Thanks, Bob, that worked. Now I've fiddled around and discovered how to use the two tables in the same template to display all the products in both tables.

    But I think there must be a more elegant way, because I have all these lengthy "SELECT" calls now to display items from the two tables.

    I've never learned how to put more than one merchant datafeed into a table, hence I have the database "XXX_dfeeds" with the tables "happymothers" (I see I forgot the ending "s" in my post above) and "gotobaby." My php script (based on a modified script "mobilebadboy" of ABW fame offered to forum members a couple of years ago) creates a single table out of a single datafeed.

    How difficult would it be to learn how to build a single table out of two or more datafeeds? Can you point me in the right direction? It seems to me that, if I used the script I currently have, it would simply overright all the data in a table with the newest datafeed.

    Sorry I'm so unclear on all this. Really don't want to mooch so much free insight and expertise from anyone here, but I'm ignorant enough about PHP/MySQL that I'm not sure where to start.

    Thanks!
    Generate more fake news.

  5. #5
    Visual Artist & ABW Ambassador lostdeviant's Avatar
    Join Date
    September 7th, 2007
    Location
    Cuautitlán, Edo. de México
    Posts
    1,725
    Hi Writerguy,
    I'm still a mysql and php newbie, but as long as you have the same columns/fields, it isn't difficult to put them in the same table. It just takes a bit of editing to the csv import script.

    Instead of creating a new table, you'll have it add to a table you've already created.

    You'll also need to have it check for old entries from the merchant and remove them before adding the new entries.

    Quote Originally Posted by writerguy

    How difficult would it be to learn how to build a single table out of two or more datafeeds? Can you point me in the right direction? It seems to me that, if I used the script I currently have, it would simply overright all the data in a table with the newest datafeed.

  6. #6
    ABW Ambassador writerguy's Avatar
    Join Date
    January 17th, 2005
    Location
    Springfield, Missouri, USA
    Posts
    3,248
    Quote Originally Posted by lostdeviant
    Hi Writerguy,
    I'm still a mysql and php newbie, but as long as you have the same columns/fields, it isn't difficult to put them in the same table. It just takes a bit of editing to the csv import script.

    Instead of creating a new table, you'll have it add to a table you've already created.

    You'll also need to have it check for old entries from the merchant and remove them before adding the new entries.
    Yeah, that's pretty much what I need to figure out. The problem is that the script I use to download datafeeds via FTP and put them into a table is made to drop the table and create it with the new datafeed. I'm struggling to figure out how to make the script download and insert a datafeed into an existing table, update or delete data currently in the table, and add the datafeed to the table -- without destroying datafeeds already in the table.

    I know a simple "insert into" command is fundamental to the process somehow, but I don't begin to know how to code it all.

    Gotta hit Google and my PHP/MySQL books some more.
    Generate more fake news.

  7. #7
    ABW Ambassador bettylou's Avatar
    Join Date
    December 27th, 2005
    Location
    Indiana
    Posts
    595
    Hey Gary,

    What I have done before is this:

    1. Keep pulling in the feeds just as you have been doing.

    2. Create a new table (let's just call it "products") that contains all the information that you will be using in your queries to create your category and product listings (name, price, description, and anything else you want). In this new table, create a field called "merchant_name" or whatever you prefer.

    3. After you have pulled in the merchant feed, let's say for happymothers, then run a query to select all products from happymothers:

    SELECT name, description, price (and so on) from happymothers

    Now, insert them into the "products" table with an INSERT statement. Be sure to include data for the "merchant_name" field that you created in #2, which would be in the situation - Happy Mothers.

    This works fine for the initial loading of a merchant into the "products" table but after that, you will need to first delete all the products from that merchant to avoid duplication.


    Gary, let me know if this doesn't make any sense. I can send you a copy of what I have.

    Good Luck!

  8. #8
    Full Member
    Join Date
    October 22nd, 2006
    Posts
    200
    Hi Gary

    You can use the UNION statement for the display and if you use a loop to create the MYSQL command it doesn't look too complicated.

    I have used this code to display products for up to 300 merchants.
    In my code the category comes from a $_GET variable and I create the $tables array dynamicaly. But here is the core parts of the code.

    PHP Code:
    <?php
    $cat
    =addslashes($category);
    $tables=array("happymothers","gotobaby");
    foreach ( 
    $tables as $key=>$value){
     
    $sqlarray[]="(SELECT Merchantname,Name,Category_Name FROM $value WHERE Category_Name LIKE '$cat')\r\n";
     }
    $sql2implode (" UNION \r\n",$sqlarray). " ORDER BY Category_Name LIMIT 0,20";
    $result2=mysql_query($sql2) or die (mysql_error());
    $i=0;
    while (
    $line=mysql_fetch_assoc($result2)){
    $prod[$i]['Merchantname']=$line['Merchantname'];
    $prod[$i]['Category_Name']=$line['Category_Name'];
    $prod[$i]['Name']=$line['Name'];
    $i++;
    }
    ?>
    If you want to combine the feeds into one table. (Assuming both tables are in the same format). Here is some code to do that
    PHP Code:
    <?php
    $merchants
    =array("happymothers","gotobaby");
    $sql="CREATE  TABLE IF NOT EXISTS newproducts
         LIKE "
    .$merchants[0];
    $result=mysql_query($sql);
    $sql"TRUNCATE TABLE newproducts";
    $result=mysql_query($sql);
    foreach (
    $merchants as $key =>$value){
    $sql="INSERT INTO newproducts SELECT * FROM $value";
    $result=mysql_query($sql)or die(mysql_error());
    }      
    ?>
    You could add this code at the end of the script you use to import the happymothers and gotobaby feeds so the combined table is refreshed automatically.

    The code creates the newtable if it doesn't already exist, empties it and adds all the products for the merchants listed in the merchants array.

    Regards Bob

  9. #9
    ABW Ambassador writerguy's Avatar
    Join Date
    January 17th, 2005
    Location
    Springfield, Missouri, USA
    Posts
    3,248
    Oh boy! Time to get busy and learn, learn, learn. Wonderful fun experimenting with all this. And it's a comfort to know if I "break" anything, I'm slowly learning how to put it back together and try again!

    Thanks again so much, Bob, for the help.

    One question: If I use the second code snippet you gave me at the end of my current table creation scripts, would I put it at the bottom of both the script for "happymothers" and the script for "gotobaby," or just at the bottom of either one? I'm a bit confused on that. (I'm at least a BIT confused on most things these days. LOL!)
    Generate more fake news.

  10. #10
    Moderator MichaelColey's Avatar
    Join Date
    January 18th, 2005
    Location
    Mansfield, TX
    Posts
    16,232
    If you're going to use all the products together (which you apparently are), it's definitely best to have them in one table. It's far more complicated, but worthwhile.

    The other problem with clearing and reloading a table is that your pages will be empty (or will get errors) while you're processing your feeds. It's best to design it so that everything continues to work properly while you update your feeds.

    The basics of one common way to accomplish those things:

    1) Create a table that has fields for a merchantId, status, and all of the fields that you currently have for any merchant. The new key should be merchantId+productId.

    2) When you process a merchant feed, update the status field for all existing products for that merchant to a value that indicates "being updated".

    3) As you process the feed, REPLACE (or selectively INSERT or UPDATE) each record, making sure to change the status to "updated" for each record that you process.

    4) Once that's done, delete (or flag as "deleted") all of the items for the merchant that are still flagged as "being updated". A best practice here is to see what percent of records would be "deleted" first, and if it's over some threshold (like 10%), leave them and email you a notification instead. Odds are it's an empty or broken datafeed rather than actual deletes.
    Michael Coley
    Amazing-Bargains.com
     Affiliate Tips | Merchant Best Practices | Affiliate Friendly? | Couponing | CPA Networks? | ABW Tips | Activating Affiliates
    "Education is the most powerful weapon which you can use to change the world." Nelson Mandela

  11. #11
    Member Sharan's Avatar
    Join Date
    May 19th, 2006
    Location
    SW Missouri
    Posts
    54
    Gary,

    Thanks for asking this question ( you have just beaten me to the punch). I am trying to work on some of the some things myself and the suggestions have been extremely helpful. Just when I think I'm beginning to get it, another idea pops up and I go searching to find out how it's done.

    I certainly appreciate all the help that's offered and the willingness to make sure we understand what is being said.

    Thanks to all for the input, especially code examples -- that's how I seem to be able to best understand by seeing it actually written out.

    Sharan

  12. #12
    Full Member
    Join Date
    October 22nd, 2006
    Posts
    200
    Hi Gary

    The script only takes about one second to merge 5000 records so it shouldn't cause any problem to add it to both scripts. It simply takes the current records for both tables and merges it into a new one.

    @Sharan

    I'm pleased to be able to help. I learned to code by copying code off forums and experimenting with changes to make it work the way I wanted it too.

    @Michael

    While I agree in principle with your suggestions. Gary (and others) are using code based on "mobilebadboy's script" to add your suggestions would mean rewriting the whole thread.

    With shared hosting searching a large table can take a much greater time than a small one. So unless you want to create a complete price comparison site it is often better to create "niche" tables as Gary is doing combining two or three related merchants.

    Regards Bob

  13. #13
    ABW Veteran Mr. Sal's Avatar
    Join Date
    January 18th, 2005
    Posts
    6,795
    Quote Originally Posted by writerguy
    Oh boy! Time to get busy and learn, learn, learn. Wonderful fun experimenting with all this. And it's a comfort to know if I "break" anything, I'm slowly learning how to put it back together and try again!

    Thanks again so much, Bob, for the help.

    One question: If I use the second code snippet you gave me at the end of my current table creation scripts, would I put it at the bottom of both the script for "happymothers" and the script for "gotobaby," or just at the bottom of either one? I'm a bit confused on that. (I'm at least a BIT confused on most things these days. LOL!)


    Well... Gary?

    You had three months to play with your scripts, so did you "break" anything, or did you get it to work your way?

    Btw, I sent you a PM.

  14. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. I Need a "Miracle"... (ASW10 Pass Needed)
    By Molly_Vanns in forum Events and Gatherings
    Replies: 8
    Last Post: January 12th, 2010, 12:32 PM
  2. Replies: 2
    Last Post: July 29th, 2005, 01:51 PM
  3. Datafeeds & Google: bad "news" - Are you a "thin affiliate" ?
    By heisje in forum Programming / Datafeeds / Tools
    Replies: 4
    Last Post: June 29th, 2005, 08:52 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •