How do I JOIN one-to-many tables using NotORM lib

[Tuan Anh]

i'm using NotOrm lib for working with data in the database. I have to follow the instructions herehttp://www.sitepoint.com/database-interaction-made-easy-with-notorm/

but did not solve my problem Here is my data structure:

Tables 
   -members 
           ---Indexes 
            PRIMARY id 

   -cards
          ---Indexes 
            PRIMARY id
            INDEX   member_id
          ---Foreign keys
            member_id   members(id) CASCADE CASCADE

I used sql command

ALTER TABLE  `cards` ADD FOREIGN KEY (  `member_id` ) REFERENCES  `xxx`.`members` (`id`)
     ON DELETE CASCADE ON UPDATE CASCADE ;

my code

$cards = $this->db->cards();    
    foreach($cards as $c){
        //echo "here";
        echo $c->members["member_id"];
    }

and response is

=> Message: Undefined index: members_id

Is my data structure correct for using NotORM ? i want to result from SQL as:

select members.f1, members.f2 from members join cards on members.id = cards.member_id where cards.email like '%%' or cards.phone like '%%'

Many thanks!

[Allysson David]

Think of the NotORM object like this:

Each $c is an object that represents a row in the `cards` table.

Once you call $c->members the result will be a object that represents the referenced row in the `members` table.

Further using $c->members['members_id'] will try to get the `members_id` field in the `members` table, which doesn't exists.

Your options are to call:

• $c->members['id']
  
• $c['members_id']
  

If you plan to work with values in the rows of the `members` table then it should be more clear using the first option. If you only need the value of the field then use the second option.

Good luck.

--
You received this message because you are subscribed to the Google Groups "NotORM" group.
To unsubscribe from this group and stop receiving emails from it, send an email to notorm+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Tuan Anh]

i've tried both way but everything is still the same :(

Type: ErrorException

Code: 8

Message: Undefined index: members_id

File: /var/www/html/source/xxx/libs/notormMaster/NotORM/Row.php

Line: 166

/** Get value of column
* @param string column name
* @return string
*/
function offsetGet($key) {
   $this->access($key);
   if (!array_key_exists($key, $this->row)) {
      $this->access($key, true);
   }
   return $this->row[$key];
}

i want to get  members.fieldX and members.fieldY,

select members.f1, members.f2 from members join cards on members.id = cards.member_id where cards.email like '%%' or cards.phone like '%%'

thanks for helps

[Allysson David]

Hey, first I'd like to remember that the default Structure rules are `referencedtable_id` for a referencing field.

Since your table is `members`, the referencing field in `cards` should be `members_id` instead of the `member_id` you used in your example query. (The structure can be freely changed by you, btw.)

Now, considering that was a typo, this is how you'd have that query using NotORM:

• $db->members()->select('members.f1, members.f2')->where('cards:email LIKE ? OR cards:phone LIKE ?', '%%', '%%');
  
• $db->members()->select('members.f1, members.f2')->where('cards:email LIKE ? OR cards:phone LIKE ?', array('%%', '%%'));
  

The second option is just in case you want to have the values that will be bound to the '?' as an array.

Both will have the same result. Just in case, if you try to echo those commands the '?' won't be bound yet, as that is done by the PDO later.

[Tuan Anh]

many thanks, i have changed member_id to members_id  and everything is ok

pls help me with

$result = $this->db->members()->select('members.freepp_id')
    ->where('cards:is_property', 1)
    ->where('cards:status', 1)
    ->where('cards:email IN ? OR cards:mobile_phone IN ? OR cards:work_phone IN ? OR cards:home_phone IN ? OR cards:telephone IN ?', $email, $mobile_phone, $work_phone, $home_phone, $telephone);
echo $result;
$c = count($result);
echo $c;
var_dump($result->fetch());
if($data = $result->fetch()){
    echo "HERE";
    return $data['members.freepp_id'];
}

• $result   =>> SELECT members.freepp_id FROM members LEFT JOIN cards ON members.id = cards.members_id WHERE (cards.is_property = 1) AND (cards.status = 1) AND (cards.email IN ? OR cards.mobile_phone IN ? OR cards.work_phone IN ? OR cards.home_phone IN ? OR cards.telephone IN ?) 
  
• $c =>> 0
• var_dump =>> false

when i test with SQL command:

SELECT members.address FROM members

LEFT JOIN cards 

ON members.id = cards.members_id 

WHERE (cards.is_property = 1) 

AND (cards.status = 1) 

AND (cards.email IN ('do...@gmail.com', 'k...@gmail.com')

OR cards.mobile_phone IN ('0934591929', '86676')

OR cards.work_phone IN ('')

OR cards.home_phone IN ('')

OR cards.telephone IN (''))

the result is ok as i want

[Allysson David]

Give me the dump for the vars $email, $mobile_phone, $work_phone, $home_phone, $telephone.

[Tuan Anh]

you really enthusiastic,

i reveiced request from client,

the client can request many emails and phone types at the same time

• email for just one type (I organized on one field Email and separated by comma==> email   | a...@gmail.com, b...@gmail.com, c...@gmail.com |
  
• phone( work, mobile, home => work | 0968594, 8980898, 67677 | , home and mobile as the same

i have build string : 

• $email : ('a...@gmail.com', 'b...@gmail.com', 'c...@gmail.com')
• phone as the same

Very thanks for support voluntarily and sorry for bother you,

if you can, plz send for me   skype name or anything else

Best Reagrds,

[Allysson David]

I talked a bit with you in the Hangout chat, but you seemed to be offline.

I'm going back to work from my lunch break in a bit, so just to not forget:

If I got it right, you're receiving all the data for email and phones from a single field (in some form, I believe) and you have to break it down and then you're making new strings to pass as parameters to NotORM.

We should probably focus there, as the query built by NotORM is alright and the failure is probably when the PDO tries to bind the values to the ? marks.

Maybe, just maybe, it's the single-quote mark ( ' ) that's causing the fuss when escaping...

Try with these values:

$email = array('do...@gmail.com', 'k...@gmail.com');
$mobile_phone = array('0934591929', '86676');
$work_phone = $home_phone = $telephone = array('');

Notice that instead of an empty array I used an array with an empty string as sole value, since that's what you used. Having an empty array there would cause strange things... I suppose you could use an empty string also:

$work_phone = $home_phone = $telephone = '';

[Tuan Anh]

my fields

1. email | a...@gmail.com, b...@gmail.com, c...@gmail.com |
2. work_phone | 66666, 5555 |
3. home_phone | 11111, 2222, 33333 |
4. mobile_phone | 7777, 9999, 0000 |

Previous, i have  explode  strings from client (  u...@gmail.com, y...@gmail.com, l...@gmail.com )  TO array

but response is  PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

i tries bind Arr to ? marks

public function test($email ){
$a = $this->db->cards()->select('members_id')
    //->where("email IN (" . $this->db->cards()->select('email')->where('email IN ?', $email) . ")")
    ->where('email IN ?', $email);
    //->where('is_property', 1)
    //->where('status', 1);
echo $a;
$c = count($a);
echo 'count: ' . $c;
if($rs = $a->fetch()){
    echo $rs['members_id'];
}

and 

function stringHandling($str){
    //$rsStr = '';
    $strArr = explode(',', $str);
    //for ($x = 0; $x < count($strArr); $x++) {
    //    if($x < count($strArr) - 1){
    //        $rsStr .= "'" . $strArr[$x] . "',";
    //    }else {
    //        $rsStr .= "'" . $strArr[$x] . "'";
    //    }
    //}
    //return "(" . $rsStr . ")";
    return $strArr;
}

and

$op = $db->test(stringHandling($email));
echo $op;

[Allysson David]

Oh, yeah, NotORM doesn't solve the array to string before passing to the PDO to be bound when we use the '?' and the PDO can't bind arrays.

Stupid me.

And you can't obtain the same result if you just chain the where/and/or because of the parenthesis...

The possible solutions are:

Modifying NotORM to do what you want;

Imploding the values yourself and concatenating the string to the query.

Sorry for not helping much. The work is kinda killing me slowly...

[Tuan Anh]

$mobileArr = array('');

$a = $this->db->cards()->select('members_id')

    ->where("email",$emailArr);

codes above is ok,

so, it's hard as i want :(

can't be combined  IN , OR clauses ==> select id, members_id, first_name, email, mobile_phone, is_property, status from cards

where email in ('do...@gmail.com', 'l...@gmail.com' ,'a...@gmail.com')

or mobile_phone in('0909090', '9789789')

anyway, i hope someone over here can give me the solution

[Allysson David]

You can't do it in one go with current NotORM, though you can make an ugly workaround like this:

$filter = $this->db->cards()->where('email', $email)->or('mobile_phone', $mobile_phone)->or('work_phone', $work_phone)->or('home_phone', $home_phone)->or('telephone', $telephone);

$result = $this->db->members()->select('members.freepp_id')->where('cards:is_property', 1)->where('cards:is_property', 1)->where('cards:status', 1)->where('cards:id', $filter);

Remember that the vars used in $filter are arrays! 

If you don't wish to use an empty string if no value is given you can do something like this:

$filter = $this->db->cards();
if(count($email))
    $filter->or('email', $email);
if(count($mobile_phone))
    $filter->or('mobile_phone', $mobile_phone);
if(count($work_phone))
    $filter->or('work_phone', $work_phone);
if(count($home_phone))
    $filter->or('home_phone', $home_phone);
if(count($telephone))
    $filter->or('telephone', $telephone);

[Tuan Anh]

so thanks

pls help me again

SELECT count(*) FROM cards

WHERE (   email LIKE '%do...@gmail.com%'

OR email LIKE '%k...@gmail.com%'

OR email LIKE '%o...@gmail.com%'    )

AND is_property = 1 AND status = 1

i explode data to Array and using LIKE clause for Search email,

• My purpose is one of the conditions is true (OR) and is_property=1 and status=1

but

for($x = 0; $x < count($emailArr); $x++){
    $card->or('email LIKE ? ', "%" . $emailArr[$x] . "%)");
}
$card->where("is_property", 1)->and("status", 1);
echo $card;

SELECT count(*)  FROM cards 

WHERE (email LIKE ? ) 

OR (email LIKE ? ) 

OR (email LIKE ? ) 

AND (is_property = 1) AND (status = 1)

thanks

[Allysson David]

Like before, use two queries:

$filter = $db->cards();
foreach($emailArr as $email)
    $filter = $filter->or("email LIKE ?", "%".$email."%");
$result = $db->cards()->where("id", $filter)->and("is_property", 1)->and("status", 1)->count();

Something like that...

[Tuan Anh]

sorry for my stupid

i don't know why ? 

 $db->cards()->where("id", $filter)

edit	id	members_id	email	status	is_property	mobile_phone	work_phone	home_phone
edit	3	1	hiepnd...@gmail.com	1	1	NULL	NULL	NULL
edit	4	4	t...@gmail.com,do...@gmail.com,hiepnd...@gmail.com	1	1	868686868,000909	NULL	NULL
edit	5	5	nghiada...@gmail.com	1	1	NULL	NULL	NULL
edit	6	5	hiepnd...@gmail.com	0	0	88888#000909	NULL	NULL

above is my data on db

and data request from client 

• {"members_id":"6","is_property":0,"first_name":"test1","mobile_phone":"2222,0934591929,86676","email":"do...@gmail.com,k...@gmail.com,o...@gmail.com"}
  

I want to check with the information sent from the client, it already exists a certain card with such information are not (did not exist on the system ? ) (is_property = 1 and status = 1)

thanks pro

[Allysson David]

I don't know if I understand what you asked...

Why did I use $db->cards()->where("id", $filter) you ask?

The $filter query works by selecting the IDs that have fit the EMAIL.

And when you use $db->cards()->where("id", $filter) it will write something like "WHERE `id` IN (4)" since row with ID 4 has EMAIL with do...@gmail.com inside it.

You could do it like this too:

$email = "";
foreach($emailArr as $email)
 $email .= "email LIKE '%$email%' ");
$result = $db->cards()->where($email)->and("is_property", 1)->and("status", 1)->count("*");

Note: I forgot to put "*" inside COUNT before... sorry!

But there one problem, since you have many emails in one field it will give wrong results.

It will match "a...@gmail.com" and "b...@gmail.com" if you search "c...@gmail.com".

[Tuan Anh]

yeah, i have seen

A member have only one MyCard ( one type for emails, other type for phones  and is_property=1 and status=1 ) <----My Card

 

member can create cards with anything information <---- card list 

one MyCard and Card List

it's difficult  to solve this problem

[Allysson David]

You would need to treat the user-input and split the emails into an array and the remove the email field from that table and create an email table with `card_id` and `email` to have 'exact' search instead of using LIKE, but it will be hard work for you.

--

[Tuan Anh]

ok, i understand your idea, i can do it. 

try and try ...

thanks for your support

[Tuan Anh]

as your sugges, i have created  EMAILS_CARD , WORKPHONES_CARD, HOMEPHONES_CARD, MOBILEPHONES_CARD

I have successfully tested between 2 and table cards emails_card. 

filter is great, but for every one table phone, would have added one more filter (emailFilter, workphoneFilter, homephoneFilter, mobilephoneFiler)

Last SQL command  will  be individually OR clause  

$result = $db->cards()->where("id", $filterEmail)->or("id", $filterMobile)->or("id", $filterMobile)

->and("is_property", 1)->and("status", 1)->count();

I want to include them as 

(email =  '' or mobilephone = '' or ....)

thanks pro!

$result = $this->db->cards()
    ->where("id", $emailFilter)
    ->or("id", $mobilePhoneFilter)
    ->or("id", $workPhoneFilter)
    ->or("id", $homePhoneFilter)
    ->or("id", $telePhoneFilter)
    ->and("is_property", 1)
    ->and("status", 1)
    ->count("*");

SELECT count(*) FROM cards 

WHERE (id IN ('92', '4')) 

OR (id IN ('4')) 

OR ((id) IS NOT NULL AND id IS NULL) 

OR ((id) IS NOT NULL AND id IS NULL) 

OR ((id) IS NOT NULL AND id IS NULL) 

AND (is_property = 1) AND (status = 1)

• count = 2 is failed ( count  = 1 is OK ) 
• cards_id 4 and 92  (do...@gmail.com) but only one cards_id 4 have  is_property=1 and status=1

Cards

edit	id	members_id	first_name	status	is_property
edit	3	1	Ku	1	1
edit	4	4	android2	1	1
edit	5	5	NULL	1	1
edit	6	5	nghia	0	0

Emails_Card

edit	id	cards_id	email
edit	1	92	do...@gmail.com
edit	2	92	lum...@gmail.com
edit	3	92	no...@gmail.com
edit	4	4	do...@gmail.com

Mobile Cards

edit	id	cards_id	mobile_phone ↓ =
edit	1	4	0934591929

[Tuan Anh]

sr ,
i have question

Insert Infomation for cards tables and email_card, mobile_card, work_card tables... 

the first, i must have to insert other informations to cards table , after then get id (card ID)  of the card that i just inser , ==> insert cards_id to  email_card  and mobile_card ....

i see, there is something wrong ....

[Allysson David]

I'm back!

So, the thing with OR ((id) IS NOT NULL AND id IS NULL) happened because you passed an empty array as argument.

You don't need multiple filters, do it like this:

$filter = $this->db->cards();
if(count($email))
    $filter->or('email', $email);
if(count($mobile_phone))
    $filter->or('mobile_phone', $mobile_phone);
if(count($work_phone))
    $filter->or('work_phone', $work_phone);
if(count($home_phone))
    $filter->or('home_phone', $home_phone);
if(count($telephone))
    $filter->or('telephone', $telephone);

$result = $db->cards()->where("id", $filter)->and("is_property", 1)->and("status", 1)->count("*");

Only apply the "or" when the array has at least one element, that will solve the problem and you will have only one filter.

About inserting.

If you are using NotORM to insert, then you can get the ID from the result!

$values = array(
    'id' => NULL, // omit this line or NULL for AUTO_INCREMENT

    'members_id' => 1,
    'first_name' => 'Ku',
    'status' => '1',
    'is_property' => '1'
);
$result = $db->cards()->insert($values);
if($result !== false){ // if not error, $result has all of the new inserted row data
    $inserted_card_id = $result['id'];
    $values = array( // ID column was ommited for AUTO_INCREMENT
        'cards_id' => $inserted_card_id,

           'email' => 'a...@gmail.com'

    );
    $db->emails_card()->insert($values);
}

This is an example of how to add the emails.

You can use a loop with an array to insert multiple emails and phones...

[Tuan Anh]

Great

i have done everything follow your instructions, very helpful

However, i have problem when user edit/update card

Client send request include cards data (members_id, cards_id, emails, work_phones.....)

I get the normal data to update the card table , and then  update email, work_phones, home_phones ..... tables 

but i don't know how ? 

i can get id from emails_card table ....

Select emails_card.id, emails_card.email, cards.members_id, cards.is_property, cards.status from cards

Left join emails_card

On cards.id=emails_card.cards_id

where cards.members_id=9

id	email	members_id	is_property	status
61	do...@gmail.com	9	1	1
69		9	0	0
70		9	0	0
71	h...@vdc.com.vn	9	0	0
72	Roj...@gmail.com	9	0	0
73	nguyent...@baoviei.com.vr	9	0	0
76	ha...@gmail.com	9	0	0
77	ha...@gemtek.com	9	0	0

• clients may need to split the table as on the workstation (cards, emails_cards, phone_cards ...)
  

pls help me for update 

many thanks!

[Allysson David]

If your form has all the "old cards" sent every time the info is updated you can just delete all the cards of that member and then insert them anew instead of "updating".

[Tuan Anh]

• After  user  create  card, i must to  returns to the client cards_id to store
  

I also have to think about this solution.

so that I can delete the card with cards_id(send from client), and then insert the new information, get cards_id ==> send to client.

i'll try

thanks

[Tuan Anh]

hi A.David

   i have some issues with query all data by cards_id

For the first time , i have query all  card lis(cards_id)  by members_id , after that  i want to get all data by cards_id

But the cards have multiple e-mail and phone number, result of the query is more rows.

SELECT cards.id, cards.members_id,cards.first_name, cards.last_name, emails_card.email

FROM cards 

LEFT JOIN emails_card 

ON cards.id = emails_card.cards_id 

LEFT JOIN mobiles_card 

ON cards.id = mobiles_card.cards_id 

LEFT JOIN work_phones_card 

ON cards.id = work_phones_card.cards_id 

LEFT JOIN home_phones_card 

ON cards.id = home_phones_card.cards_id 

LEFT JOIN telephones_card 

ON cards.id = telephones_card.cards_id 

WHERE cards.members_id=38 and cards.id=957

id	members_id	first_name	last_name	email
957	38	gefgg		ij...@gmail.com
957	38	gefgg		ok...@gmail.com

What is the best way for this problem?

I want to return to each one card will be an array of information that card.

many thanks!

[Tuan Anh]

I will have to query for each table accessories

to retrieve email, home_phone, work_phone

then built with the basic information in the cards table to return to the client cards

client can receive an array or string json.

[Allysson David]

I see, the problem is that when joining the tables 1:N you'll get multiple lines where the only difference will be the result from xxx_card tables

If I got you right this is your scenario (correct me if I got something wrong):

* Select a member

* Select all cards from member above

* Select all data (email, phone, ...) from the cards above

The "simplest" solution is to run a query to get only the card's info. After that you'll run other queries to get the data (email, phone) separately and loop through it or something.

It should be possible to convert the NotORM result to a simple array with the data using array_map and iterator_to_array as described at the bottom of the FAQ.

The downside will be the number of queries you will be executing.

Yes, you can get all the info in one single query using an aggregation method, like group_by, in that case you'll have to use some kind of concatenation in the data (email, phone, ...) columns so you won't lose things,. but if you'll just explode and loop through the result then I don't know what would be faster...

Tell me which path you prefer and I'll help you advance.

A) Multiple Simple Queries

