Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Aug 2013
    Posts
    141
    Thanks
    22
    Thanked 0 Times in 0 Posts

    mysql query returns unwanted results

    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:
    $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:
    $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:
    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)
    Last edited by daniel0816; 10-21-2013 at 01:42 PM.

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    I don't understand the issue.
    Although the implicit join probably isn't the best thing to use, you are specifically asking for details on both a customer and a company based on the selection criteria. There is no join condition here, so you could potentially get unwanted results anyway since the where also doesn't do anything to control the results.

    I'm wondering if a union isn't more what you are looking to do. The customers (tip: try to keep these table names non-plural unless its designed for collection) and company are near identical structure; you can use a union and combine it with a hard coded property to tell whether the match came from a customers or a company record.

    Unions: for when you have similar recordsets that make sense to combine into a single resultset. There is no immediate relation between the two.
    Joins: for when you need to combine data from two tables which have a common relation, other derived or absolute.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #3
    Regular Coder
    Join Date
    Aug 2013
    Posts
    141
    Thanks
    22
    Thanked 0 Times in 0 Posts
    Am not sure as to why it displays the customer details as well as the company details when the user executes a query by the company name because surely it would therefore bring back the customer details as well as the company details when the user executes a query by customer name.

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    This is where I still don't follow you.
    The join would make sense if there's a relationship between customers and business in a one to one relation (or even many to one). The problem comes from the other side, if you query for information from a company, and paginate to only one page, than that company will match one customer for each resultset assuming the implicit join worksish.
    So if you want to find a customer and a relation to a company, than you want to join. But if you go the other way, the company will provide a result, and an unordered relation for a customer is provided.
    Are you looking to:
    1. Find a customer and all related company (many to one)
    2. Find a company and all related customer (many to one)
    3. Find either a customer or a company based on criteria (no relation)

    The first two require left/right joins; inner's are used for a guaranteed recordset while left/right join's always provide a result and then any possible relations.
    The last one requires a union since there is no immediate relation.

    You could also use all three combined I guess; that would just be a union of two joined queries. Again, the union is used when you are fetching from multiple locations but there is no immediate/distinct relationship between the data. This type of behaviour is more useful for something like an inheritable type where you have a parent table that subs to multiple child tables - each child can be unioned since they are "kinda" the same, but they cannot be logically joined since they are not related to each other.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,166
    Thanks
    75
    Thanked 4,338 Times in 4,304 Posts
    How about an example?

    Let's use slimmed down versions of your two tables:
    Code:
    CUSTOMERS table:
    CUST_ID -- CUST_surname
         37 -- Adam
         82 -- Adamson
    
    COMPANY table:
    COMP_ID -- COMP_name
        111 -- ABC Inc.
        222 -- XYZ Inc.
    
    SELECT CUST_ID, CUST_surname, COMP_ID, COMP_name
    FROM Customers, Company
    WHERE CUST_surname LIKE '%adam%'
    ORDER BY 'adam'
    
    Results:
    CUST_ID -- CUST_surname -- COMP_id -- COMP_name
         37 -- Adam         -- 111     -- ABC Inc.
         82 -- Adamson      -- 111     -- ABC Inc.
         37 -- Adam         -- 222     -- XYZ Inc.
         82 -- Adamson      -- 222     -- XYZ Inc.
    The point is, you did *NOT* make any connection between the primary key in CUSTOMERS and the foreign key in COMPANY.

    So you end up with what is know as the CARTESIAN PRODUCT of all the possible record matches! And the more records in each table the more results in the cartesian product. If you had 100 customer records and 50 company records, your full results would be 5000 records from the SELECT query!!

    As a minor point, your ORDER BY clause is nonsense. It is doing nothing, since all records will have the same value ('adam') to ORDER BY.

    Time to go back and study the very basic basic principles of database joins.

    Your database design is also badly badly flawed. It makes no sense to have foreign keys pointing both ways between the two tables. One table should be the master table, with a primary key, and the other table should be the dependent table, with a foreign key. So also time to go back and study the basic principles of database design.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Regular Coder
    Join Date
    Aug 2013
    Posts
    141
    Thanks
    22
    Thanked 0 Times in 0 Posts
    got it thanks, appreciate the input


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •