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
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
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
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
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
Redirect if userid is not found in a database table
The first problem is that you're using joomlausers->id which doesn't look correct. It should be $joomlausers->id (with a $ at the beginning).
Secondly, loadObjectList() returns an indexed array ...
- 26.9k
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
SQL syntax error in query with subquery using JDatabase
to spot a sql syntax error it's useful to view the resultant sql eg.
echo $query->__toString();
you've put the alias of the subquery 'b' as the alias of the table inside the subquery. But the ...
- 3,339
3
votes
Accepted
Cannot get num rows with the database driver
I found out that I need a $dbo->execute() before the $dbo->getNumRows(). It is interesting, since the $dbo->loadObjectList() does not require it.
Andy found the important part in the docs ...
- 391
3
votes
Accepted
Global PHP functions?
To add a global function you just need to declare a function early in the lifecycle where you are not in a class - all functions per se are global in php. A reasonable place to do so that would not ...
- 3,339
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) {
...
- 496
3
votes
Accepted
How to write an Insert Select Statement?
Although this is an old question, it might be helpful to others. Here is the insert by select query using JDatabase.
Build some select query using JDatabase
$querySelect = $db->getQuery(true)
-&...
- 3,001
3
votes
Redirect if userid is not found in a database table
I don't think it is very direct to filter the result set when sql is perfectly qualified to do such logic.
As Lodder corrected, you also need to use setQuery(), fix your missing $, adjust your result ...
- 4,846
3
votes
Accepted
SQL syntax error in query with subquery using JDatabase
You can actual fetch the desired result without implementing a JOIN on a subquery.
Use a LEFT JOIN if you want the possibility of zero counts in the results. Use a regular JOIN (INNER JOIN) if you ...
- 4,846
3
votes
How do I create a new PHP variable for a blog override from the result of JDatabase join query?
Your code contains a few errors, which is why you're getting a white page.
1)
$db->('f.featured_up') needs to be replaced with $db->quoteName('f.featured_up')
2)
You're missing a comma (,) after ...
- 26.9k
2
votes
How to relate comma-separated ids from one table to the ids of another table?
What would be the best way to achieve this?
There are two pieces of "best practice" that I am compelled to share for this question.
Don't make more trips to the database than you need to.
Because ...
- 4,846
2
votes
Global PHP functions?
I would suggest avoiding writing a "global" function. Yes it would be very easy to write one, but you'll be going against Joomla's architectural design.
My suggested way of doing this would be to ...
- 610
2
votes
Accepted
What's the proper chaining for WHERE
I recommend using quote the table name.
You can try this. I guess you missed removing the dots in the from clause. If this does not work, please add the error message you get.
$query->select('...
- 459
2
votes
Accepted
Using JDatabase standalone, ie. without Joomla application bootstrapping
Without the bootstrap file being available I loaded the framework without the application to check the performance like this in a folder called app.
$time_start = microtime(1);
define('_JEXEC', 1);
...
- 3,339
2
votes
Accepted
Downloading large data sets
ad 1. The best option would be using streams. Unfortunately, JDocument does not support streams, so a temporary file might be the best available option. In your case it might be possible to send the ...
- 263
2
votes
Accepted
Raw sql query returns multiple rows, but with Joomla methods only one row is returned
I found the error in my query.
Original:
JOIN mus_virtuemart_product_categories as pc ON pc.virtuemart_product_id=vmp.product_parent_id
Joomla query:
->join('INNER', $db->quoteName('#...
- 41
2
votes
Accepted
How to apply aliases in SELECT clause when two columns from JOINed tables have the same name?
Further explanation of the issue:
As you discovered, when two columns in the SELECT clause of a query share the same column name (or alias), the latter value overwrites the former in the result set. ...
- 4,846
2
votes
Which files do i need to include to perform database query?
You will need to load the Joomla framework at the top of the PHP file, like so:
<?php
define('_JEXEC', 1);
define('JPATH_BASE', './'); // Assumes the PHP file is in the Joomla's root directory
...
- 26.9k
2
votes
Accepted
Decode json strings from a database table column
It looks like you are mangling valid json data with explode() (the wrong tool for the job).
You just need to call json_decode() on each string. This can be done with array_map() or a classic foreach()...
- 4,846
2
votes
Accepted
Connecting to an external database in Joomla 4
Use Joomla\Database\DatabaseFactory::getDriver():
$db = (new Joomla\Database\DatabaseFactory)->getDriver('mysqli', $options);
- 12k
2
votes
Accepted
MySQL "LOAD DATA LOCAL INFILE" with database connection object in Joomla 4
Since 4.0, Joomla database layer always uses prepared statements. And as the error states, the query you're trying to run is not supported when used in prepared statements. This is true at least for ...
- 12k
1
vote
Query gives a single value when two columns in SELECT clause
For the most part, your code is pretty right. As commented, you are using ->lookup() -- a method that is currently unheard of.
Generally, you would only use the LIKE operator if you were going to ...
- 4,846
1
vote
Error when populating a database table while installing a module
Your database contained more than one Joomla table set. The install script got confused by that.
- 459
1
vote
How to SET a user-defined MySQL variable and increment it to produce ranking values with JDatabaseQuery?
Joomla syntax aside, let's start by explaining your desired action.
You want to apply an incrementing counter to a specific column for all rows in the table. This is a very reasonable and common ...
- 4,846
Only top scored, non community-wiki answers of a minimum length are eligible