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();
$...
- 1,174
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,646
6
votes
Accepted
Combining 'ANDs' and 'ORs' in a complex WHERE query - Part 2
The second parameter of where, called $glue, does not work as you might expect.
This is the source code of the where function:
public function where($conditions, $glue = 'AND')
{
if (is_null($...
- 3,713
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
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,646
5
votes
Accepted
JDatabase Like Operator
try this
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query
->select('Common_name')
->from($db->qn('your_table_name'))
->where($db->qn('Common_name').' LIKE '. $...
- 2,981
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
How to use Oracle database for populating a list in a form
There is a file called configuration.php in root directory. Open that file,
find:-
public $dbtype = 'mysqli';
change that value to:
public $dbtype = 'oracle';
this will change database driver, ...
- 650
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 (' ...
- 3,713
4
votes
Accepted
Delete items of database via CLI
It's been awhile since I've done this, but as I recall putting this within doExecute should do what you want.
$basePath = JPATH_ADMINISTRATOR.'/components/com_content';
require_once $basePath.'/...
- 7,695
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.7k
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.7k
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
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,646
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.7k
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,646
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,209
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) {
...
- 486
3
votes
Accepted
JDatabaseQuery quote correctly the items into select
$db->quoteName(mixed $name, mixed $as = null)
https://api.joomla.org/cms-3/classes/JDatabaseQuery.html#method_quoteName
With the quoteName-function you can quote column names. It accepts strings ...
- 3,713
3
votes
Method for creating a subquery using JDatabase
There is also a way to execute queries which contain subqueries using the Joomla Platform API.
The basic idea on how to use subqueries is based on gunjanpatel.
Here is an example for executing ...
- 215
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 ...
- 7,415
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('#...
- 26.7k
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,209
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,646
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,646
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.7k
2
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)
-&...
- 2,981
2
votes
Accepted
How to combine several sets of 'ANDs' and 'ORs' in a complex WHERE query object?
Your OR condition is breaking it. Right now it is.
if condition1 = TRUE AND condition2 = TRUE AND condition3 = TRUE OR condition4 = TRUE
So you don't have () around conditions 3 and 4. This will ...
- 3,208
2
votes
How to refactor my iterated sequence of queries?
At least I see a way how to combine your first 2 select queries into one with the help of SQL JOIN:
$query->select('sid')->from('#__student')->where('id = '.$pk);
$db->setQuery($...
- 3,713
Only top scored, non community-wiki answers of a minimum length are eligible