HTML table presentation of a joined query

[andrew913]

Hello to all,

I'm trying to create a simple Resident Directory for our homeowners association. We previously used Ari Data Tables, with a custom table, but that extension is not J4 compatible and no indication it ever will be.

I added Users Fields for "Lot", "Address", and "Phone" and uploaded CSV files to populate the #__fields_values table.

Using https://regularlabs.com/sourcerer I added the following to a "Directory" article:

Code: Select all

<?php $query = $db->getQuery(true);
$query
        ->select('f.title')
        ->from($db->quoteName('#__fields', 'f'))
        ;
$db->setQuery($query, 0, 0);
$fv_list = $db->loadObjectList();

$q1 = $db->getQuery(true);
$q1
        ->select('u.id, u.name, f.title, fv.value')
        ->from($db->quoteName('#__users', 'u'))
        ->join('INNER', $db->quoteName('#__fields_values', 'fv') . ' ON ' . $db->quoteName('fv.item_id' ) . ' = ' . $db->quoteName('u.id'))
        ->join('INNER', $db->quoteName('#__fields',         'f') . ' ON ' . $db->quoteName('fv.field_id') . ' = ' . $db->quoteName('f.id'))
        ->order('`name` ASC')
        ;
$db->setQuery($q1, 0, 0);
$fields = $db->loadObjectList();
print_r($fields);

foreach ($fields as $fv)
{
        $name = $fv->name;
        $list[$name][0] = $name;
        $list[$name][$fv->title] = $fv->value;
}
return (array($fv_list, $list));
?>

<table class="table table-striped">
  <thead>
    <tr>
      <th>Lot</th>
      <th>Name</th>
      <th>Address</th>
      <th>Phone</th>
    </tr>
  </thead>
  <tbody>
    <?php
      if(mysql_num_rows($list)==0 ){
        echo '<tr><td colspan="4">No Rows Returned</td></tr>';
      }
      else {
        while($row = mysql_fetch_assoc($list) ) {
          echo "<tr><td>{$row['lot']}</td><td>{$row['name']}</td><td>{$row['address']}</td><td>{$row['phone']}</td></tr>\n";
        }
      }
    ?>
  </tbody>
</table>

I can echo all of the field values just fine in a long string. But I'm having difficulty with the HTML presentation of the table. I bought Robin Nixon's "Learning PHP, MySQL, and Javascript" a few months ago, so I'm a total newbie when it comes to this stuff. My goal is a simple table that looks like this:

Screenshot 2022-07-06 180109.jpg

Any suggestions would be appreciated.

Kindly,
Andy

[andrew913]

Sorry, I decided against displaying resident's email addresses. My attachment does not reflect that.
Just Lot, Name, Address, Phone.

[MarkRS]

It would be interesting to see exactly what you're getting because what you've written there, although a bit unusual in style, looks like it should work properly.

Can you show a screenshot of what you're actually getting?

[ceford]

$list is an associative array not a database resource. And mysql_fetch_assoc was removed from php in 7.0. I think you should use foreach ($list as $row) { ... } Same goes for mysql_num_rows - you can just test for an empty list: if (empty($list)) { ... } else { ... }

Presumably you are quoting code from two different files. Otherwise the return statement would render everything following unused.

[gws]

Are you aware of joomdb? It will do what you want out of the box.The pro version is free now.
https://extensions.joomla.org/extension ... base-lite/

[andrew913]

It would be interesting to see exactly what you're getting because what you've written there, although a bit unusual in style, looks like it should work properly.

Can you show a screenshot of what you're actually getting?

No output other than the article title. I can use print_r($fields); to output the array, so I know the data is being collected correctly.

The "unusual style" is due to the fact that I have no idea what I'm doing. The last time I wrote a line of code was in Delphi or FoxPro!

[MarkRS]

FoxPro? Ah, those were the days
FoxPro or VFP? If it's the latter, hopefully you're reasonably up with OOP, and so it's not too hard to see a lot of the J! stuff to be fairly easy to understand. The database handling is often woefully simplistic, but, hey ho.

Ok, so as @ceford says, this must be two files? It looks like the query is from something like the getListQuery method of a model and the HTML must be in a template file. If not that's a problem. And in fact the value of that return statement looks a bit strange. Nonetheless, where does all that code sit, exactly?

