2022 Developer Survey is open! Take survey.

Hot answers tagged

17 votes
Accepted

Method for creating a subquery using JDatabase

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 ...
user avatar
  • 186
10 votes

Method for creating a subquery using JDatabase

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 ...
user avatar
  • 1,022
9 votes
Accepted

How to Use IN Clause in Joomla Query

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 ...
user avatar
  • 4,388
8 votes
Accepted

Two left join in one query to Joomla database

$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 ...
user avatar
8 votes

Adding Multiple Users in Joomla

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 ...
user avatar
  • 19.5k
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#...
user avatar
  • 4,388
7 votes
Accepted

Table marked as crashed and repair failed

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 ...
user avatar
  • 2,295
7 votes

Joomla DB Insert, how to get last inserted id?

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 ...
user avatar
  • 26.4k
6 votes
Accepted

JDatabaseDriver->replacePrefix( ) Allowed memory size of ... bytes exhausted

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 ...
user avatar
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('...
user avatar
  • 2,971
6 votes
Accepted

How to get database information from Joomla category

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;...
user avatar
5 votes
Accepted

How do I know if the integrity of the asset table is good up or not?

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 ...
user avatar
  • 677
5 votes

How do I know if the integrity of the asset table is good up or not?

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.
user avatar
  • 1,749
5 votes
Accepted

How to get parent category title for Tag view

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='...
user avatar
5 votes

Why doesn't Joomla use foreign keys in the core table structures?

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 ...
user avatar
  • 5,527
5 votes
Accepted

Joomla update broken updates table

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 ...
user avatar
  • 1,992
5 votes
Accepted

What to use instead of getNumRows() in Joomla 3.x

[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 ...
user avatar
  • 531
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->...
user avatar
  • 437
5 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 = ...
user avatar
  • 4,388
4 votes

What to do when installation stuck on creating tables?

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 ...
user avatar
  • 876
4 votes
Accepted

Database query outputting twice

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->...
user avatar
4 votes
Accepted

How to sort my query's results by publish_up value?

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 ...
user avatar
4 votes
Accepted

MySQL error: Unknown storage engine 'InnoDB'

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 ...
user avatar
  • 13.3k
4 votes
Accepted

Joomla 2.5, how to use "HAVING" using Query Object

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');
user avatar
  • 1,791
4 votes
Accepted

Storing data in a single column of a table OR as the part of json

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 ...
user avatar
4 votes

Filter strings before saving them in the database

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-&...
user avatar
  • 7,415
4 votes

Filter strings before saving them in the database

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 ...
user avatar
  • 5,527
4 votes
Accepted

Will changing Article Category in Bulk with MySQL affect the integrity of assets table?

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 ...
user avatar
4 votes

What to use instead of getNumRows() in Joomla 3.x

getNumRows() is valid in Joomla 3.x. As for your error... "getNumRows() will return the number of result rows found by the last query and waiting to be read. To get a result from getNumRows() you ...
user avatar

Only top scored, non community-wiki answers of a minimum length are eligible