B) Single Complex Query

It will help to know how you plan to use the data (ie, if you will just implode them and print the result it would be best method B, if you will format and/or use some html around each value then it would be best method A).

[Tuan Anh]

hi,

thanks for your response!

I need an API implementation,

returns all list of cards, (getCardList), the parameter will be members id

I think each card will be an array and convert to json string

json string will list all the cards

  Best regards

[Allysson David]

Ok, first we'll need two variables:

1. The memebers.id (obviously);

2. The NotORM connection, for this you can:

1.1. Start the connection inside your getCardList() function; (do this if you will only use the connection inside that function in the whole page)

1.2. Have the connection as parameter of your getCardList($mid, $db); (do this if you will use the connection somewhere else too)

1.3. Have the connection as a global var; (better not do this)

That done, the scope of your function would be something like this:

function getCardList($mid, $db){
    $res = $db->cards()->where('cards.members_id', $mid);
    foreach($res as $card){
        $arr[$card['id']]['emails'] = array_map(
            'iterator_to_array',
            iterator_to_array(
                $db->emails_card()->where('cards_id', $card['id']),
                false
            )
        );
        $arr[$card['id']]['mobiles'] = array_map(
            'iterator_to_array',
            iterator_to_array(
                $db->mobiles_card()->where('cards_id', $card['id']),
                false
            )
        );
    }
    return json_encode($arr);
}

The output will be something like this:

{"emails":[{"id":"1","cards_id":"1","email":"a...@a.com"},{"id":"2","cards_id":"1","email":"b...@a.com"}],"mobiles":[]}

My example only has a little data, so the output is small.

In short, you should add more arrays like I did for emails and mobiles to store the row from the other tables.

The reason I added a 'false' as second param to the inner 'iterator_to_array' was so the array would have normal numeric keys instead of using the primary key as the key.

[Allysson David]

Now, if you don't need info like 'id' and 'cards_id' from those table (email, mobile, ...), then you could write it like this:

function getCardList($mid, $db){
    $res = $db->cards()->where('cards.members_id', $mid);
    foreach($res as $card){
        $arr[$card['id']]['emails'] = array_map(

            function($item){
                return $item['email'];
            },
            iterator_to_array(
                $db->emails_card()->where('cards_id', $card['id'])->select('email'),

                false
            )
        );
        $arr[$card['id']]['mobiles'] = array_map(

            function($item){
                return $item['mobile_phone'];
            },
            iterator_to_array(
                $db->mobiles_card()->where('cards_id', $card['id'])->select('mobile_phone'),

                false
            )
        );
    }
    return json_encode($arr);
}

And the output would be something like this:
{"1":{"emails":["a...@a.com","b...@a.com"],"mobiles":[]},"2":{"emails":["a...@b.com","b...@b.com"],"mobiles":[]}}

The differences where the ->select() in the inner query and the outer 'iterator_to_array' in array_map() was changed to a custom function that instead of converting the whole row to an array just returns the column you want.
In my opinion, this second version should be better.

[Allysson David]

Correction, the output of the first function (with all the row data from the email, mobile, ... tables) should be something like this:

