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
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 ...
8
votes
2answers
1k views

How do I know if the integrity of the asset table is good up or not?

1) I have migrated a lot of articles from Joomla 1.5 to joomla 3. Migration went on mostly fine. However, there are a couple of articles which do not get saved when I press the save button in article ...
8
votes
7answers
10k views

What to do when installation stuck on creating tables?

Upon installation of Joomla the process stops while creating tables. Some tables were created, but not all of them. So far this only happens with MySQL with Joomla 3.x. What should I do?
8
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 ...
6
votes
2answers
2k views

Sometimes it shows the error #1044

I've just installed Joomla! 3.3 but sometimes, when I want to change a parameter, it shows the next error: 1044 Access denied for user ''@'localhost' to database '*' SQL=LOCK TABLES h2ap7_assets ...
5
votes
3answers
2k views

What to use instead of getNumRows() in Joomla 3.x

It is my understanding that $db = JFactory::getDbo(); $db->setQuery($sql); $db->execute(); $numRows = $db->getNumRows(); Does not work in Joomla 3.x, right? I get a "...
5
votes
1answer
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 ...
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 ...
5
votes
1answer
572 views

JDatabaseDriver->replacePrefix( ) Allowed memory size of ... bytes exhausted

Reported via Issue Tracker Item #33711 When trying to insert a BLOB > 3MB in the database, the following error occurs: Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to ...
5
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->...
5
votes
1answer
635 views

K2 Add tag to article if the keyword is in the article title

This seems to be a bit out of my league and knowledge of MySQL. So if someone can help it would be much appreciated. I would like to do the following: Enter/define a keyword e.g. mercedes Find all K2 ...
5
votes
1answer
294 views

Joomla 3.6 How to migrate data from repeatable to subform

The new field type subform does not retrieve old data repeatable, even fields have the same names. I created many posts with repeatable. The data are stored in extra fields of com_conent. attribs ...
5
votes
2answers
1k views

Rapid Ajax calls causing some MySQL queries to be ignored

I am currently working on a Joomla website on a Unix server. There are no users connected at the moment because I'm the only one who can access it (so no heavy traffic). During testing, I proceed to ...
4
votes
3answers
23k 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
3k 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(); $...
4
votes
4answers
8k views

Joomla update broken updates table

I have recently updated Joomla from v 3.2.3 to 3.3.6 but for some reason when I'm trying to install anything I'm now getting an error: 1146 Table 'dbname.ku42W_updates' doesn't exist SQL=SHOW ...
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 ...
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
1answer
975 views

Table marked as crashed and repair failed

My Joomla 1.5.16 website has started to display the error below. I don't think this is a hack, and I can't find any evidence in the logs of someone trying to get in or any changes to files that ...
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
746 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
2answers
1k views

Join an existing table with a generated table of dates and count rows for each date?

I would like to use this complex mysql query in my joomla model to generate a timeline and count entries within a specific date range. My SQL Query is: SELECT T.calendar, COUNT(DATE(M.date)) as ...
4
votes
2answers
3k views

Creating a module to search the database and display the results

I have got a table in the database called #__magazines, this holds data for magazines (date published, issue number etc..). I have created a Component, Plugin and Module which is for Joomla 3.x, I ...
4
votes
2answers
181 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 ...
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! :)
3
votes
2answers
1k views

Filter strings before saving them in the database

I just created a script witch fetches mails from IMAP and stores the title and content in the database. Of course I don't want to save these information without a check; I'm a bit paranoid but I ...
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 ...
3
votes
2answers
562 views

How to sort my query's results by publish_up value?

I have a .php script that retrieve fields from my database table and then displays them in a foreach loop (here are the relevant parts of the code): <?php $jquery1 = "SELECT `...
3
votes
2answers
563 views

Joomla Debug Console does not show Database Queries

My Joomla 3.4.4 website is not showing Database Queries in Joomla Debug Console at the site front-end but it can be seen at the Administrator back-end. Below is the screenshot at the front-end And ...
3
votes
4answers
996 views

Multiple INSERTS in one query Joomla

How can I do multiple inserts like: $query = "INSERT INTO `table` (`fied1`, `field2`, `field3`) VALUES ('one', 'two', 'three'),('four', 'five', 'six'); INSERT INTO `table` (`fied1`, `field2`, `field3`...
3
votes
3answers
3k views

How to get database information from Joomla category

I know how I can retrieve all information from a article in the database. I am trying to do the same thing for a category. And just for clarification, I am talking about when view is equal to category ...
3
votes
3answers
2k views

Do I need a database to install Joomla on localhost using XAMPP?

I downloaded Joomla to create a website, but before buying any domain/hosting service, I prefer to work locally. Then, I downloaded XAMPP and moved joomla's file to XAMPP's htdocs folder. From ...
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 ...
3
votes
3answers
120 views

Jdatabase : Sorting two selections?

I have Jdatabase table having attendance data of all my students. EXAMPLE : I need to display the attendance of all roll numbers for class 3. The records having 3 in attended_no column were ...
3
votes
1answer
147 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 ...
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
1answer
905 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
1answer
134 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 ...
3
votes
2answers
2k views

AJAX post not able to update database

How can I achieve a database insert using AJAX? I've created a module and I'm using AJAX post to insert a record into the database but seem to get a silent error and no database update; I know the ...
3
votes
2answers
952 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

Jdatabase : Counting number of elements in a comma separated field

I have a Jdatabasetable having a comma separated field. What would be the best way (according to Joomla coding standards) to count the number of elements separated by commas. Like in the case below : ...
3
votes
1answer
85 views

How to tell what WAS the local time of an article?

Sorry if this was explained somewhere, but up to now - nothing found. So, I have an article published today, Aug.14th, at 11:11 local time. This means +3 hours from UTC. At MySQL table content I can ...
3
votes
4answers
935 views

How to retrieve unique values before a parenthesis in JFormFieldList Filter query?

I have a JHtml custom filter populated by a JFormField extended class. It is added in the view with the following code: JHtmlSidebar::addFilter( '- Select Height -', 'filter_height', ...
3
votes
1answer
106 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 ...
3
votes
2answers
700 views

Proper way to use MySQL fulltext search on a keyword string?

I have a keyword string on which I want to do full text searching. The current search query in the model is as follows: $jinput = JFactory::getApplication()->input; $keyword = $jinput->...
3
votes
2answers
561 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
3answers
450 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
1answer
150 views

Using JDatabaseQuery::union() causes errors

I'm getting errors when using JDatabaseQuery::union() to create a database query. With Debug disabled I get this at the bottom of the page, presumably when trying to disconnect: Warning: mysqli::...
3
votes
1answer
396 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
3answers
131 views

Is there a file that defines error messages?

I would like to catch some exceptions to inspect the error code and determine if I should re-throw it with a more user friendly message. This would probably be most beneficial with MySQL errors. Is ...

1
2 3 4 5