Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 631
  • Last Modified:

Not unique table/alias error in mysql

Hi, I need to find all category names that are bind to a product my tables looks like this:

product_category                   product_tbl                  category_tbl
-------------------------             ---------------------       -------------------------
c_id             p_id                         P_id         blah           c_id         category
  1                254152                 254152      24               1            electronics
  1                548414                 548414      15               2            books  
  2                548414

I have the product id = $p_id  and  try to get all category names from mysql
using  the folowing code.  I get:  Not unique table/alias   mysql error

code:
$result = mysql_query("select category_tbl.* from product_tbl inner join product_category on product_category.c_id = category_tbl.c_id inner join product_category on product_category.p_id = product_tbl.p_id where product_category.p_id = $product_id")or die("    "  . mysql_error());

does someone knows how to correct this pls
0
ikon32
Asked:
ikon32
  • 7
  • 4
2 Solutions
 
MrNedCommented:
Are you sure that's correct - where's the category_tbl join? I see product_tbl and product_category twice.
0
 
ikon32Author Commented:
not sure if is correct, is not working...  can you correct my code pls, very confiused...
0
 
MrNedCommented:
My guess:

select category_tbl.*
from product_tbl inner join category_tbl
  on product_tbl.c_id = category_tbl.c_id
inner join product_category
  on product_category.p_id = product_tbl.p_id
where product_category.p_id = $product_id
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
ikon32Author Commented:
returns: Unknown column 'product_tbl.c_id' in 'on clause'
0
 
MrNedCommented:
OK, try this:

select category_tbl.*
from product_tbl inner join category_tbl
  on product_tbl.p_id = category_tbl.c_id
inner join product_category
  on product_category.p_id = product_tbl.p_id
where product_category.p_id = $product_id

I'm driving blind here with no schema definitions.
0
 
ikon32Author Commented:
my tables...

mysql_query("CREATE TABLE IF NOT EXISTS product_tbl (
            p_id BIGINT(20) NOT NULL,
            p_name varchar(50) NOT NULL,
            p_color varchar(50) NOT NULL,
            p_price int(4) NOT NULL,
            p_caption varchar(255) NOT NULL,
            p_supplier varchar(50) NOT NULL,
            pic_link varchar(100) NOT NULL,
            PRIMARY KEY(p_id),
            UNIQUE KEY (p_id))");



mysql_query("CREATE TABLE IF NOT EXISTS category_tbl (c_id int(6) NOT NULL auto_increment,
category varchar(50) NOT NULL,
PRIMARY KEY(c_id), UNIQUE KEY (category))");
mysql_query("SET NAMES 'utf8'");


mysql_query("CREATE TABLE IF NOT EXISTS  product_category (
            c_id int(8) NOT NULL,
            p_id BIGINT(20) NOT NULL,
            PRIMARY KEY (c_id, p_id)
            )ENGINE=InnoDB") or die("    "  . mysql_error());


0
 
SharathData EngineerCommented:
try this.
$result = mysql_query("SELECT c.* 
  FROM product_tbl AS p 
       INNER JOIN product_category AS pc 
         ON p.p_id = pc.p_id 
       INNER JOIN category_tbl AS c 
         ON pc.c_id = c.c_id 
 WHERE pc.p_id = $product_id")or die("    "  . mysql_error());

Open in new window

0
 
ikon32Author Commented:
$temp_rows = mysql_fetch_array($result);

// the
print_r($temp_rows);

returns: Array ( [0] => 2 [c_id] => 2 [1] => electronics [category] => electronics  )

in my loco example I have asign 2 categories that sould have been returned.
how can it return only the names of all categories that are asign to the $p_id without the c_id?
0
 
MrNedCommented:
Change the select columns from * to the column you want (i assume category):

select category_tbl.category
from product_tbl inner join category_tbl
  on product_tbl.p_id = category_tbl.c_id
inner join product_category
  on product_category.p_id = product_tbl.p_id
where product_category.p_id = $product_id
0
 
ikon32Author Commented:
returns no value.
0
 
ikon32Author Commented:
the one step approch maybe faster but more complicated and confusing, so I also tried a 2
step.
First find all the (category id's) that are maped with the (product id) and then run a loop to find the names of the categories based on the (category id's) that have been return.

In this case it works and returns all names, but what is the weight that will be put on the server... can you please check and advice?
my solution:

$resu = mysql_query("select c_id from product_category where p_id ='$product_id'")or die("    "  . mysql_error());

    while($rows= mysql_fetch_row($resu)){
    $val_temp = mysql_query("select category  from category_tbl where c_id ='$rows[0]'")or die("    "  . mysql_error());
    $temp_rows = mysql_fetch_row($val_temp);
    echo "$temp_rows[0]";
}
0
 
ikon32Author Commented:
You guys are great, thanks for your comments,with few modifications I'll get it right.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now