PHP echo duplicate rows when none exist

Question

<html>
<head>
<title>Test</title>
<link rel="stylesheet" type="text/css" href="style.css"> 
</head>
<body>
    <?php 
       $username ="matt";
       $pass = "bs12kfj";
       $db = "mytest";
       $tbl = "test2";
       mysql_connect(localhost,$username,$pass);
       mysql_select_db($db) or die( "Unable to select database");
       $res = mysql_query("SELECT * FROM test2;");
       $num = mysql_numrows($res);
       echo "<table border='2'><tr>The Peeps</tr>"; 
       while ($r = mysql_fetch_array($res)) {
         echo "<tr>";
         foreach($r as $rs){
                     echo "<td>$rs</td>";
                     }
         echo "</tr>";
       }
       ?>

</body>

This code executes without error but the output contains a duplicate of every column of every row like so.

<table border='2'><tr>The Peeps</tr> <tr><td>"matt"</td><td>"matt"</td><td>"phillips"</td><td>"phillips></td><td>"[email protected]"</td><td>"[email protected]"</td><td>20</td><td>20</td></tr><tr><td>"paul"</td><td>"paul"</td><td>"franklin"</td><td>"franklin"</td><td>"[email protected]"</td><td>"[email protected]"</td><td>30</td><td>30</td></tr><tr><td>"steve"</td><td>"steve"</td><td>"jobs"</td><td>"jobs"</td><td>"[email protected]"</td><td>"[email protected]"</td><td>23</td><td>23</td>

I don't understand why each column is duplicated when there is only a single <td></td> tag in the echo statement.
I also checked the db and the table does not contain duplicate entries.

swap out mysql_fetch_array and replace with mysql_fetch_assoc (mysql_fetch_array returns both numeric AND associative if you don't specify otherwise) — OneNerd
– OneNerd, Commented Dec 22, 2009 at 17:20

Victor Nicollet · Accepted Answer · 2009-12-22 17:09:42Z

6

This is what mysql_fetch_array does if you don't provide a second argument: gets you an associative array where the value is mapped to both the column name and the column index.

You can provide MYSQL_ASSOCas a second argument to have only the column name (or use mysql_fetch_assoc).

answered Dec 22, 2009 at 17:09

Victor Nicollet

24.7k4 gold badges62 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

zombat · Accepted Answer · 2009-12-22 17:10:23Z

You are probably fetching your MySQL result array in the default mode of MYSQL_BOTH.

Try this:

while ($r = mysql_fetch_array($res,MYSQL_ASSOC)) {
             echo "<tr>";
     foreach($r as $rs){
                 echo "<td>$rs</td>";
                 }
             echo "</tr>";
       }

If you don't specify the second argument to mysql_fetch_array(), then MYSQL_BOTH is used, and you end up with both an associative array index of values, and a numeric array index, like this:

array(0 => 'field 1 val', 'field1_name'=> 'field 1 val',
      1 => 'field 2 val', 'field2_name'=> 'field 2 val', etc);

Specifying either MYSQL_ASSOC or MYSQL_NUM will only retrieve either the associative values or the numerically indexed values respectively.

Community · Accepted Answer · 2023-11-17 20:38:18Z

1

Change

while ($r = mysql_fetch_array($res)) {

to

while ($r = mysql_fetch_array($res, MYSQL_NUM)) {

or

while ($r = mysql_fetch_array($res, MYSQL_ASSOC)) {

The default value for the 2nd argument to mysql_fetch_array() is MYSQL_BOTH which gives you back an array indexed numerically and associatively. Alternatively, you could use mysql_fetch_row() (returns a numerically indexed array) or mysql_fetch_assoc() (returns an associative array) instead of mysql_fetch_array().

edited Nov 17, 2023 at 20:38

CommunityBot

11 silver badge

answered Dec 22, 2009 at 17:10

Asaph

163k25 gold badges204 silver badges204 bronze badges

2 Comments

Anti Veeranna Over a year ago

MYSQL_NUM will work with the code that controlfreak123 has, but it generally is a bad approach, because you should never assume the select fields come out of the database in the same order. The ordering can change, if you change your CREATE TABLE a bit, or when you use SELECT field1, field2 instead of *

Asaph Over a year ago

@Anti Veeranna: How is MYSQL_NUM any different than MYSQL_ASSOC if you iterate over the results using foreach?

Anti Veeranna · Accepted Answer · 2009-12-22 17:10:59Z

0

Easy.

If your SQL query is

SELECT foo FROM bar

then mysql_fetch_array returns 2 results. One with numeric index, one with the field name. Add a print_r($r) into your code to see the behaviour.

Solution? Use mysql_fetch_assoc

answered Dec 22, 2009 at 17:10

Anti Veeranna

11.6k4 gold badges45 silver badges63 bronze badges

Collectives™ on Stack Overflow

PHP echo duplicate rows when none exist

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related