Database connection

[rene.brodmuehler@gmail.com]

Dear All

in the examples how to connect to a database I always find this for the beginning:

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->insert('tablename')->set('field = ' . $db->quote('bar'))
->set('field2 = ' . $db->quote('{tablename___elementname}'));
$db->setQuery($query);
$db->execute();

I am not familiar with this kind of semantic. I guess I am more the procedural kind of guy and prefer this style:
$db = mysqli_connect($host,$user,$password,$database)
or die ("Abfrage konnte nicht durchgeführt werden 1 <br>\n");
$query = "SELECT SUM(e_netto) AS s_netto, SUM(e_netto)+1000 AS s_brutto FROM jahresrechnung_18_repeat WHERE parent_id = " . $myid;
$result = mysqli_query($db,$query);

Two questions please:

(1)
How can I translate
$db = JFactory::getDbo();
$query = $db->getQuery(true);
into my style

(2)
How can I extract the username, host, password and database from the $db variable.

I really appreciate your help.
Best regards
René

 

[lousyfool]

For Joomla's PHP "insert" syntax refer to e.g. https://docs.joomla.org/Inserting,_Updating_and_Removing_data_using_JDatabase
For "select" queries see e.g. https://docs.joomla.org/Selecting_data_using_JDatabase

To see what's in PHP arrays or objects, use print_r() or var_dump():
https://www.w3schools.com/php/func_var_print_r.asp
https://www.w3schools.com/php/func_var_var_dump.asp

 

[rene.brodmuehler@gmail.com]

Hi

again many thanks. I used print_r and can see what is in the variable. I don't know the syntax to read it out. E.g. the beginning of the read-out for $db is as follows:

JDatabaseDriverMysqli Object
(
[name] => mysqli
[serverType] => mysql
...
[optionsrotected] => Array
(
[prefix] => lvb_
....

I understand that print_r ($db->name) gives me mysqli ....

But - how can I read the value of [prefix]?

Thanks
René

 

[lousyfool]

That's the whole point of "protected": to not get it that easily.

Instead, try:

$app = JFactory::getApplication();
$pre = $app->get('dbprefix');

 

[rene.brodmuehler@gmail.com]

Will do, thanks. I guess the best is I learn this "chaining" language. .... many thanks Rene

 

[juuser]

Some simple examples in "your style" mentioned above for e.g. calc element.

1) Get data from several elements in one row:

$mydb = JFactory::getDBO();
$some_element = '{mytable___myelement_raw}';

$mydb->setQuery("SELECT item_code, description FROM mytable WHERE id = ".$mydb->Quote($some_element));
$myrow = $mydb->loadRow();

$item_code = $myrow['0'];
$item_desc = $myrow['1'];

return $item_code." - ".$item_desc;

2) Get data from multiple rows / elements:

$mydb = JFactory::getDBO();

$mydb->setQuery("SELECT item_code, description FROM mytable");
$rows = $mydb->loadObjectList();

$myhtml = "";
foreach ($rows as $row) {
   $myhtml .= $row->item_code." - ".$row->description."<br>";
}

return $myhtml;

Using these examples doesn't mean there's no need to learn the principles from the links lousyfool referred to