If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Enjoy an ad free experience by logging in. Not a member yet? Register.

What do you mean it repeats it? That should iterate the resultset once and only once ( while($row=mysql_fetch_array($result)) is what does that and there is no re-assignment of $row or reset of $result).
If you are seeing multiple records of the same data, that would likely be the results of a joined query which appears to replicate when it finds multiple matches on joins. You can fix that by using a DISTINCT query.

PHP Code:

header('HTTP/1.1 420 Enhance Your Calm');

Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

Users who have thanked Fou-Lu for this post:

Yea I am seeing repeats of the same data. I cant see anything wrong my query tbh but here it is anyway.
Thanks

$criteria = $_POST['sCriteria'];

$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, Jobs.J_RefNum, Manufacturers.MANU_Name,
Jobs.J_Model, OperatingSystems.OS_Name, Jobs.J_ReceivedBy, Jobs.J_DateRec, Jobs.J_FaultDesc,
Jobs.J_PassWinAdmin, Jobs.J_DataRecYN, Jobs.J_PowerSuppYN, JobStatus.JS_Status
FROM Customers, Jobs, Manufacturers, OperatingSystems, JobStatus
WHERE ( Customers.CUST_ID LIKE '%$criteria%') OR ( Customers.CUST_Forename LIKE '%$criteria%') OR
(Customers.CUST_Surname LIKE '%$criteria%') OR (Customers.CUST_Email LIKE '%$criteria%') OR
(Customers.CUST_Mobile LIKE '%$criteria%') OR (Customers.CUST_HomeNum LIKE '%$criteria%')
OR (Customers.CUST_AddressL1 LIKE '%$criteria%') OR (Customers.CUST_AddressL2 LIKE '%$criteria%') OR
(Customers.CUST_AddressL3 LIKE '%$criteria%') OR (Customers.CUST_Postcode LIKE '%$criteria%') OR

(Jobs.J_RefNum LIKE '%$criteria%') OR (Manufacturers.MANU_Name LIKE '%$criteria%')
OR (Jobs.J_Model LIKE '%$criteria%') OR
(OperatingSystems.OS_Name LIKE '%$criteria%') OR (Jobs.J_ReceivedBy LIKE '%$criteria%') OR
(Jobs.J_DateRec LIKE '%$criteria%') OR
(Jobs.J_FaultDesc LIKE '%$criteria%') OR (Jobs.J_PassWinAdmin LIKE '%$criteria%')
OR (Jobs.J_DataRecYN LIKE '%$criteria%') OR (Jobs.J_PowerSuppYN LIKE '%$criteria%')
";

You have implicit joins. If you only select partial records (which is likely), than what is happening is that for each of the properties that are unique within any of the tables provided it will provide an entire record with only that data changed. Problem is you don't see what that data is since its not selected.
Simply add a DISTINCT to the query selection and it should resolve that.

PHP Code:

header('HTTP/1.1 420 Enhance Your Calm');

Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

$sql="SELECT DISTINCT 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, Jobs.J_RefNum, Manufacturers.MANU_Name,
Jobs.J_Model, OperatingSystems.OS_Name, Jobs.J_ReceivedBy, Jobs.J_DateRec, Jobs.J_FaultDesc,
Jobs.J_PassWinAdmin, Jobs.J_DataRecYN, Jobs.J_PowerSuppYN, JobStatus.JS_Status
FROM Customers, Jobs, Manufacturers, OperatingSystems, JobStatus
WHERE (Customers.CUST_ID LIKE '%$criteria%') AND (Customers.CUST_ID = Jobs.J_RefNum)
OR (Customers.CUST_Forename LIKE '%$criteria%') OR
(Customers.CUST_Surname LIKE '%$criteria%') OR (Customers.CUST_Email LIKE '%$criteria%') OR
(Customers.CUST_Mobile LIKE '%$criteria%') OR (Customers.CUST_HomeNum LIKE '%$criteria%')
OR (Customers.CUST_AddressL1 LIKE '%$criteria%') OR (Customers.CUST_AddressL2 LIKE '%$criteria%') OR
(Customers.CUST_AddressL3 LIKE '%$criteria%') OR (Customers.CUST_Postcode LIKE '%$criteria%') OR

(Jobs.J_RefNum LIKE '%$criteria%') AND (Jobs.J_RefNum = Customers.CUST_ID)
OR (Manufacturers.MANU_Name LIKE '%$criteria%')
OR (Jobs.J_Model LIKE '%$criteria%') OR
(OperatingSystems.OS_Name LIKE '%$criteria%') OR (Jobs.J_ReceivedBy LIKE '%$criteria%') OR
(Jobs.J_DateRec LIKE '%$criteria%') OR
(Jobs.J_FaultDesc LIKE '%$criteria%') OR (Jobs.J_PassWinAdmin LIKE '%$criteria%')
OR (Jobs.J_DataRecYN LIKE '%$criteria%') OR (Jobs.J_PowerSuppYN LIKE '%$criteria%')
";

The full code isn't of much relevance; table structure and data would be more useful.
I would have assumed the DISTINCT would work with the implicit join, but perhaps I'm wrong. I better move this to the mysql forum instead; hopefully one of the sql wizards can point out the cause.

PHP Code:

header('HTTP/1.1 420 Enhance Your Calm');

Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

When I query the database manually using SELECT * from table_name it shows that there are only two sets of data stored in the DB. Therefore when I run this query it should not repeat the data over and over, it should only display two sets of data in the HTML table.

When I query the database manually using SELECT * from table_name it shows that there are only two sets of data stored in the DB. Therefore when I run this query it should not repeat the data over and over, it should only display two sets of data in the HTML table.

Yes, but you have to remember that while you are joining tables, foreach of the corresponding records between tables you will pull a result. So if you have one lhs table with 2x records and a rhs table with 12x records, that will be 1 record for each corresponding match, or 24x records in total.
This is why I would have expected the distinct to work. That of course only applies to an *entire* row selection, so every property would have to match to be qualified as a duplicate. If even a single property is different than the row would be unique.

PHP Code:

header('HTTP/1.1 420 Enhance Your Calm');

Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

The idea for the query is that when the user keys in a criteria into the textbox sCriteria it pulls all data from the database associated with that criteria.

For example if the name Joe is submitted. The query gets Joe's details id, address, phone number etc but it also gets the job details associated with Joe, ref number, manufacturer etc. For this the id and ref number for joe is the same.

Wait a sec. If you have implicit inner joins, do they use any of the constraints? I'm thinking. . . no.
Implement the INNER JOIN or LEFT JOIN logic and force them to specific properties instead of the implicit.

PHP Code:

header('HTTP/1.1 420 Enhance Your Calm');

Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

You would need to issue a proper JOIN between each table that you want to associate, but the records must be of a compatible type and make logical sense to associate them with.

I inferred that relationship based on the where criteria. Inner joins require a match on the ON properties between both tables to be qualified for a match in the query. A LEFT join would require all records from the LHS table (the Customers in this example), and any associated in the RHS table (the jobs). And a RIGHT join would be the reverse.

PHP Code:

header('HTTP/1.1 420 Enhance Your Calm');

Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)