Questions tagged [jdatabase]

Joomla Database connector class

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 ...
2
votes
2answers
267 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
3k 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
4answers
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); $...
21
votes
1answer
13k views

How to combine ANDs and ORs in the WHERE clause using a Query Object?

Given the desired SQL below, where Cond1 and Cond2 must be met OR Cond3 must be met for selection, what is the correct way to use getQuery() to achieve it? Desired SQL: Condition1 and Condition2 ...
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 ...
11
votes
3answers
4k views

Can multiple records be inserted all at once with the jDatabase?

Instead of using a loop, can Joomla database functions create a SQL statement like this? INSERT INTO #__tablename (col1,col2) VALUES ('1', 'one'), ('2', 'two'), ('3', 'three'), ... ('999', '...
8
votes
1answer
4k views

How to use LIMIT in getQuery

Given the desired SQL: SELECT * FROM #__tablename LIMIT 5 How do I use $query to accomplish it? $db = JFactory::getDBO(); $query = $db->getQuery(true); $query->select($db->nameQuote(...
4
votes
2answers
1k views

How to write an Insert Select Statement?

This Method for creating a subquery using JDatabase provides some great insight and I think it would be beneficial to have a agreed method of subquery documented. I came across this because I am ...
3
votes
2answers
1k views

How to print a single-row result set as a html table?

I am performing a database query like this: $db = JFactory::getDbo(); $user = JFactory::getUser(); $query = $db->getQuery(true); $query->select('*'); $query->from($db->quoteName('#...
2
votes
1answer
504 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
2answers
163 views

Redirect if userid is not found in a database table

When users login, I am trying to push them to fill their 3rd party component's Profile form properly. When they filled the form their user id is added to the "#__rbid_users" table (user id is same as ...
2
votes
3answers
72 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()...
1
vote
1answer
55 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
159 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
1answer
274 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
1answer
776 views

Select returning only one row with one column

$db = JFactory::getDbo(); $user = JFactory::getUser(); $query = $db->getQuery(true); $query->select('username'); $query->from($db->quoteName('#__selected_packages')); $query->where($db-&...
1
vote
2answers
251 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 ...
0
votes
2answers
308 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
451 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 ...