Questions tagged [jdatabase]
Joomla Database connector class
87
questions
1
vote
1answer
156 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
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/...
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
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 ...
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);
$...
0
votes
2answers
306 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.
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 ...
0
votes
1answer
67 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
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
55 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
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(...
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 ...
1
vote
1answer
342 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
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 ...
0
votes
1answer
43 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
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
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
78 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
776 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
265 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
449 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
146 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
560 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
162 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
889 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
657 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
86 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 ...
0
votes
2answers
106 views
Accessing Jdatabase via JavaScript Or Passing PHP varibles to JavaScript
I want to use this SVG Pie Chart on my Joomla site but the problem is I need to access data from Jdatabase and feed it to its index.js
$(function(){
$("#pieChart").drawPieChart([
{ title: "Cash"...
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 ...
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 ...
3
votes
2answers
920 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 ...
