Questions tagged [sql]

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

Filter by
Sorted by
Tagged with
21
votes
1answer
13k 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
votes
2answers
516 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 ...
6
votes
2answers
394 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 ...
5
votes
1answer
267 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 ...
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 ...
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->...
4
votes
1answer
84 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 ...
4
votes
2answers
680 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
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
90 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 ...
3
votes
2answers
947 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 ...
3
votes
4answers
602 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
1answer
989 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 ...
2
votes
2answers
50 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
2answers
172 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 ...
2
votes
2answers
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
2answers
52 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 ...
2
votes
2answers
111 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
votes
2answers
208 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
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()...
2
votes
2answers
345 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 ...
2
votes
1answer
164 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 ...
2
votes
0answers
17 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
4answers
14k 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 ...
1
vote
2answers
466 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 ...
1
vote
1answer
131 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 ...
1
vote
2answers
256 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 ...
1
vote
2answers
694 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, ...
1
vote
2answers
821 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 ...
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 ...
1
vote
2answers
44 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(); $...
1
vote
1answer
28 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 ...
1
vote
1answer
252 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
2answers
87 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 ...
1
vote
3answers
44 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, ...
1
vote
2answers
37 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
149 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
164 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
1answer
233 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
271 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
vote
1answer
79 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 ...
1
vote
1answer
129 views

Joomla 2.5 and Virtuemart 3 installation problems

I've tried installing Joomla 2.5 and Virtuemart 3 (full install download from Virtuemart's website) and I went through the installation process ok. However I've had a lot of errors come up and don't ...
1
vote
3answers
118 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 #...
1
vote
1answer
57 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
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
1answer
176 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 ...
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
97 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: ...
0
votes
1answer
56 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 ...