{"1":{"emails":[{"id":"1","cards_id":"1","email":"a...@a.com"},{"id":"2","cards_id":"1","email":"b...@a.com"}],"mobiles":[]},"2":{"emails":[{"id":"3","cards_id":"2","email":"a...@b.com"},{"id":"4","cards_id":"2","email":"b...@b.com"}],"mobiles":[]}}

The output example I sent before didn't have the 'cards_id' as key for the values, but the function did (I just forgot to update the message after I changed the function).

[Tuan Anh]

Amazing, i've learned a lot of from you.

How can i add other data from cards table as first_name, last_name, job, address  inside the $arr ?

Thanks,

[Tuan Anh]

How can i add other data from cards table as first_name, last_name, job, address  inside the $arr ?

How can flexibility to select and add conditions, such as status = 1 ? 

many thanks,

[Allysson David]

Here is an upgraded version of the code used before.

The output will have some differences.

The output should be an array of objects.

Each object represents a card that matches with the conditions*.

Each object will have the columns you specify* as properties, plus arrays of objects for email, mobiles, ...

Here is an example of output:

[{"id":"1","first_name":"first_name_a","last_name":"last_name_a","emails":[{"email":"a...@a.com"},{"email":"b...@a.com"}],"mobiles":[{"mobile_phone":"1111"}]},{"id":"2","first_name":"first_name_b","last_name":"last_name_b","emails":[{"email":"a...@b.com"},{"email":"b...@b.com"}],"mobiles":[{"mobile_phone":"2222"}]}]

The code should go along these lines:

function getCardList($mid, $db){
    $res = $db->cards()->where('cards.members_id', $mid);

    $cards_columns = array('id', 'first_name', 'last_name');
    $emails_columns = array('email');
    $mobiles_columns = array('mobile_phone');
    $arr = array();
    foreach($res as $card){
        $arr_ = convert_notorm_row_to_array($card, $cards_columns);
        $emails_query = $db->emails_card()->where('cards_id', $card['id']);
        $arr_['emails'] = convert_notorm_result_to_array($emails_query, $emails_columns);
        $mobiles_query = $db->mobiles_card()->where('cards_id', $card['id']);
        $arr_['mobiles'] = convert_notorm_result_to_array($mobiles_query, $mobiles_columns);
        $arr[] = $arr_;
    }
    return json_encode($arr);
}

function convert_notorm_row_to_array($row, $columns = array()){
    $arr = iterator_to_array($row);
    if(is_array($columns) && !empty($columns))
        foreach($arr as $k => $v)
            if(!in_array($k, $columns))
                unset($arr[$k]);
    return $arr;
}

function convert_notorm_result_to_array($res, $columns = array()){
    return array_map(
        function($item) use ($columns) {
            return convert_notorm_row_to_array($item, $columns);
        }, iterator_to_array($res, false)
    );
}

*Notes:

To setup conditions for the cards, please alter this line with more ->where() calls:

$res = $db->cards()->where('cards.members_id', $mid);

