Jump to content

MarioRossi

Members
  • Posts

    25
  • Joined

  • Last visited

    Never

Everything posted by MarioRossi

  1. Try... select username, (sum(rate) / count(linkid)) as point from link where rate<>-1 group by username order by point desc limit 5
  2. 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.
  3. 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.
  4. 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
  5. 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.
  6. 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!
  7. 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.
  8. 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.
  9. 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]
  10. 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?
  11. 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
  12. 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.
  13. 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";
  14. 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') ?? 
  15. 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.
  16. It would appear so, Thank you very much I'm going to study that carefully. - Andy
  17. ah right ok now i get you why not do this..... get your starttime and endtime timestamps using mktime() like you are and then start a loop adding 86400 (seconds in a day) on each iteration until you get to your enddate? If you are working with dates a lot you may want to check out [url=http://pear.php.net/manual/en/package.datetime.calendar.php]http://pear.php.net/manual/en/package.datetime.calendar.php[/url]
  18. OK try this; SELECT COUNT(DISTINCT vote) AS votecount, vote  FROM UsersTable GROUP BY vote
  19. tried the strtotime function on it? If its all in the same format you should be OK, just add another db field with a more universal format and rewrite back to the DB [url=http://uk2.php.net/strtotime]http://uk2.php.net/strtotime[/url]
  20. Think this one might need a more verbose explanation. I am struggling to work out what it is you are trying to achieve. You are generating 2 timestamps based on a start date and end date. you are the subtracting the enddate from the startdate giving you a negative? I suspect this should be the other way round to give you number of seconds between the start and end date? correct? and then you lose me...
  21. If the relationship between your images and users table is 1 -> 1 then you can do an INNER JOIN on the tables and only the rows where your "scm_mem_id" values match in both tables will be returned. Don't forget that as you are dealing with two tables in the 1 query you will have to prefix your table names This should do it although I haven't checked it. SELECT * from (sc_member INNER JOIN imagestable ON sc_member.scm_mem_id=imagestable.scm_mem_id) WHERE sc_member.scm_mem_id != '1' ORDER BY sc_member.scm_lastlogin desc LIMIT 0,2 ....And barand beat me to it :) he seems like a knowlegeable chap maybe he can help me with my availability calendar problem ;)
  22. Your curly braces don't match up in the code. you are missing a } somewhere.
  23. Your problem lies not within the PHP but the html you are outputting. If you view the source of the page you will see what I mean. what you are outputting is this <img src=/images/yourimageurl.gif alt=Your alt text> The spaces therfore are not associated with the alt attribute. Try this echo "<p class='style2'><img src='/images/".$code['photo']."' alt='".$code['alternate']."'>".nl2br($code['content'])."</p>"; I have added single quotes to contain your values for SRC and ALT Hope that works for you.
  24. Why not put a users table in you db with 3 columns (id, Name, Votes)? You could then use a dropdown box to list the names and increment the votes column on each vote.
  25. Hi there, Please bear with me on this one as it is quite a hard problem to explain but I suspect with an easy solution (I just can't get my head around it) I've started coding an availability calendar app and have the basics up and running. I can add and display entries fine with start and end dates etc.. My problem is I want to prevent double bookings and also search by availability. At the moment I have 2 tables - 1 for property and 1 for bookings the structure of the bookings table is laid out as follows. [table] [tr] [td]idBookings [/td][td]Int[/td] [/tr] [tr] [td]idProperty [/td][td]Int[/td] [/tr] [tr] [td]dateStart [/td][td]Date[/td] [/tr] [tr] [td]dateEnd [/td][td]Date[/td] [/tr] [/table] I am now struggling to work out programatically how to check availability. I think I have come up with one solution but it is extremely long winded and I fear overly-complicated. What I was planning to do was as follows (in pseudo code in places to illustrate). 1. Get user to submit a form with the start and end date they would like. 2. On the form submitted page SELECT all the property LEFT JOIN the bookings table ON idProperty = propertyID this would give me all the bookings for all the properties (Could be a lot of rows returned) 3. Loop through this recordset performing the following checks on each row (each row being a booking).   3.1 $_POST['start'] !>= start AND $_POST['start'] !< end        <-- So here I'm checking that the requested start date is not during a booking   3.2 $_POST['end'] !>= start AND $_POST['end'] !< end        <-- As above but checking end date is not during a booking   3.3 $_POST['start'] !<= start AND $_POST['end'] !>= end    <-- Here I'm checking the new requested booking doesn't straddle an existing one If I'm right then if all three of those conditions are met then the property is available for the requested period. Can anybody suggest a better way to do things as I don't want to implement anything that is overly bloated when a neater solution may exist. That code run on a busy site I would imagine could be a bit of a nightmare as say there are 1000 properties all with 10 bookings that's 10,000 rows returned to PHP straight away and then I have to loop through and do my checks. The problem would get worse if i wanted to use it as a sitewide availability check as I would return duplicate property entries for each booking that didn't conflict I hope somebody can help me with this because I can't get my head around it. Thanks in advance - Andy
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.