Questions tagged [jdatabase]

Joomla Database connector class

Filter by
Sorted by
Tagged with
32 votes
4 answers
5k views

Method for creating a subquery using JDatabase

At http://docs.joomla.org/Selecting_data_using_JDatabase, there isn't a documented method for writing a subquery using JDatabase. https://gist.github.com/gunjanpatel/8663333 exemplifies one way of ...
betweenbrain's user avatar
22 votes
1 answer
15k views

How to combine ANDs and ORs in the WHERE clause using a Query Object?

Given the desired SQL below, where Cond1 and Cond2 must be met OR Cond3 must be met for selection, what is the correct way to use getQuery() to achieve it? Desired SQL: Condition1 and Condition2 ...
GDP's user avatar
  • 7,715
13 votes
3 answers
5k views

Can multiple records be inserted all at once with the jDatabase?

Instead of using a loop, can Joomla database functions create a SQL statement like this? INSERT INTO #__tablename (col1,col2) VALUES ('1', 'one'), ('2', 'two'), ('3', 'three'), ... ('999', '...
GDP's user avatar
  • 7,715
8 votes
1 answer
4k views

How to use LIMIT in getQuery

Given the desired SQL: SELECT * FROM #__tablename LIMIT 5 How do I use $query to accomplish it? $db = JFactory::getDBO(); $query = $db->getQuery(true); $query->select($db->nameQuote(...
GDP's user avatar
  • 7,715
7 votes
2 answers
278 views

Is there a way to detect the database changes that were made by third party extensions?

I work in a development / staging / production environment and I've run into some challenges promoting code from one environment to another, especially when upgrades to Joomla and other third party ...
ContextSwitch's user avatar
6 votes
2 answers
323 views

Combining 'ANDs' and 'ORs' in a complex WHERE query - Part 2

I have been pulling my hair out with this query for the last 2 days and have read and re-read the 2 other similar posts with no luck. Scenario: I am gathering two inputs from a user: Test ID Code and ...
TJM's user avatar
  • 63
5 votes
1 answer
2k views

SQL Injections using Jfactory::getDBO and setQuery() with LIKE condition

$db = JFactory::getDBO(); $searchP = JRequest::getVar('key'); $sql = "SELECT name FROM people LIKE " . "'%" . $searchP . "%'"; $db->setQuery($sql); $fileR = $db->loadObjectList(); Will ...
Jay Shri's user avatar
5 votes
2 answers
1k views

How to write an Insert Select Statement?

This Method for creating a subquery using JDatabase provides some great insight and I think it would be beneficial to have a agreed method of subquery documented. I came across this because I am ...
poproar's user avatar
  • 333
5 votes
1 answer
579 views

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

Reported via Issue Tracker Item #33711 When trying to insert a BLOB > 3MB in the database, the following error occurs: Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to ...
Valentin Despa's user avatar
4 votes
3 answers
4k views

How can I get the names of the columns in an object or table with Joomla's SQL?

With the existing output I have from a basic query, what would be the best way to get the names of the columns in the resultant output object without having to run an additional COLUMNS query on the ...
Merlin's user avatar
  • 203
4 votes
2 answers
7k views

How to relate comma-separated ids from one table to the ids of another table?

I have a varchar(255) field in one of my Jdatabase tables containing values separated by commas. Precisely, it contains numbers separated by commas. I want to retrieve the individual values using a ...
saibbyweb's user avatar
  • 2,289
4 votes
1 answer
1k views

with insert on duplicate key update - updating multiple rows at once

