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'
";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
";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)