Displaying Query Results as a Table

Дата добавления:
25.03.2010
Последнее изменение:
25.03.2010
Хиты:
12614
Рейтинг:
 
Голосовать:
Хорошо - Плохо

Ответ

Task

We need to display list of the results of a query to Joomla! database as a table.

Solution

Set the DW Anything module settings as follows.

 

Setting Value Description
SQL Query
SELECT * FROM `#__users`

Enter SQL query to retreive the list of user names from Joomla! users table.

Note that you can use standard Joomla! '#_' prefix for database table names

Header HTML
<table>
    <thead>
        <tr>
            <th>Name</th>
            <th>User Name</th>
        </tr>
    </thead>
    <tbody>

Enter HTML code to display before the resulting list of data rows.

Table opening tag and column headers go here. Add column headers as required

Template
        <tr>
            <td>{name}</td>
            <td>{username}</td>
        </tr>

Enter HTML code to display each data row returned by SQL query.

To insert data field, type it's name enclosed within curly barckets ({}).

Add fields for all data columns you want to display here

Footer HTML
    </tbody>
</table>

Enter HTML code to display after the resulting list of data rows.

Place table closing HTML tags here

Result

Name User Name
john John Smith
mary Mary Brown

Категория

Комментарии   

 
+3 # RE: Displaying Query Results as a TableGuest 20.02.2011 19:59
How can I get alternate rows in my table to display a different background color?
Ответить | Ответить с цитатой | Цитировать
 
 
0 # RE: RE: Displaying Query Results as a TableAdministrator 21.02.2011 00:18
Jason,

To distinguish between odd and even rows, use the counter variable in your MySQL query as follows:

SELECT @parity := IF(@parity, 0, 1) AS `parity` FROM `#__content`, (SELECT @parity:=0) AS `a`
Ответить | Ответить с цитатой | Цитировать
 
 
0 # RE: RE: RE: Displaying Query Results as a TableGuest 21.02.2011 20:42
Ok, here is the SQL I currently have:

SELECT DATE_FORMAT( date_time, '%M %d, %Y' ) AS date, TIME_FORMAT( date_time, '%h:%i %p' ) AS time, name
FROM jos_chronoforms _photo_director y
ORDER BY date ASC , time ASC

How would I get odd/even rows and use in my template?

Thanks
Ответить | Ответить с цитатой | Цитировать
 
 
0 # RE: RE: RE: RE: Displaying Query Results as a TableAdministrator 21.02.2011 20:57
Query:

SELECT DATE_FORMAT( date_time, '%M %d, %Y' ) AS date, TIME_FORMAT( date_time, '%h:%i %p' ) AS time, name, @parity := IF(@parity, 0, 1) AS `parity`
FROM jos_chronoforms _photo_director y, (SELECT @parity:=0) AS `a`
ORDER BY date ASC , time ASC

Template: <tr class="row{parity}">...</tr>

Odd rows will get the "row1" class, even rows - "row0" class.
Ответить | Ответить с цитатой | Цитировать
 
 
0 # rows colorGuest 13.04.2011 17:25
hi. First of all thank you for the best module of joomla.

I rode answer abowe but i don't understand. I used query;
---------------------------
SELECT DATE_FORMAT( date_time, '%M %d, %Y' ) AS date, TIME_FORMAT( date_time, '%h:%i %p' ) AS time, name, @parity := IF(@parity, 0, 1) AS `parity`
FROM jos_chronoforms _photo_director y, (SELECT @parity:=0) AS `a`
ORDER BY date ASC , time ASC

Template: ...

Odd rows will get the "row1" class, even rows - "row0" class.
----------------------------------
but there isn't any change

please help me. I want to do
first row blue
second row white
third row blue
forth row white
....... etc
Ответить | Ответить с цитатой | Цитировать
 
 
0 # RE: rows colorAdministrator 16.04.2011 16:22
Hi Kirilmaz,

