Database Issue - Displaying Multiple Rows as one Result

obrienkev

Hi all,

I am using PHP and SQL to display results of database query.

On the html page, each row of data sholuld be displayed on html table rows.

One Database Table(tableA) contains many rows all related to one ID from another Database table(table😎.

My problem is trying to get all the results from tableA to display in one table row on the webpage.

At the moment, the multiple table results from tableA causes tableB data to be displayed repeatedly.

How do I solve this? Thanks and Happy Christmas.


// perform query
$query = "SELECT * ".
	       "FROM tableB B ".
	       "LEFT JOIN tableA A ".
	       "ON B.ID = A.ID";

$result = mssql_query($query) or die('Select Error');
$num_results = mssql_num_rows($result);

if ($num_results > 0)
{
	echo '<table> '.
	'<tr><th>SECURITY ID</th><th>EX DATE</th> '.
        '<th>SUBMIT QUERY</th></tr>';
 	 while ($row = mssql_fetch_array($result))
	 {
		echo '<tr>'.
		'<td>'.$row['security_ID'].'</td> '.
		'<td>'.$row['ex_date'].'</td> '.
		'<td><a href="submit_query/index.php?id='.$row['corporate_ID'].'" > '.
		'<img src="../images/email.gif" title="Submit Query" /> '.
		'</a></td> '.
		'</tr>';
	 }
	echo '</table>';

} // end if

MitchEvans

Do SELECT * from tableA!

or you could:

where the rows are shown put .tablea after them

eg.

id.tableA

Piranha

I don't understand exactly what you mean, but this might get you a basic idea:

// perform query
$query = "SELECT * ".
	       "FROM tableB B ".
	       "LEFT JOIN tableA A ".
	       "ON B.ID = A.ID".
	       "ORDER BY B.ID";     // This make sure that all rows in table A that is related to the same row in table B comes after each other

$result = mssql_query($query) or die('Select Error');
$num_results = mssql_num_rows($result);

if ($num_results > 0)
{
	echo '<table> '.
	'<tr><th>SECURITY ID</th><th>EX DATE</th> '.
        '<th>SUBMIT QUERY</th></tr>';
        $oldid = "";      // oldid is used to see if it is a new row or not
 	 while ($row = mssql_fetch_array($result))
	 {
                if ($oldid == $row['id'])
                {
                        // The same id, then you handle it some way
                }
                else
                {
                        $oldid = $row['id'];  // Set the correct oldid for next time
                        // A new id, you create a new row and handle it however yo like
                }
	 }
	echo '</table>';

} // end if

If you need more help please post how you want it to look when it have outputted more than one row from table A.

obrienkev

ok. here's how it should appear...

html(view source)...

<table>
<tr>
<td>tableB_ID</td>
<td>tableB_name</td>
<td>tableA, tableA, tableA</td>
</tr>
</table>

the multiple results from tableA cause it to appear as below...

<table>
<tr>
<td>tableB_ID</td>
<td>tableB_name</td>
<td>tableA</td>
</tr>
<tr>
<td>tableB_ID</td>
<td>tableB_name</td>
<td>tableA</td>
</tr>
<tr>
<td>tableB_ID</td>
<td>tableB_name</td>
<td>tableA</td>
</tr>
</table>

thanks guys!

Piranha

Then something like this (you have to fix it to 100% yourself)

// perform query
$query = "SELECT * ".
	       "FROM tableB B ".
	       "LEFT JOIN tableA A ".
	       "ON B.ID = A.ID".
	       "ORDER BY B.ID";     // This make sure that all rows in table A that is related to the same row in table B comes after each other

$result = mssql_query($query) or die('Select Error');
$num_results = mssql_num_rows($result);

if ($num_results > 0)
{
	echo '<table> '.
	'<tr><th>SECURITY ID</th><th>EX DATE</th> '.
        '<th>SUBMIT QUERY</th></tr>';
        $oldid = "";      // oldid is used to see if it is a new row or n
        echo '<tr>';    // Start the first row here
 	 while ($row = mssql_fetch_array($result))
	 {
                if ($oldid == $row['id']) // Same id
                {
                        echo ', <a href="submit_query/index.php?id='.$row['corporate_ID'].'" > '.
                       '<img src="../images/email.gif" title="Submit Query" /> '.
                       '</a>';
                }
                else  // New id
                {
                        if ($oldid =! "")  // First time it should not add the </td>
                        {
                                     echo "</td>";
                        }
                        echo '<td>'.$row['security_ID'].'</td> '.
                        '<td>'.$row['ex_date'].'</td> '.
                        '<td><a href="submit_query/index.php?id='.$row['corporate_ID'].'" > '.
                       '<img src="../images/email.gif" title="Submit Query" /> '.
                       '</a>';
                        $oldid = $row['id'];  // Set the correct oldid for next time
                }
	 }
	echo "</td> </tr></table>"; // End the last row here

} // end if

I haven't tested it, so there might be some errors. But the idea works, and you should be able to sort your problem from this.

motowater

I remember trying to look for this, its not shown repeatedly like other aspects in php and i find it extremely important, no php coder should go without this. Here is the code you should use:

echo "<table border='1' align='center'>";
echo "<tr>";
echo "<th>ID</th><th>IP Address</th><th>User Status</th><th>Username</th>";
echo "</tr>";

$result = mysql_query("SELECT * FROM tbl_yours WHERE example='2');

while ($row = mysql_fetch_array($result))
{
	echo "<tr>";
	echo "<td>", $row['id'], "</td><td>", $row['ip'], "</td><td>", $row['user_status'], "</td><td>", $row['username_if_applicable'], "</td>";
	echo "</tr>";

}
echo "</table><br /><br />";

its kind of self explanitory. the starting code is the column names. Use the while so it is $row['column_name'] from whatever came from the query $result. have fun 🙂

Piranha

If you examine the thread you will see that it is not what obrienkev wants. He already know how to do that. What he want to do is to be able to have multiple of one of the values but only have one of the others.

obrienkev

Hi Piranha. Thanks for your help. It works fine but how can I place the multiple data into a certain table cell rather than into the end cell??

The funds_affected cell is to contain the multiple results. Thanks again.

	   // perform query
	   $query = "SELECT * ".
			 	      "FROM corporate_actions c ".
							"LEFT JOIN funds_new f ".
							"ON f.corporate_ID = c.corporate_ID ".
							"LEFT JOIN funds_affected fa ".
							"ON fa.funds_affected_ID = f.funds_new ".
							"ORDER BY c.corporate_ID";
		 $result = mssql_query($query) or die('Select Error');
		 $num_results = mssql_num_rows($result);

	 if ($num_results > 0)
	 {
       echo '<table> '.
					  '<tr><th>SECURITY ID</th><th>EX DATE</th> '.
					  '<th>SECURITY NAME</th><th>EVENT TYPE</th>'.
					  '<th>MISC</th><th>FUNDS AFFECTED</th> '.
					  '<th>TAXABLE EVENT FOR US FUND</th> '.
					  '<th>CORPORATE ACTION DETAILS</th> '.
						'<th>RELATED FILES</th> '.
						'<th>SUBMIT QUERY</th></tr>';
     $oldid = "";      // oldid is used to see if it is a new row or n
		 echo '<tr>';    // Start the first row here
		 while ($row = mssql_fetch_array($result))
		 {
         if ($oldid == $row['corporate_ID']) // Same id
   			 {
             echo $row['funds_affected'].', <a href="submit_query/index.php?id='.$row['corporate_ID'].'" > '.
                  '<img src="../images/email.gif" title="Submit Query" /> '.
          				'</a>';
         }
   			 else  // NEW ID
   			 {
             if ($oldid =! "")  // First time it should not add the </td>
     				 {
        		     echo "</td>";
             }
     				 echo '<td>'.$row['security_ID'].'</td> '.
                  '<td>'.$row['ex_date'].'</td> '.
										'<td>'.$row['security_name'].'</td> '.
										'<td>'.$row['event_type'].'</td> '.
										'<td>'.$row['misc'].'</td> '.
										'<td>'.$row['funds_affected'].'</td> '.
										'<td>'.$row['us_fund'].'</td> '.
										'<td>'.$row['corporate_details'].'</td> '.
										'<td>'.$row['related_files'].'</td> '.
          				'<td><a href="submit_query/index.php?id='.$row['corporate_ID'].'" > '.
          				'<img src="../images/email.gif" title="Submit Query" /> '.
          				'</a>';
             $oldid = $row['corporate_ID'];  // Set the correct oldid for next time
         } // END ELSE
     }
		 echo "</td> </tr></table>"; // END THE LAST ROW HERE

 } // END IF

Thanks.

Piranha

I have already given you examples and explained how you can handle it. Now the rest is up to you, I won't do your job for you.