Questions tagged [jdatabase]

Joomla Database connector class

Filter by
Sorted by
Tagged with
1
vote
2answers
222 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 | +----+----...
0
votes
2answers
321 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.
1
vote
1answer
33 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 ...
0
votes
0answers
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=...
0
votes
0answers
4 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 ...
2
votes
1answer
23 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::...
0
votes
1answer
73 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 ...
1
vote
1answer
164 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->...
3
votes
1answer
48 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/...
0
votes
1answer
23 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 ...
2
votes
2answers
52 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 ...
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); $...
2
votes
2answers
170 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 ...
1
vote
1answer
49 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 ...
1
vote
1answer
57 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(...
1
vote
2answers
75 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(...
2
votes
2answers
273 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 ...
1
vote
1answer
346 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
1answer
275 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 ...
0
votes
1answer
46 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) * ...
1
vote
1answer
64 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
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 ...
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
2answers
85 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 ...
1
vote
2answers
136 views

How to refactor my iterated sequence of queries?

I'm building a component which is about to be finalized, but there is a bit of code that I want to refactor. I think this code can be completed in one statement and I used more than 3 statements. $db ...
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 ...
1
vote
1answer
782 views

Select returning only one row with one column

$db = JFactory::getDbo(); $user = JFactory::getUser(); $query = $db->getQuery(true); $query->select('username'); $query->from($db->quoteName('#__selected_packages')); $query->where($db-&...
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('#...
1
vote
2answers
251 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 ...
0
votes
1answer
29 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(); ?
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 ...
3
votes
1answer
273 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->...
0
votes
1answer
489 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 ...
1
vote
1answer
147 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 ...
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`...
1
vote
1answer
57 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 ...
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()...
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
1answer
591 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-...
1
vote
1answer
77 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->...
1
vote
1answer
165 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(...
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', '...
1
vote
1answer
117 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-&...
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(...
1
vote
2answers
899 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 = ...
4
votes
2answers
689 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 ...
1
vote
3answers
1k views

How to get the generated id after a successful INSERT query?

I would like to get the id of the last saved dataset in a JTable-Operation. At the moment I got this: // Create and populate an object. $value = new stdClass(); $value->userid = (int)$...
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
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 : ...
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 ...