SELECT...FROM...WHERE...LIKE issues when combining multiple inputs with multiple columns when selecting output

Question

I am new to PHP programming, and I am working on my first program.. This is for the beginnings of a warehouse management system, so I am needing to be able to query part numbers, EANs, item names, etc. Occasionally, all info will be known, or an exact match when input by the user, but sometimes the user will only know part of an EAN, or want to check all items with a similar name, so the results need to be LIKE the input given by the user.

Everything works okay when the user inputs info in only one input in the form (ex. ONLY the entire or portion of a part number is added to the 'partnumber' input, and it correctly returns relevant rows with the info query'd), but when there are multiple inputs added by the user to query table (ex. user inputs data into 'partnumber' AND 'EAN' input), then the result ends up being every item from the table.. same as 'SELECT * FROM table;'.

Is there a way to query and output data from multiple inputs over multiple columns? I have searched this everywhere, but have yet to find an answer relevant to my need... (or at least one with my level of understanding). Any help in the right direction would be great!

SQL query's I have used so far:

$query = "
SELECT partNumber
     , EAN
     , UPC
     , itemDescription
     , SNFlag
     , idClass
     , idType 
  FROM productinfo_table 
 WHERE partNumber LIKE '$partNumber' 
    OR EAN LIKE '$EAN' 
    OR itemDescription LIKE '$itemDescription' 
    OR SNFlag LIKE '$SNFlag'
";

And:

$query = "
SELECT partNumber
     , EAN
     , UPC 
     , itemDescription
     , SNFlag
     , idClass
     , idType 
  FROM productinfo_table 
 WHERE (partNumber,EAN,itemDescription,SNFlag) IN LIKE ('$partNumber','$EAN','$itemDescription','$SNFlag')";

Among a few others...

testissue.php

<?php //testissue.php
//establish connection
require_once "login.php";
$db_server = mysqli_connect($db_hostname,$db_username,$db_password,$db_database);
if(!$db_server) printf('Error connecting to database: %s',mysqli_error($db_server));

//if loop to acquire variables
//if all post array elements are NOT empty
if(!empty($_POST['partNumber']) ||
   !empty($_POST['EAN']) ||
   !empty($_POST['itemDescription']) ||
   !empty($_POST['SNFlag'])) {
    //if partNumber is not empty
    if(!empty($_POST['partNumber'])) {
        $partNumber = '%';
        $partNumber .= $_POST['partNumber'];
        $partNumber .= '%';
    } else {
        $partNumber = '';
    }
    //if EAN is not empty
    if(!empty($_POST['EAN'])) {
        $EAN = '%';
        $EAN .= $_POST['EAN'];
        $EAN .= '%';
    } else {
        $EAN = '';
    }
    // if itemDescription is not empty
    if(!empty($_POST['itemDescription'])) {
        $itemDescription = '%';
        $itemDescription .= $_POST['itemDescription'];
        $itemDescription .= '%';
    } else {
        $itemDescription = '';
    }
    //if SNFlag is not empty
    if(!empty($_POST['SNFlag'])) {
        $SNFlag = '%';
        $SNFlag .= $_POST['SNFlag'];
        $SNFlag .= '%';
    } else {
        $SNFlag = '';
    }
    //echo variables to confirm set, for testing
    echo "$partNumber<br/>";
    echo "$EAN<br/>";
    echo "$itemDescription<br/>";
    echo "$SNFlag<br/>";

    //query to pull data to insert into table rows
    //$query = "SELECT partNumber,EAN,UPC,itemDescription,SNFlag,idClass,idType FROM productinfo_table WHERE partNumber LIKE '$partNumber' OR EAN LIKE '$EAN' OR itemDescription LIKE '$itemDescription' OR SNFlag LIKE '$SNFlag'";
    $query = "SELECT partNumber,EAN,UPC,itemDescription,SNFlag,idClass,idType FROM productinfo_table WHERE (partNumber,EAN,itemDescription,SNFlag) IN LIKE ('$partNumber','$EAN','$itemDescription','$SNFlag')";
    $result = mysqli_query($db_server,$query);
    if(!$result) printf("Error querying database: %s",mysqli_error($db_server));
    $rows = mysqli_num_rows($result);   
} 
//if all post array elements ARE empty
else {
    echo "empty post array";
    $rows = '';
}
//echo form input
echo <<<HERE
<pre>
<form action='testissue.php' method='post'>
  Part No. <input type='text' name='partNumber' />
       EAN <input type='text' name='EAN' />
Item Desc. <input type='text' name='itemDescription' />
   SN Flag <input type='text' name='SNFlag' />
           <input type='submit' value='Search' />
</form>
</pre>
HERE;
//print post array to confirm set values, for testing
echo "<br/>";
print_r($_POST);
echo "<br/><br/>";
//echo table for output
echo <<<HERE
<table>
<tr><th>Part No.</th> <th>EAN</th> <th>UPC</th> <th>Item Desc.</th> <th>SN Flag</th> <th>Class ID</th> <th>Type ID</th></tr>
HERE;
// for loop function to populate items in table
for($a=0;$a<$rows;++$a){
    echo "<tr>";
    $col = mysqli_fetch_row($result);
    for($b=0;$b<7;++$b) echo "<td>$col[$b]</td>";
    echo "</tr>";
}
echo "</table>";
//close connection
mysqli_close($db_server);

?>

Please let me know if you need anything else to help or offer any improvements.

Thanks a lot!

Answer 1

$sql="SELECT *FROM table where partNumber = '$partnumber'order by id ASC";

Answer 2

You need to use AND clause rather than OR.

AND will give you rows that match all conditions in WHERE clause

OR will return rows that match any of the conditions in WHERE clause

$query = "
SELECT partNumber
    , EAN
    , UPC
    , itemDescription
    , SNFlag
    , idClass
    , idType 
FROM productinfo_table 
WHERE partNumber LIKE '$partNumber' 
    AND EAN LIKE '$EAN' 
    AND itemDescription LIKE '$itemDescription' 
    AND SNFlag LIKE '$SNFlag'
";

Note: Please note the suggestions provided in the comments to prevent SQL injection