Questions tagged [sql]
Structured Query Language (SQL) is a language for querying databases.
94
questions
0
votes
1
answer
25
views
SQLite Fetch Jump to certain number
Let's say I have an SQLite Database, and a Table in the said Database has thousands of records, and each record has an article worth thousands of characters within it, it is CMS of course it has an ...
0
votes
0
answers
34
views
Filtering Data in Php oop
After I click the search button - there's no data output in the web page. What should I do?
This is my view page
<label>DATE FROM:</label>
<input type="date" ...
- 1
1
vote
1
answer
20
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
62
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
0
votes
0
answers
88
views
Can't you put an insert query inside of a loop?
Here's my code:
$mysqli = new mysqli($dbhost, $dbUsername, $dbPassword, $dbName);
// Check connection
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->...
1
vote
0
answers
61
views
Query to select b where b.id is in comma separated list of a
Update: Component Code (not normalized) can be found here: Link
For my First J4 Component i've now implemented Multi Cat Option by saving the selected categories into a comma separated list (text) ...
- 525
2
votes
1
answer
81
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
0
votes
2
answers
463
views
Join tables where one column value has leading zeros and the other doesn't
I am trying to LEFT JOIN a table, but the two columns used for relating rows are not identically formatted.
My problem is the value in a.vendor, it has leading zeros on it, and c.No doesn't have ...
1
vote
1
answer
906
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
0
votes
1
answer
126
views
Decode json strings from a database table column
I have a column in my database that essentially creates an array and I am essentially trying to explode it but I cannot figure out how to get it to work. Its also going into SQL with { characters ...
- 69
0
votes
0
answers
30
views
In extensions, make the schema update SQL conditional
In the xml install file for an extension for Joomla! 4.0, there are these lines that make the SQL update :
<update>
<schemas>
<schemapath type="mysql">sql/...
- 578
1
vote
1
answer
314
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
0
votes
0
answers
23
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
0
answers
42
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
0
answers
61
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
1
answer
181
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 ...
- 13
1
vote
2
answers
162
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,067
1
vote
3
answers
287
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
2
answers
131
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. ...
- 312
1
vote
1
answer
65
views
How to use Joomla's query building methods to generate a query containing joined subqueries? [closed]
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,
...
- 11
1
vote
2
answers
155
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
...
- 43
1
vote
1
answer
70
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)...
- 525
1
vote
1
answer
137
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
133
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
2
votes
0
answers
29
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
1
answer
287
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
2
votes
2
answers
322
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 ...
- 525
1
vote
3
answers
536
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 #...
- 13
2
votes
2
answers
241
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,671
1
vote
2
answers
55
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
90
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(...
- 13
0
votes
1
answer
234
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 ...
- 129
4
votes
1
answer
190
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
3
votes
1
answer
205
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,761
0
votes
1
answer
259
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 ...
- 21
0
votes
1
answer
149
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 ...
- 101
0
votes
2
answers
260
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 ...
- 123
1
vote
1
answer
197
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
2
votes
2
answers
323
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
90
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
1
vote
1
answer
358
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
0
votes
3
answers
105
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:
...
- 265
2
votes
2
answers
1k
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,879
2
votes
1
answer
297
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 ...
- 75
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
1
vote
1
answer
225
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
1
answer
903
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 ...
- 99
1
vote
1
answer
399
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:
//...
- 179
1
vote
1
answer
59
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 ...
- 19
1
vote
2
answers
115
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 ...
- 405