To see any changes, you'll need to add CSS styling as well.

The styles you add could be something like this:


.row1 {
background-colo r: #B6F3F9;
}
.row0 {
background-colo r: #FFFFFF;
}
Ответить | Ответить с цитатой | Цитировать
 
 
0 # DW AnythingGuest 16.04.2011 09:35
can I use this to display any registered user info?
For example, I want to display registered info in a table format, such as:
image, Name, phone, address, webURL, etc...

some users do not want their info displayed...is there some sort of control for that?

I'm creating a website for a non-profit org, but am having problems displaying user info; I have AEC extension for free & paid membership, I also use Jomsocial & link Jomsocial profile & registration together...so there are quite a number of fields, ie. user images, name address, phone, website, professional specialty, paid/not paid status, groups, etc...

If this extension is capable of displaying any fields in the mySQL, can it display the above info...assuming that Jomsocial also uses mySQL?

Thank you & God bless
Ответить | Ответить с цитатой | Цитировать
 
 
0 # RE: DW AnythingAdministrator 16.04.2011 16:31
Hi thung,

Yes, you can display data from JomSocial. You'll need to select it from corresponding JomSocial tables in your MySQL query.

I don't realy know if JomSocial allows you to add custom fields to user profile, but the chance is it does (like, say Community Builder). If so, you should add the check box control to profile, so user could check it to state if he/she wants to display user info. The user choice then should be stored in JomSocial database table(s), so you could use it in the WHERE clause of your MySQL query to select only users who agreed to show their info.
Ответить | Ответить с цитатой | Цитировать
 
 
-1 # graadt33Guest 11.05.2011 17:49
I like to have a query where all fiels are seected (so select * fromt able, but the date field needs to be specified in a special date format
Ответить | Ответить с цитатой | Цитировать
 
 
0 # RE: graadt33Administrator 11.05.2011 18:09
Hi Wilbert,

Just add the formatted date to your SELECT fields list, e.g.

SELECT *, DATE_FORMAT(`da te_field`, '%W %M %Y') AS `date_formatted ` FROM `jos_your_table _name`
Ответить | Ответить с цитатой | Цитировать
 
 
0 # rt_quantiveAnthony Metcalf 27.07.2011 19:36
I am using the following query to pull information from sobi2 and display it in a table
Цитата:
SELECT a.itemid as `ID`, a.title as `Title`, GROUP_CONCAT(DISTINCT e.name ORDER BY e.name) AS `Categories`, GROUP_CONCAT(DISTINCT e.catid ORDER BY e.name) AS `Category IDs`, MAX(IF(c.fieldid=17,c.data_txt, NULL)) as `Offer`, MAX(IF(c.fieldid=18,c.data_txt, NULL)) as `Potential Savings` FROM #__sobi2_item a LEFT JOIN #__sobi2_fields_data c on c.itemid=a.itemid LEFT JOIN #__sobi2_cat_items_relations d on d.itemid=a.itemid LEFT JOIN #__sobi2_categories e ON e.catid=d.catid WHERE d.itemid=a.itemid AND a.published='1' GROUP BY a.itemid ORDER BY ID DESC
My only problem is that all entries in the db with apostrophes are being escaped by a backslash. For example
O'Daniel's Flower Shop shows up in the table as
O\'Daniel\'s Flower Shop

Any help would be appreciated
Ответить | Ответить с цитатой | Цитировать
 
 
0 # RE: rt_quantiveAdministrator 11.08.2011 03:47
You may want to use the MySQL REPLACE function to strip out slashes.

For usage example, see How do I display line breaks? article.
Ответить | Ответить с цитатой | Цитировать
 
 
0 # RE: Displaying Query Results as a Tabledemi 24.10.2011 21:05
Hi,
First of all, i have to say thanks for the great job.
For me, i ld like to ask how can display image from my restore db table?
Thanks in advance!
Ответить | Ответить с цитатой | Цитировать
 
 
0 # RE: RE: Displaying Query Results as a TableAdministrator 25.10.2011 16:27
Hi Demi,

Thank you.

Could you please be more specific about your question? What is restore DB table? How does your image stored in it?
Ответить | Ответить с цитатой | Цитировать
 
 
0 # Displaying Query Results as Mrquee in one linedon 26.10.2011 11:47
HI

First of great job on this module, but how do i display my query results as marquee text in one line taking the example above removing header and footer html and replacing template with "{name}-{userna me}" its displaying each result in a separate row how do i get it to display in one row
Ответить | Ответить с цитатой | Цитировать
 
 
0 # Displaying Query Results as Marquee text in one linedon 26.10.2011 13:54
Thanks for a great module and its working fine but how do i get the query results display in one-line as marquee text

my code:

SQL Query : SELECT * FROM `#__users`

Template: {name}-{username}

with the above code results are diplaying in separate rows how do i get them to dipaly in one row?
Ответить | Ответить с цитатой | Цитировать
 
 
+1 # RE: Displaying Query Results as Marquee text in one lineAdministrator 26.10.2011 14:21
Hi Don,

To display the marquee text in one row, please try putting the marquee opening and closing HTML tags into the Header and Footer sections of DW Anything module parameters.
Ответить | Ответить с цитатой | Цитировать
 
 
0 # RE: RE: Displaying Query Results as Marquee text in one linedon 26.10.2011 15:21
thanks it worked perfectly
Ответить | Ответить с цитатой | Цитировать
 
 
0 # notunderstoodsanjay 15.01.2012 05:33
i want to display tabular data in an article format. my tabular data has six column but i want only paraheading and orginalnumber column to be displayed in a letter form
Ответить | Ответить с цитатой | Цитировать
 
 
0 # RE: notunderstoodAdministrator 15.01.2012 16:13
Hi Sanjay,

I'm not sure I've got your question right. Do you want to display your database data as a table with a limited set of columns?
Ответить | Ответить с цитатой | Цитировать
 
 
0 # Action ButtonLucas 04.02.2012 04:42
Hi there!
Congratulations for the excellent and simple extension!

I just made the table with the query, it´s working fine.
I need to create two buttons in the same line: DELETE REGISTER and EDIT REGISTER.

The DELETE button could make a DELETE SQL command using the ID of the register.

The EDIT button could carry some variable through HTTP and link with a CHRONOFORMS form so the entire field could be edited.

Thanks in advance!
Cheers
Lucas
Ответить | Ответить с цитатой | Цитировать
 
 
0 # RE: Action ButtonAdministrator 25.02.2012 00:15
Hi Lucas,

Thanks.

You can place the HTML forms containing the DELETE and EDIT buttons within each row of your table using DW Anything Template.

Still, you'll need to point them to some server-side script which does the processing stuff for you. DW Anything won't do the processing job.
Ответить | Ответить с цитатой | Цитировать
 
 
0 # conditional colorsDavid 20.02.2012 14:57
Hello,

I am using your module to display a db table. It works very well thank you.

I need to change the color of a row depending on a field. where can I do such a thing? I see what you have done with the alternating rows but I dont believe I can use that.
Ответить | Ответить с цитатой | Цитировать
 
 
0 # conditional colorsAdministrator 25.02.2012 00:09
Please see below.
Ответить | Ответить с цитатой | Цитировать
 
 
+1 # RE: Displaying Query Results as a TableShola 23.02.2012 02:33
i installed this module and followed the steps above , my browser gave me this error when i tried to view the records

Fatal error: Class 'JFile' not found in C:\xampp\htdocs \blwgzone\modul es\mod_dwanythi ng\helper.php on line 25
Ответить | Ответить с цитатой | Цитировать
 
 
-1 # RE: RE: Displaying Query Results as a TableAdministrator 24.02.2012 23:42
Thanks, we'll see how to fix this error.
Ответить | Ответить с цитатой | Цитировать
 
 
0 # RE: Displaying Query Results as a Tabledavid drennan 24.02.2012 23:31
I am using your plugin to display an order table. However I dont know how to do dynamic formatting. I would like the row background color to vary depending on the value of a field the field comes from the database:corera di table:orders field:approved. this is the SQL i use

SELECT *, DATE_FORMAT(`Or der_Date`, '%DD %MM %YY') ,DATE_FORMAT(`A fleverings_Dato `, '%D %M %Y') FROM `Orders`
ORDER BY Order_Id DESC
LIMIT 0 , 10;
Ответить | Ответить с цитатой | Цитировать
 
 
0 # RE: RE: Displaying Query Results as a TableAdministrator 25.02.2012 00:00
David, you'll need to add a conditional column to your MySQL query using an IF() function.

E.g.:

SELECT *, IF(`approved`, 'background-col or: green', 'background-col or: red') AS `formatting`, etc...

Then, you can get the {formatting} column value and use it in your Template:

<tr style="{formatt ing}">
<td>{name}</td>
<td>{username}</td>
</tr>
Ответить | Ответить с цитатой | Цитировать
 
 
+1 # Pagination and csvrainweb 29.02.2012 01:12
Thanks for this extension.
Can i add a page navigation and a button for data export in csv format?
Ответить | Ответить с цитатой | Цитировать
 
 
0 # RE: Pagination and csvAdministrator 29.02.2012 01:40
Both of these will be quite tricky if possible at all.
Ответить | Ответить с цитатой | Цитировать
 
 
0 # QuestionGuest 03.04.2012 04:42
How can I get just the last record of the table?
Ответить | Ответить с цитатой | Цитировать
 
 
0 # RE: QuestionAdministrator 16.05.2012 11:57
To select the last record, you'll need to sort your query results by an ID number in a descending order, so that the last added row comes first, and then limit your results to a single row, e.g.:

SELECT * FROM #__table_name ORDER BY auto_incremente d_id DESC LIMIT 1

where auto_incremente d_id is most commonly the id column of your table.
Ответить | Ответить с цитатой | Цитировать
 
 
0 # RE: Displaying Query Results as a TableAnchor tags 31.05.2012 18:21
Hello - love the extension!!! One item I am trying to get to work is an anchor tag to allow the results to contain a hyperlink. I have my test template listed blow -- it does work and I see the table of information, just when I hover over the item it isn't a link. I'd appreciate any thoughts you may have (even if it is just a confirmation that anchors are supported and my issue is elsewhere)





{Name}



{Item}
Ответить | Ответить с цитатой | Цитировать
 
 
0 # RE: Displaying Query Results as a TableAdministrator 08.06.2012 22:05
Hi,

Unfortunately, no HTML allowed in user comments, so I cannot see your example.

Have you figured out how to complete your task yet?
Ответить | Ответить с цитатой | Цитировать
 
 
0 # Inline CSSTerry 07.06.2012 00:53
Can inline CSS be used in a table row.

ColA
Ответить | Ответить с цитатой | Цитировать
 
 
0 # RE: Inline CSSAdministrator 08.06.2012 22:02
Hi Terry,

Unfortunately, no HTML allowed in user comments, so I cannot see your example.

Generally, you can use inline CSS in your templates.
Ответить | Ответить с цитатой | Цитировать
 
 
0 # -Developer 25.10.2012 13:17
Did you manage to fix the following error? ->
Fatal error: Class 'JFile' not found in path/helper.php on line 25

??
Ответить | Ответить с цитатой | Цитировать
 
 
0 # RE: -Administrator 28.11.2012 15:25
Not yet, unfortunately. Working on it.

Please keep track of version updates.
Ответить | Ответить с цитатой | Цитировать
 

Добавить комментарий


Защитный код
Обновить

SEO by Artio