while loop repeating same data

[daniel0816]

I have a MySQL query which gets the required data ok. But when I submit the results into the HTML table it displays the data ok but it repeats it over and over.

Here is my code:

 

$result=mysql_query($sql) or die(mysql_error());
if($result)
{
 ?>
<table name='details' border='2'>
<thead>
 <tr>
  <th>Customer ID</th>
  <th>Forename</th>
  <th>Surname</th>
  <th>Email</th>
  <th>Mobile Number</th>
  <th>Home Number</th>
  <th>Address Line 1</th>
  <th>Address Line 2</th>
  <th>Address Line 3</th>
  <th>Postcode</th>
  <th>Job Reference Number</th>
  <th>Manufacturer</th>
  <th>Model</th>
  <th>Operating System</th>
  <th>Received By</th>
  <th>Date Received</th>
  <th>Fault Description</th>
  <th>Password - Windows Admin</th>
  <th>Data Recovery?</th>
  <th>Power Supply?</th>
  <th>Job Status</th>
 </tr>
</thead>
<tbody>
<?php
}
while($row=mysql_fetch_array($result))
{
 echo "<tr>";
 
   echo '<td>' . $row['CUST_ID'] . '</td>';
   echo '<td>' . $row['CUST_Forename'] . '</td>';
   echo '<td>' . $row['CUST_Surname'] . '</td>';
   echo '<td>' . $row['CUST_Email'] . '</td>';
   echo '<td>' . $row['CUST_Mobile'] . '</td>';
   echo '<td>' . $row['CUST_HomeNum'] . '</td>';
   echo '<td>' . $row['CUST_AddressL1'] . '</td>';
   echo '<td>' . $row['CUST_AddressL2'] . '</td>';
   echo '<td>' . $row['CUST_AddressL3'] . '</td>';
   echo '<td>' . $row['CUST_Postcode'] . '</td>';
   echo '<td>' . $row['J_RefNum'] . '</td>';
   echo '<td>' . $row['MANU_Name'] . '</td>';
   echo '<td>' . $row['J_Model'] . '</td>';
   echo '<td>' . $row['OS_Name'] . '</td>';
   echo '<td>' . $row['J_ReceivedBy'] . '</td>';
   echo '<td>' . $row['J_DateRec'] . '</td>';
   echo '<td>' . $row['J_FaultDesc'] . '</td>';
   echo '<td>' . $row['J_PassWinAdmin'] . '</td>';
   echo '<td>' . $row['J_DataRecYN'] . '</td>';
   echo '<td>' . $row['J_PowerSuppYN'] . '</td>';
   echo '<td>' . $row['JS_Status'] . '</td>';
 
 
 echo "</tr>";
 
}

?>
</tbody>
</table>

 

Thanks Again

Edited September 6, 2013 by daniel0816

[akphidelt2007]

How do you know it's getting the required data? Are you basing this off the fact it's not generating an error or have you actually checked what data it's returning?

[daniel0816]

Well if I didnt know what data it was returning do you think I would have posted with the problem of repeating the data over and over?

[mac_gyver]

there's nothing about the code you posted that would cause incorrectly repeated data to be output. its just looping over the rows from the result set. therefore, either your query is returning repeated data, some of your data stored in the database table actually contains the repeated data, the code you posted is inside of a loop, or the code you posted actually contains more code that what you showed.

 

we are not you, nor are we standing right next to you. we only see the information you supply in your post. we don't know what your data is, what you saw that leads you to belove believe there's repeated output, or what the actual expected output is.

 

if you want someone in a forum to help, you must supply information that they would need to be able to help you.

Edited September 8, 2013 by mac_gyver

[daniel0816]

Ok apologies here is my query to go along with it. By the way there are only two sets of data that are stored in the database, really dont know why it repeats them.

Thanks again.

 

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

[mac_gyver]

you are joining every row in each table to every row in all the other tables, because you are not specifying any relationship between the tables in the query.

 

in one of your previous threads for this task, the relationship between the tables was mentioned. do you actually have any columns in the tables that relate a row in one table to a row in another table? if so, you must use that relationship in the JOIN's to match up the related information. if not, these tables don't belong in the same query, especially a query using JOINs.

 

---

 

i also recommend that you clean up your column names/query (there's no need for you to type a wall of code like that) by doing two things -

 

1) in any table, your columns don't need a prefix with a portion of the table name. you know what table you are using at any time in any query. this would require that you rename your table columns, but will reduce the amount of typing and clutter in all your queries.

 

2) use table alias names in your query.

 

here's the same query with these two changes (with the existing joins/conditions) -

$sql="SELECT c.ID, c.Forename, c.Surname, c.Email, c.Mobile, c.HomeNum, c.AddressL1, c.AddressL2,
    c.AddressL3, c.Postcode, j.RefNum, m.Name as m_Name, j.Model, os.Name as os_Name, j.ReceivedBy, j.DateRec, j.FaultDesc,
    j.PassWinAdmin, j.DataRecYN, j.PowerSuppYN, js.Status

    FROM Customers c, Jobs j, Manufacturers m, OperatingSystems os, JobStatus js

    WHERE (c.ID LIKE '%$criteria%') OR ( c.Forename LIKE '%$criteria%') OR
        (c.Surname LIKE '%$criteria%') OR (c.Email LIKE '%$criteria%') OR
        (c.Mobile LIKE '%$criteria%') OR (c.HomeNum LIKE '%$criteria%') OR
        (c.AddressL1 LIKE '%$criteria%') OR (c.AddressL2 LIKE '%$criteria%') OR
        (c.AddressL3 LIKE '%$criteria%') OR (c.Postcode LIKE '%$criteria%') OR
        (j.RefNum LIKE '%$criteria%') OR (m.Name LIKE '%$criteria%') OR
        (j.Model LIKE '%$criteria%') OR (os.Name LIKE '%$criteria%') OR
        (j.ReceivedBy LIKE '%$criteria%') OR (j.DateRec LIKE '%$criteria%') OR
        (j.FaultDesc LIKE '%$criteria%') OR (j.PassWinAdmin LIKE '%$criteria%') OR
        (j.DataRecYN LIKE '%$criteria%') OR (j.PowerSuppYN LIKE '%$criteria%')
";