Number of rows in table are equal to the results squared

Question

I am in need of some assistance regarding table creation in PHP. In my code, my table is printing way too many times. For some reason, the output of the code is whatever the count of the number of results is ^2. So, if I have 4 results in the actual database, I have 16 results in the output. I would like to do a bit more research before posting in the forums, but I am new to php and have no idea where to begin.

//Count the number of rows returned
$count = mysql_num_rows($result);
echo $count;
//Table header
echo "<div><table id=\"tableheader\" bgcolor=\"#4382b5\">\n";
echo "<tr>\n";
echo "<td>&nbsp;3-4 ID:</td>\n";
echo "<td>&nbsp;First Name:</td>\n";
echo "<td>&nbsp;Last Name:</td>\n";
echo "<td>&nbsp;HCA:</td>\n";
echo "<td>&nbsp;File:</td>\n";
echo "<tr>";
echo "</table></div>";
if ($count !== 0) {
            while($row = mysql_fetch_array($result)) {
                echo "<div class=\"addform\"><form method='get' action=\"update.php\">\n";
                echo "  <input type=\"text\" value=\"".$row['tfid']."\" name=\"column1\">\n";
                echo "  <input type=\"text\" name=\"column2\" value=\"".$row['fname']."\"/>\n";
                echo "  <input type=\"text\" name=\"column3\" value=\"".$row['lname']."\"/>\n";
                echo "  <input type=\"text\" name=\"column4\" value=\"".$row['hca']."\"/>\n";
                echo "  <input type=\"text\" name=\"column5\" value=\"".$row['file']."\"/>\n";
                echo "  <input type=\"image\" src=\"images/update.png\" alt=\"Update Row\" class=\"update\" title=\"Update Row\">\n";
                echo "<a href=\"delete.php?tfid=".$row['tfid']."\"><img title='Delete Row' alt=\"Delete\" class='del' src='images/delete.png'/></a></form></div>\n";

            }
        echo "</table><br />\n";
    } else {
        echo "<b><center>NO DATA</center></b>\n";
    }

How are you obtaining data from DB ? PHP code seems fine, you can have incorrect SQL. — Martin Perry, Apr 21 '13 at 07:22
Please post (relevant) code blocks directly inside your question. — bestprogrammerintheworld, Apr 21 '13 at 07:22
I'll go out on a limb and guess that you're doing some sort of cross `JOIN` in your SQL query which multiplies the result. — deceze, Apr 21 '13 at 07:27
the result for echo $count; is 9. echo $result returns Resource id #6 (for some reason). — kayle, Apr 21 '13 at 07:33
Welcome to Stack Overflow! [Please, don't use `mysql_*` functions](http://stackoverflow.com/q/12859942/1190388) in new code. They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the red box? Learn about prepared statements instead, and use [tag:PDO] or [tag:MySQLi]. — hjpotter92, Apr 21 '13 at 07:35
the query is:
query = "SELECT staff.tfid, staff.lname, staff.fname, staff.hca, pic.file ". "FROM staff, pic"; — kayle, Apr 21 '13 at 07:41

Arup Rakshit · Accepted Answer · 2013-04-21T07:54:42.573

0

Based on your latest comment:

SELECT staff.tfid, staff.lname, staff.fname, staff.hca, pic.file ". "FROM staff, pic

you are using 2 tables staff and pic. But you do not have any joining criteria. You should have something like staff.id=pic.id

That is why you are getting Cartesian product of the two result-set i.e. squared result-set. 4 for staff table multiplied by 4 for pic table

Your query should be :

SELECT staff.tfid, staff.lname, staff.fname, staff.hca, pic.file ". " 
FROM staff, pic
where staff.id=pic.id

considering you have id as related attribute between these 2 tables.

edited Apr 21 '13 at 07:54

answered Apr 21 '13 at 07:43

Arup Rakshit

116,827
30
260
317

Thank you very much for the help. I really appreciate it. By the way, I am seeing my question being down-voted and I have no idea why. Could someone tell me the reason for this? I think I asked a legitimate question. – kayle Apr 21 '13 at 07:58
@kayle: there are several reasons why downvotes are given. The first thing to note is, don't worry about it, and keep posting! Votes are only guidance and not intended to offend. In this case, it could have been: (a) an external link to code was provided rather than including code here, which can give rise to _link rot_, (b) not providing the SQL in your code example, which would force someone to ask for it, (c) posting here even though you feel you should have done some more research first, (d) not debugging the problem. – halfer Apr 21 '13 at 09:42
That all said, treat downvotes as a learning experience, and of course do ask about the reason for downvotes in the future (it is considered ideal here for downvoters to explain their vote). Eventually it'll become clear how to ask, through trial and error, and you'll start asking questions in a way that the community prefers. – halfer Apr 21 '13 at 09:43

Number of rows in table are equal to the results squared

1 Answers1