HTML table presentation of a joined query

For Joomla! 4.x Coding related discussions, please use: http://groups.google.com/group/joomla-dev-general

Moderator: ooffick

Forum rules
Please use the mailing list here: http://groups.google.com/group/joomla-dev-general rather than this forum.
Post Reply
andrew913
Joomla! Apprentice
Joomla! Apprentice
Posts: 21
Joined: Tue Apr 05, 2022 11:33 pm

HTML table presentation of a joined query

Post by andrew913 » Thu Jul 07, 2022 1:51 am

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
You do not have the required permissions to view the files attached to this post.

andrew913
Joomla! Apprentice
Joomla! Apprentice
Posts: 21
Joined: Tue Apr 05, 2022 11:33 pm

Re: HTML table presentation of a joined query

Post by andrew913 » Thu Jul 07, 2022 2:05 am

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

MarkRS
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 109
Joined: Thu Oct 29, 2009 8:28 am
Location: UK

Re: HTML table presentation of a joined query

Post by MarkRS » Thu Jul 07, 2022 8:13 am

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?
It's a community, the more we all contribute, the better it will be.

User avatar
ceford
Joomla! Guru
Joomla! Guru
Posts: 971
Joined: Mon Feb 24, 2014 10:38 pm
Location: Edinburgh, Scotland
Contact:

Re: HTML table presentation of a joined query

Post by ceford » Thu Jul 07, 2022 11:05 am

$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
Joomla! Virtuoso
Joomla! Virtuoso
Posts: 4749
Joined: Tue Aug 23, 2005 1:56 pm
Location: South coast, UK
Contact:

Re: HTML table presentation of a joined query

Post by gws » Thu Jul 07, 2022 11:20 am

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
Joomla! Apprentice
Joomla! Apprentice
Posts: 21
Joined: Tue Apr 05, 2022 11:33 pm

Re: HTML table presentation of a joined query

Post by andrew913 » Thu Jul 07, 2022 4:47 pm

MarkRS wrote:
Thu Jul 07, 2022 8:13 am
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
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 109
Joined: Thu Oct 29, 2009 8:28 am
Location: UK

Re: HTML table presentation of a joined query

Post by MarkRS » Thu Jul 07, 2022 5:41 pm

FoxPro? Ah, those were the days 8)
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?
It's a community, the more we all contribute, the better it will be.

andrew913
Joomla! Apprentice
Joomla! Apprentice
Posts: 21
Joined: Tue Apr 05, 2022 11:33 pm

Re: HTML table presentation of a joined query

Post by andrew913 » Thu Jul 07, 2022 11:52 pm

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
You do not have the required permissions to view the files attached to this post.
Last edited by andrew913 on Thu Jul 07, 2022 11:55 pm, edited 1 time in total.

andrew913
Joomla! Apprentice
Joomla! Apprentice
Posts: 21
Joined: Tue Apr 05, 2022 11:33 pm

Re: HTML table presentation of a joined query

Post by andrew913 » Thu Jul 07, 2022 11:54 pm

gws wrote:
Thu Jul 07, 2022 11:20 am
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.

User avatar
ceford
Joomla! Guru
Joomla! Guru
Posts: 971
Joined: Mon Feb 24, 2014 10:38 pm
Location: Edinburgh, Scotland
Contact:

Re: HTML table presentation of a joined query

Post by ceford » Fri Jul 08, 2022 6:36 am

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
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 109
Joined: Thu Oct 29, 2009 8:28 am
Location: UK

Re: HTML table presentation of a joined query

Post by MarkRS » Fri Jul 08, 2022 8:37 am

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.
It's a community, the more we all contribute, the better it will be.

User avatar
ceford
Joomla! Guru
Joomla! Guru
Posts: 971
Joined: Mon Feb 24, 2014 10:38 pm
Location: Edinburgh, Scotland
Contact:

Re: HTML table presentation of a joined query

Post by ceford » Fri Jul 08, 2022 9:06 am

MarkRS wrote:
Fri Jul 08, 2022 8:37 am
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
Joomla! Apprentice
Joomla! Apprentice
Posts: 21
Joined: Tue Apr 05, 2022 11:33 pm

Re: HTML table presentation of a joined query

Post by andrew913 » Fri Jul 08, 2022 6:19 pm

ceford wrote:
Fri Jul 08, 2022 6:36 am
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}
You do not have the required permissions to view the files attached to this post.

andrew913
Joomla! Apprentice
Joomla! Apprentice
Posts: 21
Joined: Tue Apr 05, 2022 11:33 pm

Re: HTML table presentation of a joined query

Post by andrew913 » Fri Jul 08, 2022 6:25 pm

MarkRS wrote:
Fri Jul 08, 2022 8:37 am
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.

User avatar
ceford
Joomla! Guru
Joomla! Guru
Posts: 971
Joined: Mon Feb 24, 2014 10:38 pm
Location: Edinburgh, Scotland
Contact:

Re: HTML table presentation of a joined query

Post by ceford » Fri Jul 08, 2022 8:29 pm

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
Joomla! Apprentice
Joomla! Apprentice
Posts: 21
Joined: Tue Apr 05, 2022 11:33 pm

Re: HTML table presentation of a joined query

Post by andrew913 » Fri Jul 08, 2022 9:31 pm

@ceford, that's brilliant! Thank you!

How do I tie in the "name" column from #__users?
Screenshot 2022-07-08 162947.jpg
You do not have the required permissions to view the files attached to this post.

andrew913
Joomla! Apprentice
Joomla! Apprentice
Posts: 21
Joined: Tue Apr 05, 2022 11:33 pm

Re: HTML table presentation of a joined query

Post by andrew913 » Fri Jul 08, 2022 9:49 pm

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

User avatar
ceford
Joomla! Guru
Joomla! Guru
Posts: 971
Joined: Mon Feb 24, 2014 10:38 pm
Location: Edinburgh, Scotland
Contact:

Re: HTML table presentation of a joined query

Post by ceford » Sat Jul 09, 2022 3:35 am

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
Joomla! Apprentice
Joomla! Apprentice
Posts: 21
Joined: Tue Apr 05, 2022 11:33 pm

Re: HTML table presentation of a joined query

Post by andrew913 » Mon Jul 11, 2022 10:51 pm

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();
?>

User avatar
ceford
Joomla! Guru
Joomla! Guru
Posts: 971
Joined: Mon Feb 24, 2014 10:38 pm
Location: Edinburgh, Scotland
Contact:

Re: HTML table presentation of a joined query

Post by ceford » Tue Jul 12, 2022 3:18 am

andrew913 wrote:
Mon Jul 11, 2022 10:51 pm
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.


Post Reply

Return to “Joomla! 4.x Coding”