a field from table A IN a field on table B + mysql + php

Question

I have two tables with the following structure:

tblA:

+----------+---------------+
| id (int) | name (string) |
+----------+---------------+
|        1 | a             |
|        2 | b             |
|        3 | c             |
+----------+---------------+

tblB:

+----------+---------------+-------------+
| id (int) | name (string) | aid(string) |
+----------+---------------+-------------+
|        1 | x             | '1,2'       |
|        2 | y             | '2,'        |
|        3 | z             | '1,3'       |
+----------+---------------+-------------+

$a = $this::$db->prepare('SELECT * FROM tblB WHERE id= :id LIMIT 1');
$a->bindValue(':id', $ID, PDO::PARAM_INT);
$a->execute();
$r = $a->fetch(pdo::FETCH_ASSOC);

if ($a->rowCount() > 0){
    $bInf = $r['id']   . '|*|' .
            $r['name'] . '|*|' .
            $r['aid']  . '|**|';

    $b = $this::$db->prepare('SELECT id,name FROM tblA WHERE FIND_IN_SET(id,:ids)');
    $b->bindValue(':ids', $r['aid']);
    $b->execute();
    $rs = $b->fetchAll(pdo::FETCH_ASSOC);

    if ($b->rowCount() > 0)
    {
        foreach ($rs as $srow => $srval)
          $aInf .= $srval['id']   . '[|]' .
                   $srval['name'] . '[#]' ;
    } else
        $aInf = ' ';
        $aInf.=  '|***|' . $bInf; 
    }
}

i need to query from tblA and tblB as above sample, but second query dont return any records.

i also tried 'IN' operator but dont worked too...

pls help me...

Get table B data into a junction table, it will use indexes (think fast), and it will be maintainable — Drew, Sep 15 '15 at 00:03
or should I say it *can* use indexes, and I don't mean left-most only like now in that string — Drew, Sep 15 '15 at 00:14
The gentleman over [here](http://stackoverflow.com/q/32556972/1816093) had a well-explained reason for saying this and that cannot be used. His question had revisions, as proposed solutions came in. I haven't seen any of that sort of explanation here. "Cannot" often means "I don't want to do that". — Drew, Sep 15 '15 at 12:09
@Drew yes you're right, Because the query should only be implemented with 'IN' or 'FIND_IN_SET' or same other operator or func, i'll edit my post with more description soon. ty. — smartiz, Sep 15 '15 at 12:38
not that I would answer it. You mean I only want it done with `in` or `find_in_set`, (back to "I don't want to") now don't you :) Good luck either way, hope it works for you. — Drew, Sep 15 '15 at 12:41

score 1 · Answer 1 · edited May 03 '19 at 13:45

1

Try something like this:

SELECT * FROM tblb,tbla
WHERE tblb.id= 1 AND FIND_IN_SET(tbla.id, tblb.aid)

for details, refer to:

FIND_IN_SET() vs IN()

edited May 03 '19 at 13:45

vvvvv

25,404
19
49
81

answered Sep 15 '15 at 01:14

satroy

116
9

thank you, select from two table in one query is not my solution, but link was useful. – smartiz Sep 17 '15 at 13:37

Tomas Ceruti · Answer 2 · 2015-09-15T04:40:34.217

You can use a different approach by extracting each of the ids from aid column separately

$a = $this::$db->prepare('SELECT * FROM tblB WHERE id= :id LIMIT 1');
$a->bindValue(':id', $ID, PDO::PARAM_INT);
$a->execute();
$r = $a->fetch(pdo::FETCH_ASSOC);

if ($a->rowCount() > 0)
{
    $bInf = $r['id']   . '|*|' .
            $r['name'] . '|*|' .
            $r['aid']  . '|**|';

    //extract each of the ids in the variable 'aid'
    $tbla_ids = explode(',',$r['aid']);
    foreach($tbla_ids as $tbla_id){
        //case for the record where aid = '2,'
        if(strlen($tbla_id)==0){
            continue;
        }
        $b = $this::$db->prepare('SELECT id,name FROM tblA WHERE id= :ids');
        $b->bindValue(':ids', $tbla_id);
        $b->execute();
        //do what you need to do here. The query returns the single record
        //from tbla that matches the id $tbla_id
    }
}

Yes it's possible, but with low perfomance... i can't use loop's, ty — smartiz, Sep 15 '15 at 12:10

smartiz · Accepted Answer · 2015-09-16T11:57:36.380

0

I had a funny mistake, by removing ' Character from "aid" field, the problem was solved and FIND_IN_SET works right now.

edited Sep 16 '15 at 11:57

answered Sep 15 '15 at 13:26

smartiz

151
7

a field from table A IN a field on table B + mysql + php

3 Answers3