[RESOLVED] Loops and Inserts and Categories OH MY!

[Kudose]

Hello all. I must admit that I cannot do, nor even fathom, what I am looking to do.

I have a table that has a column (CATEGORIES) that holds the category name for a product.

i.e.
ThinkGeek|Pens|Laser
ThinkGeek|Pens|Ballpoint
PHPBuilder|Code|Snippet

Just like that. I have a table that holds my real categories to my store, it is setup to hold integer values for the cat_id and father_id of the cat. To build a never-ending drop down thing.

What I need to do is scan my "bkup_table" for the "CATEGORIES" and insert them into my "real_table" so they dont overlap and they all have a father ID that works and such.

I'd really like to LEARN how to do this so I dont have to ak in the future, so if someone would be kind enough to explain this, or atleast enough to get me going, I would be very appreciative.

bkup_tbl
+--------------------------------------------
+ prod_id | sku | categories | title
+--------------------------------------------

real_tbl
+--------------------------------------------
+ cat_id | father_id | cat_name | etc
+---------------------------------------------

If none of this makes sense, I can try again.

TIA.

[justsomeone]

I think I understand about half of what you're trying to do, but I may have gotten the wrong end of the stick, so forgive me if I'm way off the mark 🙂

You want to extract all of the categories in your bkup_tbl and create them in your real_tbl?
First off, do a "select distinct" on your bkup_tbl to get a list containing all the categories (but listing each only once).

You then need to explode the categories value using the pipe "|" as delimiter. You now have an array of three categories eg ("ThinkGeek", "Pens", "Laser").

Check your table to see if you already have a record with a cat name of "ThinkGeek", if so, note the cat_id, otherwise insert the record (with a "father_id" of zero) and note the assigned cat_id.

Now move on to the second element of the array, "Pens"...

Check your table to see if you already have a record with a cat name of "Pens", if so, note the cat_id, otherwise insert the record (with a "father_id" of teh cat_id of the "ThinkGeek" record) and note the assigned cat_id.

... repeat this for each element of your array
... repeat this for every row returned.

How does that sound?

[Kudose]

Sounds great. The part I was really stumped on was how to get the father_id of a cat. But I guess I can just go "down one" in the array and query the DB for the ID. Lemme see what I can come up with and I will get back with you.

Thanks. 🙂

[Kudose]

Holy shit. I did it.

[Kudose]

//get distinct at names
$class = new WM;
$distinct = $class->fetch("SELECT DISTINCT CATEGORIES FROM ".$prefix."store_wholesaleprods");

foreach($distinct as $row){
  $row = array_unique($row);
  $array = explode("|", $row[0]);
  //$array[0] = "Supplier";
  //$array[1] = "Top Cat";
  //$array[2] = "Next Cat";
  //etc . . .

  foreach($array as $key => $val){
    /* loop through cats (above) */
    $exists = $class->rows("SELECT * FROM ".$prefix."store_category WHERE category='".$val."'");
    if($exists == 0){
      /* exists, so get a father_id */
      if($key == 0)
        $father_id = 0;
      else {
        $father_id = $class->fetch("SELECT cat_id FROM ".$prefix."store_category WHERE category='".$array[$key-1]."'");
        $father_id = $father_id[0]['cat_id'];
      } /* if $key == 0 */

  /* build insert query */
  $sql = "INSERT INTO ".$prefix."store_category (category, cat_id, cat_father_id, cat_image) VALUES ";
  $sql .= "('".$val."', '', '".$father_id."', 'catnophoto.gif')";

  /* run SQL */
  if($class->query($sql))
    echo '. ';
  else
    echo mysql_error();

} /* if $exists == 0 */

  }/* foreach $array as $val */

}/* foreach distinct as row */

echo '<br><br>Complete.<br>';

[justsomeone]

What is the relationship betweena category and is father. Can each category only have one father? if so, it's quite straightforward.

Take the record

ThinkGeek|Pens|Laser

You look for a category called "ThinkGeek" in your new database. It's not there, so you insert it. It was at the start of an array, so insert it with a father_id ofzero, or null or whatever you choose to represent "no father".

Moving on to the next element in the array, you come to "Pens". Now you know from the array that "Pens" has a father of "ThinkGeek", so you don't need to look that up. Just inster the "Pens" record if it doesn't already exist.

And so on.

If you subsequently come across a record like

Websites|ThinkGeek

Then you can insert the new fatherless row for "Websites" as above. But this time when you check for ThinkGeek and see that it exists and is fatherless, you should update it to set its father to whatever the cat_id you just created for "Websites" is.

Does that help clarify at all?

[justsomeone]

Well done man!

[Kudose]

Yeah, this just made my week ... what's left of it.

Thanks for your guidance. I just couldnt get everything to fit together right in my head ... seeing it typed out made it clear though.

🙂