Questions tagged [sql]
Structured Query Language (SQL) is a language for querying databases.
77
questions
1
vote
2answers
53 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
111 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
38 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. ...
1
vote
1answer
27 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,
...
1
vote
2answers
50 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
29 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
73 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
73 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
20 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
99 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
89 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
229 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
98 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 ...
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
105 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 ...
4
votes
1answer
129 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 ...
3
votes
1answer
139 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 ...
0
votes
1answer
177 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 ...
0
votes
1answer
116 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
184 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
1answer
164 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 ...
2
votes
2answers
249 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()...
1
vote
1answer
304 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 ...
0
votes
3answers
100 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
73 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 ...
2
votes
1answer
211 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 ...
1
vote
1answer
213 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(...
0
votes
1answer
738 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
279 views
How to select records one by one using JDatabaseQuery
In programming languages there are 2 patterns for selecting records from the database:
//prepare SELECT statement
while($record = $query->fetchRow())
{
//do the job
}
And the second:
//...
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 ...
1
vote
2answers
90 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 ...
0
votes
1answer
194 views
Join two list views without a shared key
I have a front end list view for a table called #_students and a separate front end list view for a table called #_sessions. The two tables have no shared foreign key and the two data sets are ...
0
votes
1answer
390 views
Error: Incorrect table definition; there can be only one auto column and it must be defined as a key
I am trying to install joomla 3.7.5. I am facing an Error message:
Error: Incorrect table definition; there can be only one auto column and it must be defined as a key
The table '#...
1
vote
1answer
282 views
Retrieve article URL from phpMyAdmin
I am migrating a site from Joomla to WordPress. The migration tool didn't setup redirects for the old urls so I'm planning to write something to do them for me. I need a way to output all the current ...
-1
votes
1answer
57 views
add text after value that is retrieving from SQL in php [closed]
This is the code that i retrieve data from sql in this case ¨Km¨ is added before value but i will ¨Km¨ after value ..
example :
now is like this : Km 129.000
but i will 129.000 km
$insert1 =" /// ...
1
vote
1answer
193 views
Run Stored Procedure From Page Load
I have a html table that has a hyperlink in it, when the hyperlink is pressed 3 parameters are passed to a secondary page. The query that is run is a CTE SQL Server statement, and from my ...
2
votes
2answers
203 views
Transfer Joomla data from PHPMyAdmin SQL Dump
My friend's web page was created with Joomla (c. 2013), and she wants to transfer her site to a new host. Her current host sent her a SQL Dump generated by PHPMyAdmin, which contains what I assume is ...
1
vote
1answer
84 views
Adding a stand alone php (with sql queries built using mysql phpgenerator) to existing Joomla application
I am totally new to Joomla development in specific but have done good old prog days (C, Oracle, Sybase etc). So i use a Library software in Joomla and do all the installation, admin etc thing.. i want ...
0
votes
1answer
586 views
Joomla 3x sql field NOT Filtering on Change
I am using Joomla 3x sql field to filter between two select fields..
The problem is that the second field does not filter as expected. It is just populated with all the result and when I select a ...
1
vote
1answer
238 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 ...
5
votes
1answer
294 views
Joomla 3.6 How to migrate data from repeatable to subform
The new field type subform does not retrieve old data repeatable, even fields have the same names.
I created many posts with repeatable. The data are stored in extra fields of com_conent. attribs ...
1
vote
4answers
16k views
The MySQL adapter mysqli is not available - how to resolve this issue?
My website www.EliteMaths.com.au has been active until early this week. When I tried to access it this morning I got the following error message:
Error displaying the error page: Application ...
0
votes
1answer
352 views
Copy user data into custom table on user registration
upon creating a user in joomla i would need fields copied from jmla_users table to another jmla_new_users.
jml_users - jmla_new_users
with the following fields, (username,firstname,lastname,email)
...
0
votes
2answers
487 views
Joomla 3.4.8 Component SQL Update Not Working
As the title says I'm using joomla 3.4.8 I'm trying to customize the system component "com_content" what I want to do is to add an another columns on the '#__content" table in the database but the ...
0
votes
1answer
202 views
joomla 2.5 / K2 - SQL Query to delete trashed items taking care of data integrity
I have not a testing environment where put this to the test:
delete from k2_items where trash = 1
Of course this will remove everything that is marked as trash, but is there any other table that has ...
1
vote
2answers
297 views
getting some sql error
I have recently restored my joomla website and it was working perfectly fine, but suddenly I am receiving the following error:
1064 : You have an error in your SQL syntax; check the manual that ...
4
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
2answers
115 views
Do I need a custom field for a db field alias?
When viewing an individual record, I'm using a calculated field from a sql query to display as a read only field in my form. However I'm unable to get it to display (no error, just blank). Curious, do ...
