Page 1 of 1

INNER JOIN

Posted: Sat Apr 14, 2012 3:33 am
by payamstudent
Hello,
I am writing a code to categorize my store based on the raw data that I get and I get to little bit of the problem if you can help me really appreciate it because i can't figure it out myself .
Here is my issue.


Any time i run my code and i check the category on the store it will put "Audio & video and GOLD
under both subcategorys of "electronic" and "jewelry" and i dont know how to make it racognize that it should only put "Audio & video " under "0electronic /Acessory/Audio & video" and "gold under jewelry / Accessory/ GOLD"

thank you for your help in advance

Code: Select all

mytbl         
Header                      categories        categories1      categories2
Value                      electronic         Accessory         Audio & video 
Value                      jewelry            Accessory         GOLD

categories   

Header                           category_id       parent_id
      
category_descriptions
   
 Header                           category_id        name
      
categories_to_stores
   
 Header                           category_id         Store ID

and here is my code

Code: Select all

<?php
$hostname='host';
$username='username';
$password='password';
$dbname='dbname';

$ID = 1 ;
mysql_connect($hostname,$username, $password) OR DIE ('Unable to connect to database! Please try again later.');
mysql_select_db($dbname);


// Sub
$result = mysql_query("SELECT mytbl.categories FROM mytbl GROUP BY mytbl.categories ") ;

while($row = mysql_fetch_array($result))

  {  
  
$categories=$row['categories'];

if($categories <> null){
echo $ID;
Echo "<br/>" ;

 
mysql_query("INSERT INTO category_descriptions (name, category_id, language_id)
VALUES ('$categories','$ID' ,'1')");
 
mysql_query("INSERT INTO categories (category_id, parent_id)
 VALUES ('$ID' ,'' )");

mysql_query("INSERT INTO  categories_to_stores (category_id, store_id)
 VALUES ('$ID' ,'0' )");
}

$ID = $ID+1 ; 


  }


// Cat1
$result = mysql_query("SELECT mytbl.categories1, mytbl.categories, category_descriptions.category_id
FROM mytbl INNER JOIN category_descriptions ON mytbl.categories = category_descriptions.name
GROUP BY mytbl.categories1, mytbl.categories, category_descriptions.category_id;
 ") ;


 

while($row = mysql_fetch_array($result))
  
  {  
$categories=$row['categories'];
$categories1=$row['categories1']  ;
$ParentID=$row['category_id'];

if($categories1 <> null){
echo $ID;
Echo "<br/>" ;


mysql_query("INSERT INTO category_descriptions (name, category_id, language_id)
VALUES ('$categories1','$ID' ,'1')");

mysql_query("INSERT INTO categories (category_id, parent_id)
 VALUES ('$ID' ,'$ParentID' )");

mysql_query("INSERT INTO  categories_to_stores (category_id, store_id)
 VALUES ('$ID' ,'0' )");
}

$ID = $ID+1 ; 

}

$result = mysql_query("SELECT mytbl.categories1, mytbl.categories2, category_descriptions.category_id
FROM mytbl INNER JOIN category_descriptions ON mytbl.categories1 = category_descriptions.name
GROUP BY mytbl.categories1, mytbl.categories2, category_descriptions.category_id;
 ") ;


while($row = mysql_fetch_array($result))
  
  {  
$categories1=$row['categories1'];
$categories2=$row['categories2']  ;
$ParentID=$row['category_id'];



if($categories2 <> null){
echo $ID;
Echo "<br/>" ;


mysql_query("INSERT INTO category_descriptions (name, category_id, language_id)
VALUES ('$categories2','$ID' ,'1')");

 
mysql_query("INSERT INTO categories (category_id, parent_id)
 VALUES ('$ID' ,'$ParentID' )");

mysql_query("INSERT INTO  categories_to_stores (category_id, store_id)
 VALUES ('$ID' ,'0' )");
 }

$ID = $ID+1 ; 

}




?>

Logged