Questions tagged [jdatabase]

Joomla Database connector class

Filter by
Sorted by
Tagged with
0 votes
1 answer
32 views

Join one to many with helper table JDatabase

I'm currently using a two step solution to get all linked teams to a player (a player can be linked to multiple teams) that way for my backend list view: first regular SELECT from Players Then loop ...
Marco's user avatar
  • 653
0 votes
1 answer
304 views

MySQL "LOAD DATA LOCAL INFILE" with database connection object in Joomla 4

In order to import large CSV files into a Joomla database table, I use the MySQL "LOAD DATA LOCAL INFILE" command as follows: $db = $this->getDbo(); $sql = "LOAD DATA LOCAL INFILE '&...
cappu's user avatar
  • 141
0 votes
1 answer
132 views

Using MySQL "LOAD DATA LOCAL INFILE" with the Joomla database connection object

In a Joomla 3 component I use the MySQL "LOAD DATA LOCAL INFILE" command to import large CSV files into a Joomla database table. When moving to a new server, it is no longer possible to ...
cappu's user avatar
  • 141
0 votes
2 answers
462 views

How to display order data with simple SQL query?

I am trying to run a simple query in Joomla 4 to retrieve order data to display in a module. Testing the query with phpMyAdmin works fine, but despite generating no errors (set to Maximum) the below ...
Phil91's user avatar
  • 31
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
0 votes
0 answers
234 views

Connecting to an external database in Joomla 4 - further problem

I have seen the earlier question on this subject and tried the suggested code: $db = (new Joomla\Database\DatabaseFactory)->getDriver('mysqli', $options); but get an error: An error has occurred ...
johnonorvic's user avatar
1 vote
1 answer
897 views

Connecting to an external database in Joomla 4

Connecting to an external database in Joomla 3 is easy following this documentation and works like a charm. But JDatabaseDriver->getInstance method is deprecated in Joomla 4. What is the ...
Peli's user avatar
  • 103
0 votes
2 answers
848 views

Join tables where one column value has leading zeros and the other doesn't

I am trying to LEFT JOIN a table, but the two columns used for relating rows are not identically formatted. My problem is the value in a.vendor, it has leading zeros on it, and c.No doesn't have ...
Jennifer Padua's user avatar
0 votes
1 answer
197 views

Decode json strings from a database table column

