User Contributed Notes 24 notes

If you're using mysql_fetch_object and specifying a class - the properties will be set BEFORE the constructor is executed. This is generally not an issue, but can cause some major problems if you're properties are set via the __set() magic method and constructor logic must be executed first.

This method offers a nice way to fetch objects from databases. As Federico at Pomi dot net mentioned it doesn't work native as the type of the object fetched isn't the right one, but with a small typecast it works flawlessly.

Be aware how you write code in your methods: in this case, classes are used for centralize the code and they are notreally safe because you can have an additional Information ( with a Join Query for example ) without methods to access to them.So classes need get and set method generalized.( for extra see PHP 5 Manual O'Rielly on the use of generalized methods get and set )

Every query that would fail in a database frontend, such as MySQLs "Query Browser" and only will work by using the `-marks will probably give results hardly accessible in PHP especially if you have column names with "-" or " " in it.

Using the example of Simon Paridon: it is not possible to execute a query like:

SELECT id, user-id FROM unlucky_naming

only

SELECT id, `user-id` FROM unlucky_naming

will work...

so either be a bit wiser when naming the colums (e.g. user_id)

or try it with

SELECT id, `user-id` AS user_id FROM unlucky_naming

(i have not tested it in PHP yet, but i guess this will fail as well, if you have a query like "SELECT `foo name` FROM `unlucky naming 2`")

Somewhat down "amackenz at cs dot uml dot edu" mentioned to name sum, count etc. this may be a good hint for newbies: increase the speed of your php applications by using (my)sql native functions and save data transfer as well as processing time

This is a very very elegant (and costless) way to fetch an enterie query to every single field name from a "wide" table:

The tedious way fetching:<?php $idtable=mysql_connect("localhost","user","pwd");mysql_select_db("table",$idtable);$consult=mysql_query("SELECT * FROM models ORDER BY Serie,Year ASC"); while($row=mysql_fetch_object($consult)){$IdLomo=$row->IdLomo;$Serie=$row->Serie;$Model=$row->Model;$Type=$row->Type;$Year=$row->Year;$Speed=$row->Speed;$Power=$row->Power;$Price=$row->Price;$Load1=$row->Load1;$Load2=$row->Load2;$Load3=$row->Load3;$Load4=$row->Load4;$Server=$row->Servier;$Real=$row->Real;$Lomo=$row->Lomo; }?>

Resulting on 15 new variables called like their name on table wich we introduced manualy for 5 minutes. But imagine each row has 100 fields!

A way saving time with the same result:

<?php $idtable=mysql_connect("localhost","user","pwd");mysql_select_db("table",$idtable);$consult=mysql_query("SELECT * FROM models ORDER BY Serie,Year ASC");// We find the fields number$numfields=mysql_num_fields($consult);

// Now we put the names of fields in a Arrayfor($i=0;$i<$numfields;$i++){$fieldname[$i]=mysql_field_name($consult, $i); }

while($row=mysql_fetch_object($consult)){//Finally we assign the new variablesfor($i=0;$i<$numfields;$i++){ $$fieldname[$i]=$row->$fieldname[$i]; } }?>

In reviewing Eskil Kvalnes's comments (04-Mar-2003 11:59 When using table joins in a query you obviously need to name all the fields to make it work right with mysql_fetch_object()) I was left asking and, as a newbie, the reason why I'm here. I have a 28 field table. Ran SELECT * with a LEFT JOIN, etc and it appears to have worked on my test server without issue.

On further reading, MYSQL.COM has the following:* It is not allowed to use a column alias in a WHERE clause, because the column value may not yet be determined when the WHERE clause is executed. See section A.5.4 Problems with alias. * The FROM table_references clause indicates the tables from which to retrieve rows. If you name more than one table, you are performing a join. For information on join syntax, see section 6.4.1.1 JOIN Syntax. For each table specified, you may optionally specify an alias.

Aware of the fact there's a difference between tables and fields there appears to be confusion here somewhere.

This won't work! When the method get_from_db() is executed, your old object will be destroyed... you won't find anything in the attribute $doh, and if you'll try to call the method print(), it will say it doesn't exist.

That huge difference in timings may be caused by database cache issues. If mysql_fetch_object() was the first function to be used, the cache was empty, but subsequent invocations took the result from the query cache.

Some clarifications about previous notes concerning duplicate field names in a result set.

Consider the following relations:

TABLE_A(id, name)TABLE_B(id, name, id_A)

Where TABLE_B.id_A references TABLE_A.id.

Now, if we join these tables like this: "SELECT * FROM TABLE_A, TABLE_B WHERE TABLE_A.id = TABLE_B.id_A", the result set looks like this: (id, name, id, name, id_A).

The behaviour of mysql_fetch_object on a result like this isn't documented here, but it seems obvious that some data will be lost because of the duplicate field names.

This can be avoided, as Eskil Kvalnes stated, by aliasing the field names. However, it is not necessary to alias all fields on a large table, as the following syntax is legal in MySQL: "SELECT *, TABLE_A.name AS name_a, TABLE_B.name AS name_b FROM TABLE_A, TABLE_B ...". This will produce a result set formatted like this: (id, name, id, name, id_A, name_a, name_b), and your data is saved. Hooray!

return $members;}<br><br> DON'T FORGET TO DECLARE THE ARRAY. If you try to cycle through members after the function has been called and you don't declare the array first you will get a horribly (HORRIBLY!) ugly error in your page. Also, if you try to add the object into the members array inside the while condition instead of in the while loop, you will generate one extra empty space in the array due to the last iteration/check.