Page 1 of 1

mysql query returns unwanted results

Posted: Mon Oct 21, 2013 7:41 am
by sectionLeader123
I have the following MySQL queries which allows the user to carryout an existing customer/company search. I have pagination applied to the results so the user can navigate through each page until they find the record that they are looking for. But for example if the user searched for a particular customer and if that customer is part of a company I don't want the user to see the company details because that's not what they are interested in.

So at the moment when I search for a customer the query works great, it only displays the customers details, for example if there are two people called john when I execute the search for john it returns two sets of results. But when I query by company name it displays customers details but I am only interested in the details for that particular company. Anyways here is my queries I talked about at the start:
Query to count the number of rows in each table:

Code: Select all

$sql = "SELECT COUNT(*) FROM Customers
	INNER JOIN Company ON (Company.COMP_ID = Customers.CUST_ID)
	WHERE
	(Customers.CUST_Forename LIKE '%$criteria%') OR (Customers.CUST_Surname LIKE '%$criteria%')
	OR (Customers.CUST_Postcode LIKE '%$criteria%') OR (Company.COMP_Name LIKE '%$criteria%')
	ORDER BY '$criteria'
	";
Query to get the data:

Code: Select all

$sql="	SELECT Customers.CUST_ID, Customers.CUST_Forename, Customers.CUST_Surname, 
	Customers.CUST_Email, Customers.CUST_Mobile, Customers.CUST_HomeNum, 
	Customers.CUST_AddressL1, Customers.CUST_AddressL2, Customers.CUST_AddressL3,
	Customers.CUST_Postcode, Company.COMP_ID, Company.COMP_Name, Company.COMP_Email, Company.COMP_PrimaryNum,
	Company.COMP_SecondaryNum, Company.COMP_AddressL1, Company.COMP_AddressL2, Company.COMP_AddressL3,
	Company.COMP_Postcode
	FROM Customers, Company
	WHERE
	(Customers.CUST_Forename LIKE '%$criteria%') OR (Customers.CUST_Surname LIKE '%$criteria%')
	OR (Customers.CUST_Postcode LIKE '%$criteria%') OR (Company.COMP_Name LIKE '%$criteria%')
	ORDER BY '$criteria'
	LIMIT $offset, $rowsperpage
	";
Also this problem is making me doubt the relationships I have between the customer and company table. So here is the structure of the two tables:

Code: Select all

CREATE TABLE Customers(
	CUST_ID int AUTO_INCREMENT NOT NULL,
	J_RefNum int NOT NULL,
	COMP_ID int NOT NULL,
	CUST_Forename varchar(20) DEFAULT NULL,
	CUST_Surname varchar(20) DEFAULT NULL,
	CUST_Email varchar(40) DEFAULT NULL,
	CUST_Mobile varchar(20) DEFAULT NULL,
	CUST_HomeNum varchar(20) DEFAULT NULL,
	CUST_AddressL1 varchar(20) DEFAULT NULL,
	CUST_AddressL2 varchar(20) DEFAULT NULL,
	CUST_AddressL3 varchar(20) DEFAULT NULL,
	CUST_Postcode varchar(20) DEFAULT NULL,
	PRIMARY KEY(CUST_ID),
	FOREIGN KEY(J_RefNum) REFERENCES Jobs(J_RefNum),
	FOREIGN KEY(COMP_ID) REFERENCES Company(COMP_ID)

CREATE TABLE Company(
	COMP_ID int AUTO_INCREMENT NOT NULL,
	J_RefNum int NOT NULL,
	CUST_ID int NOT NULL,
	COMP_Name varchar(50) DEFAULT NULL,
	COMP_Email varchar(50) DEFAULT NULL,
	COMP_PrimaryNum varchar(40) DEFAULT NULL,
	COMP_SecondaryNum varchar(40) DEFAULT NULL,
	COMP_AddressL1 varchar(30) DEFAULT NULL,
	COMP_AddressL3 varchar(30) DEFAULT NULL,
	COMP_AddressL2 varchar(30) DEFAULT NULL,
	COMP_Postcode varchar(20) DEFAULT NULL,
	PRIMARY KEY(COMP_ID),
	FOREIGN KEY(J_RefNum) REFERENCES Jobs(J_RefNum),
	FOREIGN KEY(CUST_ID) REFERENCES Customers(CUST_ID)

Re: mysql query returns unwanted results

Posted: Tue Oct 22, 2013 9:34 am
by sectionLeader123
got it