Categories > TinyButStrong general >

Vertical listing in database - horizontal in web page layout

The forum is closed. Please use Stack Overflow for submitting new questions. Use tags: tinybutstrong , opentbs
By: Dennis Hoogendonk
Date: 2009-07-24
Time: 17:10

Vertical listing in database - horizontal in web page layout

For my family website I have a Joomla website in preparation. I combine user log in data combined with personal data by running jUser as extension. This works fine.

Elsewhere in the site I want to display personal data. I want to use TBS to gather data from the table, but I cannot manage this.

Extension jUser creates and additional table called 'jos_users_extended_data' and it look like this :



<table id="table_results" class="data">
  <tbody>
    <tr class="odd">
      <td class="nowrap" align="right"><b>id</b></td>
      <td class="nowrap" align="right"><b>user_id</b></td>
      <td class="nowrap" align="right"><b>field_id</b></td>
      <td><b>uvalue</b></td>
    </tr>
    <tr class="odd">
      <td class="nowrap" align="right">1</td>
      <td class="nowrap" align="right">62</td>
      <td class="nowrap" align="right">6</td>
      <td>Tolhuislaan 33</td>
    </tr>
    <tr class="even">
      <td class="nowrap" align="right">2</td>
      <td class="nowrap" align="right">62</td>
      <td class="nowrap" align="right">7</td>
      <td>Dennis en Elly Hoogendonk</td>
    </tr>
    <tr class="odd">
      <td class="nowrap" align="right">3</td>
      <td class="nowrap" align="right">62</td>
      <td class="nowrap" align="right">8</td>
      <td>4875 AH</td>
    </tr>
    <tr class="even">
      <td class="nowrap" align="right">4</td>
      <td class="nowrap" align="right">62</td>
      <td class="nowrap" align="right">9</td>
      <td>Etten-Leur</td>
    </tr>
    <tr class="odd">
      <td class="nowrap" align="right">15</td>
      <td class="nowrap" align="right">64</td>
      <td class="nowrap" align="right">6</td>
      <td>Spektakelplein 40</td>
    </tr>
    <tr class="even">
      <td class="nowrap" align="right">16</td>
      <td class="nowrap" align="right">64</td>
      <td class="nowrap" align="right">7</td>
      <td>Hugo en Maartje Hoogendonk</td>
    </tr>
    <tr class="odd">
      <td class="nowrap" align="right">17</td>
      <td class="nowrap" align="right">64</td>
      <td class="nowrap" align="right">8</td>
      <td>2676 XK</td>
    </tr>
    <tr class="even">
      <td class="nowrap" align="right">18</td>
      <td class="nowrap" align="right">64</td>
      <td class="nowrap" align="right">9</td>
      <td>Maasdijk</td>
    </tr>
  </tbody>
</table>


I can get data from the table but only in a vertical way, as it is stored in the database table.

Wat I want is to have data presented in a horizontal way like :


<table border="0" cellspacing="2" cellpadding="2">
  <tr>
    <td>Name</td>
    <td>Address</td>
    <td>Postal code </td>
    <td>City</td>
  </tr>
  <tr>
    <td>Dennis en Elly Hoogendonk </td>
    <td>Tolhuislaan 33 </td>
    <td>4875 AH </td>
    <td>Etten-Leur</td>
  </tr>
  <tr>
    <td>Hugo en Maartje Hoogendonk</td>
    <td>Spektakelplein 40</td>
    <td>2676 XK</td>
    <td>Maasdijk</td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
</table>



Is there somebody around who can advise me ? Thanks in advance.


Dennis Hoogendonk
The Netherlands
By: TomH
Date: 2009-07-24
Time: 23:59

Re: Vertical listing in database - horizontal in web page layout

From what I can figure from your post, you intend to use TBS to display data from the database in Joomla...

If you are new to TBS and Joomla - your learning is really two separate issues you need to work on

(1) to get data to display in tables designed to your liking - this is basic learning TBS - I would recommend you begin in the TBS examples here http://www.tinybutstrong.com/examples.php Look at the various 'data' examples and adapt the source code (PHP and templates)

(2) doing the above in Joomla requires installing the TBS extension for Joomla into the Joomla site you have. See http://www.tinybutstrong.com/plugins/joomla/tinybutstrong_help.html

Hope that helps,
TomH
By: Skrol29
Date: 2009-07-25
Time: 00:15

Re: Vertical listing in database - horizontal in web page layout

Hi Dennis,

You can solve this by using a adapted SQL query.
For example :
SELECT n.uvalue AS Name
, a.uvalue AS Address
, p.uvalue AS PostalCode
, c.uvalue AS City
FROM jos_users_extended_data AS n
LEFT JOIN jos_users_extended_data AS a ON (n.user_id=a.user_id) AND (a.field_id=6)
LEFT JOIN jos_users_extended_data AS p ON (n.user_id=p.user_id) AND (p.field_id=8)
LEFT JOIN jos_users_extended_data AS c ON (n.user_id=c.user_id) AND (c.field_id=9)
WHERE (n.field_id=7)
ORDER BY n.uvalue
By: TomH
Date: 2009-07-25
Time: 21:56

Re: Vertical listing in database - horizontal in web page layout

Skrol29

Wow, that's some very clever query stuff.

Thanks for teaching me something with every post you make.


By: Dennis Hoogendonk
Date: 2009-07-26
Time: 12:18

Re: Vertical listing in database - horizontal in web page layout

Thanks Skrol29. I'll use the query and try to parse it in tbs.


Dennis
By: Dennis Hoogendonk
Date: 2009-07-27
Time: 10:57

Re: Vertical listing in database - horizontal in web page layout

Query works perfect in phpMyAdmin. I only had to parse the query into TBS.

This is the result in the webpage :

<table border="0" cellspacing="2" cellpadding="2">
  <tr>
    <td>Name</td>
    <td>Address</td>
    <td>PostalCode</td>
    <td>City</td>
  </tr>
  <tr>
    <td>{a2.Name;block=tr}</td>
    <td>{a2.Address}</td>
    <td>{a2.PostalCode}</td>
    <td>{a2.City}
</td>
  </tr>
</table>

This is the code :

{tbs}mergeblock=a2;sql=SELECT n.uvalue AS Name, a.uvalue AS Address, p.uvalue AS PostalCode, c.uvalue AS City FROM jos_users_extended_data AS n LEFT JOIN jos_users_extended_data AS a ON (n.user_id=a.user_id) AND (a.field_id=6) LEFT JOIN jos_users_extended_data AS p ON (n.user_id=p.user_id) AND (p.field_id=8) LEFT JOIN jos_users_extended_data AS c ON (n.user_id=c.user_id) AND (c.field_id=9) WHERE (n.field_id=7) ORDER BY n.uvalue{/tbs}

In addition to Skrol29 's MySQL code I have added :

'mergeblock=a2' to define where the result must be placed
'sql = ' as predecessor to SELECT statement


I can now easily extend this SQL line with more fields.


Thanks Skrol29 for your help AND this magnificent Plug-in.


Dennis Hoogendonk
The Netherlands
By: Dennis Hoogendonk
Date: 2009-07-27
Time: 11:03

Re: Vertical listing in database - horizontal in web page layout

2 additional remarks - for anyone who wants to learn from my example

1. Make sure the SQL statement is placed in 1 line without any line breaks !
2. I use {} for tbs instead of [] which is the standard


Dennis Hoogendonk
The Netherlands