To setup conditions for the email, mobiles, ... you (which I think you shouldn't, since the conditions will probably be set in the card selection) you can do it in these lines:

...
$emails_query = $db->emails_card()->where('cards_id', $card['id']);
...
$mobiles_query = $db->mobiles_card()->where('cards_id', $card['id']);
...

To setup which columns you want just fill an array with the columns' names to be used later as seen in these lines:

...
$cards_columns = array('id', 'first_name', 'last_name');
$emails_columns = array('email');
$mobiles_columns = array('mobile_phone');
...
$arr_ = convert_notorm_row_to_array($card, $cards_columns);

...
$arr_['emails'] = convert_notorm_result_to_array($emails_query, $emails_columns);

...
$arr_['mobiles'] = convert_notorm_result_to_array($mobiles_query, $mobiles_columns);

...

Important: If any of your queries contains multiple tables in the result, you may want to call ->select() so you'll be sure to get the columns you want. If two tables joined (because of the conditions) have columns with the same name, like ID, the last value will overwrite the any previously stored value, in case you want to keep both, please use AS to rename the column inside the ->select().

[Tuan Anh]

$emails_columns = array('email');

$emails_query = $this->db->emails_card()->where('cards_id', 1070);

function convert_notorm_result_to_array($emails_query, $columns = array()){

    return array_map(
        function($item) use ($columns) {

            return $this->convert_notorm_row_to_array($item, $columns);
        }, iterator_to_array($emails_query, false)

    );
}

hi pro,

    convert_notorm_row_to_array() func is ok,

  but convert_notorm_result_to_array  have problem

  I have checked  iterator_to_array($emails_query, false) is OK , but i don't know understand why the function doesn't run.

 STATUS 500 Internal Server Error

$emails_query = $this->db->emails_card()->where('cards_id', $card['id']);
echo $emails_query;
$arr_['emails'] = $this->convert_notorm_result_to_array($emails_query, $emails_columns);

function($item) use ($columns) {   <----- Is there a problem in here ?

so thanks

[Allysson David]

What is your PHP's version?

[Tuan Anh]

hi, my PHP version is 5.3 

[Allysson David]

Strange...

Can you check the logs?

Or try to set Error Reporting:

error_reporting(E_ALL);
ini_set('display_errors', 'On');

[Tuan Anh]

Fatal error: Using $this when not in object context in /var/www/html/source/xxx/include/DbHandler.php on line 1207

return $this->convert_notorm_row_to_array($item, $columns);

remove $this

Fatal error: Call to undefined function convert_notorm_row_to_array() in /var/www/html/source/kikilink/include/DbHandler.php on line 1207

return convert_notorm_row_to_array($item, $columns);

[Allysson David]

I'm not exactly sure if I remember correctly, but try putting convert_notorm_row_to_array function first, then convert_notorm_result_to_array and lately getCardList and try again.

Maybe PHP is going the strict route and it wasn't defined when it was used or something.

[Tuan Anh]

foreach($res as $card){
    $arr = $this->convert_notorm_row_to_array($card, $cards_columns);
    print_r($arr);
}

Array ( [id] => 1083 [first_name] => Cuong [last_name] => Duy [address] => Hn,VietNam )

is OK ,

[Allysson David]

So you're using the functions inside a class you made.

The ability to use $this inside anonymous functions is only available since PHP 5.4.0, since that's not your case...

Make those functions static and call them as ClassName::convert_notorm_row_to_array instead. There shouldn't be any need for the $this in the two convert_notorm_ functions I wrote since they're more like Utils.

[Tuan Anh]

I have tried using self but can not

we can separate out array_map callback method ()

[Tuan Anh]

public function test($mid){
    $res = $this->db->cards()->where('cards.members_id', $mid);
    $cards_columns = array('id', 'first_name', 'last_name', 'address');
    $emails_columns = array('cards_id','email');
    $arr = array();
    foreach($res as $card){
        $arr = DbHandler::convert_notorm_row_to_array($card, $cards_columns);

        $emails_query = $this->db->emails_card()->where('cards_id', $card['id']);

        $arr_['emails'] = DbHandler::convert_notorm_result_to_array($emails_query, $emails_columns);

        $arr[] = $arr_;
    }
    return json_encode($arr);
}

public static function convert_notorm_row_to_array($row, $columns = array()){

    $arr = iterator_to_array($row);
    if(is_array($columns) && !empty($columns))
        foreach($arr as $k => $v)
            if(!in_array($k, $columns))
                unset($arr[$k]);
    return $arr;
}

public static function convert_notorm_result_to_array($res, $columns = array()){

    return array_map(
         function($item) use ($columns) {

            return DbHandler::convert_notorm_row_to_array($item, $columns);
        }, iterator_to_array($res, false)
    );
}

result 

{
  "id": "1122",
  "first_name": "Thuy",
  "last_name": "",
  "address": "Nguyen Trai Hanoi Vietnam  ",
  "0": {
    "emails": [
      {
        "cards_id": "1122",
        "email": "thuy....@gmail.com"
      }
    ]
  }
}

but this member have many card and the card have many  email

edit	id	cards_id	email

edit	692	1074	thuy....@gmail.com
edit	693	1075	th...@gmail.com

nice weekend!

[Allysson David]

Hey, there's something very wrong happening...

First, this line:

$arr = DbHandler::convert_notorm_row_to_array($card, $cards_columns);

Should have been:

$arr_ = DbHandler::convert_notorm_row_to_array($card, $cards_columns);

You probably tried to apply the corrections I made to your adapted version of the first function I sent instead of adapting the latest version.

Maybe that's why something is going wrong? You missed another change I made at some point?

Next, why are you storing the cards_id of the email if you are getting cards.id from the cards table? It's the same value.

Here it is represented by 1122 (the 0 shouldn't have been there!):

{
    "id": "1122", 

    "first_name": "Thuy", 

    "last_name": "", 

    "address": "Nguyen Trai Hanoi Vietnam ",
    "0": { 

        "emails": [
            {
                "cards_id": "1122",
                "email": "thuy....@gmail.com"
            }
        ]
    }
}

And the table you sent doesn't match!?

edit	id	cards_id	email

edit	692	1074	thuy....@gmail.com
edit	693	1075	th...@gmail.com

This shows two emails (thuy....@gmail.com and th...@gmail.com) from two cards (1074 and 1075) that weren't in the json. Maybe you cut them from the json to make it short? But I can't find "why" the function isn't displaying all the entries if your function doesn't match mine.

You can:

A) Copy the 3 functions I sent before and re-adapt them (using $this-> or DbHandler:: when necessary) to see if that solves the problem.

B) Send the functions you are using (with the conditions applied and all), the full output you're getting and what rows from what tables you expect to be shown.

[duy cuong]

hi, 

public function test($mid){

        $res = $this->db->cards()->where('cards.members_id', $mid);

        $cards_columns = array('id', 'first_name', 'last_name', 'address');

        $emails_columns = array('cards_id','email');

        $arr = array();

        foreach($res as $card){

            $arr = DbHandler::convert_notorm_row_to_array($card, $cards_columns);

            $emails_query = $this->db->emails_card()->where('cards_id', $card['id']);

            $arr_['emails'] = DbHandler::convert_notorm_result_to_array($emails_query, $emails_columns);

            $arr[] = $arr_;

        }

        return json_encode($arr);

    }

    public static function convert_notorm_row_to_array($row, $columns = array()){

        $arr = iterator_to_array($row);

        if(is_array($columns) && !empty($columns))

            foreach($arr as $k => $v)

                if(!in_array($k, $columns))

                    unset($arr[$k]);

        return $arr;

    }

    public static function convert_notorm_result_to_array($res, $columns = array()){

        return array_map(

             function($item) use ($columns) {

                return DbHandler::convert_notorm_row_to_array($item, $columns);

            }, iterator_to_array($res, false)

        );

    }

and result 

{  

   "id":"1122",
   "first_name":"Thuy",
   "last_name":"",
   "address":"Nguyen Trai Hanoi Vietnam  ",
   "0":{  
      "emails":[  
         {  
            "cards_id":"1122",
            "email":"thuy....@gmail.com"
         }
      ]
   }
}

this is all cardlist with members_id =35

SELECT * FROM cards WHERE (members_id = 35)

id
members_id
first_name
last_name
address
fax
job
website
photo
avatar
photo_other_card
note
created
modified
group_name
status
is_property
1032
35
NULL
NULL
NULL
NULL
NULL
 
 
 
 
 
NULL
NULL
 
1
1
1059
35
 
CSKH
NULL
NULL
NULL
 
 
 
 
 
2015-04-02 14:26:17
NULL
 
0
0
1060
35
Em Thắm
 
NULL
NULL
NULL
 
 
 
 
 
2015-04-02 14:26:18
NULL
 
0
0
1061
35
Hoá Androi
 
NULL
NULL
NULL
 
 
 
 
 
2015-04-02 14:26:18
NULL
 
0
0
1070
35
.1. M. .l|
,.'xHHfl.
åœ°å €å’€ä½£è§¦åŸºéš†å¸‚æš–æš–åŒ¾æš–æš–è¡—åˆ ç ¥6楼  
NULL
NULL
 
 
 
 
 
2015-04-02 14:50:50
NULL
 
0
0
1071
35
Giang
 
Me Tri Ha Ha Noi Vietnam
NULL
NULL
 
 
 
 
 
2015-04-02 14:50:51
NULL
 
0
0
1072
35
Giang
 
Me Tri Ha Ha Noi Vietnam  
NULL
NULL
 
 
 
 
 
2015-04-02 14:50:56
NULL
 
0
0
1073
35
Linh
Nguyen
Ba LaNguyen Trai Ha Noi Vietnam
NULL
NULL
 
 
 
 
 
2015-04-02 14:50:56
NULL
 
0
0
1074
35
Thuy
 
Nguyen Trai Hanoi Vietnam
NULL
NULL
 
 
 
 
 
2015-04-02 14:50:57
NULL
 
0
0
1075
35
Thuy
 
Nguyen Trai Hanoi Vietnam  
NULL
NULL
 
 
 
 
 
2015-04-02 14:50:58
NULL
 
0
0

[duy cuong]

i see, 

1122 35   Thuy Nguyen Trai Hanoi Vietnam NULLNULL     2015-04-02 15:24:02NULL 00

is last records

hi, 

            "email":"thuy.gem...@gmail.com"
         }
      ]
   }
}

