Questions tagged [sql]

Structured Query Language (SQL) is a language for querying databases.

Filter by
Sorted by
Tagged with
1
vote
1answer
48 views

How to store component parameters after modification

As I understand it, the current values of a component configuration are stored as JSON in the params field of the #__extensions database table, in the entry that matches the component name as element. ...
5
votes
1answer
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 ...
0
votes
0answers
18 views

How do I get Edit Profile to not save with invalid date input on Joomla Community Builder

On my volunteering website, users can edit their profile and enter a date in the future when they are available to volunteer. If they are already signed up for a volunteer opportunity, they shouldn't ...
0
votes
0answers
33 views

ERROR 1054 when login in admin section

An exception has occurred while executing: SELECT t0.id AS id_1, t0.first_name AS first_name_2, t0.middle_name AS middle_name_3, t0.last_name AS last_name_4, t0.email AS ...
0
votes
1answer
196 views

Use the “sql” form field type in extension xml only if the table exists

I developed a plugin for Joomla!, and I have the below sql parameter field in my sampleplugin.xml <field name="cb_fields" type="sql" label="Select CB fields" query="SELECT name ...
1
vote
1answer
36 views

How to use Joomla's query building methods to generate a query containing joined subqueries?

I am trying to write a query using Joomla's query building methods, but I cannot manage to write the subquery into the query. Here is my desired raw sql with php variables: "SELECT track_type, ...
0
votes
0answers
36 views

Translate sql with nested queries into query builder methods [duplicate]

I have written a SQL query which works fine and output provides all the data that I need: Please see this screenshot: This is the SQL code: SELECT P.name, C.project_id, C.choice_id, C.time_stamp,...
1
vote
2answers
60 views

How to count aggregate data from RSForm submission values with Joomla's query building methods?

user SubmissionId FormId FieldName FieldValue bob 2 16 state mystate1 bob 2 16 qda myqad1 bob 2 16 city mycity1 rob 3 16 state mystate2 rob 3 16 qda myqad1 rob 3 16 city mycity1 jac 4 16 state ...
1
vote
1answer
49 views

Create Plotalot chart from RSForm Pro data using pivot query

I'm trying to extract RSForm Pro form data from the database to create Plotalot charts. There is a field on the form called "resolved" to which users respond "Yes or No". I would ...
1
vote
2answers
64 views

How to use SQL statements not supported by JDatabaseQuery

Recently, I have needed to use some SQL statements that are not supported by the JDatabaseQuery class, specifically REPLACE INTO and INSERT IGNORE. The only way that I am able to get something to ...
1
vote
3answers
128 views

Join #__content with #__fields_values then filter and sort using #__fields_values data

I wrote an override in Joomla 3.X and sorted the result from the DB using this query, which works: $query = "SELECT content.*, fieldvalues.* FROM #__content AS content, #...
1
vote
1answer
246 views

Dynamic component SQL parameter type

I'm looking to add a Component field which will display a list of Joomla users who have a relationship with current the Component item. Below is some sample XML where by I would like [current ...
3
votes
4answers
1k views

Multiple INSERTS in one query Joomla

How can I do multiple inserts like: $query = "INSERT INTO `table` (`fied1`, `field2`, `field3`) VALUES ('one', 'two', 'three'),('four', 'five', 'six'); INSERT INTO `table` (`fied1`, `field2`, `field3`...
1
vote
1answer
37 views

Joomla Select from DB AND / OR grouping

I did not found any guide on how to handle such queries in Joomla: I would like to select all items from db which are: Published AND (the publish up date is 0000-00-00 00:00:00 or smaller then datenow)...
1
vote
1answer
80 views

RSFORM combine 2 submission form results in one Plotalot view

After requesting help from local db-masters with no result, I would like to ask this question here. The output required is this for example: Shelter ClientID Year Month Fees Amount ...
1
vote
2answers
76 views

How to display specific K2 extra field of current item with k2 tool module

I am trying to display specific extra field values of a specific item with the K2 tool module. I have tried to get just BRAND, but I'm getting stdClass::brand() error. $db = JFactory::getDBO(); $...
2
votes
0answers
23 views

Using the SQL `Delimiter` statement in component update

For the next version of my component I need split some data into different tables, which requires some rather more complex SQL than I would usually use. The problem is that there doesn't seem to be a ...
1
vote
1answer
133 views

Where to find article page title in database?

When managing an article, there is a "Browser Page Title" field in article options tab. It's the last field in the tab, just below the "Read More Text". Why it's not in the publishing tab makes no ...
2
votes
2answers
122 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
3answers
280 views

How to add the current user's user id in query of a custom field for a plugin?

If I hardwire a created_by clause in a joomla sql custom field, the query works as it should (for this specific userid the clause is, and created_by = 305): SELECT id AS value, title AS text FROM #...
2
votes
2answers
116 views

Unable to update NULL via updateObject

We got 2 tables where one table is parent and another table as a child with primary and foreign key reference. Assume table1 is the parent and table2 is the child as below. table1: id <- ...
1
vote
2answers
47 views

