Joomla! Documentation - User contributions [en]

Selecting data using JDatabase

2017-10-27T14:21:11Z

Bicudomaschio:

Selecting data using JDatabase

2017-10-27T14:19:40Z

Bicudomaschio:

J3.x:Developing a component frontend update function/csv and xlsx import

2017-10-05T19:37:43Z

Bicudomaschio: /* 23.3 Source files for this step */

{{Chunk:Developing a component frontend update function - Contents}}
__NOTOC__

== 23 csv and xlsx import ==

=== 23.1 Description ===

If your Joomla application requires updates with a substantial amount of data, it may be more convenient to have the user collect the data offline and upload it in a file than to require the data to be entered interactively. Fortunately, it is quite easy to upload files using html and php facilities, but it does require carefully designed code to avoid upload problems or security issues. See for instance [http://www.w3schools.com/php/php_file_upload.asp w3schools.com] or [http://php.net/manual/en/features.file-upload.post-method.php php.net] .

File upload is based on an html form that includes an ''<input type=”file”>'' tag. This ''<input>'' tag is displayed as a button, which pops up a file selection dialog when clicked. The data from the file will be posted to the server when the form is submitted, much like data that a user has entered by filling in an ''<input type="text">'' text field.

The upload functionality is implemented by means of the following classes:
* first, an upload button is defined that is attached to a form with the ''<input type=”file”>'' tag. When this button is clicked, a file selection dialog is displayed and the user can submit the file for upload.The button is defined in'' 'admin/button/importfile.php' .''
* The uploaded file is received as post data by controller'' 'admin/controllers/helloimport.php' .''
* The data is stored in the database by model class'' 'admin/models/helloimport.php' .''

=== 23.2 The changes ===

==== 23.2.1 Files: ====
* README.md
* helloworld.xml

Again, these files have been updated to reflect version info.

==== 23.2.2 admin/button/importfile.php ====

This file defines a button class of type'' 'importFile' ''which inserts a form with the ''<input type=”file”>'' button in the view presented to the user.

==== 23.2.3 admin/views/helloworlds/view.html.php ====

This view is updated to include the above button.

==== 23.2.4 admin/controllers/helloimport.php ====

This controller receives the uploaded data. It has two main entrypoints: ''importcsv()'' for processing an uploaded csv file, and ''importxls()'' for an uploaded Excel file. The Excel data is read using the ''PHPexcel'' package, as described in the export chapters 22 of this tutorial.

==== 23.2.5 admin/models/helloimport.php ====

The database is updated with this model class. For simplicity of this tutorial, only the fields'' 'id', 'uid' ''and'' 'greeting' ''are imported.

=== 23.3 Source files for this step ===
* [https://github.com/Hy-Fly/Joomla-Hello-Front-End/archive/v1-23.zip Download Joomla-Hello-Front-End v1-23]
* [https://github.com/Hy-Fly/Joomla-Hello-Front-End/commit/c84f0dc262a3fec2cc3455972ba510f61a4fc2a9 View Joomla-Hello-Front-End v1-23]
* Requires library https://github.com/vdespa/PHPExcel-Joomla-Library

=== 23.4 Testing the features of this step ===
* Create a csv or Excel file to import. You can use a previously exported file and adapt that, or you can create a new file from scratch.
* Log in either the backend or the frontend as a user with upload permission.
* Upload the file by clicking'' 'Import csv' ''for a csv import file, or'' 'Import xls' ''for an Excel file.
* Check the import by inspecting the item list overview page, and/or the item edit page.

<noinclude><div class="row">
<div class="large-6 columns">{{Basic button|J3.x:Developing_a_component_frontend_update_function/Excel_xlsx_export|Prev: Excel xlsx export|class=expand success}}</div>
<div class="large-6 columns">{{Basic button|J3.x:Developing_a_component_frontend_update_function/Import_with_summary_screen|Next: Import with summary screen|class=expand}}</div>
</div></noinclude>

J3.x talk:Developing a component frontend update function/Excel xlsx export

2017-10-05T19:27:46Z

Bicudomaschio:

~ I need help to fix this page. The "Excel xlsx export" page should be item 22 and it is repeating the contents of the previous page, item 21 - somehow the contents of the page are missing or the links broken, pointing to previous page.

The previous page is https://docs.joomla.org/J3.x:Developing_a_component_frontend_update_function/Improved_export_function

The next page is https://docs.joomla.org/J3.x:Developing_a_component_frontend_update_function/csv_and_xlsx_import

J3.x talk:Developing a component frontend update function/Excel xlsx export

2017-10-05T19:24:19Z

Bicudomaschio: Inconsistent navigation of pages

~ I need help, to fix this page. The "Excel xlsx export" page should be item 22 and it is repeating the contents of the previous page, item 21

Using the union methods in database queries

2016-09-25T17:40:29Z

Bicudomaschio:

<noinclude><languages /></noinclude>

<noinclude>{{Joomla version|version=3.3|time=and after}}</noinclude>
{{-}}

{{amboxNew
| type = serious
| title = <translate>
This page covers Joomla! 3.3 or later only.</translate>
| image = content
| text = <translate>
'''Although the union methods are present in Joomla! 2.5 and 3.x, they ''do not work'' in any release prior to 3.3.'''</translate>}}

<translate>

Using a '''UNION''' in a database query is a useful way to combine the results of two or more database '''SELECT''' queries that are not necessarily linked by a database relationship. It can also be a useful performance optimisation. Using a '''UNION''' to combine the results of different queries can sometimes be significantly faster than a single query with a '''WHERE''' clause especially when the query involves joins to other large tables.
</translate>

<translate>

For those familiar with a little set theory a '''UNION''' does exactly what you would expect; it merges the set of results from one query with the set of results from another query to produce a set of results which is the set union of both of the individual result sets. If you want the set to be ordered then you will need to pay some particular attention to the way that is done, as will be explained later.
</translate>

<translate>
==The basics== 
</translate>
<translate>

In order to use a '''UNION''' you need to be aware of the basic requirements established by the SQL server you are using. These are not enforced by Joomla, but if you don't comply with them you will get database errors. In particular, each '''SELECT''' query must return the same number of fields in the same order and with compatible data types in order for the '''UNION''' to be successful.
</translate>

<translate>
==A simple example== 
</translate>
<translate>

Suppose you have want to send out a mailshot to a group of people but the database is such that the names and email addresses you want to send to are not all in the same table. To invent an arbitrary example, suppose that you want to send the mail to all customers and all suppliers and that that the names and email addresses are stored in tables called, unsurprisingly, ''customers'' and ''suppliers'', respectively.</translate>

<translate>

This query will retrieve all the customer information that we need for the mailshot:
</translate>

<source lang="php">
$query
->select('name, email')
->from('customers')
;
$mailshot = $db->setQuery($query)->loadObjectList();
</source>

<translate>

while this query will do the same for all suppliers:
</translate>

<source lang="php">
$query
->select('name, email')
->from('suppliers')
;
$mailshot = $db->setQuery($query)->loadObjectList();
</source>

<translate>

Then you can combine the results into a single query like this:
</translate>

<source lang="php">
$query
->select('name, email')
->from('customers')
->union($q2->select('name , email')->from('suppliers'))
;
$mailshot = $db->setQuery($query)->loadObjectList();
</source>

<translate>

The result set obtained from the union query will actually be a little different from running the individual queries separately because the union query will automatically eliminate duplicates. If you don't mind that the result set may contain duplicates (which mathematically speaking means it isn't a set), then using <tt>unionAll</tt> instead of <tt>union</tt> will improve performance.
</translate>

<translate>
==Lots of ways to use union== 
</translate>

<translate>

The <tt>union</tt> (and <tt>unionAll</tt>) methods are quite flexible in what they will accept as arguments. You can pass an "old-style" string query, a <tt>JDatabaseQuery</tt> object, or an array of <tt>JDatabaseQuery</tt> objects. For example, suppose you have three tables, similar to the example above:
</translate>

<source lang="php">
$q1->select('name, email')->from('customers');
$q2->select('name, email')->from('suppliers');
$q3->select('name, email')->from('shareholders');
</source>

<translate>

Then all of these queries will produce the same results:
</translate>

<source lang="php">
// The union method can be chained.
$q1->union($q2)->union($q3);

// The union method will accept string queries.
$q1->union($q2)->union('SELECT name, email FROM shareholders');

// The union method will accept an array of JDatabaseQuery objects.
$q1->union(array($q2, $q3));

// It doesn't matter which query object is at the "root" of the query. In this case the actual query that is produced will be different but the result set will be the same.
$q2->union(array($q1, $q3));
</source>

<translate>
==union, unionAll and unionDistinct== 
</translate>

<translate>

There are actually three union methods available.
</translate>

<translate>
* <tt>union</tt> produces a true set union of the individual result sets; that is, duplicates are removed. The process of eliminating duplicates may or may not incur a performance hit, depending on the data sets and database structures involved.</translate>

<translate>
* <tt>unionAll</tt> produces a union of the individual result sets but duplicates are not removed.</translate>

<translate>
* <tt>unionDistinct</tt> is identical in behaviour to <tt>union</tt> and is merely a proxy for the <tt>union</tt> method.</translate>

<translate>==Using UNION instead of OR== </translate>

<translate>

There are some instances where using <tt>union</tt> can give a significant performance boost instead of the more commonly used alternative of an '''OR''' or an '''IN''' in a <tt>where</tt> clause.
</translate>

<translate>

For example, suppose you have a table of products and you want to extract just those products that belong to two particular categories. Typically this would be coded something like this:
</translate>

<source lang="php">
$query
->select('*')
->from('products')
->where('category = ' . $db->q('catA'), 'or')
->where('category = ' . $db->q('catB'))
;
$products = $db->setQuery($query)->loadObjectList();
</source>

<translate>

However, it is likely that you will see a useful increase in performance using <tt>union</tt> instead:
</translate>

<source lang="php">
$query
->select('*')
->from('products')
->where('category = ' . $db->q('catA'))
;
$q2
->select('*')
->from('products')
->where('category = ' . $db->q('catB'))
;
$query->union($q2);
$products = $db->setQuery($query)->loadObjectList();
</source>

<translate>

Of course, if you want to select products from more than just two categories, you can combine more individual queries together.


You should see a similar performance boost when replacing a <tt>where</tt> clause containing an '''IN''' statement.
</translate>

<translate>==Ordering results== </translate>

<translate>

If you want your results to be ordered then you need to be aware of how the database deals with '''ORDER BY''' clauses. The following comments apply to MySQL but probably apply to other databases too.
</translate>

<translate>

Suppose you want to output the names and email addresses in alphabetical order in the mailshot example above. Then you would simply do this:
</translate>

<source lang="php">
$q2
->select('name , email')
->from('suppliers')
;
$query
->select('name, email')
->from('customers')
->union($q2)
->order('name')
;
$mailshot = $db->setQuery($query)->loadObjectList();
</source>

<translate>

But suppose, for some reason, you wanted to output the names and email addresses in alphabetical order with all customers first, then all suppliers. This query will '''not''' give the expected result:
</translate>

<source lang="php">
$q2
->select('name , email')
->from('suppliers')
->order('name')
;
$query
->select('name, email')
->from('customers')
->order('name')
->union($q2)
;
$mailshot = $db->setQuery($query)->loadObjectList();
</source>

<translate>

This is because an '''ORDER BY''' clause in the individual '''SELECT''' statements implies nothing about the order in which the rows appear in the final result. A '''UNION''' produces an unordered set of rows. The query above will be syntactically correct, but the MySQL optimiser will simply ignore the '''ORDER BY''' clause on the suppliers '''SELECT''' statement and the '''ORDER BY''' clause on the customers '''SELECT''' statement will be applied to final result set rather than the individual result set.
</translate>

<translate>

The way around this is to add an additional column to the result set and sort on that in such a way that the results will have the desired ordering. Here's one way to do that:
</translate>

<source lang="php">
$q2
->select('name , email, 1 as sort_col')
->from('suppliers')
;
$query
->select('name, email, 2 as sort_col')
->from('customers')
->union($q2)
->order('sort_col, name')
;
$mailshot = $db->setQuery($query)->loadObjectList();
</source>

<translate>==Advanced ordering== </translate>

<translate>

However, there may be occasions when it is important to have an '''ORDER BY''' clause on the individual queries that won't be dropped by the query optimiser. Suppose you want to send a special offer to your top 10 customers and your top 5 suppliers. You will therefore want to apply a '''LIMIT''' clause in combination with the '''ORDER BY''' clause and in that case the query optimiser will not ignore the ordering. This is how you could do it:
</translate>

<source lang="php">
$q2
->select('name , email, 1 as sort_col')
->from('suppliers')
->order('turnover DESC')
->setLimit(5)
;
$q1
->select('name, email, 2 as sort_col')
->from('customers')
->order('turnover DESC')
->setLimit(10)
;
$query
->select('name, email, 0 as sort_col')
->from('customers')
->where('1 = 0')
->union($q1)
->union($q2)
->order('sort_col, name')
;
$mailshot = $db->setQuery($query)->loadObjectList();
</source>

<translate>

It's necessary to use a dummy query in this case because otherwise the <tt>order</tt> and <tt>setLimit</tt> methods would be applied to the final result set rather that the individual one.
If you experience errors reproducing this example, check if a patch is necessary: ticket tracker 4127
</translate>

<translate>

[[Category:Database]]
</translate>