...

[Allysson David]

Yep, definitely, found the problem:

$arr = DbHandler::convert_notorm_row_to_array($card, $cards_columns);

Please change $arr to $arr_ in this line as I mentioned before.

Each loop is resetting $arr which was supposed to store all of the output.

Also:

$cards_columns = array('id', 'first_name', 'last_name', 'address');

$emails_columns = array('cards_id','email');

Remember that the column `id` in the `cards` table will always have the same value as the column `cards_id` in the `emails_card` table. That way, setting it to be saved in the $emails_columns variable is redundant since you easily access it from "above". But that's not a problem, it's just me saying that you will have lots of repeated value this way.

P.S.:

Surprisingly there's no row with `id` = 1122 in the `cards` table with `members_id` = 35? Why was it returned by the function?

[Allysson David]

Ah, just saw your latest email, please ignore previous P.S.

[Tuan Anh]

{

    "status": 1,

    "card_list": [

        {

            "id": "2258",

            "first_name": "Pú Tin",

            "last_name": null,

            "fax": null,

            "job": null,

            "company": null,

            "website": [

                "abc.com",

                "123.vn"

            ],

            "emails": [

                {

                    "email": "do...@gmail.com"

                }

            ],

            "mobiles": [],

            "home_phones": [],

            "work_phones": [],

            "telephone_phones": [],

            "other_phones": []

        },

        {

            "id": "2264",

            "first_name": "Blackberry",

            "last_name": null,

            "fax": null,

            "job": null,

            "company": null,

            "website": [

                ""

            ],

            "emails": [

                {

                    "email": "black...@gmail.com"

                },

                {

                    "email": "b...@gmail.com"

                }

            ],

            "mobiles": [],

            "home_phones": [],

            "work_phones": [],

            "telephone_phones": [],

            "other_phones": []

        }

    ]

}

hi David, pls check for me

i want to  emails array as

"emails": [
      "a...@a.com",
      "b...@a.com"
    ]

not 

 "emails": [

                {

                    "email": "black...@gmail.com"

                },

                {

                    "email": "b...@gmail.com"

                }

            ],

and if array is null

