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,543
8
votes
Accepted
Two left join in one query to Joomla database
$db = JFactory::getDBO();
$query = $db->getQuery(true);
$query->select('a.id, a.name, c.id as parent_id, c.name as parent_name')
->from('#__records as a');
$query->select('b.parent as ...
- 4,056
8
votes
Adding Multiple Users in Joomla
I see in the tags of your question you are using mysql and phpmyadmin, so I assume you are after a a way to INSERT records of users directly into the Joomla database.
However please note that, the ...
- 19.6k
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,543
7
votes
Joomla DB Insert, how to get last inserted id?
To get the ID of the last inserted row, you can use insertid() like so:
$db->query();
$lastRowId = $db->insertid();
You can then use $lastRowId where ever you wish in your next query
Update ...
- 26.6k
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,981
6
votes
Accepted
How to get database information from Joomla category
All I had to do was change content to category and I was able to access all data within the category table using the same method I did with the content.
$input = JFactory::getApplication()->input;...
- 191
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,543
5
votes
Accepted
Joomla update broken updates table
I resolved this by a fresh and clean install of Joomla 3.3.6, then I migrated across all of the relevant database tables. Not pretty (or quick) but it's working now at least!
* UPDATE *
This fix ...
- 2,046
5
votes
Accepted
What to use instead of getNumRows() in Joomla 3.x
[EDIT]
Use the Get Affected Rows function for INSERT, UPDATE, REPLACE or DELETE queries in Joomla
$db->getAffectedRows();
.
--Below is included as the original answer but not really the most ...
- 533
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->...
- 437
4
votes
Accepted
Joomla 2.5, how to use "HAVING" using Query Object
If you are using having clause without group_by then it will act just like where clause.
JDatabaseQuery class provides method for having clause.
Try this
$query->having('distance <=50');
- 1,791
4
votes
Accepted
Storing data in a single column of a table OR as the part of json
There are several ways to store data in databases, how you wish to do it depends on your application, it's depth and future growth.
I'm no expert in this area, but I do have the experience to know ...
- 166
4
votes
Filter strings before saving them in the database
To avoid MySQL-Injection-Attacks, you can have to properly escape and quote special characters. For example:
$query = 'SELECT * FROM #__table WHERE ' . $db->quoteName( $field-name ) . '=' . $db-&...
- 7,415
4
votes
Filter strings before saving them in the database
Additionally to quoting the data as Anibal already correctly explained, you can use JFilterInput->clean() to clean the various inputs. That's especially helpful if you expect the data to be of a ...
- 5,537
4
votes
Accepted
Will changing Article Category in Bulk with MySQL affect the integrity of assets table?
Yes it will.
Instead go to:
Joomla! Administrator
-> Content
-> Articles
Click the tick box to tick all article.
Then click 'Batch' up the top.
Under:
To Move or Copy your selection please ...
- 440
4
votes
What to use instead of getNumRows() in Joomla 3.x
getNumRows() is valid in Joomla 3.x.
As for your error...
"getNumRows() will return the number of result rows found by the last query and waiting to be read. To get a result from getNumRows() you ...
- 502
4
votes
The safety of direct SQL-requests in Joomla templates/views
Joomla is built on the MVC design pattern, so querying the db from a view is bad practice. But with your scenario, let's consider a few alteratives:
changing the model: this makes no sense: you can't ...
- 896
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 ...
- 502
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 ...
- 716
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('...
- 26.6k
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.6k
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.6k
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,186
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,543
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,543
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,543
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,543
Only top scored, non community-wiki answers of a minimum length are eligible