Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [jdatabase]

Joomla Database connector class

0
votes
1answer
16 views

Connect to two MySQL db's

I want to use a second database for the product data. Is it possible to connect to two database and using of JFactory::getDbo(); ?
1
vote
1answer
72 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
53 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
80 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`...
2
votes
3answers
55 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
97 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
64 views

Why does the truncateTable() method create an empty string for a query and fail to delete rows?

I have the following code attempting to truncate a table. The Joomla documentation makes me believe this will work, but it does not. What am I missing? $db = JFactory::getDbo(); truncate_query = $db-&...
1
vote
2answers
40 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(...
1
vote
1answer
57 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->...
5
votes
1answer
500 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 ...
1
vote
1answer
110 views

Error when populating a database table while installing a module

I am using the following code for creating tables in my .sql file as part of a module. CREATE TABLE IF NOT EXISTS `#__gruposlocales` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(...
0
votes
1answer
43 views

Downloading large data sets

My site has a facility to download a potentially large body of data from my database as a CSV file. This is currently implemented using a view.csv.php file that serves this data by first setting the ...
0
votes
1answer
157 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
39 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 ...
2
votes
2answers
93 views

Using JDatabase standalone, ie. without Joomla application bootstrapping

Can one instantiate a jdatabase object without the Joomla application? The obvious use case is when you want to open a direct endpoint into some standalone code in your site for an api or cron etc. ...
1
vote
1answer
49 views

Join two similar table SQL [closed]

In database it shows: -Table 1 data show discount_category_id as: 1 2 3 -Table 2 data show category_id as: ,1, ,2, ,3, So is anything wrong or any syntax error with my code because i tried print_r ...
3
votes
2answers
71 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 ...
1
vote
3answers
604 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
185 views

How to query a table column for values that start with a non-alphabetic character?

How can rows starting with non alphabetic characters (all "special" characters, including spaces and underscores) be selected? I don't know if it changes anything, but my_table will also contain ...
2
votes
1answer
330 views

Multiple Count from same table

