Inserting and Updating Records Using JDatabase in Joomla

When you are doing simple single table inserts and updates you can use the JDatabase methods to do so. The advantages of using the JDatabase methods is it saves your time from hand coding sql and will escape and correctly quote your inputs for you.

The following will insert a new record into a table:

<?php 

$db = JFactory::getDBO();
//Create data object
$row = new JObject();
$row->title = 'The Title';
$row->author = 'Bob Smith';
//Insert new record into #__book table.
$ret = $db->insertObject('#__book', $row);
//Get the new record id
$new_id = (int)$db->insertid();

?>

This will update an existing record:

<?php
$db = JFactory::getDBO();
//Create data object
$row = new JObject();
//Record to update
$row->rec_id = 200;
$row->title = 'The Title';
$row->author = 'Bob Smith';
//Update the record. Third parameter is table id field that will be used to update.
$ret = $db->updateObject('#__book', $row,'rec_id');

?>

You can also use JTable to insert and update records, but requires more initial setup since you have to create a new JTable class for each table you want to modify. Using JTable is preferred if table has many fields to update from a form submit. JTable will automatically bind the form fields to corresponding table fields using the bind() method.

Here is more info on using JTable.

Multiple Left JOIN in One SQL Query

Two Tables
1. w6h8a_community_favloc
2. w6h8a_csearchm_rides

SELECT CONCAT( (
SELECT statecode
FROM w6h8a_states
WHERE w6h8a_states.state = f1.states
), '|', f1.city, '|', f1.zip, '|', r.event_type, '|', f1.address1, '|', f2.address1, '|', '', '|', (
SELECT statecode
FROM w6h8a_states
WHERE w6h8a_states.state = f2.states
), '|', f2.city, '|', f2.zip, '|', '', '|', f1.name, '|', f2.name, '|', r.ride_name ) AS value, r.ride_name AS text
FROM w6h8a_csearchm_rides AS r
LEFT JOIN w6h8a_community_favloc AS f1 ON f1.name = r.pickup_fav_loc_name
AND f1.userid = r.user_id
LEFT JOIN w6h8a_community_favloc AS f2 ON f2.name = r.dropoff_fav_loc_name
AND f2.userid = r.user_id
WHERE r.user_id =82
GROUP BY r.id

It will not getting right output to generating value string.

Solution : 

SELECT CONCAT( (
SELECT statecode
FROM w6h8a_states
WHERE w6h8a_states.state = f1.states
), '|', f1.city, '|', f1.zip, '|', r.event_type, '|', f1.address1, '|', f2.address1, '|', '', '|', (
SELECT statecode
FROM w6h8a_states
WHERE w6h8a_states.state = f2.states
), '|', f2.city, '|', f2.zip, '|', '', '|', f1.name, '|', f2.name, '|', r.ride_name ) AS value, r.ride_name AS text
FROM (
(
w6h8a_csearchm_rides AS r
LEFT JOIN w6h8a_community_favloc AS f1 ON f1.name = r.pickup_fav_loc_name
AND f1.userid = r.user_id
)
LEFT JOIN w6h8a_community_favloc AS f2 ON f2.name = r.dropoff_fav_loc_name
AND f2.userid = r.user_id
)
WHERE r.user_id =82
GROUP BY r.id

In this you have to put two braces after FROM and complete first braces after first join and second braces after second join like this it will working perfectly.

Sorting strings and numbers(varchar type column) in SQL query

Problem :
                  I have a problem with sorting by a string-column when i use SQL query on MySQLServer. There are both numbers, strings and mixture of both in this column and when i sort it numbers are not looks in sorting. ex. 7.5 6 14 8.5 9. I would like to sort numbers numerically and both strings and mixed alphabetically, 14 6 7.5 8.5 9.


Soluation :

Set your query in sorting by using "order by '0000000000'+rtrim(FEILDNAME)" 

Where FEILDNAME is your varchar type column name which you want to sort. This will build up a string for the numbers with leading zeros, so they will get sorted correctly.

Second Highest Value SQL query

select max(FIELDNAME)
from TABLENAME
where FIELDNAME not in
(select max(FIELDNAME)
from TABLENAME);

SQL Server - An identity feild can have negative seed value and negative increments also

Always everyone will start identity value from 1 and increment by 1. However, an identity column can have negative seed value and can be negatively also incremented. Suppose below table will have 4 rows with id values -100,-101,-102,-103

declare @t table

(

Id int identity(-100,-1),

Col1 int

)

insert into @t values (1),(2),(3),(4)

select * from @t

Insert, Update and Delete Query in Joomla

Now its Insert, Update and Delete Query in Joomla,

For INSERT query in Joomla structure
$db = &JFactory::getDBO();
             $insert_query = "INSERT into #__TABLENAME(feild1,feild2) values('value1','value2') ";
             $db->setQuery( $insert_query );
             $db->query();

For UPDATE query in Joomla structure
             $update_query = "UPDATE #__TABLENAME SET feild1 = 'value1' ";
             $db->setQuery( $update_query );
             $db->query();

For DELETE query in Joomla structure
             $delete_query = "DELETE FROM #__TABLENAME WHERE feild1 = 'value1' ";
             $db->setQuery( $delete_query );
             $db->query();

SQL Query in Joomla

Hi to everyone,

In Joomla you create sql query easy way. Only you have to get DBO connection to start database connection only one time in a page.

$db = &JFactory::getDBO();
        $query = "select * from #__TABLENAME";
        $db->setQuery($query);
        $rows = $db->loadObjectList();

in which "TABLENAME" is your joomla table name and "#__" is behalf of "jos_". "jos_" is predefined value in every jomla table. $rows is an array in which all records has been fetched from database.