Joomla 3.x insert query attempts don't work as desired

Needing a little help to point me to the issue. I am building a component and need to insert a row into the Joomla DB. This line works, but I have to create the table name myself and I'd rather use ...
-2
votes
3answers
6k views

1054 Unknown column 'extra_query'

I have a new Joomla installation with this error: 1054 Unknown column 'extra_query' in 'field list' SQL=SELECT DISTINCT update_site_id, type, location, last_check_timestamp, extra_query FROM ...
1
vote
1answer
78 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(...
0
votes
1answer
128 views

Is it possible to edit the SQL custom field to be multi-select?

I want to be able to select multiple values with the SQL custom field. Is this possible? When I use a query which as multiple results, I can only pick one. I found this post: Creating custom field ...
1
vote
3answers
47 views

Trying to set up new joomla from old joomla files

So basically my Joomla site was attacked a few weeks ago by SQL injection and was taken down. What I am hoping to do, is to go into the file manager and download all of my articles, categories, ...
3
votes
1answer
147 views

Issues to be aware of when calling `concatenate()`, `regexp()`, `rand()`, and `castAsChar()` in sql queries

I'm taking it upon myself to become aware of some of the lesser known/used query methods because I like toying with MySQL and I want to implement Joomla calls when possible/sensible -- because they ...
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 ...
4
votes
1answer
143 views

(RuntimeException $e) vs (JDatabaseExceptionExecuting $e) for SQL exception handling

Through out the current version of Joomla (3.9.6) I find a mixture of ways that SQL exceptions are handled and I was wondering if anyone could give me some definitive situations where one should be ...
0
votes
1answer
120 views

Bulk delete disabled k2 users Joomla 2.5

I have thousands of spam users. I use K2 and I would want to delete them from "k2_users" and from "users". I want to delete the blocked ones. I have tried deleting K2_users from the admin, but it does ...
0
votes
2answers
197 views

Ordering Joomla menus in code

I am trying to order jos_menu items by the order in which they appear on the menu. In other words, I am using jdatabase sql queries to fetch menu objects then iterate through them to display them. I ...
1
vote
3answers
2k views

Retrieving multiple results from the database

I don't have much Joomla or PHP experience so I'm probably missing something really simple. I'm trying to load a certain list of user IDs into an array. This code works except it only loads one result....
0
votes
3answers
101 views

Convert PHP code to Jooma PHP syntax?

I'm running a query on phpmyadmin which has the "IN" function included. It is to extract data from the same column for different fields' (Waybill1, Waybill2 etc.) I shall post the SQL query below: ...
2
votes
2answers
114 views

Component Update: DB error file function returned no error

I have been working on a component but noticed when I update it I get an error (something like "Component Update: DB error file function returned no error", not exact but close). I notice this is due ...
5
votes
3answers
1k views

How to rewrite SQL-query with CASE/WHEN to Joomla-based "format"

I have the following SQL-query to database: $jinput = JFactory::getApplication()->input; $category_id = $jinput->get('virtuemart_category_id'); $db = JFactory::getDBO(); $query = $db->...
0
votes
1answer
786 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
167 views

How to construct a JOIN query with Joomla's query methods?

I'm really new to SQL queries but now stuck with this one. I need the first and last name of a user from table #__comprofiler where their cb_job_role = 'Team Leader' and their cb_service_name = [the ...
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
2answers
259 views

How to query certain rows from a table based on other associated rows in the same table?

I'm trying to query my jos_rsform_submission_values table for FieldValue data in rows with a FieldName of Container# which shares a SubmissionId with another row where the FieldValue is "Pending" and ...
2
votes
3answers
81 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 ...
1
vote
1answer
323 views

Delete row from custom made SQL when deleting article in Joomla

I´m still new to Joomla and to coding SQL. I was thinking about how I should manage a big system I made with information that is saved in a sql table, that contains some user input and the "id" of an ...
1
vote
1answer
215 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(...
1
vote
2answers
93 views

Rewriting code as per Joomla guide, but page showing 404 error

I am rewriting some old code according to the latest reference, although the page description is coming out correctly in the old code. But when rewritten into new code, the page is redirecting to ...
2
votes
1answer
226 views

Joomla Migrating Live Site To Localhost -Navigation Not Working

I just migrated a Joomla site from live to localhost. Have the front end up and can access the backend without problem. Front end wise none of the menu items work. I have renamed the live site ...
4
votes
2answers
1k 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 ...
3
votes
4answers
608 views

How to only transfer new column values from one table to another?

I'm attempting to create a new table: _clients that will be simply populated with, for now, with the user_id column from #__user_usergroup_map. Ideally, the below code should pull all id's from #...
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 ...
2
votes
2answers
127 views

What does sql_select="e.*" do?

On https://docs.joomla.org/SQL_form_field_type it talks about using the SQL fields. I'm wondering what sql_select="e.*" does. I know that the star is a wildcard for everything. I'm just not sure ...
3
votes
2answers
1k 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 ...