21
Including the parentheses that are needed, with Chaining , produced the desired SQL:
With Chaining: specifying OR and wrapping Cond1/2 in parentheses
$query = $db->getQuery(true);
$query->select('* FROM #__myTable')
->where('(condition1 = true AND condition2 = true)','OR')
->where('condition3 = true');
Resulting SQL: (includes parentheses)
...
17
Yes, as far as i'm concerned, the way you built the subquery is the one adopted by the majority of joomla's extensions developers.
I use that same method on some of my extensions and custom extensions made for clients.
There is no "official" way of doing this, but doing it as you showed lets you use the query builder and still retain a good amount of ...
12
This can be accomplished by using ->insert() and ->values(), with the $values being an array of the strings with the columns to insert.
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$columns = array('col_one','col_two');
$values = array();
// Proper escaping/quotes should be done here, and probably in a loop, but cluttered the answer, so ...
10
Try this:
$rows = $db->loadrow();
echo '<table>';
echo '<tr>';
foreach($rows as $row) {
// display your results here
}
echo '</tr>';
echo '</table>';
You first echo out the base table structure and then display your results
10
AFAIK there isn't a built in way to do easy subqueries, which is probably a deficiency in the system and should be corrected via PR.
However, I see no issue with your example - seems reasonable enough.
~~~
Here's an example in response to @DavidFritsch's comment below. The more that I think about it though, the better I like the more simple approach ...
9
Both J2.5 and 3.x will vary.
Joomla 2.5:
$db->setQuery($query, 0, 5);
where 0 is the start/offset and 5 is the limit
Joomla 3.x
$query->setLimit(5);
This also accepts a 2nd argument which is the offset, but is not required
9
Simple answer is no.
Extended answer is mysql can log queries, however it has limits, and if there is even one difference between the environments it would lead to those queries not functioning. Even with that the only way to do it is though the server itself, since as far as I am aware there is no special logging added to JDatabase (Also if a component ...
8
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();
$query = $db->getQuery(true);
$query->select('*');
$query->from('#__table');
$db->setQuery($query);
$results = $db->loadObjectList();
// this will ...
8
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#Secure_on_search
Secure on search
Special attention should be paid to LIKE clauses
which contain the % wildcard character as these require special
escaping in ...
7
You are making this way too difficult. Joomla provides you with a feature called ACL that allows just that. For each record, Joomla will create an extra line in the #__assets table.
What you need to do is add an asset_id field to the database table:
`asset_id` INT(10) UNSIGNED NOT NULL DEFAULT '0',
create a role in a component (usually in a /...
6
There is basically nothing wrong with replacePrefix().
What actually happens is that you are sending to MySQL a string which is too large for a predefined setting (max_allowed_packet) and is reporting the error MySQL server has gone away, as explained in MySQL server has gone away obstructing import of large dumps.
Now, Joomla thinks this is just a ...
6
$db = JFactory::getDbo();
$query = $db->getQuery(true)
->select('user.firstname, user.lastname, leadership.leadname, leadership.lead_des')
->from('CLUB,LEADERSHIP,USER')
->where('CLUB.CLUB_ID=11')
->where('LEADERSHIP.USER_ID=USER.USER_ID')
->where('CLUB.CLUB_ID=Leadership.CLUB_ID');
Multiple where clauses by default clubbed with ...
6
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($this->where))
{
$glue = strtoupper($glue);
$this->where = new JDatabaseQueryElement('WHERE', $conditions, " $glue ");
}
else
...
5
To check for a value in multiple tables that do not have a relationship you are going to need either to make multiple queries, 1 for each table, or better make a UNION query.
Now, Joomla has the UNION database methods, but those work only on Joomla 3.3 and later.
The concept of the query you are going to need for your case should be similar to this:
$query ...
5
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 '. $db->quote($db->escape('o%')));
$db->setQuery($query);
$results = $db->loadObjectList(); // use loadResult() for single value result
Documentation ...
5
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->loadResult().
Can't thing about anything simpler.
// First get all commas
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select($db->...
4
Databases tends to be a big problem with all software development. Keeping things synced as a major pain for us too especially with staging and production sites.
One thing I have started to look at lately but not yet implemented is:
http://phinx.org/
Another 'poor mans way' is to export the DB schemas (if you're concerned for schemas, data is probably a ...
4
You have to type which values you want to insert
$pre->insert($db->qn('#__table'))->columns($db->qn($columns))->values(here values);
4
Joomla core support basic SQL queries. You can create a class to store several inserts and create a single final query to execute a single insert at the end.
public function flushInserts()
{
$db = JFactory::getDbo();
foreach ($this->bulk_tables as $table)
{
// No inserts
if (!array_key_exists($table, $this->...
4
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, list of drivers files can be found in:-
libraries/joomla/database/driver folder,
For more info see this article. Hope this helps.
Update -
If you want to ...
4
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 (' .$db->quoteName('a.animal'). 'LIKE' .$db->quoteName('b.id').' OR ' . $db->quoteName('a.animal') . ' IS NULL)')
4
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.'/models/article.php';
$artmodel = new ContentModelArticle(array('table_path' => $basePath.'/tables'));
$db = JFactory::getDbo();
$q = "SELECT * FROM #__content ...
4
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('#__attendance'))
->order('roll_no ASC');
$db->setQuery($query);
$results = $db->loadObjectList();
echo '<ul>';
foreach ($results as $row)
...
4
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->getQuery(true);
$query->select('*')
->from($db->qn('#__mcloud_mgallery'))
->where($db->qn('model_id') . ' = ' . $userid)
->order('...
4
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->quoteName('#__parts_analogues') VALUES ("your", "values");
$db->setQuery($query)->execute();
It works like an insert, but does not return an error.
Keep in ...
4
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 = ['id', 'date'];
$data = [
[79, '2018-12-01'],
[78, '2018-10-01'],
[76, '2018-06-01'],
[80, '2019-01-01']
];
$db = JFactory::getDbo();
try {
...
4
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') AS estDate, COUNT(id) AS cnt
FROM #__events
GROUP BY estDate DESC
Implement like so: (no quoting calls are necessary for your query)
$db = JFactory::getDbo(...
4
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 of PHP objects, so I don't believe in_array will work with this.
Instead, I'd suggest using $db->loadColumn(), which will return an array of all userid's, ...
4
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 reporting turned on/up, then I expect that you would have seen several of these:
Notice: Undefined index: p.ComponentId in ...
I have a few additional pointers to ...
Only top voted, non community-wiki answers of a minimum length are eligible