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 " ";
Best Answer
Plain
JOIN a,b
generate the cartesian product where each row froma
is combined with each row ofb
, 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 byDISTINCT
there is no guarantee you'll get the proper row from the cartesian set. You have toJOIN .. ON
by some column but you have to provide your tables structure for further analysis.