php mysql multiple table search-CodePudding

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"; } 
              //////////////////////////////////////////////////////////////////         

             }

CodePudding user response：

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.

CodePudding user response：

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%';