Query from two tables

[Bravat]

Hello,

I am using MySQL Version :5.1.36. I have to tables: product and manufacturer and i want to do cross query.

 

SELECT *, name FROM product, manufacturer WHERE 
            `Rim` = '$rimp' AND `Dimenzija` = '$dimenzijap'
            OR `Rim` = '$rimp' AND `Težinski_Indeks` =   '$tezinski_index' 
            OR `Dimenzija` = '$dimenzijap' AND `Težinski_Indeks` =   '$tezinski_index'
            OR `Rim` = '$rimp' AND `Brzinski_Indeks` =   '$brzinski_index' 
            OR `Dimenzija` = '$dimenzijap' AND `Brzinski_Indeks` =   '$brzinski_index'
            OR `Težinski_Indeks` = '$tezinski_index' AND `Brzinski_Indeks` =   '$brzinski_index'
            OR `Rim` = '$rimp' AND `name` = '$proizvodjac' 
            OR `Dimenzija` = '$dimenzijap' AND `name` = '$proizvodjac' 
            OR `Težinski_Indeks` = '$tezinski_index' AND `name` = '$proizvodjac' 
            OR `Brzinski_Indeks` = '$brzinski_index' AND `name` = '$proizvodjac' 
             ";

 

This code is working only the half way. When

 

OR `Rim` = '$rimp' AND `name` = '$proizvodjac' 
            OR `Dimenzija` = '$dimenzijap' AND `name` = '$proizvodjac' 
            OR `Težinski_Indeks` = '$tezinski_index' AND `name` = '$proizvodjac' 
            OR `Brzinski_Indeks` = '$brzinski_index' AND `name` = '$proizvodjac' 
             ";

 

is activated it is working correctly but when something from the first half is activated

 

`Rim` = '$rimp' AND `Dimenzija` = '$dimenzijap'
            OR `Rim` = '$rimp' AND `Težinski_Indeks` =   '$tezinski_index' 
            OR `Dimenzija` = '$dimenzijap' AND `Težinski_Indeks` =   '$tezinski_index'
            OR `Rim` = '$rimp' AND `Brzinski_Indeks` =   '$brzinski_index' 
            OR `Dimenzija` = '$dimenzijap' AND `Brzinski_Indeks` =   '$brzinski_index'
            OR `Težinski_Indeks` = '$tezinski_index' AND `Brzinski_Indeks` =   '$brzinski_index'

 

it shows 8 results (there are 8 entries in manufacturer, so i guess that is the problem). In the first half rows are from the product table and in the second half rows are from both tables. How can i exclude second table in the first half of the code?

[DarkKnight2011]

Could you please provide some further information, eg tables and sample data, results your getting and an explanation of what the query is doing and why, there seems to be way too many or statements in there for my personal liking, i think it could be optimized or improved

[Bravat]

I am creating search form which is consist of 5 drop down select menu. This code is a case if two drop down menus are selected and other three are not. I managed to make code to work:

SELECT manufacturer.manufacturer_id ,manufacturer.name, product.*
                            FROM  product, manufacturer
                            WHERE product.manufacturer_id = manufacturer.manufacturer_id 
            AND `Rim` = '$rimp' AND `Dimenzija` = '$dimenzijap' 
            OR product.manufacturer_id = manufacturer.manufacturer_id AND `Rim` = '$rimp' AND `Težinski_Indeks` =   '$tezinski_index' 
            OR product.manufacturer_id = manufacturer.manufacturer_id AND `Dimenzija` = '$dimenzijap' AND `Težinski_Indeks` =   '$tezinski_index'
            OR product.manufacturer_id = manufacturer.manufacturer_id AND`Rim` = '$rimp' AND `Brzinski_Indeks` =   '$brzinski_index' 
            OR product.manufacturer_id = manufacturer.manufacturer_id AND `Dimenzija` = '$dimenzijap' AND `Brzinski_Indeks` =   '$brzinski_index'
            OR product.manufacturer_id = manufacturer.manufacturer_id AND `Težinski_Indeks` = '$tezinski_index' AND `Brzinski_Indeks` =   '$brzinski_index'
            OR product.manufacturer_id = manufacturer.manufacturer_id AND `Dimenzija` = '$dimenzijap' AND `name` =   '$proizvodjac' 
             OR product.manufacturer_id = manufacturer.manufacturer_id AND `Rim` = '$rimp' AND `name` =   '$proizvodjac'
             OR product.manufacturer_id = manufacturer.manufacturer_id AND `Težinski_Indeks` = '$tezinski_index' AND `name` =   '$proizvodjac' 
             OR product.manufacturer_id = manufacturer.manufacturer_id AND `Brzinski_Indeks` = '$brzinski_index' AND `name` =   '$proizvodjac' 

 

There is also case when 3 out of 5, and 4 out of 5 and 1 of 5.

 

I am new to PHP and MySQL and any advice how to short this code is very helpful.

[DarkKnight2011]

Well personally myself i would generate the query based on what is selected... just a basic example....

 

$query = "SELECT manufacturer.manufacturer_id ,manufacturer.name, product.*
                            FROM  product, manufacturer
                            WHERE product.manufacturer_id = manufacturer.manufacturer_id ";

