how to fetch data from DB

For Joomla! 3.x Coding related discussions, please use: http://groups.google.com/group/joomla-dev-general

Moderator: ooffick

Forum rules
Please use the mailing list here: http://groups.google.com/group/joomla-dev-general rather than this forum.
Post Reply
kmchen
Joomla! Intern
Joomla! Intern
Posts: 57
Joined: Sat Oct 21, 2006 12:57 am
Location: marseille (13) france
Contact:

how to fetch data from DB

Post by kmchen » Thu Sep 06, 2018 7:43 am

Hi, on a whole hudge database process I don't want to use loadObjectList() method that would fill my whole memory with an array. Instead I want to use the php fetchObject() to get records one by one.
I just can't see that function in the Joomla API. Did I miss something ?

SharkyKZ
Joomla! Ace
Joomla! Ace
Posts: 1267
Joined: Fri Jul 05, 2013 10:35 am
Location: Unknown

Re: how to fetch data from DB

Post by SharkyKZ » Thu Sep 06, 2018 7:57 am

First you need to set query limit with $query->setLimit($limit, $offset) or $db->setQuery($query, $limit, $offset). Then use loadObject(), loadAssoc() or loadRow(). Or loadResult() if result is single column.

https://docs.joomla.org/Selecting_data_using_JDatabase

kmchen
Joomla! Intern
Joomla! Intern
Posts: 57
Joined: Sat Oct 21, 2006 12:57 am
Location: marseille (13) france
Contact:

Re: how to fetch data from DB

Post by kmchen » Fri Sep 07, 2018 1:24 pm

Thanks for your reply; Do you mean setting limit to 1 and increasing limitstart after each load Object ?
It looks heavy to me.
Having no answers since 2 days I forgot Joomla database methods and used mysqli->fetchObject() whitch works like a charm. My script now leaves space to other applications and goes quicker cause no more swaps.
The only thing is that I had to set the mysqli::set_charset():

$config = JFactory::getConfig();
$this->db = mysqli_connect($config->get('host'),$config->get('user'),$config->get('password'),$config->get('db'));
if (mysqli_connect_errno())
{
die ("Failed to connect to MySQL: " . mysqli_connect_error());
}
$this->db->set_charset ( "utf8" );
...
$results = $this->db->query("$query");
while($i = $results->fetch_object()){
...
Finaly I wonder why using joomla database classes when php ones are very easy to use and perfectly documented...

SharkyKZ
Joomla! Ace
Joomla! Ace
Posts: 1267
Joined: Fri Jul 05, 2013 10:35 am
Location: Unknown

Re: how to fetch data from DB

Post by SharkyKZ » Fri Sep 07, 2018 3:15 pm

I misunderstood your issue, it seems. loadObjectList() actually runs the same loop with fetch_object() and returns all results results as an array.

To answer your last question, it's because Joomla supports multiple database types. If you hardcode MySQLi functions, your code will not work on other Joomla-supported databases.

kmchen
Joomla! Intern
Joomla! Intern
Posts: 57
Joined: Sat Oct 21, 2006 12:57 am
Location: marseille (13) france
Contact:

Re: how to fetch data from DB

Post by kmchen » Fri Sep 07, 2018 7:46 pm

sO IN CLEAR, if you have 1 million records in your Joomla database you have to load them all in RAM to treat them one by one.

SharkyKZ
Joomla! Ace
Joomla! Ace
Posts: 1267
Joined: Fri Jul 05, 2013 10:35 am
Location: Unknown

Re: how to fetch data from DB

Post by SharkyKZ » Sat Sep 08, 2018 9:58 am

No, you can do the same in Joomla with loadNextObject():

Code: Select all


$db = JFactory::getDbo();
$db->setQuery($query);
while($i = $db->loadNextObject())
{
...
}
Not everything is documented in user docs. It's best to refer to API documentation https://api.joomla.org/cms-3/classes/JD ... river.html

kmchen
Joomla! Intern
Joomla! Intern
Posts: 57
Joined: Sat Oct 21, 2006 12:57 am
Location: marseille (13) france
Contact:

Re: how to fetch data from DB

Post by kmchen » Sun Sep 09, 2018 7:30 am

Great ! I'll have a look.
But I see the Joomla's doc:

loadNextObject

Method to get the next row in the result set from the database query as an object.

loadNextObject(string $class = 'stdClass') : mixed
deprecated
since 11.1
throws \RuntimeException
deprecated
12.3 (Platform) & 4.0 (CMS) - Use getIterator() instead

Arguments
$class
stringThe class name to use for the returned row object.
Response
mixedThe result of the query as an array, false if there are no more rows.

1/ Result is an array or an object ?
2/ How do you know that Joomla do not load the whole results in RAM with that documentation ?
2/ Deprecated - Is there something I could use that will survice next year ?

User avatar
Per Yngve Berg
Joomla! Master
Joomla! Master
Posts: 24885
Joined: Mon Oct 27, 2008 9:27 pm
Location: Akershus, Norway

Re: how to fetch data from DB

Post by Per Yngve Berg » Sun Sep 09, 2018 8:33 am

It's better if you explain what you are trying to achieve. Why do you have to go through that many rows?

You can use the Pagination functions build into MySQL, or perhaps better use a stored procedure that executes it all in the database.

https://dev.mysql.com/doc/refman/5.7/en ... views.html
https://dev.mysql.com/doc/refman/5.7/en ... -rows.html


Post Reply

Return to “Joomla! 3.x Coding”