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)
...
9
As Milton already wrote you specify where those SQL files are in the install manifest file. There are three different locations for install, uninstall and update.
<install>
<sql>
<file driver="mysql" charset="utf8">sql/install.mysql.utf8.sql</file>
</sql>
</install>
<uninstall>
<sql>
...
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
What I would recommend you do is:
Export the database for the Joomla 1.5 site, apart from the #__users table
Install a fresh copy of Joomla 1.5 on your localhost
Import the database dump you downloaded before
You should now have a fresh Joomla 1.5 site with all you content.
Install redMIGRATOR which is a Joomla 1.5 extension that will migrate your site to ...
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 ...
5
I guess the example you are talking about is this:
"SELECT e.* FROM #__example AS e GROUP BY name ORDER e.id ASC"
Ths basically mean select everything from the table #__example (which has an alias name in the query of e) and group by name and order by id in ascending order.
if you didn't use AS e in the query you had to write the query as follows:
SELECT *...
4
That practice would actually be terrible for SEO. You would be hurting your search content by regularly replacing content on the same id. Aside from that, even if you did reuse the ids, they would still continue to get larger,but at a slower rate. This is because you wouldn't have access to ids that are smaller than the last remaining id.
Ex: if you have ...
4
You give very little information to give a good answer, but there are a couple of things you can try:
Make sure both Joomla and your template is up to date
Re-upload your template if possible
Fix your database by going to Extension manager -> Database, and click the Fix button in the upper left corner
4
Finally, I've done the export/import via CSV (as I also had to edit all the fulltext entries) and here are the little things that I had to search for:
export the Db via CSV, then edit it with Open Office Calc*
edit the column so that they match the Joomla3.3 structure.
set the column asset_idFK to O, Joomla will assign the correct value when you will open/...
4
The convention is usually the admin directory, but beyond that it shouldn't matter as long as you reference the files' location correctly inside of your main xml file.
like:
<install>
<sql>
<file driver="mysql" charset="utf8">sql/tables/install.sql</file>
</sql>
</install>
or:
<install>
<...
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
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 data is necessary for context. (db-fiddle.com demo)
CREATE TABLE IF NOT EXISTS `#__calendar` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`month` varchar(100) ...
4
The difference between a RuntimeException and a JDatabaseExceptionExecuting is a matter of semantics and inheritance.
In PHP, RuntimeException extends the Exception class (the base class -- which before php7, did not provide a Throwable interface).
https://www.php.net/manual/en/class.exception.php
https://php.net/manual/en/class.runtimeexception.php
...
3
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 statement to build it with Query API $query->select(...)->from(...).
It is better to just handle the query with a big string, avoiding PHP performance bottlenecks.
...
3
You can define multiple database install scripts for every database driver like MySQL or MSSQL that you like to support in your installation XML file. Have a look at the example XML here in the official Joomla Docs:
https://docs.joomla.org/J3.x:Developing_a_MVC_Component/Using_the_database#helloworld.xml
Your XML file could look like this then:
<...
3
To answer the actual question, this code will give you a listbox of UserIDs and Names. Retrieving all the extra data would then just be a SQL select statement
$myGroupID = 2; //Your User Group to list
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('a.id as value, a.name as text');
$query->from($db->quoteName('#__users')....
3
You can split the sql and run the statements using below code
function insertSql($filename){
$db = JFactory::getDbo();
$status = true;
//parsing of SQL file
$sql_file_path = JPATH_ADMINISTRATOR.'/components/xxx/sql/install/mysql/'.$filename.'.sql';
$queries = JDatabaseDriver::splitSql(file_get_contents($...
3
Use JDatabaseDriver::splitSql($query) so your function will look like this:
$query = "INSERT INTO `table` (`fied1`, `field2`, `field3`) VALUES ('one', 'two', 'three'),('four', 'five', 'six');
INSERT INTO `table` (`fied1`, `field2`, `field3`) VALUES ('seven', 'eight', 'nine'),('ten', 'eleven', 'twelve');";
$db = JFactory::getDbo();
$queries = $...
3
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) {
// here $key will be column name and $value will be type
// process array code
}
3
I had this error after a reboot on one site of 12 on my server. All my sites are Joomla. We recently upgraded to php7.1 I tried everything then found the fix.
The fix was to comment out the suPHP line in .htaccess.
Remember to show hidden files to find the .htaccess in the root.
DirectoryIndex index.php
#<IfModule mod_suphp.c>
# suPHP_ConfigPath /...
3
When you are only referencing one table in your query, there is no need to explicitly reference the table when writing columns. Also, the default order direction for sql is ASC.
So your syntax could be simplified to:
<field
name="example_group"
type="sql"
label="COM_EXAMPLE_GROUP"
sql_select="id, name"
sql_from="#__example"
sql_group="name"
...
2
Check the mysql join map here:http://www.codeproject.com/KB/database/Visual_SQL_Joins/Visual_SQL_JOINS_orig.jpg
One of them is what you are looking for.
2
Figured out the issue, when making a component you need to have at least 1 blank update sql file or it will fail. Not just the typical index.html file to make sure then installer does not skip the folder.
Typically the file would be something like sql/updates/mysql/VERSION.sql Replacing version with the actual needed version. I am confident this is a bug, ...
2
This situation can happen if K2 was installed in the past and not removed correctly or after a failed install attempt.
Have a look with your FTP client for either of the directories below as K2 checks for them to establish if you already have it installed:
administrator/components/com_k2
components/com_k2
Option 1
Remove these directories and try to ...
2
Run this query. Don't forget to change the table name and database name to your's bq1ry_k2_categories.
CREATE TABLE IF NOT EXISTS `bq1ry_k2_categories` (
`id` int(11) unsigned NOT NULL,
`name` varchar(255) NOT NULL,
`alias` varchar(255) NOT NULL,
`description` text NOT NULL,
`parent` int(11) DEFAULT '0',
`extraFieldsGroup` int(11) NOT NULL,
`...
2
What you can do is have a separate file for each database drive type, for example:
<install>
<sql>
<file driver="mysql" charset="utf8">path/to/sql/file</file>
<file driver="mssql" charset="utf8">path/to/mssql/file</file>
</sql>
</install>
Simply define the driver type like so:
driver="...
2
The "official" Joomla way is to use the JDatabaseQuery class to create the query and give it to the Database layer. Then you use the loadAssocList or loadObjectList function to get a array with all rows.
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select('*');
$query->from('#__users_usergroup_map');
$query->where($db->...
2
Hi Check your data base xxx_module must assign AUTO_INCREMENT STATUS, after it will be ok!
2
$query->select('a.virtuemart_product_id')
->from('#__virtuemart_product_categories AS a')
->select('(CASE WHEN '.$db->qn('b.product_override_price').' > 0
THEN '.$db->qn('b.product_override_price').
' WHEN '.$db->qn('b.product_discount_id').' > 0
THEN '.$db->qn('b.product_price').' - ('.$db->qn('b.product_discount_id').' * '.$...
Only top voted, non community-wiki answers of a minimum length are eligible