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 15 of 15
  1. #1
    Regular Coder
    Join Date
    Aug 2013
    Posts
    141
    Thanks
    22
    Thanked 0 Times in 0 Posts

    while loop repeating same data

    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

  • #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
    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'); 

  • Users who have thanked Fou-Lu for this post:

    daniel0816 (09-08-2013)

  • #3
    Regular Coder
    Join Date
    Aug 2013
    Posts
    141
    Thanks
    22
    Thanked 0 Times in 0 Posts
    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%')
    ";

  • #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
    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'); 

  • Users who have thanked Fou-Lu for this post:

    daniel0816 (09-08-2013)

  • #5
    Regular Coder
    Join Date
    Aug 2013
    Posts
    141
    Thanks
    22
    Thanked 0 Times in 0 Posts
    Thanks but it didnt work I added DISINCT in So now I have SELECT DISTINCT Customers.CUST_ID, etc.....

  • #6
    Regular Coder
    Join Date
    Aug 2013
    Posts
    141
    Thanks
    22
    Thanked 0 Times in 0 Posts
    Here is my full page of code maybe you can spot a problem.
    Thanks

    <html>
    <body>
    <?php
    error_reporting(E_ALL);
    $connect = mysql_connect("dbinfo", "dbinfo", "dbinfo");
    //select database
    mysql_select_db("dbinfo", $connect);

    $criteria = $_POST['sCriteria'];

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

    $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>
    <?php
    mysql_close();
    ?>
    </body>
    </html>

  • #7
    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
    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'); 

  • #8
    Regular Coder
    Join Date
    Aug 2013
    Posts
    141
    Thanks
    22
    Thanked 0 Times in 0 Posts
    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.

  • #9
    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
    Quote Originally Posted by daniel0816 View Post
    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'); 

  • #10
    Regular Coder
    Join Date
    Aug 2013
    Posts
    141
    Thanks
    22
    Thanked 0 Times in 0 Posts
    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.

  • #11
    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
    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'); 

  • #12
    Regular Coder
    Join Date
    Aug 2013
    Posts
    141
    Thanks
    22
    Thanked 0 Times in 0 Posts
    Sorry but can you show me an example?

  • #13
    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
    Yes, but it'll be a simple example:
    Code:
    SELECT Customers.CUST_ID, Customers.CUST_Forename, Customers.CUST_Surname, Jobs.J_RefNum, Jobs.J_Model, Jobs.J_ReceivedBy, Jobs.J_DateRec, Jobs.J_FaultDesc,
    Jobs.J_PassWinAdmin, Jobs.J_DataRecYN, Jobs.J_PowerSuppYN, JobStatus.JS_Status
    FROM Customers
    INNER JOIN Jobs ON Jobs.J_RefNum = Customers.CUST_ID
    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'); 

  • Users who have thanked Fou-Lu for this post:

    daniel0816 (09-09-2013)

  • #14
    Regular Coder
    Join Date
    Aug 2013
    Posts
    141
    Thanks
    22
    Thanked 0 Times in 0 Posts
    OK thanks for the example appreciate it

  • #15
    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
    You betcha. It's very much as you have now its just that you need to split up the implicit and create an explicit join with the proper properties that relate.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 


  •  

    Posting Permissions

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