I used to get multiple COUNT in one query using the SUM(CASE WHEN $db =& JFactory::getDBO(); $query = "SELECT SUM(CASE WHEN last_name LIKE 'A%' THEN 1 ELSE 0 END) as alpha, SUM(...
0
votes
1answer
73 views

JDatabase Update +1

I am trying to update the database with the original column's value plus (+1) or minus (-1) $column_name = 'mycolumnname'; $table_name - 'mytablename'; $db = JFactory::getDbo(); $query = $db->...
2
votes
1answer
119 views

Cannot get num rows with the database driver

Any idea how to fix this? $dbo = JFactory::getDbo(); $query = 'SELECT `#__example`.`example_value` AS `value` FROM `#__example`'; $dbo->setQuery($query); $rowsCount = $dbo->getNumRows(); $...
1
vote
0answers
103 views

Joomla query for monthly result in a graph

I have this query below and it gives me the result attached as result.jpg, but I want to generate the report on a monthly basis. I have attached the expected result as monthly.jpg <td> <...
0
votes
2answers
42 views

Cannot query Joomla database as a guest user

I have a site mostly targeted for registered users but I want to provide guests (potential new users) with registration form. This registration form is a Menu item publicly available (it consists of a ...
2
votes
2answers
611 views

Global PHP functions?

How can I make few global php functions which can be called from any Joomla custom module? E.g I wish to make a global function executing a select database query: function select($row,$table_name,$...
3
votes
1answer
378 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, ...
0
votes
1answer
128 views

Select newly inserted record in JDatabase [duplicate]

I am manually adding a record using PHP (sourcerer) into Jdatabase. I wish to select the record that was added using the insert query below. Is there a wayout without using another query with where ...
0
votes
1answer
71 views

Custom Admin Dashboard: Manual Menu Assignment to Modules

I need to create a custom Admin dashboard for my client. He should be able to add, edit custom modules via this dashboard. As far as I know I need to play with #__modules table (jdatabase).I figured ...
1
vote
1answer
88 views

Example form on posting data through JInput class

I've been told numerous times to use JInput Class as opposed to using 'method="post" on my forms. I have no problem finding example of how to retrieve data using JInput, but after searching numerous ...
1
vote
1answer
35 views

No syntax errors & Query works without one parameter

The following query will not return any results... if (empty($from_date)) { $from_date = 2000-01-01; } if (empty($to_date)) { $to_date = 3000-01-01; } if (empty($_POST['supervisor'])) { $...
1
vote
3answers
431 views

PHP function with JDatabase queries

I have these two blocks of identical code in one of my PHP modules. The only difference between these two blocks is that in first block it is ... active = ' .$db->q('1')); and in the second block ...
0
votes
2answers
43 views

Accessing Jdatabase via JavaScript Or Passing PHP varibles to JavaScript

I want to use this SVG Pie Chart on my Joomla site but the problem is I need to access data from Jdatabase and feed it to its index.js $(function(){ $("#pieChart").drawPieChart([ { title: "Cash"...
2
votes
2answers
16 views

Jdatabase: Finding part of the string which doesn't match $X string

I use some mysql queries to retrieve data from a Jdatabase table as strings. Now I need to find the part of that string which doesn't match another string (which actually is a sub-string of the main ...
4
votes
2answers
576 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
3answers
75 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 ...
4
votes
2answers
476 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
1answer
573 views

JDatabase Like Operator

I would like to know how to perform pattern search using JDatabase Here's my code: $query->select('Common_name'); $query->from($db->quoteName('common_name')); $query->where($db->...
3
votes
1answer
107 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::...
2
votes
1answer
1k views

How to check whether a specific row exists in a JDatabase table?

What would be the best way to check whether a specific row exists already in a JDatabase table? Instead of INSERT INTO Command, I need to execute a UPDATE command if the row already exists. Help ...
1
vote
0answers
14 views

Need some suggestion regarding strategy of scraping article view times from another webpage to use in K2 item

I am building a video blog page using Joomla 3.0 + k2 hosted on my own Ubuntu 14.04 server, the videos will be uploaded to sites like youtube or vimeo too. I want to show the viewed times of the ...
7
votes
2answers
132 views

Combining 'ANDs' and 'ORs' in a complex WHERE query - Part 2

I have been pulling my hair out with this query for the last 2 days and have read and re-read the 2 other similar posts with no luck. Scenario: I am gathering two inputs from a user: Test ID Code and ...
0
votes
1answer
80 views

Get HWD media share video thumbnail url from Joomla database

I have videos on my site component HWDmedia share. They haven't schema.org integration and trying add markup to videos. For videoobject have 4 types and i add 3 of them (name,description and date) but ...
0
votes
1answer
115 views

JDatabase subQuery not work [duplicate]

I have products table and brads table, i want show record from brands table whit product count. The query: SELECT a.*, ( SELECT COUNT(*) FROM jos_products AS p WHERE ...
1
vote
1answer
621 views

Joomla JFactory::getDBO() - Prefix is wrong?

I am pretty new to Joomla, and I'm stuck! I'm trying to access the Joomla DB from a PHP script, using the JFactory::getDBO(); method. I noticed all my queries were returning '0' results, even though ...
3
votes
1answer
114 views

COUNT(*) function not working in SELECT query

I wrote a simple SQL statement that counts the number of category IDs in a table. Problem is, its returning NULL. $db = JFactory::getDbo(); $query = $db->getQuery(true); $query->select($db->...
2
votes
1answer
858 views

JDatabaseQuery quote correctly the items into select

I have written a database query, like so: $query = $db->getQuery(true) ->select("u.*,ju.username,ju.email,ju.lastvisitDate as last_login") ->from("#__component_users AS u") ->...
1
vote
2answers
735 views

Transforming a Query for JDATABASE

I need to transform a simple php query into a query that JDATABASE will understand. Here is the example: $resDays = 7; // On how many days to build the top? $resLimit = ...
1
vote
1answer
261 views

Delete items of database via CLI

I would like to delete some items with state "trashed" via CLI. Is it possible to call the delete-method of JTable from CLI? Thanks for your help :-)
4
votes
3answers
2k views

How can I get the names of the columns in an object or table with Joomla's SQL?

With the existing output I have from a basic query, what would be the best way to get the names of the columns in the resultant output object without having to run an additional COLUMNS query on the ...
2
votes
2answers
950 views

Get ID of deleted database-entry

I would like to create a delete-method, which deletes an entry in a table AND the attached entries in a second table. This is my source so far: // Delete entry in users_handles $db = ...