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
57 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
57 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
68 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
40 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
98 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
42 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
25 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
50 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
78 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
84 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
59 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
30 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
33 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
62 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
49 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
76 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
62 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
31 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
0answers
22 views
Joomla login issue after xampp upgrade mysql to mariadb
I have shifted my Joomla 3 site to another drive on a local windows server, performed some tweaks and everything went okay, but I am unable to login to the Joomla administrator account.
This issue is ...
0
votes
1answer
76 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
101 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 ...
0
votes
0answers
39 views
Component SQL install script works in phpMyAdmin not in script
I have an install sql script for my component which works fine standalone but not when it is run during the component installation process. (in file sql/install.mysql.utf8.sql)
I am adding a column (...
1
vote
2answers
38 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
94 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
176 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
93 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
53 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
85 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
104 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
112 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
397 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
160 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
149 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
0answers
69 views
passing an array from view to controller to model
I have a component made with Component Creator. I am trying to form an array in the view, pass that array through the controller to the model, and save it to the database via a function in the model. ...
0
votes
2answers
419 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
350 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
89 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
26 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
259 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
237 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
45 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
119 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
136 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
37 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
35 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
132 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 = $...
0
votes
0answers
146 views
making component,data not saving
Good day to all. I create my first component, for internal needs. up to a certain stage, the data was stored in a database, but then I decided to split the functionality a bit according to different ...
2
votes
3answers
76 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 ...
3
votes
2answers
61 views
Joomla plugin to override table prefix
Overview: I have a single Joomla website that have various copies of the database tables all in the same database, but with different table prefixes.
Depending on which user is accessing the site, i ...
0
votes
1answer
390 views
Fabrik Cross database joins
I have two separate databases with the same credentials: in db1.table1 there is a column containing filepaths that I need to update with a filename that is stored in db2.table2 and that I can retrieve ...
