Skip to main content
12 votes
Accepted

How to Use IN Clause in Joomla Query

First, the trouble with ->where($db->quoteName('status') .' IN '.$db->quote(1,2,4)) is not only that a syntax error is generated on the query, but also that $db->quote() is treating 1 as ...
mickmackusa's user avatar
  • 4,826
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#...
mickmackusa's user avatar
  • 4,826
6 votes
Accepted

with insert on duplicate key update - updating multiple rows at once

Building a REPLACE INTO query is just as simple as building an INSERT ... ON DUPLICATE KEY UPDATE. Note that there is a difference between REPLACE INTO AND ON DUPLICATE KEY UPDATE. Code: $columns = ...
mickmackusa's user avatar
  • 4,826
6 votes
Accepted

Joomla 4 create article programmatically

I rewrote the "Content Uploader" for Joomla 4 because I couldn't reach the developer. I had exactly the same problem as you described in the first example. The reason is that the Workflow ...
Gratia-Mira's user avatar
6 votes

Joomla 4 create article programmatically

This will work. $newapp = JFactory::getApplication(); $mvcFactory = $newapp->bootComponent('com_content')->getMVCFactory(); $articleModel = $mvcFactory->createModel('Article', 'Administrator',...
Na Nguyễn's user avatar
5 votes
Accepted

How to relate comma-separated ids from one table to the ids of another table?

Just 2 simple queries, with first you select commas and then you check if IDs are inside that string. Just tested and it works using MySql IN() function with $commas string value fetched from $db->...
Adam M.'s user avatar
  • 447
4 votes

Display a Value from a Specific DB Field

I suspect the issue is due to your select statement missing quoteName and a malformed where statement you should also use chaining (and qn alias for quoteName q for quote) This should be the right ...
Walt Sorensen's user avatar
4 votes

What does 'lft' and 'rgt' do in #__menu table?

The lft and rft code is quite old in Joomla, and only used in a sub-set of core functionality. Basically it means... lft: the item id of the item to the left rgt: the item id of the item to the ...
AndyGaskell's user avatar
4 votes
Accepted

Jdatabase : Sorting two selections?

I don't believe you need to use 2 queries. try the following: $db = JFactory::getDbo(); $query = $db->getQuery(true); $query->select(array('roll_no', 'attended_no')) ->from($db->qn('...
Lodder's user avatar
  • 26.9k
4 votes
Accepted

SQL syntax error when attempting to filter list view - how to build WHERE clause?

Try changing your query to the following: $query->select('#__users_2679886', 'name') ->from($db->qN('#__users', '#__users_2679886')) ->where($db->qN('id') . ' = ' . $db->...
Lodder's user avatar
  • 26.9k
4 votes
Accepted

Component SQL Updates Not Running

Figured this out. There was a bad version number in the #__schemas table. I rolled that version number back to a valid one and the updates work again.
Zachary Draper's user avatar
4 votes
Accepted

MySQL SELECT query with LIMIT to show a maximum of 3 images

Problem being is you're using loadObject() which will only ever return 1 result. If you want multiple, you'll need to use loadObjectList(), like so: $db = JFactory::getDbo(); $query = $db->...
Lodder's user avatar
  • 26.9k
4 votes
Accepted

Insert query with ignore of duplicates (without key)

As you're using MySQL, you can create an unique index over your columns original_id and analogue_id. Then use a raw query like: $db = JFactory::getDbo(); $query = 'INSERT IGNORE INTO ' . $db->...
christian's user avatar
4 votes

Why my custom query only returns one result?

You should use loadColumn instead of loadResult. loadResult is used when you want to get only 1 value from the query (and is typically used when the query returns just one value), loadColumn is used ...
itoctopus's user avatar
  • 3,226
4 votes

How to check if specific table names exist in a database?

What is likely to be tripping you up is that your $tableArray doesn't have prefixes. This is evident in your query because you prepend the tablename with #__ in the FROM clause. The resultset will ...
mickmackusa's user avatar
  • 4,826
4 votes
Accepted

How to write a query with GROUP BY and ORDER BY using Joomla's query methods?

I see minor typos in each of your coding attempts (misspelled method names: selcet and loadObjectLsit). Write the GROUP BY order like so: (Reference Link) SELECT id, DATE_FORMAT(estEventDate, '%b %Y'...
mickmackusa's user avatar
  • 4,826
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 ...
mickmackusa's user avatar
  • 4,826
4 votes
Accepted

loadAssocList() is not working properly when assigning a column name as the key parameter

Most critical to fixing your issue is to include the p.ComponentId column in your select() call so that it is available at the time the result set is being constructed. If you have your error ...
mickmackusa's user avatar
  • 4,826
4 votes

Is the following everything one should do to prevent SQL injection?

First, when reading user input you should make sure it's the correct type. The Input/Filter APIs don't handle array/string conversion. So if you're expecting STRING filter to return a string be aware ...
Sharky's user avatar
  • 11.5k
3 votes
Accepted

Quotes Displayed in Database Value

As far as I can tell from the profiles plugin code, the speech marks come from data being JSON encoded before being entered into the database. $tuples = array(); $order = 1; ...
RichardB's user avatar
  • 3,082
3 votes

Display Entry when referencing 2 Tables

You should review how to use JDatabase for queries, what you wrote is basically a RAW SQL statement and left off the join for the second table. Here is the correct structure with chaining (it should ...
Walt Sorensen's user avatar
3 votes

How to share table between 2 or more databases?

Yes, you can share the users from one Joomla! site to multiple other Joomla! sites (as long as you keep them on the same J! version). This is how you do it in Joomla! 2.5 - 3.6 (current version): ...
Michael Yaeger's user avatar
3 votes

Joomla Debug Console does not show Database Queries

This is kinda of necroposting, but an issue still has no fix from Falang author, so here is it. In the file ./plugins/system/falangdriver/falangdriver.php Find (309 row for Falang 2.6.0): $db->...
Shader's user avatar
  • 31
3 votes
Accepted

Joomla Debug Console does not show Database Queries

Found out that the joomla database driver is being overridden by another one which belongs to Falang component that allows you to manage all the content translations. Falang database driver has no ...
Aung Myo Linn's user avatar
3 votes

How can I test Joomla performace

For website performance test you can use some online service like: http://www.webpagetest.org/ http://tools.pingdom.com/ https://gtmetrix.com/ https://developers.google.com/speed/pagespeed/insights/ ...
AlejandroVega's user avatar
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 ...
Gregory O'Connor's user avatar
3 votes
Accepted

How to tell what WAS the local time of an article?

In database tables, dates are always in UTC. Coordinated Universal Time (French: Temps universel coordonné), abbreviated as UTC, is the primary time standard by which the world regulates clocks ...
Anibal's user avatar
  • 7,445
3 votes
Accepted

Jdatabase : Counting number of elements in a comma separated field

One approach would be the following: $db = JFactory::getDbo(); $id = 20; // Example ID $query = $db->getQuery(true); $query->select($db->qn('roll_no')) ->from($db->qn('#...
Lodder's user avatar
  • 26.9k
3 votes
Accepted

Optimize Joomla SQL Query to store data in array and pass on array output to field

There's no need for multiple queries in your case. You can simply select email and mobile from the table, like so: $id = JFactory::getApplication()->input->getInt('vid', ''); $db = JFactory::...
Lodder's user avatar
  • 26.9k
3 votes

AJAX post not able to update database

After more searches, I managed to work it out. Hopefully the below will offer some small mercy to some lost joomla soul like I was. The main challenge was constructing the URL and get a response. So ...
Phillip's user avatar
  • 61

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