The database selection(s) in the first part should give you the data you want (although it's not really right).
The HTML based code in the second part should be somewhere (a template file really) that it can access that array (either associative or array of objects, just pick one).
Then your line "mysq_fetch_assoc" is wrong, and it should simply be "foreach($yourArray as $aSingleRow)"

Then the "unusual" I was referring to is in having one echo line (although I don't think it is really the problem).
Look at core code in various components and you'll see the preferred style, taking more space but really much easier to see connections in. Your example here should read something like

Code: Select all

	...
	foreach($yourArray as $aSingleRow) { ?>
		<tr>
			<td><?php echo $aSingleRow['lot']; ?></td>
			<td><?php echo $aSingleRow['name']; ?></td>
			<td><?php echo $aSingleRow['address']; ?></td>
			<td><?php echo $aSingleRow['phone']; ?></td>
		</tr>
	<?php } ?>

(In fact I think the loop should end with "endforeach", but I'm doing this off the top of my head).

Getting clearer?

[andrew913]

Thank you @ceford and @MarkRS.

I've made some adjustments per your suggestions and at least now I'm getting an empty table to display with the correct number of rows. I've preceded the table with print_r($list) so you can see that I'm generating the dataset. All of the code is in a featured article using the Sourcerer plugin.

Code: Select all

{source}

<?php $query = $db->getQuery(true);
$query
    ->select('f.title')
    ->from($db->quoteName('#__fields', 'f'))
    ;
$db->setQuery($query, 0, 0);
$fv_list = $db->loadObjectList();

$q1 = $db->getQuery(true);
$q1
      ->select('u.id, u.name, f.title, fv.value')
      ->from($db->quoteName('#__users', 'u'))
      ->join('INNER', $db->quoteName('#__fields_values', 'fv') . ' ON ' . $db->quoteName('fv.item_id' ) . ' = ' . $db->quoteName('u.id'))
      ->join('INNER', $db->quoteName('#__fields',         'f') . ' ON ' . $db->quoteName('fv.field_id') . ' = ' . $db->quoteName('f.id'))
      ->order('name ASC')
      ;
$db->setQuery($q1, 0, 0);
$fields = $db->loadObjectList();

foreach ($fields as $fv)
{
        $name = $fv->name;
        $list[$name][0] = $name;
        $list[$name][$fv->title] = $fv->value;
}
?>

<table class="table table-striped">
  <thead>
    <tr>
      <th>Lot</th>
      <th>Name</th>
      <th>Address</th>
      <th>Phone</th>
    </tr>
  </thead>
  <tbody>
<?php foreach($list as $aSingleRow) { ?>
       <tr>
             <td><?php echo $aSingleRow['lot']; ?></td>
             <td><?php echo $aSingleRow['name']; ?></td>
             <td><?php echo $aSingleRow['address']; ?></td>
             <td><?php echo $aSingleRow['phone']; ?></td>
       </tr> <?php } ?>  
   </tbody>
</table>

<?php return (array($fv_list, $list)); ?>

{/source}

Screenshot 2022-07-07 165119.jpg

[andrew913]

Are you aware of joomdb? It will do what you want out of the box.The pro version is free now.
https://extensions.joomla.org/extension ... base-lite/

I appreciate suggestion. I actually tried that extension but couldn't figure out how to join the tables.

[ceford]

I recommend you use var_dump rather than print_r to see what is in a variable. It gives a nicer layout. So for example in this line:

<?php foreach($list as $aSingleRow) { var_dump($aSingleRow); ?>

[MarkRS]

I think you're getting tied up with converting an array of objects into an associative array keyed (somewhat bizarrely for what it looks like you need) on names. Also, it doesn't look like there's any real need for the whole of that first query extracting field names.

Just use the array of objects from the second query. Then the things you want in the row are simply things like "$aSingleRow->lot" and "$aSingleRow->name" &tc.

[ceford]

Just use the array of objects from the second query. Then the things you want in the row are simply things like "$aSingleRow->lot" and "$aSingleRow->name" &tc.

The snag with that is that there is one row for each field for each user. It might be possible to do it with a subquery instead of a join. I can't find my db thinking cap right now!

[andrew913]

I recommend you use var_dump rather than print_r to see what is in a variable. It gives a nicer layout. So for example in this line:

<?php foreach($list as $aSingleRow) { var_dump($aSingleRow); ?>

Okay, I added var_dump, and getNumRows, and loadRowList with some interesting results. Is this saying I only have three rows (lot, address, phone)? Or did I do the getNumRows wrong? The var_dump correctly shows name, lot, address, and phone for all 367 records.

Screenshot 2022-07-08 131023.jpg

Code: Select all

{source}

<?php
$query = $db->getQuery(true);
$query
    ->select('f.title')
    ->from($db->quoteName('#__fields', 'f'));

$db->setQuery($query);
$fv_list = $db->loadObjectList();

$q1 = $db->getQuery(true);
$q1
      ->select('u.id, u.name, f.title, fv.value')
      ->from($db->quoteName('#__users', 'u'))
      ->join('INNER', $db->quoteName('#__fields_values', 'fv') . ' ON ' . $db->quoteName('fv.item_id') . ' = ' . $db->quoteName('u.id'))
      ->join('INNER', $db->quoteName('#__fields',              'f') . ' ON ' . $db->quoteName('fv.field_id') . ' = ' . $db->quoteName('f.id'))
      ->order('name ASC');

$db->setQuery($q1);
$fields = $db->loadObjectList();

foreach ($fields as $fv)
{
        $name = $fv->name;
        $list[$name][0] = $name;
        $list[$name][$fv->title] = $fv->value;
}

echo '<pre>';
foreach($list as $aSingleRow) var_dump($aSingleRow);
echo '</pre>';

$db->setQuery($query);
$db->execute();
$num_rows = $db->getNumRows();
print_r($num_rows);
$result = $db->loadRowList();
echo '<br/>';
print_r($result);

?>

<table class="table table-striped">
  <thead>
    <tr>
      <th>Name</th>
      <th>Lot</th>
      <th>Address</th>
      <th>Phone</th>
    </tr>
  </thead>
  <tbody>
<?php foreach($list as $aSingleRow) { ?>
       <tr>
             <td><?php echo $aSingleRow['name']; ?></td>
             <td><?php echo $aSingleRow['lot']; ?></td>
             <td><?php echo $aSingleRow['address']; ?></td>
             <td><?php echo $aSingleRow['phone']; ?></td>
       </tr> <?php } ?>  
   </tbody>
</table>

{/source}

[andrew913]

I think you're getting tied up with converting an array of objects into an associative array keyed (somewhat bizarrely for what it looks like you need) on names. Also, it doesn't look like there's any real need for the whole of that first query extracting field names.

Just use the array of objects from the second query. Then the things you want in the row are simply things like "$aSingleRow->lot" and "$aSingleRow->name" &tc.

Yes, I'd prefer to sort by lot but it's sorting on the #__users table.

[ceford]

Try this:

Code: Select all

<?php
$query = $db->getQuery(true);
$query->select('u.id')
->select("(SELECT fv.value FROM #__fields_values AS fv INNER JOIN #__fields AS f ON f.id = fv.field_id WHERE u.id = fv.item_id AND 'lot' = f.name) AS lot")
->select("(SELECT fv.value FROM #__fields_values AS fv INNER JOIN #__fields AS f ON f.id = fv.field_id WHERE u.id = fv.item_id AND 'address' = f.name) AS address")
->select("(SELECT fv.value FROM #__fields_values AS fv INNER JOIN #__fields AS f ON f.id = fv.field_id WHERE u.id = fv.item_id AND 'phone' = f.name) AS phone")
->from('#__users AS u')
->order('lot ASC');
$db->setQuery($query);
$rows = $db->loadObjectList();
?>

<table class="table table-striped">
<thead>
    <tr>
      <th>Lot</th>
      <th>Name</th>
      <th>Address</th>
      <th>Phone</th>
    </tr>
  </thead>
  <tbody>
<?php foreach($rows as $row) : ?>
		<tr>
			<td><?php echo $row->lot; ?></td>
			<td><?php echo $row->name; ?></td>
			<td><?php echo $row->address; ?></td>
			<td><?php echo $row->phone; ?></td>
		</tr>
	<?php endforeach; ?>
	</tbody>
</table>

It works for me on a different set of custom fields but I may have introduced mistakes in modifying it for your data.

[andrew913]

@ceford, that's brilliant! Thank you!

How do I tie in the "name" column from #__users?

Screenshot 2022-07-08 162947.jpg

[andrew913]

Ha! It was simple as adding a line for SELECT name AS name. I now have a completed resident directory sorted by lot#.

Thank you guys for all of the help!!

Have a great weekend,
Andy

[ceford]

Change $query->select('u.id') to $query->select('u.id, u.name')

That was one of the things I overlooked for your data set.

[andrew913]

Additional question: How could I use the results of the query to insert data into a new table? Based on some Google searches, I tried the following but it doesn't work. Am I even close?

Code: Select all

<?php
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('u.id, u.name, u.email')
->select("(SELECT fv.value FROM #__fields_values AS fv INNER JOIN #__fields AS f ON f.id = fv.field_id WHERE u.id = fv.item_id AND 'lot' = f.name) AS lot")
->select("(SELECT fv.value FROM #__fields_values AS fv INNER JOIN #__fields AS f ON f.id = fv.field_id WHERE u.id = fv.item_id AND 'address' = f.name) AS address")
->select("(SELECT fv.value FROM #__fields_values AS fv INNER JOIN #__fields AS f ON f.id = fv.field_id WHERE u.id = fv.item_id AND 'phone' = f.name) AS phone")
->from('#__users AS u')
->order('lot ASC');
$db->setQuery($query);
$values = $db->loadObjectList();

$query = $db->getQuery(true);
$selectString = $values->__toString();
$columns = (array('lot', 'name', 'email', 'address', 'phone'));
$query
    ->insert($db->quoteName('#__users_directory'))
    ->columns($db->quoteName($columns))
    ->values(implode(',', $selectString));
$db->setQuery($query);
$db->execute();
?>

[ceford]

Additional question: How could I use the results of the query to insert data into a new table? Based on some Google searches, I tried the following but it doesn't work. Am I even close?

Why would you even want to do this? Duplication of data is always a bad idea. You could easily end up with multiple instances of the same data in users_directory.

Try var_dump($query->__tostring()); to see what the actual query contains.