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?

Entanglement

are you want to fetch the information for particular user – Utilize

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. – Entanglement

@SeanLang:I have update the code.Please have a look. – Dorthydortmund

Answer 1

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();

Dorthydortmund

Got this working now! was just be being plain stupid ha! Just one further question, how would i add the "where" part to it? – Entanglement

@SeanLang:You can put it after db object and before setquery.see my update answer. – Dorthydortmund

sorted thanks :D is there a way to count the number of rows returned? – Entanglement

You can use either count in mysql which will again a query or simply $result=$db->loadObjectList();count($result); – Dorthydortmund

one final question.. how would i have multiple where clauses? – Entanglement

@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.docs.joomla.org/API16:JDatabaseQuery/where – Dorthydortmund

fixed thanks! I had done it correctly just missed the quotes around the value! – Entanglement

Answer 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.

Unearned

no, nothing at all :S where should i be putting the code or should it work anywhere? – Entanglement

code goes in the model replace your code with what I have provided – Unearned

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. – Entanglement

let us continue this discussion in chat – Entanglement

yes have a function with the above code. Make the call to that function in the controller – Unearned

Answer 3

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; 

Eindhoven

Answer 4

$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

Duda