Questions tagged [sql]
Structured Query Language (SQL) is a language for querying databases.
93
questions
22
votes
1
answer
15k
views
How to combine ANDs and ORs in the WHERE clause using a Query Object?
Given the desired SQL below, where Cond1 and Cond2 must be met OR Cond3 must be met for selection, what is the correct way to use getQuery() to achieve it?
Desired SQL: Condition1 and Condition2 ...
- 7,715
7
votes
2
answers
528
views
Recommended practices regarding transfering database from Joomla 1.5 to Joomla 3
I want to transfer about 300 articles from a Joomla 1.5 website to a Joomla 3.3.1 website.
I have access only to the Joomla 1.5 database but not to the backend (I have access to both for the joomla ...
- 976
6
votes
2
answers
472
views
Structure folder of sql files in my component
What folder should I put my sql files in my component for joomla execute them automatically?
If in the first version I created a table and in later version I want to change, how should I organize that ...
- 807
5
votes
1
answer
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 ...
- 53
5
votes
3
answers
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->...
- 891
5
votes
1
answer
352
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 ...
- 167
4
votes
3
answers
4k
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 ...
- 203
4
votes
1
answer
206
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 ...
- 275
4
votes
2
answers
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 ...
- 677
3
votes
4
answers
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
...
- 133
3
votes
4
answers
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`...
3
votes
2
answers
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 ...
- 341
3
votes
1
answer
220
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 ...
- 4,791
3
votes
4
answers
629
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 #...
3
votes
1
answer
1k
views
Migration Files - post update sql migration scripts generation
I am looking for an organised way to run joomla updates locally and then update production systems by means of code deployment + db migrations, so that i do not have to update joomla on production, or ...
- 131
2
votes
2
answers
2k
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 ...
- 1,909
2
votes
2
answers
214
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 ...
- 23
2
votes
2
answers
3k
views
Error 1146 while installing K2 extension
While installing K2 extension, Joomla 3.0, GoDaddy hosting) received error message:
Table 'iWebazoid.bq1ry_k2_categories' doesn't exist SQL=SHOW FULL COLUMNS FROM bq1ry_k2_categories
Unable to ...
2
votes
2
answers
534
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 ...
- 663
2
votes
2
answers
311
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 <- ...
- 2,731
2
votes
2
answers
124
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 ...
- 2,856
2
votes
2
answers
335
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 ...
- 265
2
votes
3
answers
97
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()...
- 23
2
votes
2
answers
370
views
Old update sqls being run on update
I'm building a custom component, and on initial install on Joomla 3.3.6 it installs properly. However, when I install again, rather than running the update sql only from the current version+, it ...
- 1,119
2
votes
1
answer
100
views
What is the correct way to insert the first category called "Uncategorized" in the categories table in the SQL file during installation of component?
I also need to know if the insert of the table #__content_types is correct and if there is something missing in the SQL installation code.
SET time_zone = "+00:00";
CREATE TABLE IF NOT ...
- 35
2
votes
1
answer
328
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 ...
- 175
2
votes
0
answers
31
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 ...
- 239
1
vote
4
answers
19k
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 ...
- 203
1
vote
2
answers
482
views
Extension install scripts without raw sql
I am new to Joomla development, coming from Magento.
Working through the tutorials with a certain extension in mind I stumbled upon a little problem.
By now Joomla supports MySql and MSSQL and more ...
- 165
1
vote
1
answer
150
views
Recycling IDs: re-using articles, category's ID that have been trashed for new articles
I like to work with small IDs. Is there a way to automatically re-use the IDs of trashed* items? (* when using "empty trash")
Is it safe to run this query:
ALTER TABLE jos_content AUTO_INCREMENT=0
...
- 478
1
vote
1
answer
2k
views
Attempt to assign property "team" on bool
I am trying to create a model in Joomla that will display various information pertaining to a certain ID. When I create the function:
public function getItem ($id = null)
{
if ($this->_item ===...
- 69
1
vote
2
answers
206
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 ...
- 3,927
1
vote
2
answers
328
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 ...
- 133
1
vote
2
answers
860
views
After Joomla update duplicate or create new module get id : 0
Joomla is updated to 3.3 and create of a new module or duplicate existing module with no success.
Its possible to create a new, or duplicate modules but module id is 0 for each new module I create, ...
- 13
1
vote
2
answers
1k
views
How to Create a Foreach Loop that Returns Users In a Specific Usergroup
My goal is to create an html select box via a foreach loop that returns all users within a specific custom usergroup (ex. usergroup = Agents).
Once, the query pulls in all records (users) for that ...
- 165
1
vote
3
answers
4k
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 ...
- 173
1
vote
1
answer
98
views
After joining two tables in phpMyAdmin how do I save the results as a dynamic table?
In order to query the exact featured status of an article in a blog override (i.e. currently featured, not pending or expired or not featured at all) I have created a query to join two tables and ...
- 411
1
vote
1
answer
87
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)...
- 663
1
vote
2
answers
155
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();
$...
- 55
1
vote
1
answer
379
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 ...
- 213
1
vote
1
answer
366
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 ...
- 513
1
vote
2
answers
129
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 ...
- 415
1
vote
3
answers
48
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, ...
- 23
1
vote
1
answer
37
views
How can I add an additional column to a database view in phpMyAdmin?
I have created a new view in my MariaDB database using phpMyAdmin combining the #_content and #content_frontpage tables called #__content_with_featured.
I am now attempting to add a new column to this ...
- 411
1
vote
1
answer
407
views
Calculate wins, losses and ties for each team in database
I have been struggling with this one for a couple days. I am trying to build a Joomla component that will display team standings based on record and then points for and points against. Currently I ...
- 69
1
vote
3
answers
357
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
1
answer
164
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 ...
- 47
1
vote
2
answers
60
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
1
answer
219
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 ...
- 23
1
vote
1
answer
234
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(...