Jump to content

Tanja

Members
  • Content Count

    53
  • Joined

  • Last visited

Community Reputation

1 Neutral

About Tanja

  • Rank
    Regular Member
  • Birthday 12/22/1968

Profile Information

  • Gender
    Female
  • Location
    Germany
  • Age
    48
  1. without i get Warning: Creating default object from empty value ...
  2. i have an old script, written in mysql. The most other ones are up to date in pdo. MySqli - the paramenter binding is not easy and the list() parameter will get in PHP7 another direction ... The old script (it is from Barand), creating a pedigree table with pictures and healthresults with variabel generations function printTree($dogid, $name, $healthstatus, $N, $max) { //database-connection if ($name == '') $name = ' '; if ($healthstatus == '') $healthstatus = ' '; $rspan = pow(2, $max-$N); if(file_exists("photo/".$dogid."/".$dogid.".jpg")) $img = "/photo/".$dogid."/".$dogid.".jpg"; else $img = "/main/img/platzhalter.png"; $name = "<a href=".$dogid.">".$name."</a><br><img src='$img' height='50' alt='' /><br>".$healthstatus; if ($rspan > 1) echo "\t<td rowspan='$rspan' >".$name."</td>\n"; else echo "\t<td>$name</td>\n"; if ($N == $max) echo "</tr>\n<tr>\n"; if ($N < $max) { $sql = " SELECT a.father_id, a.mother_id, CONCAT('<span class=\'warning\'>',s.dog_warning,'</span><br>',s.dogname), CONCAT('<span class=\'warning\'>',d.dog_warning,'</span><br>',d.dogname), CONCAT(s.hd,'<br>DM:',s.dm,'<br>DW:',s.dw), CONCAT(d.hd,'<br>DM:',d.dm,'<br>DW:',d.dw) FROM dog a INNER JOIN dog s ON a.father_id = s.id INNER JOIN dog d ON a.mother_id = d.id WHERE a.id = '$dogid' "; $res = $dbi->query($sql); list($s, $d, $sn, $dn, $shealth, $dhealth) = mysqli_fetch_row($res); printTree($s, $sn, $shealth, $N+1, $max); printTree($d, $dn, $dhealth, $N+1, $max); } } Very bad is dogid here - it comes from url ... I´m not able to switch the line with list() to pdo ... $sql = new stdClass(); $result = $sql->query=" SELECT ... WHERE a.id = :dogid "; $stmt = $conn->prepare($result); $stmt->bindParam(':dogid', $givenid, PDO::PARAM_INT); $stmt->execute(); .... while (list($s, $d, $sn, $dn, $shealth, $dhealth) =$stmt->fetch()); { printTree($s, $sn, $shealth, $N+1, $max); printTree($d, $dn, $dhealth, $N+1, $max); } i tried with serval fetch methods, with and without while... Any ideas?
  3. Barand is right ;-) for my problem - i solved based on Barands answer - with handling the given array from first part $gen_1_to_2 = array_merge($generationshere[1], $generationshere[2]); $unique_gen_2 =count(array_unique($gen_1_to_2)); and so on for following generations. Thanks for help
  4. Part one works perfect, the result-array is what i want. The second part works also correct -but not in this case. In the fourth generation f.e. here will only count the fourth gen - but i must count from 1-4 (a dog is in gen 3 AND in gen 4 -in every gen only once but in fourth generation twice). So i think i must work with merging generation an then array unique.
  5. I have two arrays, one for sire and one for dam. In each array there are the dog id as key and the generation . sires Array ( [1] => Array ( [0] => 6 [1] => 7 ) [3] => Array ( [0] => 6 [1] => 7 ) [6] => Array ( [0] => 3 [1] => 4 ) [18] => Array ( [0] => 5 [1] => 6 ) [23] => Array ( [0] => 2 [1] => 3 ) [43] => Array ( [0] => 3 ) [59] => Array ( [0] => 2 ) [73] => Array ( [0] => 3 [1] => 4 ) [104] => Array ( [0] => 1 ) [124] => Array ( [0] => 4 [1] => 5 ) [357] => Array ( [0] => 5 [1] => 6 ) [359] => Array ( [0] => 4 [1] => 5 ) ) dams Array ( [1] => Array ( [0] => 6 [1] => 8 [2] => 6 [3] => 6 ) [2] => Array ( [0] => 8 ) [3] => Array ( [0] => 6 [1] => 6 [2] => 6 ) [4] => Array ( [0] => 5 ) [5] => Array ( [0] => 5 ) [15] => Array ( [0] => 7 ) [18] => Array ( [0] => 5 [1] => 5 [2] => 5 ) [21] => Array ( [0] => 4 ) [22] => Array ( [0] => 6 ) [28] => Array ( [0] => 6 ) [44] => Array ( [0] => 3 ) [45] => Array ( [0] => 4 ) [50] => Array ( [0] => 4 ) [66] => Array ( [0] => 7 ) [73] => Array ( [0] => 3 ) [74] => Array ( [0] => 3 ) [115] => Array ( [0] => 3 ) [116] => Array ( [0] => 2 ) [124] => Array ( [0] => 4 ) [149] => Array ( [0] => 4 ) [151] => Array ( [0] => 5 ) [265] => Array ( [0] => 1 ) [325] => Array ( [0] => 2 ) [328] => Array ( [0] => 5 ) [341] => Array ( [0] => 5 ) [342] => Array ( [0] => 5 ) [357] => Array ( [0] => 5 ) [359] => Array ( [0] => 4 [1] => 4 ) [367] => Array ( [0] => 4 ) ) for example, dog 1 is on father-side in generation 6 and 7; on motherside three times in generation 6 and once in 8. I want to calculate the ancestor loss for each generation, so i need to get which dogs are in generation 1, in generation 2 and so on ... I have already how many dogs there in each generation, but i need how many different there are. In this example there are 58 dogs but only 33 different. Any ideas?
  6. Seems, that (my) MySQL isn´t already right for this .... Barands version will crash if dog has many generations of descedants
  7. Oh, without special dog the result is right! Why? Barand - you are my hero!
  8. I edit the query to "my" pdo with special dog $parent= 13425; $sql = "SELECT id , dogname , mother_id as dam , father_id as sire , gender FROM dog WHERE mother_id= :parent OR father_id = :parent ORDER BY id"; $dogs = []; $dogs[0] = ['name' => 'N/A', 'gender' => 'N/A', 'pups' => [] ]; $stmt = $conn->prepare($sql); $stmt->bindParam(':parent', $parent, PDO::PARAM_INT); $stmt->execute(); $results = $stmt->fetchAll(); Rest from Barands code i copied - it give me on calling $dog_id = 13425; descendants($dog_id, $dogs, 3); only the direct childrens, but also in array the children for the used males (in this case 13425 is a female and was mated with two different males) - this is not really neccessary here.... If i change the level it will only change from descendants($dog_id, $dogs, 3); ----- ----- ----- ----- Chunami from Bandit's World (female) to descendants($dog_id, $dogs, 1); ----- ----- Chunami from Bandit's World (female) and two undefined index on name and gender in echo "$indent{$dogs[$id]['name']} ({$dogs[$id]['gender']})<br>";
  9. Maybe it could be a compromise to limit generation and have full information.... Yes MySQL is here the looser - so i tried with arrays ....
  10. i want to find all offsprings for a given id. Information in database is stored with id, father_id, mother_id. $parentID will come over a session variable, for gender too. I call database with function findDescendants($parentId, &$descendants) {include(INCLUDE_DIR.'xxx'); //database connect $res = " SELECT CONCAT(dog.id, '#', dog.dogname, '#',dog.father_id, '#',dog.mother_id ) AS id FROM dog WHERE mother_id= :parentId OR father_id = :parentId ORDER BY dog.id DESC "; $stmt = $conn->prepare($res); $stmt->bindParam(':parentId', $parentId, PDO::PARAM_INT); $stmt->execute(); while($row = $stmt->fetch(PDO::FETCH_ASSOC)) { $descendants[] = $row['id']; findDescendants($row['id'], $descendants); } } now i explode this array to get foreach [id] => 14333 [name] => Chunami from Bandit's World [father] => 3305 [mother] => 13425 [parents] => 3305,13425 In this way i get all descedants - but without generations. This is what i want to get: -Child A -Child B -Child C --GrandChild C1 ---GrandgrandChild C1 a Child D Maybe the first query isn´t correct for this .... Actual i think to do it with arrays ... Find first generation: filter all which have start id as mother / father, add this id as parent and generation $firstgeneration = array_filter($newdescedants,function ($ar){ return ($ar['mother'] == 13425); }); foreach ($firstgeneration as &$val) $val['parent'] = '13425'; foreach ($firstgeneration as &$val) $val['generation'] = '1'; Now i have an array with all childrens like this [id] => 14333 [name] => Chunami from Bandit's World [father] => 3305 [mother] => 13425 [parents] => 3305,13425 [parent] => 13425 [generation] => 1 For next generation my head bangs ....
  11. Tanja

    slow complex query

    The table owner is for persons ... f.e. one person is stored with name, adress - the other also with kennelname so in owner will be id 1 Name Tanja Kennel doggie country_short de id 2 Name Psycho Kennel (empty) country_short ca and in dog it will be owner_id 2 (you) and breeder_id 1 (me) in dog i need birthday (to get last litter or age of bitch) in puppy i call actual matings
  12. Tanja

    slow complex query

    Oh sorry - i don´t saw the messages until now ... Dog has 27000k rows....
  13. Tanja

    slow complex query

    There are three tables: dog with birthday, gender, breeder id, owner id, breeding licence owner with id, kennelname, kennel_note, and country (short) -> every owner can be also a breeder, if I breed a dog and you buy it there are two rows in owner puppy with planned_birthday, breeder id and active (here can breeders insert a planned litter - also these ones which will have their first litter), this table is also used for showing planned litters in breed I want to show all owners which have a female dog under 8 years with breeding licence (dog.owner_id - owner.id) all owners which have a kennelname without note and have a litter last 8 years (dog.breeder_id - owner.id) -> there could be double names (i have a bitch with breeding licence under 8 years and my last litter was 2015) and which of them planned a litter at all? This query will show all, but without breeders with litters that hasn´t have a own bitch (i can rent your bitch if you don´t want to have a own kennel) SELECT owner.id, owner.kennelname, owner.country, owner.country_short, owner.kennel_note, dog.id AS dogid, dog.breeder_id, dog.date_of_birth, dog.gender, dog.owner_id, puppy.breeder_id, MAX(puppy.sollgeboren) AS birthday, DATE_ADD(MAX(puppy.sollgeboren), INTERVAL 84 DAY) AS database_dateadd, DATE_SUB(MAX(puppy.sollgeboren), INTERVAL 60 DAY) AS database_datesub, puppy.active AS showing FROM owner INNER JOIN dog ON dog.owner_id = owner.id LEFT JOIN puppy ON (dog.owner_id = puppy.breeder_id AND puppy.active='1') WHERE dog.gender='female' AND dog.date_of_birth >= DATE_SUB(CURDATE(), INTERVAL 8 YEAR) AND owner.kennelname !='' AND owner.kennel_note ='' AND dog.owner_id = owner.id GROUP BY owner.country_short, kennelname ORDER BY country_short DESC Testing with your Join this query runs much faster (1.53 seconds), but a.) not fast enough and b.) sending all breeders (also these one which never has a litter and an old bitch) SELECT o.id, o.kennelname, o.country, o.country_short, o.kennel_note, d.id AS dogid, d.breeder_id, d.date_of_birth, d.gender, d.owner_id, MAX(YEAR(d.date_of_birth)) AS lastlitter, p.breeder_id, MAX(p.sollgeboren) AS birthday, DATE_ADD(MAX(p.sollgeboren), INTERVAL 84 DAY) AS database_dateadd, DATE_SUB(MAX(p.sollgeboren), INTERVAL 60 DAY) AS database_datesub, p.active AS showing FROM owner o LEFT JOIN dog d ON (d.owner_id = o.id AND d.gender='female' AND d.breeding_approval_since !='0000' AND d.date_of_birth >= DATE_SUB(CURDATE(), INTERVAL 8 YEAR)) OR (d.breeder_id = o.id AND d.date_of_birth >= DATE_SUB(CURDATE(), INTERVAL 8 YEAR)) LEFT JOIN puppy p ON (o.id = p.breeder_id AND p.active='1' ) WHERE o.kennelname !='' AND o.kennel_note ='' AND o.country_short = 'de' GROUP BY o.country_short, o.kennelname ORDER BY o. country_short DESC
  14. Tanja

    slow complex query

    Active Breeders are all owners which have a.) a bitch with breedeing licence and under 8 years AND b.) which have a litter last 8 years (f.e. my own bitch is 10, my last litter 2014). So that calling only owner will not show my kennel, but the breeder join. The puppy table ist joined for actual puppies (if "sollgeboren" (=planned birthday) there is shown an icon x days before / after pl. birth to see directly in which kennel there are puppies. Sometimes breeders add a planned litter and bitch is empty -> active will go to zero.... Country_short is only for "de" for testing speed - later it must be for all countrys. The different Join condition i must test after work (starts at 4 am)....
×
×
  • 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.