0

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

enter image description here

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 "
      ";
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.

  • I added the code, for you to take a look, thank you for taking the time to help me – Matthew Sep 20 '18 at 12:55
  • The only suggestion is about the columns naming. There is no much sense to compare userid and pcid. User is not a PC, so there is meaningless to compare their IDs. If they are related they whould be named accordingly - like userid and ownerid. – Kondybas Sep 20 '18 at 14:23
0

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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.