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 ...
- 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 ...
- 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 ...
- 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 ...
- 4,056
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 ...
- 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#...
- 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 ...
- 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 ...
- 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 ...
- 3,640
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,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;...
- 191
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 ...
- 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.
- 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='...
- 819
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 ...
- 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 ...
- 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 ...
- 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->...
- 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 = ...
- 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 ...
- 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->...
- 4,056
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 ...
- 4,056
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 ...
- 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');
- 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 ...
- 166
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-&...
- 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 ...
- 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 ...
- 440
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 ...
- 502
Only top scored, non community-wiki answers of a minimum length are eligible