Questions tagged [mysql]
MySQL is a database system used by many websites on the Internet. Joomla is based on PHP and MySQL.
286
questions
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
4
votes
1
answer
1k
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-...
- 143
1
vote
1
answer
215
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 ...
- 65
0
votes
1
answer
61
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: ...
- 11
0
votes
1
answer
322
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 ...
- 103
0
votes
2
answers
142
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 ...
- 532
2
votes
2
answers
295
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 ...
- 265
1
vote
2
answers
124
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(...
- 11
1
vote
2
answers
1k
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 ...
- 11
3
votes
2
answers
3k
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|[email protected] |
I have a frontend where users can sign up and then I have this for ...
- 103
0
votes
2
answers
1k
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 ...
- 103
1
vote
1
answer
101
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
1
answer
108
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 ...
- 418
0
votes
1
answer
845
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` ...
- 101
1
vote
2
answers
639
views
UPDATE query to add 1 to multiple columns
I have a website for my football team. I have some statistics and so on in there, for example how many games each player has played and so on.
What I would like, is for me to write in the names of ...
- 103
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
6
votes
1
answer
4k
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();
$...
- 249
0
votes
1
answer
760
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 ...
- 103
0
votes
2
answers
2k
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
1
vote
1
answer
1k
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 ...
- 13
1
vote
2
answers
181
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 ...
- 173
0
votes
1
answer
112
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 ...
- 63
0
votes
1
answer
1k
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. ...
- 111
0
votes
1
answer
960
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 ...
- 610
0
votes
1
answer
52
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
2
answers
293
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
1
answer
934
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 ...
- 109
1
vote
1
answer
88
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
2
answers
85
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
1
answer
712
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 = $...
- 25
3
votes
3
answers
648
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 ...
- 109
4
votes
2
answers
221
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 ...
- 41
0
votes
1
answer
1k
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 ...
- 1
1
vote
1
answer
379
views
How to debug a 500 can't load error?
I have moved a website running joomla from my old VPS to my own server. The server is working fine, other sites are loading. However, this joomla site doesn't load.
Please can someone explain what ...
1
vote
1
answer
235
views
Why my custom query only returns one result?
$dbwsk = JFactory::getDBO();
$querywsk = "SELECT `title` FROM `#__content` WHERE 1";
$dbwsk->setQuery($querywsk);
$rowswsk = $dbwsk->loadResult();
var_dump($rowswsk);
I've been working and ...
- 1,389
0
votes
1
answer
213
views
Join two list views without a shared key
I have a front end list view for a table called #_students and a separate front end list view for a table called #_sessions. The two tables have no shared foreign key and the two data sets are ...
- 111
1
vote
1
answer
326
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 ...
- 133
2
votes
4
answers
2k
views
Insert query with ignore of duplicates (without key)
I have a query:
$query_add_analogue
->insert($db->quoteName('#__parts_analogues'))
->columns($db->quoteName($columns))
->values($value);
$db->setQuery($query_add_analogue);
$...
- 136
2
votes
1
answer
574
views
Accessing current users custom field
I´ve added a custom users field (called "test"). Now I want to call the value of this custom field for the current user who is logged in.
In the mysql databse I see the custom field under _fields. So ...
- 21
0
votes
1
answer
90
views
Can I insert custom attribute information into the MySQL content table?
I'd like to be able to give each article an additional attribute. Not sure if I can use the columns that exist in #_content. I'm concerned any modifications I do will be overwritten or cause errors. ...
- 167
0
votes
1
answer
96
views
Random token in DB on each login
I'm looking to somehow add a "token" on login and temperately store it in the database (same as table *_users) until next login (important).
I have a game of sorts that uses a selected user and ...
- 3
2
votes
1
answer
879
views
Correct ways to verify update query is successful under conditions
I was trying to find out correct ways to verify db update query is successful but some problems i am fetching. Even if update query($db->execute()) is not done/row does not exists.... but it ...
- 1,351
1
vote
1
answer
347
views
MySQL SELECT query with LIMIT to show a maximum of 3 images
I want to show up to 3 images that are assigned to the logged in user in a database table. I have written the following MySQL query:
$db = JFactory::getDBO();
//GET GALLEERY IMAGES
$query ...
- 2,078
2
votes
1
answer
239
views
Component SQL Updates Not Running
I have been troubleshooting this all morning and am stuck. Hoping someone can help. Using Joomla! 3.7.0.
I have a component that contains a number of MySQL update files. These files have been working ...
- 1,236
0
votes
1
answer
587
views
filter db by a single date - how to hack the standard calendar form field
I used component-creator.com to build a component. The component has a table with a date field and a frontend view which shows the contents of the table with a filter which can show the items between ...
- 111
3
votes
1
answer
140
views
SQL syntax error when attempting to filter list view - how to build WHERE clause?
I built a Joomla component for a school using component-creator.com and now I am trying to modify the source files.
My current view lists off all parents and students in my database, visible to all ...
- 111
2
votes
1
answer
485
views
Where query in Joomla component - what file do I edit?
Okay, so I am completely new to Joomla and I am trying to edit the source files of a component I built using component-creator.com.
I have a database which stores different data for multiple ...
- 111
1
vote
2
answers
472
views
Select+Checkboxes fields = query a Database [closed]
I want to make a form which contains one select field (dates) and multiple checkbox fields (teams), so when I select a date or choose a team, automatically makes a query a database, filtering by the ...
- 11
2
votes
1
answer
613
views
JDatabase, SQL statement template
Is there a way to use a statement template, like by Hibernate HQL?
SQLQuery sql=s.createSQLQuery("SELECT AVG(RATING) as r, COUNT(*) as c FROM RATINGS WHERE ADVENTURE_ID = ?");
sql.setParameter(0, ...
- 391
2
votes
1
answer
1k
views
Select data from multiple tables and output results as json [closed]
I wrote this query for getting data from 3 different tables which has one similar column.
The trouble is, it throws an error:
500 internal server error
I need the output to be in the form of json,...
- 21