"website": [

                "abc.com",

                "123.vn"

            ]

 "website": [

                ""  <--- ( i want as [] , not [""]

            ],

my codes

public function getCardList($mid){
    $res = $this->db->cards()->where('cards.members_id', $mid)->and('is_property', 0);
    $cards_columns = array('id', 'first_name', 'last_name', 'job', 'company', 'fax');

    $emails_columns = array('email');
    $mobiles_columns = array('mobile_phone');

    $home_phones_columns = array('home_phone');
    $work_phones_columns = array('work_phone');
    $telephones_columns = array('telephone');
    $other_phones_columns = array('other_phones');

    $website_columns = array('website');
    $address_columns = array('address');

    $arr = array();

    foreach($res as $card){

        $arr_ = DbHandler::convert_notorm_row_to_array($card, $cards_columns);

        $arr_['website'] = explode(',', DbHandler::dissection_notorm_row($card, $website_columns));
        $arr_['address'] = explode(',', DbHandler::dissection_notorm_row($card, $address_columns));

        //((is_array($arr_['website']) && !empty($arrTmp)) ? $arr_['website'] : '[]');

        $emails_query = $this->db->emails_card()->where('cards_id', $card['id']);
        $arr_['emails'] = DbHandler::convert_notorm_result_to_array($emails_query, $emails_columns);

        $mobiles_query = $this->db->mobiles_card()->where('cards_id', $card['id']);
        $arr_['mobiles'] = DbHandler::convert_notorm_result_to_array($mobiles_query, $mobiles_columns);

        $home_phones_query = $this->db->home_phones_card()->where('cards_id', $card['id']);
        $arr_['home_phones'] = DbHandler::convert_notorm_result_to_array($home_phones_query, $home_phones_columns);

        $work_phones_query = $this->db->work_phones_card()->where('cards_id', $card['id']);
        $arr_['work_phones'] = DbHandler::convert_notorm_result_to_array($work_phones_query, $work_phones_columns);

        $telephone_query = $this->db->telephones_card()->where('cards_id', $card['id']);
        $arr_['telephone_phones'] = DbHandler::convert_notorm_result_to_array($telephone_query, $telephones_columns);

        $other_phones_query = $this->db->other_phones_card()->where('cards_id', $card['id']);
        $arr_['other_phones'] = DbHandler::convert_notorm_result_to_array($other_phones_query, $other_phones_columns);

        $arr[] = $arr_;
    }

    return $arr;
}

public static function dissection_notorm_row($row, $columns = array()){
    $arr = iterator_to_array($row);
    $value = '';

    if(is_array($columns) && !empty($columns))
        foreach($arr as $k => $v)

            if(in_array($k, $columns))
                $value = $v;
    return $value;
}

/**
 * @param $row
 * @param array $columns
 * @return array
 */

public static function convert_notorm_row_to_array($row, $columns = array()){
    $arr = iterator_to_array($row);

    //print_r($arr);

    if(is_array($columns) && !empty($columns))
        foreach($arr as $k => $v)
            if(!in_array($k, $columns))
                unset($arr[$k]);
    return $arr;
}

/**
 * @param $res
 * @param array $columns
 * @return array
 */

public static function convert_notorm_result_to_array($res, $columns = array()){
    return array_map(
         function($item) use ($columns) {
            return DbHandler::convert_notorm_row_to_array($item, $columns);
        }, iterator_to_array($res, false)
    );
}

[Tuan Anh]

hi, address and website is two fields on card table

They are stored in a string, and separated by ,  

website :   abc.vn,123.com.vn,ufo.net

i want to as  (array of objects )

"websites": [

               {

                   "website": "abc.com"

               },

               {

                   "website": "123.com.vn"

               }

           ],

[Tuan Anh]

other way if I do not want to split more tables and website address ?

[Tuan Anh]

hi David,

 I've split to other tables ( adress and website ) and the issue is resolved. 

pls suggest for me about   search my card , search all my card on server ( is_property = 1, and status = 1)

Client will request to server with   other keyword search,

many thanks!

[Allysson David]

Did you solve the thing with the website? If not, let me know.

And please explain further about this search.

Didn't we work on search (listing) cards before?

[Tuan Anh]

yes, i have solved that problem

About search,

Clients can search for all fields ( into all my card ( is_property = 1 , status = 1 )

User has only one mycard  ( is_property = 1 , status = 1 )

and many other card list ( is_property = 0 , status = 1 or 0 ) 

Card status to distinguish card that already exists on the system or not ( if exists ==> status = 1 ,  not ==> 0 )

if( status = 1 ) ===>  user can chat with each other ( 1 1 )

[Tuan Anh]

 public function getCardList($mid){

        $res = $this->db->cards()->where('cards.members_id', $mid)->and('is_property', 0);  // <--- getCardList by mId ( is_property = 0 )

        $cards_columns = array('id', 'first_name', 'last_name', 'job', 'company', 'fax', 'status', 'freepp_id');

        $emails_columns = array('email');

        $mobiles_columns = array('mobile_phone');

        $home_phones_columns = array('home_phone');

        $work_phones_columns = array('work_phone');

        $telephones_columns = array('telephone');

        $other_phones_columns = array('other_phones');

        $website_columns = array('website');

        $address_columns = array('address');

        $arr = array();

        foreach($res as $card){

            $arr_ = DbHandler::convert_notorm_row_to_array($card, $cards_columns);

            $website_query = $this->db->websites_card()->where('cards_id', $card['id']);

            $arr_['website'] = DbHandler::convert_notorm_result_to_array($website_query, $website_columns);

            $address_query = $this->db->address_card()->where('cards_id', $card['id']);

            $arr_['address'] = DbHandler::convert_notorm_result_to_array($address_query, $address_columns);

            $emails_query = $this->db->emails_card()->where('cards_id', $card['id']);

            $arr_['emails'] = DbHandler::convert_notorm_result_to_array($emails_query, $emails_columns);

            $mobiles_query = $this->db->mobiles_card()->where('cards_id', $card['id']);

            $arr_['mobiles'] = DbHandler::convert_notorm_result_to_array($mobiles_query, $mobiles_columns);

            $home_phones_query = $this->db->home_phones_card()->where('cards_id', $card['id']);

            $arr_['home_phones'] = DbHandler::convert_notorm_result_to_array($home_phones_query, $home_phones_columns);

            $work_phones_query = $this->db->work_phones_card()->where('cards_id', $card['id']);

            $arr_['work_phones'] = DbHandler::convert_notorm_result_to_array($work_phones_query, $work_phones_columns);

            $telephone_query = $this->db->telephones_card()->where('cards_id', $card['id']);

            $arr_['telephone_phones'] = DbHandler::convert_notorm_result_to_array($telephone_query, $telephones_columns);

            $other_phones_query = $this->db->other_phones_card()->where('cards_id', $card['id']);

            $arr_['other_phones'] = DbHandler::convert_notorm_result_to_array($other_phones_query, $other_phones_columns);

            $arr[] = $arr_;

        }

        return $arr;

    }

// <--- getCardList by mId ( is_property = 0 )

[Tuan Anh]

I got the idea for the search

I will add the card table in a search field (all info)

Select * from is_property = 1and cards where status = 1 and text search '% keyword%'

keywork from client

What do you think, I do not have experience with the index, unique, pk

[Tuan Anh]

hi David,

i've complete search with all fields

Next Steps

I want to give notice to the client using ApnsPHP (IOS)

I need to notify to  the client 

1. when A  update myCard (myCardA), and BCD holding myCardA (myCardA in the list of BCD card list) ,
2. when receive notification when BCD, BCD can synchronize information with myCard
3. When A wants to exchange cards with B (or BCD)

thanks for 

[Allysson David]

I never used ApnsPHP, sorry can't help here.

And I think NotORM won't help either...

[Tuan Anh]

hi David,

with notorm ,

Don't know what will happen ?

When i reference the id of the table members to the table notifications

members  1  - n  notifications

id PK                   id  PK

                            sender_id   INDEX   FK

                            reveicer_id  INDEX   FK

[Allysson David]

Check this: https://groups.google.com/forum/#!topic/notorm/pst5v6sWMr8

It's almost the same thing.