Questions tagged [jdatabase]
Joomla Database connector class
92
questions
1
vote
2answers
405 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
108 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
186 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->...
1
vote
2answers
230 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 |
+----+----...
1
vote
1answer
42 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
5 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
25 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::...
3
votes
1answer
56 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
24 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
60 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
194 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
67 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
76 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
83 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(...
3
votes
2answers
305 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
1answer
351 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
279 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
50 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
75 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
249 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
109 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
137 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
818 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
259 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
30 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
4k 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
325 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
575 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
150 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
76 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
688 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
182 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
121 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
915 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
825 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
93 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 ...