I have a column in my database that essentially creates an array and I am essentially trying to explode it but I cannot figure out how to get it to work. Its also going into SQL with { characters ...
Brandon's user avatar
  • 69
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
1 vote
0 answers
26 views

MySQL - Is a query with many results or several queries with one result more efficient? [duplicate]

I need to get category titles for which I am using the a model where $result returns all the data from the categories table, what I need is only the titles, that by making a loop to $result and a ...
Leoalv's user avatar
  • 167
0 votes
0 answers
26 views

Translate query from PHP to Jdatabase [duplicate]

This query: SELECT rank() over (ORDER BY max(fs_score) desc) AS nr, nome_e_cognome AS "Player Name", max(fs_score) AS "Best score" FROM scheda_atleta INNER JOIN at on at.id=...
Frullix's user avatar
  • 13
1 vote
2 answers
494 views

Selecting data from two tables and make a ranking in an HTML table

I want take the result from scheda_atleta table for every player and make an HTML table with the best result. I've two tables: first named: at +----+----------------+ | id | nome_e_cognome | +----+----...
Frullix's user avatar
  • 13
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
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
0 votes
0 answers
14 views

how to fix array error on spgenries.php of sp movie database for joomla

This is my first time using sp movie database on joomla. I am getting an error on spgenries, please help with this.... Warning: count(): Parameter must be an array or an object that implements ...
user avatar
0 votes
1 answer
34 views

Joomla DB where statement not works as expected

I don't understand the world right now. I try to make a simple query from the DB, but it fails with the message that the column is not known. I do not understand why Joomla! tries to see the value I ...
Marco's user avatar
  • 653
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
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
1 vote
1 answer
854 views

Retrieve data from database to front-end

I created a form using ConvertForm extension for guests to submit information (name, age, ...etc) into my Joomla database. Is there a way to display the information the guests submitted to another ...
zexa's user avatar
  • 41
0 votes
1 answer
365 views

Retrieve the value of a line a checkbox

I'm blocking on retrieving an item from a data list. My code: <div id="contenu2" class="content"> <div> <form action="" method="post" id="adminForm" name="adminForm"> <table ...
user14167's user avatar
1 vote
1 answer
97 views

How to collect multi-row data from #__fields_values and assign specific aliases for each value in the result set?

I have the following (working) query to get name, username and the value of a custom field: // Get a db connection. $db = JFactory::getDbo(); // Create a new query object. $query = $db->getQuery(...
FollaKY's user avatar
  • 13
0 votes
1 answer
118 views

Joomla 3 database query select with limit [duplicate]

How can I use this query with joomla 3 with limit to 3 SELECT * , (3956 * 2 * ASIN(SQRT( POWER(SIN(( $coorlat - latitude) * pi()/180 / 2), 2) +COS( $coorlat * pi()/180) * COS(latitude * pi()/180) * ...
wbdevlop's user avatar
1 vote
1 answer
165 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....
Oktarin's user avatar
  • 41
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
1 vote
1 answer
327 views

Delete query and left join with JDatabaseQuery doesn't work

I'm trying to convert a delete query with left join to use JDatabaseQuery, but I am encountering an issue in the query. The query that I'm trying to convert and which works is the following : $db->...
user3533235's user avatar
1 vote
2 answers
217 views

Filtering product query with two sets of filters. (J2Store)

I have two disjointed sets of product filters, $special_filters and $other_filters. I'm trying to retrieve the products from the database which match any of the filters in $special_filters AND any of ...
William's user avatar
  • 299
0 votes
1 answer
39 views

Connect to two MySQL db's [duplicate]

I want to use a second database for the product data. Is it possible to connect to two database and using of JFactory::getDbo(); ?
Lovntola's user avatar
  • 344
1 vote
1 answer
293 views

How to sort title of items with natural sort order?

When you have in a column a list of items such as: Title1 Title2 ... Title10 Title11 and you filter on Title (alphabetically), the output is like: Title1 Title10 Title11 Title2 I want it to sort ...
Franky's user avatar
  • 65
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
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
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
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
1 vote
1 answer
213 views

Why does the truncateTable() method create an empty string for a query and fail to delete rows?

I have the following code attempting to truncate a table. The Joomla documentation makes me believe this will work, but it does not. What am I missing? $db = JFactory::getDbo(); truncate_query = $db-&...
Rob's user avatar
  • 173
1 vote
2 answers
124 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(...
Gibzy's user avatar
  • 11
1 vote
1 answer
101 views

Query gives a single value when two columns in SELECT clause

I do have a database connection earlier in the script. $db = JFactory::getDbo(); Here's the query code: $albumartistidquery = $db->getQuery(true); $albumartistidquery->select($db->...
Rich Starkie's user avatar
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
1 vote
1 answer
234 views

Error when populating a database table while installing a module

I am using the following code for creating tables in my .sql file as part of a module. CREATE TABLE IF NOT EXISTS `#__gruposlocales` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(...
Pablo Castelo's user avatar
0 votes
1 answer
112 views

Downloading large data sets

My site has a facility to download a potentially large body of data from my database as a CSV file. This is currently implemented using a view.csv.php file that serves this data by first setting the ...
John Rix's user avatar
  • 551
0 votes
1 answer
934 views

How to SET a user-defined MySQL variable and increment it to produce ranking values with JDatabaseQuery?

I'm trying to write this query: SET @rownumber = 0; UPDATE #__hikers SET rank = (@rownumber:=@rownumber+1) ORDER BY score DESC; like JDatabaseQuery, but still got an error. Please help UPDATE: I ...
Midhat's user avatar
  • 109
1 vote
1 answer
88 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 ...
Phil Schaming's user avatar
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
1 vote
1 answer
59 views

Join two similar table SQL [closed]

In database it shows: -Table 1 data show discount_category_id as: 1 2 3 -Table 2 data show category_id as: ,1, ,2, ,3, So is anything wrong or any syntax error with my code because i tried print_r ...
Jamysim's user avatar
  • 19
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
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
1 vote
2 answers
311 views

How to query a table column for values that start with a non-alphabetic character?

How can rows starting with non alphabetic characters (all "special" characters, including spaces and underscores) be selected? I don't know if it changes anything, but my_table will also contain ...
John's user avatar
  • 308
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
0 votes
1 answer
114 views

JDatabase Update +1

I am trying to update the database with the original column's value plus (+1) or minus (-1) $column_name = 'mycolumnname'; $table_name - 'mytablename'; $db = JFactory::getDbo(); $query = $db->...
John's user avatar
  • 308
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
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