Questions tagged [mysql]

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

Filter by
Sorted by
Tagged with
5
votes
1answer
2k 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 ...
32
votes
4answers
4k views

Method for creating a subquery using JDatabase

At http://docs.joomla.org/Selecting_data_using_JDatabase, there isn't a documented method for writing a subquery using JDatabase. https://gist.github.com/gunjanpatel/8663333 exemplifies one way of ...
4
votes
3answers
22k views

Joomla DB Insert, how to get last inserted id?

I have to insert my data into 2 different tables, in the first table I have the following query which works perfectly: $insertProjectUser = 'INSERT INTO #__pmanager_user (user_cms_id, user_email, ...
4
votes
1answer
2k 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(); $...
2
votes
1answer
653 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 ...
3
votes
2answers
936 views

How to share table between 2 or more databases?

I have a Joomla! site and now I want to make some subdomains. But, I want that the users must be the same in both. Like, if a user logs on to the main site and then goes to the subdomain at the same ...
2
votes
2answers
370 views

How to combine several sets of 'ANDs' and 'ORs' in a complex WHERE query object?

I want to create a complex WHERE query that checks: If (condition1 = TRUE) AND (condition2 = TRUE) AND (condition3 = TRUE OR condition4 = TRUE) So the first two conditions MUST always be true and ...
3
votes
1answer
138 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
2answers
1k views

Migrating Joomla site to local machine

I borked a Joomla site while trying to upgrade to Joomla 3. One of the extensions was not supported and I had to restore my backup, including the backup of the database. Unfortunately, it did not work ...
1
vote
4answers
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); $...
1
vote
2answers
254 views

Selecting data from two tables and make a ranking in an HTML table

I want take the result from scheda_atleta table for every player and make an HTML table with the best result. I've two tables: first named: at +----+----------------+ | id | nome_e_cognome | +----+----...
0
votes
1answer
736 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 ...
7
votes
2answers
2k views

Why doesn't Joomla use foreign keys in the core table structures?

I am developing a custom component and want to use a foreign key user_id referenced to #__users(id). I noticed that Joomla doesn't use any foreign key in its core tables for example in #__user_notes ...
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 ...
4
votes
5answers
5k views

MySQL error: Unknown storage engine 'InnoDB'

I have a couple of Joomla 3.3.6 websites, and suddenly this morning I started to get random error messages, like this: Error displaying the error page: Application Instantiation Error: Unknown ...
3
votes
1answer
834 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-...
3
votes
2answers
2k views

Adding Multiple Users in Joomla

How can to add multiple users in Joomla 3 without using an extension? Thank you! :)
1
vote
1answer
139 views

Charts/Graphs for RSFORM using mysql for VChart and/or PlotaLot

I am working for a homeless organisation in Capetown. We are using RSForms to register our clients (about 3500 per year). We have a sizable database, but it's very hard to create a dashboard. We are ...
1
vote
1answer
161 views

How to query for users from a specific user group?

I want to list only the users with a group_id value of 10. Why doesn't the following query work? $db = JFactory::getDbo(); $query = $db->getQuery(true); $query->select('a.id, a.name, a....
5
votes
2answers
2k views

Two left join in one query to Joomla database

I have two tables: #__records and #__parents. In the first table some records may have parent records that are stored in the same table but relations between childs and parents are stored in the ...
4
votes
3answers
5k views

How to get parent category title for Tag view

I want to get my current article's category and parent category link into my page. I get the category via the following code: $db = JFactory::getDbo(); $id = $item->core_catid; $db->setQuery("...
4
votes
2answers
5k views

How to relate comma-separated ids from one table to the ids of another table?

I have a varchar(255) field in one of my Jdatabase tables containing values separated by commas. Precisely, it contains numbers separated by commas. I want to retrieve the individual values using a ...
4
votes
2answers
708 views

Get Timestamp from Database

I have data stored in a db table, with an auto timestamp. In a module I want to then display a result based on the latest timestamp. It's not working and I'm getting an error Warning: strtotime() ...
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->...
3
votes
1answer
391 views

Front end DB error, back end works

I recently moved a Joomla site to a new server, and for some reason the front end only gives a DB error: 0 - Could not connect to MySQL. This is usually due to wrong DB connection details in ...
3
votes
2answers
517 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.
3
votes
1answer
2k 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`...
3
votes
2answers
2k 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 ...
2
votes
3answers
81 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
246 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 ...
2
votes
2answers
160 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 ...
2
votes
2answers
582 views

Transform PHP SQL to Joomla SQL query

I am moving a PHP page to my Joomla website and I was advised that I "should use Joomla's coding standards and methods for everything, this includes database queries" My question is: How should I ...
2
votes
2answers
369 views

How to retrieve RSForm submission data grouped by SubmissionId?

I have a table with 3 columns: SubmissionId, FieldName and FieldValue. 1) SubmissionId keeps ID submission. 2) FieldName stores field name of a form. 3) FieldValue stores field value of form field. ...
2
votes
1answer
80 views

PlotALOT RSForm Query showing only one result

After being helped very well by Stack community and learning a bit I created a query to show the sum of payments per client. A client can pay multiple times. I would like to know the sum of payments ...
2
votes
1answer
545 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, ...
1
vote
1answer
194 views

Delete query and left join with JDatabaseQuery doesn't work

I'm trying to convert a delete query with left join to use JDatabaseQuery, but I am encountering an issue in the query. The query that I'm trying to convert and which works is the following : $db->...
1
vote
2answers
238 views

Database query outputting twice

I have the following database query: $database = JFactory::getDBO(); $database->setQuery("SELECT items.headline, items.image, items.copy, items.source FROM ...
1
vote
1answer
291 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 ...
1
vote
4answers
3k views

Display a Value from a Specific DB Field

I'm trying to figure out what I've got going on here thats not working - I'm using RsTicketsPro and I'm trying to pull a specific value from their custom fields section in the database. I reached out ...
1
vote
2answers
383 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 ...
0
votes
2answers
856 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 ...
0
votes
2answers
138 views

Display Entry when referencing 2 Tables

Ok this is building on what I was previously working on in my last post: <?php $db = JFactory::getDBO(); $db->setQuery("SELECT `profile_value` FROM #__user_profiles WHERE `user_id`='customer_id'...
0
votes
1answer
261 views

export user details from database via cron

Is there any way I can export user details from joomla database such as name, password, email address, and import them into owncloud database via a cronjob?
0
votes
1answer
78 views

How to write a DELETE query with WHERE conditions using JDatabaseQuery syntax?

I have a query which I am trying to convert to JDatabaseQuery syntax, but I'm not able to find the correct way. The query is the following: DELETE upt FROM #__mycomponent_user_posts AS upt WHERE upt....