skip to Main Content

I have two tables
Table products : product_id,name,barcode1,barcode2,barcode3
Table more_barcodes: product_id,barcode

How to join those two tables when user search barcode ?

select products.* from products 
LEFT JOIN more_barcodes ON products.product_id=more_barcodes.product_id
where (
       (products.barcode1     LIKE '%$user_search%') 
    OR (products.barcode2     LIKE '%$user_search%' ) 
    OR (products.barcode3     LIKE '%$user_search%' )
    OR (more_barcodes.barcode LIKE '%$user_search%' )
      )
GROUP by products.products_id


  $sql_check=mysql_query($query); 
    while ($row_check = mysql_fetch_array($sql_check)) 
     {

              echo "<br>".$row_check[name];
              
              // show results from table products 
          echo $row_check[barcode]; 
              if ($row_check[barcode2]!="") { echo "<br>".$row_check[barcode2]; } 
          if ($row_check[barcode3]!="") { echo "<br>".$row_check[barcode3]; } 
              
              // show results from table more_barcodes
              // here is the problem ///////////////////////////////////////////
              if (barcode in table more_barcodes) { echo "ALL BARCODES"; } 
              //////////////////////////////////////////////////////////////////         

             }

2

Answers


  1. As I can understand, No two different products have the same barcode so I can use something like that :

    /* Search in more_barcodes and get product_id*/
    SELECT @PId := product_id FROM more_barcodes
    WHERE barcode LIKE '%$user_search%' ;
    
    select * from products 
    where (
           (barcode1     LIKE '%$user_search%') 
        OR (barcode2     LIKE '%$user_search%' ) 
        OR (barcode3     LIKE '%$user_search%' )
        OR (product_id = @PId) /* product_id we get from more_barcodes*/
          );
    

    You can also dived your search into two steps in PHP code. First search in more_barcodes then search in products.

    Login or Signup to reply.
  2. Query:

    SELECT t1.product_id, t1.name, t1.barcode1, t1.barcode2, t1.barcode3, 
           t2.barcode 
    FROM products t1 
    INNER JOIN more_barcodes t2 ON t1.products = t1.product_id 
    WHERE t2.barcode LIKE '%$user_search%';
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search