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 ...
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
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 the $text parameter and 2,4 as the expected bool value for the $escape parameter. (I code on PhpStorm and this fact is spelled out)
Unfortunately for your ...
8
$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 parentid');
$query->join('LEFT', '#__parents AS b ON b.child = a.id');
$query->join('LEFT', '#__records AS c ON b.parent = c.id');
$db->setQuery($query)...
8
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 exact approach should depend on from where you want to import these users and the actual user-data you already have and want to import.
Also, if these users ...
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 might need to use MySQL to manually repair the table.
If you can run arbitrary MySQL on your server, run the mysql CLI and use:
REPAIR TABLE jos_users;
Alternatively, phpMyAdmin (supplied by many hosts to manage MySQL databases) allows you to repair tables through a GUI. To do so:
Access phpMyAdmin and select the Joomla database from the left-hand ...
7
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 with latest DB coding standards (J2.5)
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$columns = array('user_cms_id', 'user_email', '...
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
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;
$catId = $input->getInt('id');
$cat = JTable::getInstance('category');
$cat->load($catId);
5
Make sure you have cleared out all elements marked trash(if any), you may have conflicting alias.
Try the 'Rebuild Menu items' in Joomla Menu Manager (I know not articles but can clear up assets table issues sometimes).
And then as @cppl mentions ACL manager is excellent tool for fixing remaining assets table issues without having to touch the database. I ...
5
Apart from the mentioned documents, have you looked at ACL Manager which has built in repair tools which we find essential for sites post migration.
5
Try this(tested and worked in Joomla 3.3.0):
Put this code:
<?php
$db = JFactory::getDbo();
$id = $item->core_catid;
$db->setQuery("SELECT cat.parent_id FROM #__categories cat WHERE cat.id='$id'");
$parent_category_id = $db->loadResult();
$db->setQuery("SELECT cat.title FROM #__categories cat WHERE cat.id='$parent_category_id'");
$...
5
Joomla of course uses foreign keys to reference primary keys in other tables. That's a basic concept in databases and it wouldn't work otherwise.
What you mean is something different. You're talking about binding them together so they keep the integrity on a database level. Eg if a row in the primary table is deleted, the "child" tables gets their rows ...
5
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 works much better:
DROP TABLE #####_updates;
CREATE TABLE IF NOT EXISTS `#####_updates` (
`update_id` int(11) NOT NULL AUTO_INCREMENT,
`update_site_id` int(...
5
[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 helpful answer
This has been answered on stackoverflow here: https://stackoverflow.com/questions/27968427/how-to-count-numbers-of-row-of-sql-query-in-joomla-and-...
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
Installation of Joomla requires a properly configured infra. It looks like several stacks are not properly configured out-of-the-box for this very common Joomla setup.
Increase max_execution_time in php.ini and restart apache untill installation of Joomla succeeds. Installation should continue creating the tables and finish in a timely fashion.
4
You are likely getting duplicate items because of the join of the categories table. And since you don't actually select anything from the categories table, why not just do this:
$database->setQuery("SELECT items.headline, items.image, items.copy, items.source
FROM ms95f_createalist_items items");
It also looks like you might have an id that you ...
4
There are probably other differences (or maybe not: https://stackoverflow.com/questions/2193049/php-objects-vs-arrays) between objects and arrays, but it is mostly a style difference (at least when using a stdClass object).
For objects, you use the syntax of $variable->property and for arrays you use $variable['property']. var_dump will usually show an ...
4
This sounds like an error with your database server rather than your website.
Are you by any chance using a Hostgator Reseller account? Hostgator is currently experiencing problems with some of their servers, and I think it's been like that all day. Check http://forums.hostgator.com/oct-29-02-00-emergency-maintenance-t328219.html for more information on the ...
4
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');
4
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 that JSON-storage rarely is efficient, ultimately it strikes back at you.
If you only plan on storing this simple data in one simple table, maybe it seems easier ...
4
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->quote( $field-value );
Reference: Secure coding guidelines
4
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 certain format.
So if you expect the title of the mail to be a simple string without any fancy stuff in it, you can use
$filter = JFilterInput::getInstance();
$...
4
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 select a Category.
Select your new category, make sure 'Move' is selected and then click Process.
This will do it all for you and update the relevant category ...
4
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 change a model you don't own, or you'll be tied to applying your changes with every new release; additionally, you mention it's overly complicated;
query from ...
4
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 way to do queries using the Joomla query builder syntax
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select($db->qn('value'))
...
Only top voted, non community-wiki answers of a minimum length are eligible