21
votes
Accepted
How to combine ANDs and ORs in the WHERE clause using a Query Object?
Including the parentheses that are needed, with Chaining , produced the desired SQL:
With Chaining: specifying OR and wrapping Cond1/2 in parentheses
$query = $db->getQuery(true);
$query->...
- 7,665
9
votes
Accepted
Structure folder of sql files in my component
As Milton already wrote you specify where those SQL files are in the install manifest file. There are three different locations for install, uninstall and update.
<install>
<sql>
...
- 5,527
8
votes
Accepted
How can I get the names of the columns in an object or table with Joomla's SQL?
Your foreach will result in situation where $key is array key and $value is one row from database. If you want to get column names from result just use get_object_vars:
$db = JFactory::getDbo();
$...
- 1,154
8
votes
Accepted
SQL Injections using Jfactory::getDBO and setQuery() with LIKE condition
You won't need to worry about sql injection attacks if you use the quote() ( or q()) and quoteName() (or qn()) methods appropriately.
From https://docs.joomla.org/Secure_coding_guidelines#...
- 4,388
7
votes
Accepted
Recommended practices regarding transfering database from Joomla 1.5 to Joomla 3
What I would recommend you do is:
Export the database for the Joomla 1.5 site, apart from the #__users table
Install a fresh copy of Joomla 1.5 on your localhost
Import the database dump you ...
- 26.4k
6
votes
Accepted
How to write my MySQL query with JOIN logic using JDatabase methods?
$db = JFactory::getDbo();
$query = $db->getQuery(true)
->select('user.firstname, user.lastname, leadership.leadname, leadership.lead_des')
->from('CLUB,LEADERSHIP,USER')
->where('...
- 2,971
5
votes
Accepted
What does sql_select="e.*" do?
I guess the example you are talking about is this:
"SELECT e.* FROM #__example AS e GROUP BY name ORDER e.id ASC"
Ths basically mean select everything from the table #__example (which has an alias ...
- 206
4
votes
Accepted
Recycling IDs: re-using articles, category's ID that have been trashed for new articles
That practice would actually be terrible for SEO. You would be hurting your search content by regularly replacing content on the same id. Aside from that, even if you did reuse the ids, they would ...
- 394
4
votes
1054 Unknown column 'extra_query'
You give very little information to give a good answer, but there are a couple of things you can try:
Make sure both Joomla and your template is up to date
Re-upload your template if possible
Fix ...
- 13.3k
4
votes
Recommended practices regarding transfering database from Joomla 1.5 to Joomla 3
Finally, I've done the export/import via CSV (as I also had to edit all the fulltext entries) and here are the little things that I had to search for:
export the Db via CSV, then edit it with Open ...
- 986
4
votes
Structure folder of sql files in my component
The convention is usually the admin directory, but beyond that it shouldn't matter as long as you reference the files' location correctly inside of your main xml file.
like:
<install>
<...
- 422
4
votes
Accepted
JDatabase JOIN on multiple values
In your ON statement you should be able to add multiple conditions combined by AND and OR. So something like this should do the trick:
->join('INNER', $db->quoteName('app_animal', 'b') . 'ON (' ...
- 3,693
4
votes
Accepted
Issues to be aware of when calling `concatenate()`, `regexp()`, `rand()`, and `castAsChar()` in sql queries
All of my demonstrations will assume this line is already declared:
$db = JFactory::getDBO();
I will also provide a "guinea pig" table as a demo reference and playground for researchers when sample ...
- 4,388
4
votes
Accepted
(RuntimeException $e) vs (JDatabaseExceptionExecuting $e) for SQL exception handling
The difference between a RuntimeException and a JDatabaseExceptionExecuting is a matter of semantics and inheritance.
In PHP, RuntimeException extends the Exception class (the base class -- which ...
- 4,388
3
votes
Join an existing table with a generated table of dates and count rows for each date?
You can just execute it in the same way than a simple query.
db->setQuery($my_big_query);
$row = $db->loadObject();
Of course, it would be very difficult to translate the SQL ...
- 7,415
3
votes
Accepted
Extension install scripts without raw sql
You can define multiple database install scripts for every database driver like MySQL or MSSQL that you like to support in your installation XML file. Have a look at the example XML here in the ...
- 3,693
3
votes
Accepted
How to Create a Foreach Loop that Returns Users In a Specific Usergroup
To answer the actual question, this code will give you a listbox of UserIDs and Names. Retrieving all the extra data would then just be a SQL select statement
$myGroupID = 2; //Your User Group to ...
- 7,665
3
votes
Multiple INSERTS in one query Joomla
You can split the sql and run the statements using below code
function insertSql($filename){
$db = JFactory::getDbo();
$status = true;
//parsing of SQL file
$...
- 158
3
votes
Accepted
Multiple INSERTS in one query Joomla
Use JDatabaseDriver::splitSql($query) so your function will look like this:
$query = "INSERT INTO `table` (`fied1`, `field2`, `field3`) VALUES ('one', 'two', 'three'),('four', 'five', 'six');
...
- 1,154
3
votes
How can I get the names of the columns in an object or table with Joomla's SQL?
To get all columns of a table as arrays use JDatabase class method as follows:
$db=JFactory::getDBO();
$columnArr=$db->getTableColumns("#__table_name");
foreach ($columnArr as $key => $value) {
...
- 486
3
votes
The MySQL adapter mysqli is not available - how to resolve this issue?
I had this error after a reboot on one site of 12 on my server. All my sites are Joomla. We recently upgraded to php7.1 I tried everything then found the fix.
The fix was to comment out the suPHP ...
3
votes
What does sql_select="e.*" do?
When you are only referencing one table in your query, there is no need to explicitly reference the table when writing columns. Also, the default order direction for sql is ASC.
So your syntax could ...
- 4,388
2
votes
How can I select the IDs of a category and all its (n levels) subcategories?
Check the mysql join map here:http://www.codeproject.com/KB/database/Visual_SQL_Joins/Visual_SQL_JOINS_orig.jpg
One of them is what you are looking for.
- 21
2
votes
Accepted
Component Update: DB error file function returned no error
Figured out the issue, when making a component you need to have at least 1 blank update sql file or it will fail. Not just the typical index.html file to make sure then installer does not skip the ...
- 2,856
2
votes
Error 1146 while installing K2 extension
This situation can happen if K2 was installed in the past and not removed correctly or after a failed install attempt.
Have a look with your FTP client for either of the directories below as K2 ...
- 275
2
votes
Error 1146 while installing K2 extension
Run this query. Don't forget to change the table name and database name to your's bq1ry_k2_categories.
CREATE TABLE IF NOT EXISTS `bq1ry_k2_categories` (
`id` int(11) unsigned NOT NULL,
`name` ...
- 47
2
votes
Extension install scripts without raw sql
What you can do is have a separate file for each database drive type, for example:
<install>
<sql>
<file driver="mysql" charset="utf8">path/to/sql/file</file>
...
- 26.4k
2
votes
Retrieving multiple results from the database
The "official" Joomla way is to use the JDatabaseQuery class to create the query and give it to the Database layer. Then you use the loadAssocList or loadObjectList function to get a array with all ...
- 2,033
2
votes
Accepted
After Joomla update duplicate or create new module get id : 0
Hi Check your data base xxx_module must assign AUTO_INCREMENT STATUS, after it will be ok!
- 36
2
votes
How to rewrite SQL-query with CASE/WHEN to Joomla-based "format"
$query->select('a.virtuemart_product_id')
->from('#__virtuemart_product_categories AS a')
->select('(CASE WHEN '.$db->qn('b.product_override_price').' > 0
THEN '.$db->qn('b....
- 1,120
Only top scored, non community-wiki answers of a minimum length are eligible