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
1k 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 ...
31
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
20k 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, ...
3
votes
2answers
766 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 ...
3
votes
2answers
1k 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
2answers
237 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 ...
4
votes
2answers
2k 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 ...
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
3answers
1k 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); $...
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 ...
6
votes
2answers
1k 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 ...
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
2answers
1k views

Adding Multiple Users in Joomla

How can to add multiple users in Joomla 3 without using an extension? Thank you! :)
1
vote
1answer
73 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
949 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->...
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("...
3
votes
1answer
351 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
639 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
1answer
493 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, ...
2
votes
1answer
477 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 ...
2
votes
3answers
67 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
228 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. ...
1
vote
2answers
277 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 ...
1
vote
2answers
192 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
1answer
49 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(...
1
vote
1answer
271 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 ...
0
votes
2answers
214 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.
0
votes
1answer
177 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
392 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 ...
0
votes
2answers
228 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
132 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'...