21 votes
Accepted

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

Including the parentheses that are needed, with Chaining , produced the desired SQL: With Chaining: specifying OR and wrapping Cond1/2 in parentheses $query = $db->getQuery(true); $query->...
user avatar
  • 7,685
12 votes
Accepted

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

This can be accomplished by using ->insert() and ->values(), with the $values being an array of the strings with the columns to insert. $db = JFactory::getDbo(); $query = $db->getQuery(true);...
user avatar
  • 7,685
10 votes
Accepted

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

Try this: $rows = $db->loadrow(); echo '<table>'; echo '<tr>'; foreach($rows as $row) { // display your results here } echo '</tr>'; echo '</table>'; You ...
user avatar
  • 26.5k
9 votes
Accepted

How to use LIMIT in getQuery

Both J2.5 and 3.x will vary. Joomla 2.5: $db->setQuery($query, 0, 5); where 0 is the start/offset and 5 is the limit Joomla 3.x $query->setLimit(5); This also accepts a 2nd argument which is ...
user avatar
  • 26.5k
8 votes
Accepted

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

Your foreach will result in situation where $key is array key and $value is one row from database. If you want to get column names from result just use get_object_vars: $db = JFactory::getDbo(); $...
user avatar
8 votes
Accepted

SQL Injections using Jfactory::getDBO and setQuery() with LIKE condition

You won't need to worry about sql injection attacks if you use the quote() ( or q()) and quoteName() (or qn()) methods appropriately. From https://docs.joomla.org/Secure_coding_guidelines#...
user avatar
  • 4,448
7 votes
Accepted

Allowing Registered Users To Edit Only Specific JDatabase records?

You are making this way too difficult. Joomla provides you with a feature called ACL that allows just that. For each record, Joomla will create an extra line in the #__assets table. What you need ...
user avatar
6 votes
Accepted

How to write my MySQL query with JOIN logic using JDatabase methods?

$db = JFactory::getDbo(); $query = $db->getQuery(true) ->select('user.firstname, user.lastname, leadership.leadname, leadership.lead_des') ->from('CLUB,LEADERSHIP,USER') ->where('...
user avatar
  • 2,971
6 votes
Accepted

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

There is basically nothing wrong with replacePrefix(). What actually happens is that you are sending to MySQL a string which is too large for a predefined setting (max_allowed_packet) and is ...
user avatar
6 votes
Accepted

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

The second parameter of where, called $glue, does not work as you might expect. This is the source code of the where function: public function where($conditions, $glue = 'AND') { if (is_null($...
user avatar
  • 3,693
5 votes
Accepted

Selecting data from multiple JDatabase tables?

To check for a value in multiple tables that do not have a relationship you are going to need either to make multiple queries, 1 for each table, or better make a UNION query. Now, Joomla has the UNION ...
user avatar
  • 19.5k
5 votes
Accepted

JDatabase Like Operator

try this $db = JFactory::getDbo(); $query = $db->getQuery(true); $query ->select('Common_name') ->from($db->qn('your_table_name')) ->where($db->qn('Common_name').' LIKE '. $...
user avatar
  • 2,971
5 votes
Accepted

How to relate comma-separated ids from one table to the ids of another table?

Just 2 simple queries, with first you select commas and then you check if IDs are inside that string. Just tested and it works using MySql IN() function with $commas string value fetched from $db->...
user avatar
  • 437
5 votes
Accepted

with insert on duplicate key update - updating multiple rows at once

Building a REPLACE INTO query is just as simple as building an INSERT ... ON DUPLICATE KEY UPDATE. Note that there is a difference between REPLACE INTO AND ON DUPLICATE KEY UPDATE. Code: $columns = ...
user avatar
  • 4,448
4 votes

How to write an Insert Select Statement?

You have to type which values you want to insert $pre->insert($db->qn('#__table'))->columns($db->qn($columns))->values(here values);
user avatar
  • 570
4 votes

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

Joomla core support basic SQL queries. You can create a class to store several inserts and create a single final query to execute a single insert at the end. public function flushInserts() { $...
user avatar
  • 7,415
4 votes
Accepted

How to use Oracle database for populating a list in a form

There is a file called configuration.php in root directory. Open that file, find:- public $dbtype = 'mysqli'; change that value to: public $dbtype = 'oracle'; this will change database driver, ...
user avatar
4 votes
Accepted

JDatabase JOIN on multiple values

In your ON statement you should be able to add multiple conditions combined by AND and OR. So something like this should do the trick: ->join('INNER', $db->quoteName('app_animal', 'b') . 'ON (' ...
user avatar
  • 3,693
4 votes
Accepted

Delete items of database via CLI

It's been awhile since I've done this, but as I recall putting this within doExecute should do what you want. $basePath = JPATH_ADMINISTRATOR.'/components/com_content'; require_once $basePath.'/...
user avatar
  • 7,685
4 votes
Accepted

Jdatabase : Sorting two selections?

I don't believe you need to use 2 queries. try the following: $db = JFactory::getDbo(); $query = $db->getQuery(true); $query->select(array('roll_no', 'attended_no')) ->from($db->qn('...
user avatar
  • 26.5k
4 votes
Accepted

MySQL SELECT query with LIMIT to show a maximum of 3 images

Problem being is you're using loadObject() which will only ever return 1 result. If you want multiple, you'll need to use loadObjectList(), like so: $db = JFactory::getDbo(); $query = $db->...
user avatar
  • 26.5k
4 votes
Accepted

Insert query with ignore of duplicates (without key)

As you're using MySQL, you can create an unique index over your columns original_id and analogue_id. Then use a raw query like: $db = JFactory::getDbo(); $query = 'INSERT IGNORE INTO ' . $db->...
user avatar
4 votes
Accepted

How to write a query with GROUP BY and ORDER BY using Joomla's query methods?

I see minor typos in each of your coding attempts (misspelled method names: selcet and loadObjectLsit). Write the GROUP BY order like so: (Reference Link) SELECT id, DATE_FORMAT(estEventDate, '%b %Y'...
user avatar
  • 4,448
4 votes
Accepted

Redirect if userid is not found in a database table

The first problem is that you're using joomlausers->id which doesn't look correct. It should be $joomlausers->id (with a $ at the beginning). Secondly, loadObjectList() returns an indexed array ...
user avatar
  • 26.5k
4 votes
Accepted

loadAssocList() is not working properly when assigning a column name as the key parameter

Most critical to fixing your issue is to include the p.ComponentId column in your select() call so that it is available at the time the result set is being constructed. If you have your error ...
user avatar
  • 4,448
4 votes

SQL syntax error in query with subquery using JDatabase

to spot a sql syntax error it's useful to view the resultant sql eg. echo $query->__toString(); you've put the alias of the subquery 'b' as the alias of the table inside the subquery. But the ...
user avatar
  • 3,134
3 votes

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

You can just execute it in the same way than a simple query. db->setQuery($my_big_query); $row = $db->loadObject(); Of course, it would be very difficult to translate the SQL ...
user avatar
  • 7,415
3 votes
Accepted

View Users Front End - php code

Along with @Tim Wilkinson's answer, I would also use up to date coding standards for doing your database query like so: $db = JFactory::getDbo(); $query = $db->getQuery(true) ->select($db-...
user avatar
  • 26.5k
3 votes

View Users Front End - php code

Try this, echo $row->id.'|'.$row->username.'|'.$row->email.'<br>';
user avatar

Only top scored, non community-wiki answers of a minimum length are eligible