MarioRossi

Try... select username, (sum(rate) / count(linkid)) as point from link where rate<>-1 group by username order by point desc limit 5

I suppose it doesn't necessarily have to be an object I'm just following an article here... http://www.phpriot.com/articles/nested-trees-2 ... The whole class is displayed here... http://www.phpriot.com/articles/nested-trees-2/7 I'm trying to learn as well not just plug something that works. It's the rebuild() method and the resulting recursion aspect of the class I'm having difficulty getting my head around. I understand the rest of the theory and methods OK. Like you say I'm not even sure an object is necessary or indeed preferred could the same thing be achieved using an array? To put it in context I'm calling rebuild() from my applications controller which is then grabbing all of the category data from the DB. Now I believe what the troublesome part of the code is doing is reformatting/nesting the data so each node also contains references to it's children. The resulting data is then used recursively to update the database.

hmmm "array" (Would this need to return an object instead?) Thing is I won't be able to alter $this->DB->fetch() as it's part of an application's framework that I am writing an add-on for.

SELECT * FROM table WHERE DATE_SUB(CURDATE(), INTERVAL 5 Minutes) <= date_col; If you're using MySQL's date field types. This should help... http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

I'm having issues with the following function in PHP 5... function getTreeWithChildren() { $category_id = $this->fields['id']; $parent_id = $this->fields['parent']; $this->DB->build( array( 'select' => join(',', $this->getFields()), 'from' => $this->table, 'order' => $this->fields['sort'] ) ); $this->DB->execute(); // create a pseudo root level object $root = new stdClass; $root->$category_id = 0; $root->children = array(); $arr = array($root); // populate array and create empty child array while ($row = $this->DB->fetch()) { $arr[$row->$category_id] = $row; $arr[$row->$category_id]->children = array(); } // build child data foreach ($arr as $id => $row) { if (isset($row->$parent_id)) $arr[$row->$parent_id]->children[$id] = $id; } return $arr; } I'm getting the error which is... $arr[$row->$category_id]->children = array(); I've tried typecasting "$this->DB->fetch()" to an object with... while ($row = (object) $this->DB->fetch()) { but get a maximum execution time exceeded error instead. The code was originally written for PHP 4 so I suspect it might be a way the objects are handled and the use of stdClass()? I'd be extremely grateful if somebody could give me pointers to make this PHP5 compatible (5.2.9)? Thanks in advance.

Great stuff. I think i'll give that a go, I see no reason why it shouldn't work so thanks very much for your help - great solution!

OK I think I've got a working solution, though I had hoped for something a little more elegant. SELECT DISTINCT pty_att_rel.id_pty FROM pty_att_rel WHERE id_pty IN (SELECT DISTINCT pty_att_rel.id_pty FROM pty_att_rel WHERE id_att = 7) AND id_pty IN (SELECT DISTINCT pty_att_rel.id_pty FROM pty_att_rel WHERE id_att = 2) AND id_pty IN (SELECT DISTINCT pty_att_rel.id_pty FROM pty_att_rel WHERE id_att = 3) this means for every id_att I want to check against I will have to add another AND to the WHERE condition, can you see a more elegant solution or should I use this? Am I likely to hit any performance issues? I should say I will probably have about 10-15 of these conditions at the most and will be doing a few left joins to the returned rows to retrieve some extra info.

Thanks for your help, I have researched UNION but from what I can tell I need to use INTERSECT instead. A UNION would still return the results of both seperate queries. From what I can see INTERESECT is not supported with MySQL so I think I need an alternative method of doing this. Once I get this resolved I will need to do more than check for 2 values I may have to check against 2,7,9,13,25 etc so I need a solution that will allow this without too much of a performance hit. Thanks again.

Say I have a relational table as in the attached image. My current query is.... SELECT DISTINCT pty_att_rel.id_pty FROM pty_att_rel WHERE id_att IN (2,7) which returns 2 rows - id_pty equaling 1 and 4. What I want to achieve is to return only those rows where id_att equals 2 AND 7. If I alter the query to "WHERE id_att = 2 AND id_att = 7" obviously this cannot work. Any suggestions? [attachment deleted by admin]