With latest joomla version I try to achieve one query to the database and updating multiple rows at once. I want to make a query like this: INSERT INTO `tablename` (`id`,`date`) VALUES ('79','2018-...
user348246's user avatar
4 votes
1 answer
2k views

Selecting data from multiple JDatabase tables?

I am trying to select data from multiple Jdatabase tables and use a IF-ELSE statement with this : $db = JFactory::getDbo(); $user = JFactory::getUser(); $query = $db->getQuery(true); $query->...
saibbyweb's user avatar
  • 2,289
4 votes
2 answers
1k views

Join an existing table with a generated table of dates and count rows for each date?

I would like to use this complex mysql query in my joomla model to generate a timeline and count entries within a specific date range. My SQL Query is: SELECT T.calendar, COUNT(DATE(M.date)) as ...
MyFault's user avatar
  • 677
4 votes
2 answers
221 views

Joomla plugin to override table prefix

Overview: I have a single Joomla website that have various copies of the database tables all in the same database, but with different table prefixes. Depending on which user is accessing the site, i ...
Shaun's user avatar
  • 41
3 votes
2 answers
2k views

How to print a single-row result set as a html table?

I am performing a database query like this: $db = JFactory::getDbo(); $user = JFactory::getUser(); $query = $db->getQuery(true); $query->select('*'); $query->from($db->quoteName('#...
saibbyweb's user avatar
  • 2,289
3 votes
4 answers
2k views

How to write my MySQL query with JOIN logic using JDatabase methods?

I'm having a difficult time translating this SQL script to JDatabase methods because I can't find the AND query method. SELECT user.firstname, user.lastname, leadership.leadname, leadership.lead_des ...
Sosa's user avatar
  • 133
3 votes
2 answers
167 views

View Users Front End - php code

I am using http://www.nonumber.nl/extensions/sourcerer in order to place some custom php code in an article to display all my registered users in the front end of my Joomla 3.3 site. I am new to this ...
jonboy's user avatar
  • 3,603
3 votes
3 answers
173 views

Jdatabase : Sorting two selections?

I have Jdatabase table having attendance data of all my students. EXAMPLE : I need to display the attendance of all roll numbers for class 3. The records having 3 in attended_no column were ...
saibbyweb's user avatar
  • 2,289
3 votes
2 answers
1k views

JDatabase JOIN on multiple values

What is the JDatabase syntax for a JOIN statement that joins two tables on a variable that is either equal to x or is null? Eg it joins on multiple values. I have found some examples that show me you ...
Hannah Smith's user avatar
3 votes
1 answer
3k views

How to write a query with GROUP BY and ORDER BY using Joomla's query methods?

I have the following query that I need to convert to Joomla using JDatabase. This query executes as intended in the phpMyAdmin. SELECT `id`, DATE_FORMAT(`estEventDate`, '%b %Y') AS estDate, COUNT(`id`...
mixahlos's user avatar
  • 418
3 votes
2 answers
2k views

Jdatabase : Counting number of elements in a comma separated field

I have a Jdatabasetable having a comma separated field. What would be the best way (according to Joomla coding standards) to count the number of elements separated by commas. Like in the case below : ...
saibbyweb's user avatar
  • 2,289
3 votes
1 answer
211 views

Trying to connect to an external ODBC data source

I have a need to connect to a data source on an old unix box via ODBC, but I don't seem to be getting far and I can't find any examples to help me. I have started with https://docs.joomla.org/...
Irata's user avatar
  • 3,917
3 votes
2 answers
893 views

How to get Custom Fields related to Joomla Content?

I want to know how they get the values of custom user fields within Joomla articles. This is to contribute ideas and ways to solve it.
Leoalv's user avatar
  • 167
3 votes
1 answer
170 views

Using JDatabaseQuery::union() causes errors

I'm getting errors when using JDatabaseQuery::union() to create a database query. With Debug disabled I get this at the bottom of the page, presumably when trying to disconnect: Warning: mysqli::...
Bathmat's user avatar
  • 492
3 votes
1 answer
661 views

COUNT(*) function not working in SELECT query

I wrote a simple SQL statement that counts the number of category IDs in a table. Problem is, its returning NULL. $db = JFactory::getDbo(); $query = $db->getQuery(true); $query->select($db->...
Navitua's user avatar
  • 165
2 votes
2 answers
509 views

SQL syntax error in query with subquery using JDatabase

I'm coding my first Joomla! 4 component. There are two tables for the component: pfx_list, and pfx_member_to_list. Table pfx_list hash primary key id, and table pfx_member_to_list has foreign key ...
phunsoft's user avatar
  • 230
2 votes
2 answers
307 views

Redirect if userid is not found in a database table

When users login, I am trying to push them to fill their 3rd party component's Profile form properly. When they filled the form their user id is added to the "#__rbid_users" table (user id is same as ...
okisan's user avatar
  • 55
2 votes
4 answers
2k views

Insert query with ignore of duplicates (without key)

I have a query: $query_add_analogue ->insert($db->quoteName('#__parts_analogues')) ->columns($db->quoteName($columns)) ->values($value); $db->setQuery($query_add_analogue); $...
user3774771's user avatar
2 votes
2 answers
1k views

Global PHP functions?

How can I make few global php functions which can be called from any Joomla custom module? E.g I wish to make a global function executing a select database query: function select($row,$table_name,$...
saibbyweb's user avatar
  • 2,289
2 votes
1 answer
2k views

JDatabase Like Operator

I would like to know how to perform pattern search using JDatabase Here's my code: $query->select('Common_name'); $query->from($db->quoteName('common_name')); $query->where($db->...
Joshua's user avatar
  • 23
2 votes
2 answers
532 views

How to apply aliases in SELECT clause when two columns from JOINed tables have the same name?

I am trying to build a gamelist, right now I'm struggling getting the detailed team information. #__game id team_home team_guest 1 1 2 2 2 3 #__team id ...
Marco's user avatar
  • 653
2 votes
2 answers
222 views

Using JDatabase standalone, ie. without Joomla application bootstrapping

Can one instantiate a jdatabase object without the Joomla application? The obvious use case is when you want to open a direct endpoint into some standalone code in your site for an api or cron etc. ...
jamesgarrett's user avatar
  • 3,299
2 votes
1 answer
300 views

Cannot get num rows with the database driver

Any idea how to fix this? $dbo = JFactory::getDbo(); $query = 'SELECT `#__example`.`example_value` AS `value` FROM `#__example`'; $dbo->setQuery($query); $rowsCount = $dbo->getNumRows(); $...
inf3rno's user avatar
  • 391
2 votes
1 answer
1k views

JDatabaseQuery quote correctly the items into select

I have written a database query, like so: $query = $db->getQuery(true) ->select("u.*,ju.username,ju.email,ju.lastvisitDate as last_login") ->from("#__component_users AS u") ->...
user3533235's user avatar
2 votes
2 answers
578 views

How to combine several sets of 'ANDs' and 'ORs' in a complex WHERE query object?

I want to create a complex WHERE query that checks: If (condition1 = TRUE) AND (condition2 = TRUE) AND (condition3 = TRUE OR condition4 = TRUE) So the first two conditions MUST always be true and ...
Hannah Smith's user avatar
2 votes
1 answer
52 views

Which files do i need to include to perform database query?

I am trying to practice Joomla database query. For learning purpose, I'd like to know in a blank PHP file under Joomla's root folder, which files do I need to include to be able to do $db = JFactory::...
shenkwen's user avatar
  • 1,389
2 votes
3 answers
97 views

Confusion about using two select() method calls in one query

I am using Joomla's JDatabaseQuery methods to select some columns from two joined tables, but I don't understand something about the following snippet. //Initialize variables. $db = JFactory::getDbo()...
William Zhao's user avatar
2 votes
3 answers
866 views

Ordering a UNION

I have two queries that I am unioning and would like to order them. I would like to order them on the second element, the date. My queries look like this: $query2 ->select ($db->quoteName(...
Hannah Smith's user avatar
2 votes
1 answer
530 views

Multiple Count from same table

I used to get multiple COUNT in one query using the SUM(CASE WHEN $db =& JFactory::getDBO(); $query = "SELECT SUM(CASE WHEN last_name LIKE 'A%' THEN 1 ELSE 0 END) as alpha, SUM(...
John's user avatar
  • 308
2 votes
1 answer
613 views

JDatabase, SQL statement template

Is there a way to use a statement template, like by Hibernate HQL? SQLQuery sql=s.createSQLQuery("SELECT AVG(RATING) as r, COUNT(*) as c FROM RATINGS WHERE ADVENTURE_ID = ?"); sql.setParameter(0, ...
inf3rno's user avatar
  • 391
2 votes
2 answers
25 views

Jdatabase: Finding part of the string which doesn't match $X string

I use some mysql queries to retrieve data from a Jdatabase table as strings. Now I need to find the part of that string which doesn't match another string (which actually is a sub-string of the main ...
saibbyweb's user avatar
  • 2,289
2 votes
1 answer
2k views

How to check whether a specific row exists in a JDatabase table?

What would be the best way to check whether a specific row exists already in a JDatabase table? Instead of INSERT INTO Command, I need to execute a UPDATE command if the row already exists. Help ...
saibbyweb's user avatar
  • 2,289
2 votes
2 answers
2k views

Get ID of deleted database-entry

I would like to create a delete-method, which deletes an entry in a table AND the attached entries in a second table. This is my source so far: // Delete entry in users_handles $db = ...
MyFault's user avatar
  • 677
2 votes
3 answers
273 views

Getting data from mutiple JDatabase Tables based on results from first

I'm attempting to pull user id's from users who are part of a group. I then need to take those user id's and get the email addresses for those user id's. The results need to be comma separated. ...
Todd's user avatar
  • 23
1 vote
2 answers
78 views

How do I create a new PHP variable for a blog override from the result of JDatabase join query?

In a category blog override I am attempting to reflect the featured status of an article. In order to find out whether it is currently actively featured - i.e. not 'pending' or 'expired' I am querying ...
Gillian's user avatar
  • 411
1 vote
1 answer
371 views

loadAssocList() is not working properly when assigning a column name as the key parameter

I have a complex query like below. I want the p.ComponentId values to be the first level keys of the multidimensional result set array. However, it does not work as expected. In my case, when I don't ...
TLWebdesignNL's user avatar
1 vote
1 answer
347 views

MySQL SELECT query with LIMIT to show a maximum of 3 images

I want to show up to 3 images that are assigned to the logged in user in a database table. I have written the following MySQL query: $db = JFactory::getDBO(); //GET GALLEERY IMAGES $query ...
RustyJoomla's user avatar
  • 2,078
1 vote
1 answer
375 views

Delete items of database via CLI

I would like to delete some items with state "trashed" via CLI. Is it possible to call the delete-method of JTable from CLI? Thanks for your help :-)
MyFault's user avatar
  • 677
1 vote
2 answers
959 views

Transforming a Query for JDATABASE

I need to transform a simple php query into a query that JDATABASE will understand. Here is the example: $resDays = 7; // On how many days to build the top? $resLimit = ...
Victor's user avatar
  • 13