2 steps relations? about php-crud-api HOT 19 CLOSED

Is that really the result? That is worrying as it is not even valid JSON. Can you confirm this?

How did you get this result? Please provide as much details as possible.

from php-crud-api.

Yep I have the same issue @mevdschee. I have a db as this model:

If I want to read one row of "RezeptMischungen", I have this structure by your api:
{ "RezepteMischungen": [ { "ID": "1", "Rezept_ID": "1", "Aroma_ID": "1" } ], "rezeptemischungen": [ { "ID": "1", "Rezept_ID": "1", "Rezepte": [ { "ID": "1", "Name": "Erdbeer", "Datum": "2016-02-09 12:00:42", "User_ID": "1", "Base_ID": "1", "Bewertung_ID": "2", "Reifezeit": "7" } ], "Aroma_ID": "1", "Aromen": [ { "ID": "1", "Name": "Erdbeer", "Datum": "2016-02-09 12:01:29", "Hersteller_ID": "1", "Bewertung_ID": "1" } ] } ], "Basen": [ { "ID": "1", "Name": "VPG", "Datum": "2016-02-09 11:43:19", "Hersteller_ID": "1", "Bewertung_ID": "2" }, { "ID": "2", "Name": "Traditional", "Datum": "2016-02-09 12:14:20", "Hersteller_ID": "1", "Bewertung_ID": "1" } ] }

Formated image of the return of your api:

My call of your api:
api.php/RezepteMischungen,Aromen,Rezepte,Basen?filter=rezept_id,eq1&callback=callback

Which is generally ok, but the table "Rezepte" has a relation to "Basen" which isn't applicated.
As you can see, there is a third level of a relation between RezepteMischung -> Rezepte -> Basen
I hope you know what I mean. If not, feel free to ask me anything.

Attached I send you my sql dump.
d020a380.txt

I expect a result like this:

Hope thats detailed enough inout for your debugging.
BTW: I'm using php_crud_api_transform.js

from php-crud-api.

Thank you very much! I will look into this and report back after reproducing your findings.

from php-crud-api.

Does this help?

{
    "RezepteMischungen": [
        {
            "ID": "1",
            "Rezept_ID": "1",
            "Rezepte": [
                {
                    "ID": "1",
                    "Name": "Erdbeer",
                    "Datum": "2016-02-09 12:00:42",
                    "User_ID": "1",
                    "Base_ID": "1",
                    "Basen": [
                        {
                            "ID": "1",
                            "Name": "VPG",
                            "Datum": "2016-02-09 11:43:19",
                            "Hersteller_ID": "1",
                            "Bewertung_ID": "2"
                        }
                    ],
                    "Bewertung_ID": "2",
                    "Reifezeit": "7"
                }
            ],
            "Aroma_ID": "1",
            "Aromen": [
                {
                    "ID": "1",
                    "Name": "Erdbeer",
                    "Datum": "2016-02-09 12:01:29",
                    "Hersteller_ID": "1",
                    "Bewertung_ID": "1"
                }
            ]
        }
    ]
}

And why do you get the table name both in mixed and in lower case? I can not reproduce that bug

from php-crud-api.

I really don't know why. Could be a collation problem I guess. I will cross check that and your solution tomorrow. Thanks for you effort! :)

from php-crud-api.

Okay, your contribution is much appreciated. You are probably running MySQL on Windows, right?

from php-crud-api.

Oh no. Im running mysql within XAMPP under OSX El Capitan. I can check that with my external Apache with MySQL. But I created that DB with Navicat not PHPmyadmin.

from php-crud-api.

@Disane87 Thank you for your cooperation on this second bug.

Please post the returned JSON on your machine and please also post the output of:

SELECT 1 as `N`,`TABLE_NAME` FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_NAME` LIKE 'RezepteMischungen' UNION
SELECT 2,`TABLE_NAME` FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_NAME` = 'RezepteMischungen' UNION
SELECT 3,`TABLE_NAME` FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_NAME` COLLATE 'utf8_bin' LIKE 'RezepteMischungen'  UNION
SELECT 4,`TABLE_NAME` FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_NAME` LIKE 'rezeptemischungen';

on my machine it is:

N,TABLE_NAME
1,RezepteMischungen
2,RezepteMischungen
3,RezepteMischungen
4,RezepteMischungen

because:

Suppose that a query searches the SCHEMATA.SCHEMA_NAME column for the test database. On Linux, file systems are case sensitive, so comparisons of SCHEMATA.SCHEMA_NAME with 'test' match, but comparisons with 'TEST' do not:
...
On Windows or OS X where file systems are not case sensitive, comparisons match both 'test' and 'TEST':

see: https://dev.mysql.com/doc/refman/5.1/en/charset-collation-information-schema.html

Your help is very valuable!

from php-crud-api.

Sorry my example wasn't the exact result. @Disane87 explains the same issue perfectly. I am using MySQL with XAMPP on Windows.

from php-crud-api.

@henninghall You can update your "api.php" with the latest version as I applied a fix. Please let me know whether or not it works for you (it should).

from php-crud-api.

@mevdschee It works brilliantly! Thanks a lot for keeping this api so great!

from php-crud-api.

@Disane87 Can you still help me to identify the other bug (with the casing) by sending the returned JSON and executing the SQL queries on your machine? I have no OSX to test on here.

from php-crud-api.

@mevdschee
My result of your script:

N   TABLE_NAME
1   RezepteMischungen
2   rezeptemischungen
3   RezepteMischungen
4   RezepteMischungen

Returned JSON with your updated api.php:

{
    "RezepteMischungen": [
        {
            "ID": "1",
            "Rezept_ID": "1",
            "Rezepte": [
                {
                    "ID": "1",
                    "Name": "Erdbeer",
                    "Datum": "2016-02-09 12:00:42",
                    "User_ID": "1",
                    "Base_ID": "1",
                    "Bewertung_ID": "2",
                    "Reifezeit": "7"
                }
            ],
            "Aroma_ID": "1"
        }
    ],
    "rezepte": [
        {
            "ID": "1",
            "Name": "Erdbeer",
            "Datum": "2016-02-09 12:00:42",
            "User_ID": "1",
            "Base_ID": "1",
            "Basen": [
                {
                    "ID": "1",
                    "Name": "VPG",
                    "Datum": "2016-02-09 11:43:19",
                    "Hersteller_ID": "1",
                    "Bewertung_ID": "2"
                }
            ],
            "Bewertung_ID": "2",
            "Reifezeit": "7"
        }
    ],
    "rezeptemischungen": [
        {
            "ID": "1",
            "Rezept_ID": "1",
            "Aroma_ID": "1",
            "Aromen": [
                {
                    "ID": "1",
                    "Name": "Erdbeer",
                    "Datum": "2016-02-09 12:01:29",
                    "Hersteller_ID": "1",
                    "Bewertung_ID": "1"
                }
            ]
        }
    ]
} 

from php-crud-api.

Ah.. thank you very much! Very unexpected to me!

SELECT `TABLE_NAME` FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_NAME` COLLATE 'utf8_bin' = 'RezepteMischungen';

Can you post the output of this SQL query as well?

from php-crud-api.

Sure :)

TABLE_NAME
RezepteMischungen

from php-crud-api.

Thank you, I will prepare a new version tonight that should fix the bug.

from php-crud-api.

Thank you! :)
I will use your API for a bigger project, so I guess we'll stay in contact. But very great work! Really appreciate it!

from php-crud-api.

The case sensitivity issue should also be fixed now. See: 7bd9919

from php-crud-api.

Thanks, works now!

from php-crud-api.