CODEWITHSUNDEEP
php
Alpesh Trivedi
Alpesh Trivedi

Reputation: 979

Duplicate data displayed in PHP page

I am working on a project with PHP. I have a php page which displays single row two times in web page. Whereas there is no duplicate data in the table of database. Plz help me out !!!!! Here is two tables and php Code...

maincat_desktop
--------------------
maincat_id  maincat_nm
    1       Desktop
    2       Leptop

**subcat_desktop**
--------------------
subcat_id   maincat_id    subcat_nm1    subcat_disc subcat_amt  subcat_prod_img subcat_nm
        1   1             Acer123           ABCEDFG 25000       NULL            Acer
        2   1             Dell123           ABCEDFG 8000        NULL            Dell
        3   1             ompaq123          ABCEDFG 12000       NULL            Compaq
        4   1             Lenovo123         ABCEDFG 32000       NULL            Lenovo
        5   1             HP123             ABCEDFG 18000       NULL            HP
        6   1             Toshiba123        ABCEDFG 45000       NULL            Toshiba

<html>
<?php

    $query = mysql_query("select c.maincat_nm,c.maincat_id,s.subcat_nm1,s.subcat_disc,s.subcat_id,s.subcat_amt,s.subcat_prod_img from maincat_desktop c, subcat_desktop s where s.subcat_nm like '%Acer%'");

        while ($row = mysql_fetch_array($query))
        {
            if(mysql_affected_rows()<0)
            {
                echo "No data found";
            }
            else
            {
                echo "<table border=0 width=80% style='border-bottom:1px solid grey' align=center>";
                echo "<tr height='80px'>";
                echo "<td rowspan=20 width='80px'><img src='admin/upload/".$row['subcat_prod_img']."' width='70' heigth='70' style='vertical-align:middle'>";
                echo "</td>";
                echo "<td>";

                echo "<a href='acer_desktop_discription.php?subcat_id=$row[subcat_id]' style=color:blue><b>".$row['subcat_nm1']."</b></a>";
                echo "</td>";
                echo "</tr>";
                echo "</table>";
            }
        }

            echo "<br>";

?> </html>

Upvotes: 1

Views: 2348

Answers (4)

Marc B
Marc B

Reputation: 360652

Notable problems:

a) Your query is incorrect. You're doing a join operation (selecting from multiple tables), but not specifying how to relate the tables. So MySQL is doing a cartesian join, which produces all possible results, which you then do some minor filtering with the 'like' clause. You should have:

SELECT 
  c.maincat_nm, c.maincat_id, s.subcat_nm1, s.subcat_disc, s.subcat_id, s.subcat_amt, s.subcat_prod_img
FROM
  maincat_desktop c, subcat_desktop s
WHERE
  (c.maincat_id = s.parent_cat_id) AND (s.subcat_nm like '%Acer%');
   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ -- the join clause

If you have 10 maincats and 50 subcats, the cartesian join will produce 500 candidate rows, which are then reduced to just the ones where the subcat_nm contains 'Acer' - in your case, that's two rows. By specifying the explicit join clause, you force MySQL to consider only the main cat/sub cats where there actually is a main/sub relationship.

b) You have to check if there's any resulting rows BEFORE you try to fetch: 

$results = mysql_query(...) or die(mysql_error());

if (mysql_num_rows($results) == 0) {
   echo "No results";
} else {
   while($row = mysql_fetch_assoc($results)) {
      ...
   }
}

Also, mysql_affected_rows() is for insert/update queries. mysql_num_rows() is for select queries, and tells you how many rows are available to be fetched. This will always be 0 or more - a MySQL query result will never return a negative number of rows. It's either 0 (no rows), a positive number (1 or more rows), or a boolean FALSE (error occured).

Upvotes: 2

KilZone
KilZone

Reputation: 1615

There are several things that are wrong, but most important you are selecting things wrong. You are selecting several 'columns' from maincat_desktop but over all rows (hence, no where-clause for that table) and you are also selecting something from subcat_desktop (this one is limited). Due to the fact that you have two rows in your maincat_desktop table, you get the results twice. You should use Greenisha query instead (or Marc B for a better explination of cartesian joins).

Also you are using mysql_affected_rows() which is not working here, you ment to say mysql_num_rows(). More important the place of mysql_num_rows() is wrong. If there are no results, you can't go into the while loop and you will be left with an error (nothing displayed) that you have no data, you need to do something like:

if(mysql_num_rows() > 0)
{
    while(...)
    { /* normal code */ }
}
else
{
    // No results;
}

If you fix these errors you should be left with a working code which displays only one line.

Upvotes: 1

marcelog
marcelog

Reputation: 7180

mysql_affected_rows() is for insert/update operations. use mysql_num_rows(). also, when no results, this will be =0 and not < 0. and you should do the if before entering the loop. so first check mysql_num_rows() and THEN do the fetch. an right after executing the query, check if the result is FALSE (and if this is the case, check mysql_error() to get the errors).

Upvotes: 0

Greenisha
Greenisha

Reputation: 1437

modify select to 

select c.maincat_nm,c.maincat_id,s.subcat_nm1,s.subcat_disc,s.subcat_id,s.subcat_amt,s.subcat_prod_img from maincat_desktop c, subcat_desktop s where c.maincat_id=s.maincat_id and s.subcat_nm like '%Acer%'

a little explanation: when you select from table1,table2 it's like table1 inner join table2 so you have to mention an additional clause

Upvotes: 3

Related Questions