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 ...
- 4,846
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,846
7
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 ...
- 100
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 = ...
- 4,846
6
votes
Joomla 4 create article programmatically
This will work.
$newapp = JFactory::getApplication();
$mvcFactory = $newapp->bootComponent('com_content')->getMVCFactory();
$articleModel = $mvcFactory->createModel('Article', 'Administrator',...
- 103
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->...
- 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.
- 1,236
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->...
- 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->...
- 56
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 ...
- 3,236
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 ...
- 4,846
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'...
- 4,846
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,846
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 ...
- 4,846
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 ...
- 12k
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 ...
- 61
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::...
- 26.9k
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
Accepted
SQL WHERE clause with a variable (current article ID)
Your code contains several errors, including a syntax error. You should use an IDE with proper code completion and signature hints (any modern IDE should be able to do that).
$query->select('value'...
- 263
3
votes
Accepted
Check if INSERT query was successful or failed because of a duplicate UNIQUE KEY
You will need to perform a select query against your database table, searching for the email address. If a result is returned, you can throw an alert message, else perform the insert query, something ...
- 26.9k
3
votes
How to generate missing column data using a reformatted substring of another column?
I believe this should do it:
UPDATE m5cob_mt_links
SET alias = REPLACE(SUBSTRING_INDEX(LOWER(link_name), ' ', 5),' ','-');
EDIT: Added REPLACE for ampersand, comma, and double dashes as well as a ...
- 1,901
3
votes
Accepted
How to write a query with a WHERE clause with two conditions?
Try this:
$query->WHERE($db->quoteName('test_id').'= 1 AND '.$db->quoteName('user_id').'='. $user->id)
- 1,834
3
votes
Accepted
How to check if specific table names exist in a database?
When using SHOW TABLES load results with loadColumn() to receive a simple array with table names as values.
print_r(JFactory::getDbo()->setQuery('SHOW TABLES')->loadColumn());
Output:
Array
(
...
- 12k
3
votes
Accepted
How to query for users that have all tags when searching for multiple tags?
Tags - Similar module uses HAVING clause to achieve this.
$query->having('COUNT(' . $db->quoteName('tags.tag_id') . ') = ' . count($tags));
But I'm not sure if this is the best possible ...
- 12k
3
votes
Accepted
How to write the logged in user id in Plotalot queries?
Plotalot includes various variables that can be used in sql queries, eg.
%%J_USER_ID%%
is the currently logged in user_id. The Plotalot User Documentation Available as PDF here
- 3,339
3
votes
Linking JOOMLA! database to a flutter application
To connect Joomla! to a 3rd party app you will need an API connection.
There are a few options:
You can install Jbackend on Joomla and use rest API to access the database. https://extensions.joomla....
- 31
3
votes
How to add pagination in joomla custom php code?
You have the instruction in your code, but commented.
The query setLimit() function requires two parameters:
$limit: the number of records to return, and
$offset: the first record you want to return, ...
- 941
3
votes
Accepted
How to show multidimensional data in my CanvasJS chart?
Your original coding attempt is repeatedly overwriting the $dataPoints variable inside of the loop -- this is why you only see the last iteration's data.
To create the desired multidimensional array ...
- 4,846
3
votes
Accepted
using Ajax to save contents of html how to make safe
On the SQL side, data inserted using JDatabaseDriver::insertObject() and JDatabaseDriver::updateObject() methods is escaped automatically.
On HTML side, you should filter user input to prevent XSS. ...
- 12k
3
votes
Accepted
How can I get the names of the columns with their corresponding row values as a list to use in a field (select list) of a form in Joomla MySQL
Start with:
$assoc = $db->loadAssoc();
https://docs.joomla.org/Selecting_data_using_JDatabase#loadAssoc.28.29
This will provide a flat, associative result set array:
$assoc = [
'AIG' => '...
- 4,846
Only top scored, non community-wiki answers of a minimum length are eligible