problem with select distinct

[rpjd]

I have 5 entries in a table

$sql = "select count(distinct columnName) from table";
$result = mysql_result($sql);
$count = mysql_fetch_array($result);
echo $count[0];

The output is 5 as expected.

$sql = "select distinct columnName from table";
$result = mysql_result($sql);
$count = mysql_fetch_array($result);
echo $count[0];

the ouput is the first file name as expected, however

echo $count[1];

gives undefined offset 1, which does not make any sense.

Can anyone explain why the offset 1 is undefined if the count is 5?

 

 

[prestonwinfrey]

You are only fetching the first row into the array.

 

$rows = array();

while($row = mysql_fetch_array($result)) {
     
     $rows[] = $row;
}

 

[rpjd]

I used a for loop on $row, but all I got was

ArrayArrayArrayArrayArrayArrayArrayArrayArrayArrayArrayArrayArrayArrayArray

How do I show the filenames?

[prestonwinfrey]

$row['tableColumnName'];

[rpjd]

Nearly there if think.  I want to put the changes to each file into a seperate table, a table for each file.

I have this:

$AmendmentsQuery = "select File, Text from Changes  group by File order by File";
$Amendments= mysql_query($AmendmentsQuery);
$FilesQuery = "select distinct File from Changes order by File ASC";
$Files= mysql_query($FilesQuery);
$row = array();
while($rows = mysql_fetch_array($Files)) 
	{
	$row[] = $rows;
	while($changes = mysql_fetch_array($Amendments))
		{
		for($i=0; $i<count($row); $i++)
			{
			if($rows['File'] = $row[$i])
				{
				echo $changes['Reference'] . "</td><td>" . $changes['Text'];
				}
			}
		}
	}

Instead of printing the file name it prints array, it prints the text .  If I use this

while($refs = mysql_fetch_array($Reference)) 
	{
	$row[] = $rows;
	while($changes = mysql_fetch_array($Amendments))
		{
		echo $changes['Reference'] . "</td><td>" . $changes['Text'];
		}
	}

It prints both filename and text.  Can anyone explain why the code above won't print the file name? 

[rpjd]

I got it printing both the file name and text, but its all in the one table.  I want to compare the file name in the first (outer) while loop to the file name in the second (inner) while loop and create a new table when the file name changes, but not having any luck figuring out how. 

while($row = mysql_fetch_array($Files))

{

echo "<table border='1'>";

while($changes = mysql_fetch_array($Amendments))

      {

      echo "<tr><td>" . $changes['Reference'] . "</td><td>" . $changes['Text'];

      }

echo "</table>";

}

 

[btherl]

You will need to mysql_data_seek($Amendments, 0) before you run the inner loop each time.  http://php.net/manual/en/function.mysql-data-seek.php

 

Then you can reinstate the "if" that you had in your earlier code, to ensure that only changes related to the current file are displayed inside each table.

[rpjd]

Trying to implement mysql_data_seek() but keep getting an infinite loop.  Not entirely sure I'm implementing it correctly mind u. 

$row = array();

while($rows = mysql_fetch_array($Files))

{

$row[] = $rows;

                for($x=0; $x<mysql_num_rows($Amendments); $x++)

                    {

                    if(mysql_data_seek($Amendments, $x))

                      {

        while($changes = mysql_fetch_array($Amendments))

      {

      for($i=0; $i<count($row); $i++)

  {

    if($rows['File'] = $row[$i])

{

echo $changes['Reference'] . "</td><td>" . $changes['Text'];

}

  }

      }

        }

                    }

              }

[btherl]

Try this code:

 

while($rows = mysql_fetch_array($Files)) {
  $current_file = $rows['File'];
  print "<table><tr><td>";
  mysql_data_seek($Amendments, 0);
  while ($changes = mysql_fetch_array($Amendments)) {
    if ($changes['File'] == $current_file) {
      echo $changes['Reference'] . "</td><td>" . $changes['Text'];
    }
  }
  print "</td></tr></table>";
}

 

Make sure you are selecting Reference from the table as well if you are going to try to display it.

[rpjd]

thanks btherl, got it working.  Cheers.

[rpjd]

I don't know if you have tested it or not, but all the files have their own table except for table1.  Even though the data seek is set to 0 and I start the table immediately after the first while.  I put a border on the table just to make sure.

while($rows = mysql_fetch_array($Files)) 
      {
      print "<table border='1'>";
      $current_file = $rows['File'];
      mysql_data_seek($Amendments, 0);
      while ($changes = mysql_fetch_array($Amendments))
             {
             if ($changes['File'] == $current_file) 
               {
               echo "<tr><td>$changes['File'] . "</td><td>" . $changes['Text'] . "</td></tr>";
               }
             }
      print "</table>";
      }

[rpjd]

Just needed an additional table outside the outer while loop.