Joining mysql tables

Question

I am trying to join tables for a search form, and when I do the search exports the result x3 the output could you guys give me a hand please?

   $raw_results = mysqli_query($db,"SELECT * FROM inventoryuser, inventorypc
      WHERE (`firstname` LIKE '%".$query."%')
      OR (`lastname` LIKE '%".$query."%')
      OR (`department` LIKE '%".$query."%')
      ")
      or die(mysqli_error($db));

The output

I need to search inventorypc for pc_model and pc_serial that's under a different table.

My tables are:

CREATE TABLE `inventoryuser` (
  `userid` INT(11) NOT NULL AUTO_INCREMENT,
  `firstname` VARCHAR(30) NOT NULL,
  `lastname` VARCHAR(30) NOT NULL,
  `department` VARCHAR(30) NOT NULL,
PRIMARY KEY (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `inventorypc` (
  `pcid` INT(11) NOT NULL AUTO_INCREMENT,
  `pc` VARCHAR(30) NOT NULL,
  `jcid` VARCHAR(30) NOT NULL,
  `job_number` VARCHAR(30) NOT NULL,
  `product_id` VARCHAR(30) NOT NULL,
  `pc_model` VARCHAR(30) NOT NULL,
  `pc_serial` VARCHAR(30) NOT NULL,
  `software` VARCHAR(30) NOT NULL,
PRIMARY KEY (`pcid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And I the code I have is

  $query = $_GET['query'];
  // gets value sent over search form

  $min_length = 2;
  // you can set minimum length of the query if you want

  if(strlen($query) >= $min_length){ // if query length is more or equal minimum length then

      $query = htmlspecialchars($query);
      // changes characters used in html to their equivalents, for example:  0){ // if one or more rows are returned do following

          echo "
              
                
                  
                    
                    First Name
                    Last Name
                    Company Laptop
                    Company Mobile
                    Department
                    Actions
                  
                
          ";

              while($results = mysqli_fetch_array($raw_results)){
              // $results = mysql_fetch_array($raw_results) puts data from database into array, while it's valid it does the loop


              echo "
                    
                    
                      
                      ".$results['firstname']."
                      ".$results['lastname']."
                      ".$results['pc']."
                      ".$results['mobile']."
                      ".$results['department']."
                      
                        View
                        Edit
                        Delete
                      
                    
                  
                ";
              }

          }
          else{ // if there is no matching rows do following
              echo "No results";
          }

      }
      else{ // if query length is less than minimum
          echo "Minimum length is ".$min_length;
      }

      echo "
      ";

Answer 1

Plain JOIN a,b generate the cartesian product where each row from a is combined with each row of b, despite of related they are or no. There are not only duplicates for certain user but also the rows for PCs not related to the certain user. If you restrict the output by DISTINCT there is no guarantee you'll get the proper row from the cartesian set. You have to JOIN .. ON by some column but you have to provide your tables structure for further analysis.

Answer 2

Ok I think I worked it out thanks to your guidance Kondybas!

  $raw_results = mysqli_query($db,"SELECT * FROM inventoryuser AS A
      LEFT JOIN inventorypc AS B ON b.pcid = a.userid
          WHERE (`firstname` LIKE '%".$query."%')
          OR (`lastname` LIKE '%".$query."%')
          OR (`department` LIKE '%".$query."%')
          OR (`pc_serial` LIKE '%".$query."%')
      ")

Does this make sence to you?