Comments for SQL 2 Excel - Tutorial at http://joomla-r-us.com , comment 1 to 33 out of 20 comments http://joomla-r-us.com Tue, 07 Sep 2010 13:16:41 +0100 FeedCreator 1.7.3 http://joomla-r-us.com/home/48-sql-2-excel/62-sql-2-excel-tutorial#comment-379 @nungtirt: Use substitution variables in your SQL query. See these pages for examples and more info: http://joomla-r-us.com/faq/31-general/66-sql-2-excel-variable-substitution-how-to http://joomla-r-us.com/faq/31-general/64-sql-2-excel-variable-substitutions -Mats - Mats@Joomla-R-Us Fri, 13 Aug 2010 00:40:14 +0100 http://joomla-r-us.com/home/48-sql-2-excel/62-sql-2-excel-tutorial#comment-378 Hi please let me know how'sthe sql syntax for filtering data if the filter--> show only data if custcode equal with username. i also don't now the user name parameter if someone login into joomla. Thx - nungtirt Thu, 12 Aug 2010 10:30:00 +0100 http://joomla-r-us.com/home/48-sql-2-excel/62-sql-2-excel-tutorial#comment-308 @Rudolf: You can use any valid SQL query in SQL 2 Excel. The problem is that your query is NOT valid. You can only join two queries like that if the column count AND names are identical. Run the query in phpMyAdmin and you'll see the problem. You could do something like this: SELECT imgid as 'ID obrázku', rating as 'Hodnocení', userid as 'Hlasující', date as 'Datum' FROM jos_phocagallery_img_votes SELECT id as 'obrázku', catid as 'Hodnocení', '' as 'Hlasující', '' as 'Datum' FROM jos_phocagallery The query should be ok, BUT you won't get a separate column heading for the second query - so it will probably look very bad/strange. You can only add data from one query in the free version of SQL 2 Excel. SQL 2 Excel Pro however, can add results from multiple queries into the worksheet. You can specify the first query as your main query. Then specify the second query as an "Extra Cell" sql query and position the output on the side, or below the first results etc etc. You can add as many "Extra Cell" queries/data that you need in SQL 2 Excel Pro. -Mats - Mats @ Joomla-R-Us Tue, 27 Apr 2010 11:09:33 +0100 http://joomla-r-us.com/home/48-sql-2-excel/62-sql-2-excel-tutorial#comment-306 Can i use this?: SELECT imgid as 'ID obrázku', rating as 'Hodnocení', userid as 'Hlasující', date as 'Datum' FROM jos_phocagallery_img_votes SELECT id as 'Obrázek', catid as 'ID kategorie' FROM jos_phocagallery I need in one worksheet some field from some another table and make one woorksbook. Why? Field imgid (ID photo) is in table jos_phocagallery_img_votes, title (NAME photo) is in the table jos_phocagallery. Can SQL Query join this information in in workssheet? Thanks Rudolf - Rudolf Mon, 26 Apr 2010 18:41:39 +0100 http://joomla-r-us.com/home/48-sql-2-excel/62-sql-2-excel-tutorial#comment-283 OK. Anyway - huge thanks to you. - Priit Tue, 30 Mar 2010 01:26:53 +0100 http://joomla-r-us.com/home/48-sql-2-excel/62-sql-2-excel-tutorial#comment-282 @Priit : Either the real link is stored in some other table, using this string as a key, or they encode the link in some way in the database. I can't help you with that. Best bet is to look in the source code of the application and figure out how they retrieve the link. -Mats - mats @ Joomla-R-Us Tue, 30 Mar 2010 01:24:05 +0100 http://joomla-r-us.com/home/48-sql-2-excel/62-sql-2-excel-tutorial#comment-281 No link. The same DghYtr745fgfdDGhjkg75dTjjkh.. etc, as it appears in excel sheet. But in JForms, when I look at the records, there is a nice and working link. - Priit Tue, 30 Mar 2010 01:11:52 +0100 http://joomla-r-us.com/home/48-sql-2-excel/62-sql-2-excel-tutorial#comment-280 @Priit: What does the data look like when you look at it using phpMyAdmin? Do you see the link, or do you see something else? - Mats @ Joomla-R-Us Tue, 30 Mar 2010 01:08:59 +0100 http://joomla-r-us.com/home/48-sql-2-excel/62-sql-2-excel-tutorial#comment-279 ;D First in my life I used phpMyAdmin!! Data type of this column is text - Priit Tue, 30 Mar 2010 01:06:03 +0100 http://joomla-r-us.com/home/48-sql-2-excel/62-sql-2-excel-tutorial#comment-278 Well, it's embarrassing. I know nothing about phpMyAdmin. I just know, that I'm using MySQL database. How to manage with them together ... that's Chinese to me :( - Priit Tue, 30 Mar 2010 00:55:18 +0100 http://joomla-r-us.com/home/48-sql-2-excel/62-sql-2-excel-tutorial#comment-277 @Priit: Use phpMyAdmin to examine the table structure and which datatype this column has. Also, when using phpMyAdmin, look at the content of this column - how is it stored in the database. - Mats @ Joomla-R-Us Tue, 30 Mar 2010 00:31:23 +0100 http://joomla-r-us.com/home/48-sql-2-excel/62-sql-2-excel-tutorial#comment-276 Well, I'm too stupid to determine the data type :( All the other columns contain a regular text, this column contains links (blue, underlined). If I open the JForms record table, I can simply click the link and the desired picture opens in a new window. How and where could I find the JForms table column data type? - Priit Mon, 29 Mar 2010 19:09:07 +0100 http://joomla-r-us.com/home/48-sql-2-excel/62-sql-2-excel-tutorial#comment-275 @Priit: Not sure why it doesn't come through to the spreadsheet properly. What data type is the column? - Mats @ Joomla-R-Us Mon, 29 Mar 2010 18:51:14 +0100 http://joomla-r-us.com/home/48-sql-2-excel/62-sql-2-excel-tutorial#comment-274 In the database table they are usual links, leading to the actual location of the particular picture. - Priit Mon, 29 Mar 2010 18:39:21 +0100 http://joomla-r-us.com/home/48-sql-2-excel/62-sql-2-excel-tutorial#comment-273 @Priit: Regarding the picture links.. How are they stored in the database table? What is the data type of the column? You can use phpMyAdmin etc to check out the table. PS! No, I'm not Estonian.. - Mats @ Joomla - R - Us Mon, 29 Mar 2010 18:32:57 +0100 http://joomla-r-us.com/home/48-sql-2-excel/62-sql-2-excel-tutorial#comment-272 Great! It really works! Thank you, Mats. By the way - Mats is a traditional Estonian name. Are you from there? :D Still one more question. I have pictures in those tables. Or actually links to the pictures. But those links appear in excel-report as 10000-digits codes or something... Would be great to have those links also in excel sheet. Is it possible? - Priit Mon, 29 Mar 2010 18:29:01 +0100 http://joomla-r-us.com/home/48-sql-2-excel/62-sql-2-excel-tutorial#comment-271 @Priit: JSforms saves the the form entries in the database. Each form saves the data into a separate database table. The table name is listed the JSForms backend, for example table name = jos_jforms_9d92d You can generate a spreadsheet from the data in this table using SQL 2 Excel. For example: SELECT * FROM jos_jforms_9d92d You will find that the column names are cryptic. You can rename the columns by using aliases, like this: SELECT h90b7 as 'First Name', h0861 as 'Last Name' FROM jos_jforms_9d92d (You have to adjust with your table and column names...) -Mats - Mats @ Joomla - R - Us Mon, 29 Mar 2010 17:14:08 +0100 http://joomla-r-us.com/home/48-sql-2-excel/62-sql-2-excel-tutorial#comment-270 Hi. I have tables, created with JForms. Can I get the reports from those tables in database? What and how to define? Thanks - Priit Mon, 29 Mar 2010 16:35:24 +0100 http://joomla-r-us.com/home/48-sql-2-excel/62-sql-2-excel-tutorial#comment-264 @mhpbkk : You can use substitution variables in the SQL query.{USER_ID} for example, will be replaced with the current users user id. More info about substitution variables: http://joomla-r-us.com/faq/31-general/66-sql-2-excel-variable-substitution-how-to PS! Please use the forum for support questions in the future. - Mats @ Joomla-R-Us Mon, 22 Mar 2010 00:39:30 +0100 http://joomla-r-us.com/home/48-sql-2-excel/62-sql-2-excel-tutorial#comment-261 thanks! that unix statement seems to work. thanks for the super fast support! another question, I want to filter the reports based on a user's session. ie. detect who is logged in then filter the report by that persons userID and their session to show the data that applies to them only. I am using ProjectFork and am trying to use your module to report on tasks, ProjectFork is a Joomla component and so should be linked to the session log for joomla I presume. could you point me in the right direction how to do this? - mhpbkk Sun, 21 Mar 2010 17:44:41 +0100