Hi there, I'm looking for a best practice methodology of how to approach this kind of search technique. If you are unfamiliar with what I am talking about then please take a look at... http://shop.ebay.com/Books_Books__W0QQ_trksidZp3910Q2ec3Q2em24Q2el1104 Basically the user clicks on one of the refinement options which updates the relevant search results. I am quite confident I can get most of this done by parsing the query string and building the SQL query dynamically based on a set of key/value pairs in the WHERE condition. The initial query string is set on the links for each refinement. correct so far? My confusion comes when i want to add the number of products in parenthesis for each refinement .... Used (230,000) etc. Can anybody guide me in the correct direction, please?

You do not have to use $_GET you can simply use.... <input type="checkbox" name="checkbox2" value="1" <?php if ($_POST['checkbox2'] == "1") echo "checked=\"checked\"";?> /> Checkbox 2

You are right the joins were getting a bit out of hand so decided to get the books out and perform my first ever sub select :o working a treat now. Had to throw DATE_ADD() and DATE_SUB() into the mix as well to account for the fact that an arrival date could be the same as a departure date. Thanks so much for your help there's a site donation winging its way over now.

This is now getting so close. My query runs fine in all circumstances without any MySQL errors but it only works for the first booking in the bookings table. So say I have the following two rows in the bookings table only the first will be validated against. ID      Start              End 1      2006-09-11      2006-09-18 2      2006-10-18      2006-10-27 Could you please look through this query to see where i'm going wrong? It's quite a biggy, I've left out the Group BY as I know the problem is not in there and I've also split my final join for clarity as I believe this is where I am going wrong. I realise it's quite a bit to look through so I would be extremely grateful Thanks Again SELECT Villa_Property.idVilla_Property, min(Villa_Images.Image)  AS DisplayImage, Villa_Property.Title, Villa_Property.idOwner, Villa_Property.Sleeps, Villa_Property.Pool, Villa_Property.Parking, Villa_Property.TV, Villa_Property.PrivateGarden, Villa_Property.Internet, Villa_Property.Description, Villa_Property.Email, Villa_Property.DateAdded, Villa_Property.idPropertyType, Villa_Property.Cooker, Villa_Property.Fridge, Villa_Property.Freezer, Villa_Property.WashingMachine, Villa_Property.idLocation, Villa_Property.Safe, Villa_Property.Bath, Villa_Property.Shower, Villa_Property.DrinkingWater, Villa_Property.BabyChanging, Villa_Property.Phone, Villa_PropertyTypes.Name, Villa_Locations.idParent, Villa_Locations.idVilla_Locations, Villa_Locations.LocationName FROM ((((Villa_Property LEFT JOIN Villa_PropertyTypes ON Villa_PropertyTypes.idVilla_PropertyTypes=Villa_Property.idPropertyType) LEFT JOIN Villa_Locations ON Villa_Locations.idVilla_Locations=Villa_Property.idLocation) LEFT JOIN Villa_Images ON Villa_Images.idVilla_Property=Villa_Property.idVilla_Property)"; $query_PropertySearchQuery.= "INNER JOIN Villa_Bookings ON ('$startdate' NOT BETWEEN Villa_Bookings.dateStart AND Villa_Bookings.dateEnd) AND ('$enddate' NOT BETWEEN Villa_Bookings.dateStart AND Villa_Bookings.dateEnd) AND NOT (('$startdate' < Villa_Bookings.dateStart) AND ('$enddate' >= Villa_Bookings.dateEnd)) AND Villa_Property.idVilla_Property = Villa_Bookings.idVilla_Bookings"; $query_PropertySearchQuery.= ") WHERE Villa_Property.Active=1";

Just out of interest is it possible to do the following? SELECT * FROM (PropertyTable LEFT JOIN BookingsTable ON b.arrive NOT BETWEEN '$d1' AND '$d2' AND b.depart NOT BETWEEN '$d1' AND '$d2') ?? 

Yeah thats how I got to my original plan - I was thinking backwards though, I was going to return all the rows then use php to check when I should really be doing the check in the MySQL query We can also disregard the 3rd situation there as the first two will perform that check already i.e. for the 3rd situation to be true either the 1st or 2nd must also be true. Thanks again I think I have a plan of attack again. I may need help with selecting distinct rows though at some point.