if (isset($tezinski_index) && isset($rimp)) {
    $query .= "AND `Rim` = '$rimp' AND `Težinski_Indeks` =   '$tezinski_index' ";
} elseif (isset($rimp) && isset($brzinski_index)) {
    $query .= "AND `Rim` = '$rimp' AND `Brzinski_Indeks` =   '$brzinski_index' ";
}

 

then just run your query.

 

It will be alot shorter and probably more accurate and faster.

 

note: you may need to test what is set differently ie

 

if($rimp != null && $rimp != '') 

 

hope this helps... i was just trying to get you on the right track, you will need to change the above code to suit and expand on it

[Bravat]

Code you works, but there is a problem with mine search. Drop down menu (<select><option>...) have a default value and because of that $rimp and others are always set, and never null. How can i null the  values to make you code work with me?

[DarkKnight2011]

i would just put another option in there <option value="null">Please Select</option>

 

then if no one selects it you can easily identify it and ignore it

[Bravat]

This solution work quite fine  , but i am trying to combine it with keyword search and again I am stuck  . This is the hole code:

      
       $rimp = $_POST['Rim'];
       $dimenzijap = $_POST['dimenzija'];
       $tezinski_index = $_POST['tezinski_indeks'];
       $brzinski_index = $_POST['brzinski_indeks'];
       $proizvodjac = $_POST['proizvodjac'];
       $kategorija = $_POST['kategorija'];
       $keyword = mysql_real_escape_string($_POST['keywords']);
       
       	 $query = "SELECT manufacturer.manufacturer_id ,manufacturer.name, product.*
                            FROM  manufacturer 
						INNER JOIN product  ON
                            manufacturer.manufacturer_id = product.manufacturer_id WHERE";   
              
                if (isset($keyword)){            
                if (strlen($keyword) <= 2 && ($keyword != null)) {
                echo "Uneti pojam za trazenje je kratak.";
                } else {
                echo "Pojam koji ste trazili je<br> <b>$keyword</b><br />";
                }           
                
                $search_explode = explode (" ", "$keyword");                   
                
                $x = 0;
                foreach ($search_explode as $search_each){
                
                $search_each = strtolower($search_each);                        
                   
                $x++;
                if ($x==1)                        
                $constract = "meta_keywords LIKE '%$search_each%'";
                else 
                $constract .= "OR meta_keywords LIKE '%$search_each%'";       
                }                
                $constract = "SELECT product_description.*, product.* FROM product_description 
                INNER JOIN product 
                ON product_description.product_id = product.product_id
                WHERE product_description.$constract ";
                $run = mysql_query($constract);   
                $foundnum = mysql_num_rows ($run);
                if($foundnum == 0){                     
                echo "<p>Nije nadjeno nista</p>";                     
                } else {
                echo "<p><b>$foundnum</b> proizvoda je pronadjeno.</p>";
                }while ($row = mysql_fetch_array($run)){
                $model = $row["model"];
                echo "<ul><li>$model</li></ul>" ;
                }   
                }
elseif ($rimp != null) {$query .= "`Rim` = '$rimp'";} 
elseif($dimenzijap) {$query .= "`Dimenzija` = '$dimenzijap'";}
elseif($tezinski_index != null) {$query .= "`Težinski_Indeks` = '$tezinski_index'";} 
elseif($brzinski_index != null) {$query .= "`Brzinski_Indeks` = '$brzinski_index'";}
elseif($proizvodjac != null) {$query .= "`name` = '$proizvodjac'";}
elseif($kategorija != null) {$query .= " Kategorija = '$kategorija'";}
elseif (isset($rimp) && isset($dimenzijap)) {$query .= "`Rim` = '$rimp' AND `Dimenzija` =   '$dimenzijap' ";}
elseif (isset($tezinski_index) && isset($rimp)) {$query .= "`Rim` = '$rimp' AND `Težinski_Indeks` =   '$tezinski_index' ";}
elseif (isset($rimp) && isset($brzinski_index)) {$query .= "`Rim` = '$rimp' AND `Brzinski_Indeks` =   '$brzinski_index' ";}


$result = mysql_query($query) or die("Couldn't execute query");[code]

When i select something from drop down menu it list all the results in from the first query (if $keyword isset...) and for [code]elseif ($rimp != null) {$query .= "`Rim` = '$rimp'";}

it says Couldn't execute query. What am I doing wrong now???

 

P.S Thank you for helping me

 

[DarkKnight2011]

Hi,

 

Echo each of your queries before you execute them, then if they fail try running them directly in MySql either through phpmyadmin or direct. it will then tell you where your going wrong and you can also check that your building the query correctly.

 

To be honest this seems a little too complicated... could you post a screenshot or something of the form your using, im sure this can be simplified somewhat

 

Thanks

 

 

 

 

 

[Bravat]

I am working in OpenCart and I needed to modify search for my needs, and at the moment this is working code for 6 drop down boxes (i still need to do one more). I had to return values for drop down boxes because that way this works.

 

[attachment deleted by admin]

[fenway]

Sorry, but no one is going to sift to 16kB to figure out your issue -- I doubt it's that complex -- just narrow it down and post the relevant snippet.

[Bravat]

At the moment the code works quite well. When I get more knowledge about php and mysql i will try to narrow it down  .