Questions tagged [jdatabase]

Joomla Database connector class

Filter by
Sorted by
Tagged with
31
votes
4answers
4k 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 ...
21
votes
1answer
13k 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 ...
11
votes
3answers
4k 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', '...
8
votes
1answer
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(...
7
votes
2answers
240 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 ...
6
votes
2answers
192 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 ...
5
votes
1answer
1k views

SQL Injections using Jfactory::getDBO and setQuery()

$db = JFactory::getDBO(); $searchP = JRequest::getVar('key'); $sql = "SELECT name FROM people LIKE " . "'%" . $searchP . "%'"; $db->setQuery($sql); $fileR = $db->loadObjectList(); Will ...
5
votes
1answer
565 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 ...
4
votes
3answers
3k 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 ...
4
votes
2answers
3k 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 ...
4
votes
2answers
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 ...
4
votes
1answer
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->...
4
votes
2answers
666 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 ...
4
votes
2answers
144 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 ...
3
votes
2answers
1k 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('#...
3
votes
4answers
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 ...
3
votes
2answers
156 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 ...
3
votes
3answers
87 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 ...
3
votes
1answer
1k 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`...
3
votes
2answers
928 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 ...
3
votes
2answers
1k 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 : ...
3
votes
1answer
571 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-...
3
votes
1answer
46 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/...
3
votes
1answer
126 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::...
3
votes
1answer
269 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->...
2
votes
2answers
163 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 ...
2
votes
2answers
950 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,$...
2
votes
1answer
976 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->...
2
votes
2answers
51 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 ...
2
votes
1answer
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") ->...
2
votes
2answers
267 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 ...
2
votes
3answers
72 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()...
2
votes
2answers
168 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. ...
2
votes
1answer
207 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(); $...
2
votes
3answers
703 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(...
2
votes
1answer
435 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(...
2
votes
1answer
504 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, ...
2
votes
2answers
18 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 ...
2
votes
1answer
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 ...
2
votes
2answers
1k 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 = ...
2
votes
3answers
248 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. ...
1
vote
4answers
1k 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); $...
1
vote
1answer
274 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 ...
1
vote
1answer
343 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 :-)
1
vote
2answers
890 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 = ...
1
vote
3answers
3k views

How can I select the IDs of a category and all its (n levels) subcategories?

Beside the parent category, I would like to recursively collect all of its child rows. (Pseudo code) SELECT IDs FROM #_categories WHERE parent = father As an alternative, SELECT IDs WHERE the first ...
1
vote
1answer
60 views

Raw sql query returns multiple rows, but with Joomla methods only one row is returned

For some reason the query I put directly to DB works, but when set in Joomla syntax it is returning only 1 result, and there should be a lot more... Clear Mysql query: SELECT vmp....
1
vote
2answers
74 views

How to re-write sql for joomla JDatabase

I am new to custom Joomla coding so excuse me for this, I want to execute the following statement: "SELECT MAX(CASE WHEN v.FieldName = 'Team_Name' THEN v.FieldValue END) 'Team_Name' , MAX(...
1
vote
1answer
56 views

What's the proper chaining for WHERE

This statement works: $query = " SELECT COUNT(*) FROM " . $this->s_createtableName . " WHERE tm BETWEEN UNIX_TIMESTAMP(DATE(NOW())) AND UNIX_TIMESTAMP(DATE(NOW()) + INTERVAL 1 DAY)"; In ...
1
vote
3answers
639 views

PHP function with JDatabase queries

I have these two blocks of identical code in one of my PHP modules. The only difference between these two blocks is that in first block it is ... active = ' .$db->q('1')); and in the second block ...