Hot answers tagged

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->...
user avatar
  • 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> ...
user avatar
  • 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(); $...
user avatar
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#...
user avatar
  • 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 ...
user avatar
  • 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('...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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> <...
user avatar
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 (' ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 $...
user avatar
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'); ...
user avatar
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) { ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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.
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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` ...
user avatar
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> ...
user avatar
  • 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 ...
user avatar
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!
user avatar
  • 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....
user avatar
  • 1,120

Only top scored, non community-wiki answers of a minimum length are eligible