Stack Exchange Network

Stack Exchange network consists of 174 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
2answers
15 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
0answers
21 views

“Trying to get property of non-object” notice on stdClass OBJECT?

I'm getting the following notice when I'm trying to access my object: Notice: Trying to get property of non-object in /home/gonative/public_html/dev/modules/gng/gng_dal.php on line 1109 The ...
3
votes
1answer
40 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
45 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
65 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
53 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
37 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
53 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
326 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
101 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
37 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
117 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
36 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
82 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
48 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
56 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
509 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
1answer
160 views

Regex in Database query - How can non alphabetic characters be selected?

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
296 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
65 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
97 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
95 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
39 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
566 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
342 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
93 views

Select newly inserted record in JDatabase

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
69 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
86 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
390 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
38 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
15 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
513 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
72 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
1answer
449 views

Jdatabase : Working with comma separated values?

I have varchar(255) field in one of my Jdatabase tables having values separated by commas. Precisely, it contains numbers separated by commas I want to retrieve the individual values using a custom ...
1
vote
1answer
502 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
101 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
13 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
112 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
73 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
109 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
617 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 ...
2
votes
1answer
789 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
692 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
249 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
862 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 = ...
1
vote
3answers
639 views

How to get the generated id after a successful INSERT query?

I would like to get the id of the last saved dataset in a JTable-Operation. At the moment I got this: // Create and populate an object. $value = new stdClass(); $value->userid = (int)$...
1
vote
1answer
138 views

Very slow query using JDatabase vs Manual

I have a simple query that is taking almost a minute using JDatabase but takes less than a second when I use sqlsrv_connect. $time_pre = microtime(true); $conn_array = array ( "...