Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [mysql]

MySQL is a database system used by many websites on the Internet. Joomla is based on PHP and MySQL.

1
vote
1answer
33 views

JCE convert unicode after save in my DB

I added 🔒 in the source code of JCE. JCE convert it to 🔒. The problem is after save in my database ist is a scratch characters 🔒. My question now is should I modyfy JCE (How?) or ...
0
votes
0answers
13 views

idx_XXX error after reimport from content table

I export only the xxx_content table from SQL with TRUNCATE. After reimport the table the follow error message comes: ALTER TABLE `XXX_content` ADD PRIMARY KEY (`id`), ADD KEY `idx_access` (`access`...
1
vote
2answers
38 views

Filtering product query with two sets of filters. (J2Store)

I have two disjointed sets of product filters, $special_filters and $other_filters. I'm trying to retrieve the products from the database which match any of the filters in $special_filters AND any of ...
1
vote
1answer
36 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
1answer
50 views

How to integrate my custom Administrator component with core Joomla database tables during install?

Background: I am starting to package up three associated custom Joomla extensions that I've developed. (This is directly associated with my previous JSE question, if you want more context.) The ...
1
vote
1answer
69 views

Installation page not shown due to MySql access denied and 500 server error

On Ubuntu 18.04.1 LTS, the installation page is not shown. Instead, I see a blank page with Error On MySql log I see this: Access denied for user ''@'localhost' (using password: NO) However I ...
1
vote
1answer
66 views

How to write the logged in user id in Plotalot queries?

I am trying to use plotalot to graph user results. The program only uses mysql queries to pull data. How can I pull session specific user data with a mysql query? Normal Joomla session variables ...
1
vote
1answer
79 views

How to sort title of items with natural sort order?

When you have in a column a list of items such as: Title1 Title2 ... Title10 Title11 and you filter on Title (alphabetically), the output is like: Title1 Title10 Title11 Title2 I want it to sort ...
0
votes
2answers
60 views

How to get Custom Fields related to Joomla Content?

I want to know how they get the values of custom user fields within Joomla articles. This is to contribute ideas and ways to solve it.
2
votes
2answers
101 views

How to query for users that have all tags when searching for multiple tags?

I have this custom module that shows all contacts based on some tags. I can add one or more tags in a field in the module What I want is to add the option in the module to show: all contacts that ...
1
vote
1answer
50 views

SQL Error when updating custom component in Joomla 3.9

I have a custom component and I want to add language filter to my extension. So in my sql update file I added the following two lines to Alter my tables. ALTER TABLE `#__contract_contracts` ADD `...
0
votes
1answer
60 views

MySQL update select one table from another

I'm struggling to get this MySQL query to work and hoping someone out there can help please. I wish to "copy" one record from table traincomprofiler to course_dates where both tables share the column ...
3
votes
1answer
103 views

How to write a query with GROUP BY and ORDER BY using Joomla's query methods?

I have the following query that I need to convert to Joomla using JDatabase. This query executes as intended in the phpMyAdmin. SELECT `id`, DATE_FORMAT(`estEventDate`, '%b %Y') AS estDate, COUNT(`id`...
1
vote
1answer
84 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
2answers
132 views

How to check if specific table names exist in a database?

All I want to do is check if a table exists or not and I can't figure it out. I've tried: for ($r = 0; $r < count($tableArray); $r++) { $db = JFactory::getDbo(); $query = $db->getQuery(...
2
votes
2answers
82 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 ...
-1
votes
1answer
33 views

How to write a conditional check on a column value with a mysql query?

I am trying to setup a simple default statement on the results page of a testing system. This seems like it would be relatively simple, but I cannot get my if statement to process properly. The ...
2
votes
1answer
43 views

How to count the rows in a database table that have a date in the current year?

I am writing a module that displays a count of the number of records depending on the date_time column. I am able to get the result that I am looking for when using a static date as below. However, I ...
1
vote
2answers
82 views

How to write a query with a WHERE clause with two conditions?

I cannot seem to get this select statement to work with the WHERE conditions. Depending how it loads the data, I either get the first test result or all of the test results and not the user_id-...
2
votes
3answers
58 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
1answer
114 views

with insert on duplicate key update - updating multiple rows at once

With latest joomla version I try to achieve one query to the database and updating multiple rows at once. I want to make a query like this: INSERT INTO `tablename` (`id`,`date`) VALUES ('79','2018-...
1
vote
1answer
67 views

How do I set the flag CLIENT_MULTI_STATEMENTS for calling multiple stored procedures in a single stored procedure?

How do I set the flag CLIENT_MULTI_STATEMENTS for calling multiple stored procedures in a single stored procedure? I have 30 stored procedures I would like to execute with a button click to process ...
-1
votes
1answer
37 views

Joomla / PHPMyAdmin bug when “select” is in the value

This question was originally asked on StackExchange, and it was recommended by @mickmackusa that I ask it here. Background We recently changed over from one server to a brand new one. Old server: ...
0
votes
1answer
92 views

How to access user-submitted values from a dynamic form?

I want to insert a row in my db-table with inputs from users. The form the users fill out is made with a foreach-loop: foreach ($results as $row) { echo '<div class="vm_kampe"><input ...
0
votes
2answers
108 views

How to generate missing column data using a reformatted substring of another column?

Due to a process issue (i.e. client not following instructions) I have over 900 urls with missing aliases in a mosets tree table. If possible I would like to create an SQL query that will generate ...
1
vote
2answers
142 views

How to refactor two select queries on RSForm submission data?

I was wondering if anyone could help me JOIN multiple queries into one query. Currently, these 2 blocks of code retrieve information from the DB. I am using the "Sourcerer" plugin to add custom PHP ...
1
vote
2answers
43 views

How to re-write sql for joomla JDatabase

I am new to custom Joomla coding so excuse me for this, I want to execute the following statement: "SELECT MAX(CASE WHEN v.FieldName = 'Team_Name' THEN v.FieldValue END) 'Team_Name' , MAX(...
0
votes
2answers
173 views

Use one pdo connection across multiple modules?

I have multiple modules on the same page that are individually connecting to the same external database through pdo connections. The website is getting slower since I added more modules. My guess is ...
3
votes
2answers
270 views

Check if INSERT query was successful or failed because of a duplicate UNIQUE KEY

I have a db-table called league where the mail column is the unique id: |name |mail | |Bryan|bryan@hotmail.com | I have a frontend where users can sign up and then I have this for ...
0
votes
2answers
114 views

Rows with same id in same div

I have a db-table called vm_kupon. It looks a little like this: |id |uid |bet | |1 |20 |1 | |2 |20 |2 | |1 |21 |X | |2 |21 |1 | |1 |22 |X | |2 |22 |X | I ...
1
vote
1answer
59 views

Query gives a single value when two columns in SELECT clause

I do have a database connection earlier in the script. $db = JFactory::getDbo(); Here's the query code: $albumartistidquery = $db->getQuery(true); $albumartistidquery->select($db->...
3
votes
1answer
86 views

What is the prefered method for sharing code and SQL queries in joomla 3.9+?

I'm developing a component that is targeting Joomla 3.9+. The code is not using the legacy classes and is instead using the new namespaces. The component will duplicate much of the functionality from ...
0
votes
1answer
141 views

Php-sql result in joomla displayed in question marks

I have a simple sql query, in a joomla php server, rs forms pro. The result contains Greek characters. However, the result is displayed in question marks. $sql = "SELECT `FieldValue` FROM `table` ...
1
vote
2answers
143 views

Update multiple columns with plus 1

I have a website for my footballteam. I have some statistics and so on in there, fx how many games each player has played and so on. What I would like, is for me to write in the names of every player ...
5
votes
1answer
573 views

SQL Injections using Jfactory::getDBO and setQuery()

$db = JFactory::getDBO(); $searchP = JRequest::getVar('key'); $sql = "SELECT name FROM people LIKE " . "'%" . $searchP . "%'"; $db->setQuery($sql); $fileR = $db->loadObjectList(); Will ...
2
votes
2answers
249 views

How to Use IN Clause in Joomla Query

I am trying to select records where a column is in a suggested list if values using the IN clause. I have tried the query below but the system is not working. $db = JFactory::getDbo(); $...
0
votes
1answer
161 views

Update specific row in FOREACH loop

I've tried and tried, but I can't get this to work. I use Joomla and I have 2 articles/pages, one for edit and one for posting the form. I have a database with all the matches for the World Cup coming ...
0
votes
2answers
610 views

Multiple primary key defined error when installing Jomestate plugin

I have Joomla 3.8.5 stable version. The code I am tring to execute is: Pastebin SQL code (I am tring to install the JOMESTATE plugin) I get the following error: JInstaller: :Install: Error SQL ...
1
vote
1answer
420 views

SQL WHERE clause with a variable (current article ID)

I am trying to do a simple calculation on two Joomla custom fields in a module. I get the article ID as a variable and want to put this in the WHERE clause to get the right custom field value. Have ...
1
vote
2answers
93 views

Localize output of SQL query

I make a query directly on the database like this: SELECT title,id, DATE_FORMAT(publish_up, "%d %M %Y") AS 'GEORGE' FROM #__k2_items WHERE published=1 AND trash=0 AND created_by=[id] ORDER BY ...
0
votes
1answer
29 views

Copy users from one Joomla db to a slightly newer one

Assuming I have an earlier copy of a Joomla database can I simply copy the jos_users, jos_userkeys, jos_usernotes, jos_userprofiles and jos_usergroup_map tables to a newer copy of the database - by ...
0
votes
1answer
319 views

How can I save multiple dynamic values using check boxes?

I have a form which fetches and displays data from two databases (sessions, students) and allows a curriculum manager to select different combinations of students and classes by clicking check boxes. ...
0
votes
1answer
303 views

Restore deleted user

I have a Joomla system (v3.8) which is running well. Unfortunately, however, one of our administrator user record has accidentally been deleted. I need to restore it because we're now getting errors ...
0
votes
1answer
46 views

Why my code doesn't work?

I am using the following code: $db = JFactory::getDBO(); $query = $db->getQuery(true); $query->select('name') ->from ('#__gruposlocales'); $query-> where('latitudX = ' . (int) $lat); $...
2
votes
1answer
132 views

How can I translate SQL code into something that Joomla will take?

I am in the same case discussed on https://stackoverflow.com/questions/11709043/mysql-update-column-with-value-from-another-table, that is, I need to copy the value of a column from tableA to tableB ...
0
votes
1answer
180 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
39 views

What's the proper chaining for WHERE

This statement works: $query = " SELECT COUNT(*) FROM " . $this->s_createtableName . " WHERE tm BETWEEN UNIX_TIMESTAMP(DATE(NOW())) AND UNIX_TIMESTAMP(DATE(NOW()) + INTERVAL 1 DAY)"; In ...
0
votes
2answers
42 views

How to get banners clients when exists banners with this client?

I need to retrieve the banners_clients data only when exists banners for this client id: Example: banner one - client id 1 banner two - client id 1 banner three - client id 4 client id 1 - name ...
0
votes
1answer
173 views

Calling a Model's Method from View [duplicate]

I have a function in my model to sum several columns of a table. public static function get_sums() { // Create a new query object. $db = $this->getDbo(); $query = $...
3
votes
3answers
86 views

Button to execute a mysql query

I made a mysql query that will copy 3 columns of data from one table to another: INSERT INTO eio3k_hikers (link_id, link_name, user_id) SELECT link_id, link_name, user_id FROM eio3k_mt_links where ...