Michael Mormon Michael Mormon -4 years ago 71
PHP Question

Code Does Not Return Rows

I am attempting to write the query results to a html table. The sql is valid but for some reason the table only displays the header row. This should return roughly 15 rows. What is incorrect with the syntax, and how should I modify so that it writes the data to the table?

<?php

header('Access-Control-Allow-Origin: *');
$option = array(); //prevent problems

$option['driver'] = 'mssql';
$option['host'] = 'XXX.XXX.XX.XX';
$option['user'] = 'user';
$option['password'] = 'pass';
$option['database'] = 'database';
$option['prefix'] = '';

$db = JDatabase::getInstance( $option );
$result = $db->getQuery(true);
$result->select($db->quoteName(array('Host', 'Location', 'NumberOfPlayersAttending', 'NumberOfPlayersCompeting')));
$result->from($db->quoteName('[EventInfo'));
$db->setQuery($result);
$results = $db->loadObjectList();
?>

<table border="1">
<thead>
<tr>
<th>Host</th>
<th>Location</th>
<th>NumberOfPlayersAttending</th>
<th>NumberOfPlayersCompeting</th>
</tr>
</thead>
<tbody>
<?php
foreach( $options as $option )
{
?>
<tr>
<td><?php print_r($option['Host']); ?></td>
<td><?php print_r($option['Location']); ?> </td>
<td><?php print_r($option['NumberOfPlayersAttending']); ?></td>
<td><?php print_r($option['NumberOfPlayersCompeting']); ?></td>
</tr>
<?php
}
?>
</tbody>
</table>


EDIT

Also altering the syntax to
foreach( $results as $option )
only produces a header row, still no data being returned.

EDIT 2

This is the data that
$results
is returning

Array ( [0] => stdClass Object ( [Host] => Red Robin [Location] => Omaha, Neb [NumberOfPlayersAttending] => 25 [NumberOfPlayersCompeting] => 13 ) )

Answer Source

Iterate through $results instead of $options, as this is the variable that contains your query output.

Update

Also the HTML won't print new rows for each result.

to fix your code, access the results of your query using object operators, as that is what is returned:

   <table border="1">
    <thead>
        <tr>
            <th>Host</th>
            <th>Location</th>
            <th>NumberOfPlayersAttending</th>
            <th>NumberOfPlayersCompeting</th>
        </tr>
    </thead>
    <tbody>
        <?php
            foreach( $options as $option ) { 
              print "<tr>";
              print "<td>".$option->Host."</td>";
              print "<td>".$option->Location."</td>";
              print "<td>".$option->NumberOfPlayersAttending."</td>";
              print "<td>".$option->NumberOfPlayersCompeting."</td>";
              print "</tr>";
            } 
            ?>
    </tbody>
  </table>

loadObjectList, a method of the class JDatabase returns objects for each row parsed. As you can see here:

Method to get an array of the result set rows from the database query where each row is an object.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download