$db-> I want objects

[Someone]

I can appreciate nostalgia nostalgia of fetching rows in an array. But $row[4] and $row[6] does not tell you very much when reading code.

Think we need $row->last_post, there is $row['last_post'] when using $db->LoadData() but that only works when fetching single rows (I think). Unless there is significant optimization benefits from using $row[0]

[a_bertrand]

It's faster to not get back the column name. That's what PHP manual says, and that's why I don't get it so far.

[Someone]

Answer accepted. I did a read up sometime on mysql_fetch_object vs mysql_fetch_array and that was claimed to be insignificant difference. But that compared to not geting the column names I can see be slower. I do like speed, so Ill live fine with $row[0] (dont know the technical term for it)

[gmoore]

Do you think it will make the system too laggy?

Greg

[a_bertrand]

Honestly, for a small game I doubt you will see much of a difference, yet, the more load you get the more those small difference can count. So I don't know.

[Someone]

Did some Googling, did not find much for mysqli_, google still insits on showing mysql_ results.

 

An object is just a more advanced array with more features and abilities but with that comes a price of speed and memory footprint. So no, mysql_fetch_array and mysql_fetch_assoc are faster and less resource intensive than mysql_fetch_object.

---

I recommend using mysql_fetch_assoc because 1) mysql_fetch_array always stores twice as much, and 2) because using associative arrays allows you to manipulate the crap out of your db without breaking your site.

---

Yup, I always use mysql_fetch_assoc. You can do mysql_fetch_array($sql, ASSOC) which does the same thing as mysql_fetch_assoc.

---

i always use mysql_fetch_array($result, MYSQL_NUM); because that is the fastest (the same as mysql_fetch_row()) and i never use select * so i always see the order of what i'm selecting and using everything

php.net says mysql_fetch_object() is a hair slower than mysql_fetch_row()

---

Yeah, you shouldn't be selecting * anyway - but that was just for illustration purposes.

Also, I did a rough benchmark about 6 months ago doing 1,000 mysql_fetch_assocs vs 1,000 mysql_fetch_objects and the fetch_assocs where about 10% faster (IIRC).

And, I think the enhanced readability of $row['fieldname'] is worth the minor slowdown over fetch_row.

http://forums.devshed.com/php-development-5/mysql-fetch-object-vs-mysql-fetch-array-211260.html Dec 2004(!!!)

I think it depends on what you are doing for it to be worth the extra performance. That is if things are the same with mysql_ and mysqli_

- Never use SELECT *, select only the columns that will be used http://phillipnb.wordpress.com/2011/07/07/php-fetching-unnecessary-column-names-in-sql/

- NWE does have rather few columns per table, not 100% sure if that helps fetching column names faster, but common sense tells me it should be a good thing.

- If selecting large amount of records, or if the code is ran on every page load, like in a Auto_pre_content.php, use the proper mysqli_ for best performance. Most of the time, in our games I think we select max 20-50 at a time, think it rather safe to not think to much about performance in those cases.

Leaning back to wanting $row->columnname and/or $row['columnames'], available (with time), then its up to the game owners and module developers to use it properly.

Edited January 14, 2013 by Someone
Auto_pre_content is better example than lib.php

[a_bertrand]

Will check how to do it properly. Not fully sure honestly as the DB class is a bit of a pain. Mainly due to the simplifications it gives to the usage, like hide the MySQLi prepare statement and such.

[Someone]

Or I can stop being lazy :), and declare variables if am doing hard to read stuff.

$var = $row[0];

 

That db class looks pretty advanced

[gmoore]

You know, I really, really like the column names to be used. But I doubt going back at this point is gonna fly.

.Greg

[a_bertrand]

I think I found an elegant solution which should do the trick. I will try to implement it and let you know.

[a_bertrand]

Ok I think I managed to do something which:

1) Doesn't break anything we currently have

2) Let you access the data as $result->fields[0] or $result->fields['id'] or $result->fields->id

It certainly has some speed penalty, how much? well you will have to check. I will send you via email the code Someone, please comment it back, as if you like it I will include it in the next release.

BTW in my development version I added a config flag which let you choose to use this feature or disable it and only access via column position.

Multiple ways to access your data now:

$result=$db->Execute("select id,username from users");
// Using a while loop
while(!$result->EOF)
{
   // Old access is still valid
   echo "{$result->fields[0]}, {$result->fields[1]}<br>";
   // Accessing via column name index
   echo "{$result->fields['id']}, {$result->fields['username']}<br>";
   // Accessing it like an object
   echo "{$result->fields->id}, {$result->fields->username}<br>";
   $result->MoveNext();
}
$result->Close();

$result=$db->Execute("select id,username from users");
// Using a foreach
foreach($result as $row)
{
   // Works here as well as object
   echo "{$row->id}, {$row->username}<br>";
   // Or foreach inside the $row (however the $k is a number not the column name)
   foreach($row as $k => $v)
   {
       echo "$k=$v, ";
   }
   echo "<br>";
}
$result->Close();

[KyleMassacre]

Thank you for this I think its easier to access info via column name. The field[#] confuses me and i havent gotten the hang of it yet haha

[a_bertrand]

Well I hardly see the problem, you just count the columns and know which one it is. A very good reason also to not work with select * ;)

The main concern is the performance hit. But that's then matter of the game owner / coder. An option to enable / disable this features will be put in the config.php

[Djkanna]

Well I hardly see the problem, you just count the columns and know which one it is. A very good reason also to now work with select * ;)

The main concern is the performance hit. But that's then matter of the game owner / coder. An option to enable / disable this features will be put in the config.php

I think you mean not* work with select *. :P

Also wouldn't a config option break modules?

Would it be more beneficial to separate the methods of getting the returned data, rather than packaged together.

Would then most likely limit the potential speed decreases to specific modules, rather than the entire engine?

If the norm is still an easy approach, yet the secondary methods require extra steps by the module developer, then so be it, this way it's down to choice rather than force.

Though then again, would all depend on these speed penalties, and whether the difference is worth making a fuss over.

[a_bertrand]

Indeed... should be "not to".

The official modules will continue to use the column number instead of the name. So you will be free to enable or not the loading via text.

For a second way to access the data it would be possible but maybe not very intuitive... I'm unsure here.

[a_bertrand]

Maybe I found a compromise which would please all (me too):

$result->fields will by default not query the column names till you access them, at that point the column names will be retrieved and populated, which means if you don't access via column names you should have very little impact in performances and yet if a module requires it it will retrieve the column names and therefore have the compatibility safe. What do you think? Would that be acceptable?

I will make some tests tomorrow if I have time (tomorrow being a nasty day for me).

[Someone]

Gonna look through it, I dont mind spending some time to perhaps load the database with a few thousand dummy records, just to get a feel of any penalty.

[a_bertrand]

Ok I managed to do yet even more, basically the library doesn't load the column names till you try to use them either as array key or as object property. So until you use them you will have very little speed impact (nearly none). So a module which uses them will use them otherwise it will remains more or less like before. Code compatibility is saved, as well as speed yet offers different ways to access the data.

This will be included in the 1.1.5 which shall be available in the coming days.