how to build a sql select query in joomla 2.5?

Question

im new to joomla and im trying to build a component which is an addon for viruemart to allow users to access invoice PDF's in their user area. To do this i need to write a select query which retreives this information from the database for me.

I have been messing around with it and came up with the following, but it doesnt seem to do anything:

$id =JFactory::getUser()->id;
$db =& JFactory::getDBO();
$sql = "SELECT * FROM jos_vm_orders"; 
$db->setQuery($sql);
$options = $db->loadObjectList();
return $options;

Am i missing something?

yes, for the current logged in user. but im trying to return it all first just to get it working before i start filtering it out. — Sean Lang, Nov 01 '12 at 12:16

Irfan · Accepted Answer · 2012-11-06T10:02:59.853

10

You can check this doc for database queries - http://docs.joomla.org/API16:JDatabaseQuery

Cross check your table prefix.Or try this-

$db =& JFactory::getDBO();
$query = $db->getQuery(true);
$query->select('*');
$query->from('#__vm_orders'); 
$query->where('id = 1');   //put your condition here    
$db->setQuery($query);
//echo $db->getQuery();exit;//SQL query string  
//check if error
if ($db->getErrorNum()) {
  echo $db->getErrorMsg();
  exit;
}
return $db->loadObjectList();

edited Nov 06 '12 at 10:02

answered Nov 01 '12 at 12:08

Irfan

7,029
3
42
57

Got this working now! was just be being plain stupid ha! Just one further question, how would i add the "where" part to it? – Sean Lang Nov 06 '12 at 09:57
1

@SeanLang:You can put it after db object and before setquery.see my update answer. – Irfan Nov 06 '12 at 10:01
sorted thanks :D is there a way to count the number of rows returned? – Sean Lang Nov 06 '12 at 10:02
1

You can use either count in mysql which will again a query or simply $result=$db->loadObjectList();count($result); – Irfan Nov 06 '12 at 10:04
one final question.. how would i have multiple where clauses? – Sean Lang Nov 06 '12 at 10:49
1

@SeanLang:You can use as many where as you want.The final query will be joined with "AND" condition if second parameter is not passed.http://docs.joomla.org/API16:JDatabaseQuery/where – Irfan Nov 06 '12 at 10:55
1

fixed thanks! I had done it correctly just missed the quotes around the value! – Sean Lang Nov 06 '12 at 10:57

Techie · Answer 2 · 2012-11-01T12:21:55.870

2

$db =& JFactory::getDBO();       
$query  = $db->getQuery(true);
$query->select('*');
$query->from('#__vm_orders');      
$db->setQuery($query);  
$options = $db->loadObjectList();
return $options;

OR

$db =& JFactory::getDBO();
$sql = "SELECT * FROM #__vm_orders";
$db->setQuery($sql);  
$options = $db->loadObjectList();
return $options;

Try this and let me know if you have any issues.

edited Nov 01 '12 at 12:21

answered Nov 01 '12 at 12:14

Techie

44,706
42
157
243

no, nothing at all :S where should i be putting the code or should it work anywhere? – Sean Lang Nov 01 '12 at 12:25
code goes in the model replace your code with what I have provided – Techie Nov 01 '12 at 12:28
so in the model i would have a function with that code, what would i place in the view? Sorry if its a stupid question. – Sean Lang Nov 01 '12 at 12:33
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/18917/discussion-between-sean-lang-and-dasun) – Sean Lang Nov 01 '12 at 12:38
yes have a function with the above code. Make the call to that function in the controller – Techie Nov 01 '12 at 14:25

score 1 · Answer 3 · answered Nov 01 '12 at 12:35

1

Try This

$user =JFactory::getUser();
$userId = $user->id;//also u get name,email etc
$db =& JFactory::getDBO();
$sql = "SELECT * FROM table where condition"; 
$db->setQuery($sql);
$db->query();
$options = $db->loadObjectList();
return $options;

answered Nov 01 '12 at 12:35

Jobin

8,238
1
33
52

score 1 · Answer 4 · answered Feb 23 '14 at 11:13

$id =JFactory::getUser()->id;
$db =& JFactory::getDBO();
$sql = "SELECT * FROM #__vm_orders"; 
$db->setQuery($sql);
$options = $db->loadObjectList();
return $options;

I would also recommend to use query chaining when using complex queries for Joomla 2.5 and futher version. Here you read about it : http://m4extensions.com/index.php/tutorials/3-access-database-from-your-joomla-extension

how to build a sql select query in joomla 2.5?

4 Answers4