Tanja

I am using this login-script, but i am not able to translate the errormessages. f.e. function register($subuser, $subpass, $subconf_pass, $subemail, $subconf_email, $subfirstname, $sublastname, $subkennel, $subdog, $subidentify){ global $database, $form, $mailer; //The database, form and mailer object $token = $this->generateRandStr(16); $config = $database->getConfigs(); /* Username error checking */ $field = "user"; //Use field name for username if(!$subuser || strlen($subuser = trim($subuser)) == 0){ $form->setError($field, "*Username not entered"); } i have languages files, fetched via url. In this file there are the translations. I tried $form->setError($field, $username_already_used); //nothing is shown $form->setError($field, "."$username_already_used"."); //Parse error: syntax error, unexpected '$username_already_used' (T_VARIABLE) in /www/htdocs/w00fa8ed/include/session.php on line 299 $form->setError($field, "*".$username_already_used."*");//only shown ** $form->setError($field, $$username_already_used);//nothing is shown $form->setError($field, "* Username not entered x {$username_already_used} xx");//* Username not entered x xx any ideas?

as soon as I register a dog, it also has a breeder. Therefore without static values. Currently I'm trying a solution with php and if (after mysql). Maybe it's not really possible to distinguish between two dates - and/or no dates - in a reasonable way

Both - kennel A had a litter and kennel B the bitch query-part for the last litter in connection with the breeder_id and the query-part for the bitch via the owner_id I use data which are in database at the time the page is called. It is not important who was owner before. The breeder is always the same for a dog, if a dog is sold only the owner is changed.

Table dog contains (among other things) id, birthday, sex, owner_id and breeder_id. Table owner contains (among other things) id, kennelname, kennel_note, country, country_short. Kennel_note is for "special" people which don´t breed correct way. If filled, no display 😉. Maybe the name of table owner is inconvenient - it is breeder and/or owner. An owner could be also a breeder, then there is a kennelname. If there is no kennelname - it is not a breeder. owner_id and breeder_id are different fields (i could be the breeder and you owner), so they are not interchangeable. Every dog has a breeder, but not every dog has an owner. It is daily work to complete, but most of them are complete. Maybe some breeder buy a bitch at age 3 - query must intercept that. Users are able to edit the owner of dogs - so if i hold one i enter me as owner. table dog: +-------+---------------+---------------+-------+---------------+---------------+ |id | dogname | birthday | sex | owner_id | breeder_id | +-------+---------------+---------------+-------+---------------+---------------+ | 1 | dog1 green |2020-08-02 |male | | 1 | | 2 | dog2 green |2020-08-02 |female | 1 | 1 | | 3 | dog3 green |2000-01-01 |female | 1 | 3 | | 4 | dog4 red |2007-11-19 |male | | 2 | | 5 | dog5 green |2020-08-02 |female | 5 | 1 | | 6 | dog6 yellow |2019-06-29 |male | 4 | 4 | | 7 | dog7 yellow |2019-06-29 |female | 7 | 4 | +-------+---------------+---------------+-------+---------------+---------------+ table owner: +-------+---------------+---------------+ | id | name | kennelname | +-------+---------------+---------------+ | 1 | green horn | green | | 2 | red lady | red | | 3 | blue boy | blue | | 4 | yellow girl | yellow | | 5 | silver woman | silver | | 6 | black man | black | | 7 | white angel | | +-------+---------------+---------------+

kennelname comes from table owner, first / last litter is MIN / MAX date in dog (and here then via breeder_id), oldest / youngest bitch also from dog via owner_id. F.E. i am breeder green (id 1) and breed last litter on 2020-08-02 (and hold a bitch from this litter in my kennel), so breeder_id and owner_id in dog is also 1 Oldest and youngest bitch and first/last litter must be build from dog data (date of birth). I try to get first only the active and in another query (other site) the non active ones...

I try to sort which kennels are active (have a litter last 8 years AND / OR a bitch with max age 8 years) and other hand which are not active. It could be that a kennel never had a litter but an active bitch. +---------------+---------------+---------------+---------------+---------------+ |kennelname |first litter |last litter |oldest bitch |youngest bitch | +---------------+---------------+---------------+---------------+---------------+ |green |2006-01-15 |2020-08-02 |2002-01-06 |2020-08-02 | |red |2007-11-19 |2007-11-19 |2005-01-29 |2011-05-14 | |blue |1997-11-26 |2011-12-18 |1994-10-22 |2006-12-19 | |yellow |2014-03-22 |2019-06-29 |2010-12-02 |2013-10-07 | |silver | | |2016-11-08 |2016-11-08 | |black | | |2002-02-23 |2009-05-28 | +---------------+---------------+---------------+---------------+---------------+ green ist active, there is a litter and a bitch under 8 red and blu are inactive, no litter and no bitch under 8 yellow is active - litter under 8 but no bitch silver is active - no litter (yet) but bitch under 8 black is inactive - no litter and no bitch I tried with different JOIN versions, but never get right results. My last attempt (UNION) also does not deliver the desired values, i get totaly wrong dates for some kennels. For result first ones must be females, for second ones not (because in a litter could born only males) One table is owner (id and kennelname), the other is dog, with breeder_id and owner_id (breeder and owner could be same ones) Any ideas? SELECT owner_bitch.id, owner_bitch.kennelname, owner_bitch.country, owner_bitch.country_short, owner_bitch.kennel_note, bitch.id AS dogid, bitch.date_of_birth, bitch.gender, bitch.owner_id, max(bitch.date_of_birth) as youngest, max(bitch.date_of_birth) as youngestbitch FROM owner AS owner_bitch LEFT JOIN dog AS bitch ON owner_bitch.id = bitch.owner_id WHERE bitch.gender='female' AND owner_bitch.kennelname !='' AND owner_bitch.kennel_note ='' AND owner_bitch.country_short ='de' UNION SELECT breeder_litter.id, breeder_litter.kennelname, breeder_litter.country, breeder_litter.country_short, breeder_litter.kennel_note, litter.id AS litter_id, litter.date_of_birth AS litter_date_of_birth, litter.gender, litter.breeder_id, max(litter.date_of_birth) as youngest, max(litter.date_of_birth) as youngestlitter FROM owner AS breeder_litter LEFT JOIN dog AS litter ON breeder_litter.id = litter.breeder_id WHERE breeder_litter.kennelname !='' AND breeder_litter.kennel_note ='' AND breeder_litter.country_short ='de' GROUP BY kennelname HAVING youngest <= DATE_SUB(CURDATE(), INTERVAL 8 YEAR)

Seems, that (my) MySQL isn´t already right for this .... Barands version will crash if dog has many generations of descedants

i will give it a try after work ....

Oh, without special dog the result is right! Why? Barand - you are my hero!

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>";

Maybe it could be a compromise to limit generation and have full information.... Yes MySQL is here the looser - so i tried with arrays ....

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 ....

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

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

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