Using mySQL query result in making another query in PHP

Question

It's a very weird kind of problem.

Here's my code :

function lvlmem($a_id)
{   
    global $con;
    global $mem;
//                  $a_id = "'".$a_id."','220'";
    $results = [];
    $stmt = $con->prepare("SELECT id FROM `$mem` where parent_id in (?) && (plan!='bm' || takencf=1) && martyr!=1 && status='verified'");
    $stmt->bind_param("s", $a_id);
    $stmt->execute();
    $result3 = $stmt->get_result();
    $stmt->close();

    echo "<br>SELECT id FROM `".$mem."` where parent_id in (".$a_id.") && (plan!='bm' || takencf=1) && martyr!=1 && status='verified'";
    while ($row3 = $result3->fetch_assoc())
    {
        $results[] = $row3['id'];
        var_dump($row3['id']);
    }
    return $results;
}
$lvl1 = lvlmem($row2['id']);  // row2[id] is a no like 219
$imp = implode("','", array_map('intval', $lvl1));
$lvl2 = lvlmem($imp);
$imp = implode("','", array_map('intval', $lvl2));
$lvl3 = lvlmem($imp);
$imp = implode("','", array_map('intval', $lvl3));
$lvl4 = lvlmem($imp);
$imp = implode("','", array_map('intval', $lvl4));
$lvl5 = lvlmem($imp);
$imp = implode("','", array_map('intval', $lvl5));
$lvl6 = lvlmem($imp);
$imp = implode("','", array_map('intval', $lvl6));
$lvl7 = lvlmem($imp);
$imp = implode("','", array_map('intval', $lvl7));
$lvl8 = lvlmem($imp);
$imp = implode("','", array_map('intval', $lvl8));
$lvl9 = lvlmem($imp);
$imp = implode("','", array_map('intval', $lvl9));
$lvl10 = lvlmem($imp);
$imp = implode("','", array_map('intval', $lvl10));

$count = count($lvl1)+count($lvl2)+count($lvl3)+count($lvl4)+count($lvl5)+count($lvl6)+count($lvl7)+count($lvl8)+count($lvl9)+count($lvl10);

my mission: i want to get the levels

the function lvlmem is supposed to give me a array of id where parent_id matches to ids given in $a_id

so first time $a_id = 200; and let say function returns me 201, 202, 203

then second time $a_id is going to be 201,202,203 <--- the problem comes with these commas and function should return me further ids like 206,214,219 or something

when i send the $a_id first time it goes in code like

SELECT id FROM `member` where parent_id in (219) && (plan!='bm' || takencf=1) && martyr!=1 && status='verified'

this one is successfull

but next one is not...

i tried sending it with different comma styles, every single one of them is working for me in phpmyadmin but they are not working in code


SELECT id FROM `member` where parent_id in ('219','220') && (plan!='bm' || takencf=1) && martyr!=1 && status='verified' //this doesnt bring any results. pasting same command in phpmyadmin does bring result and i am pretty sure it is happening becuas of the style of upper commas

SELECT id FROM `member` where parent_id in (220,221) && (plan!='bm' || takencf=1) && martyr!=1 && status='verified' // and quering it this way brings same result as the next one.. seems like it ignore every id after first comma btw using this query in phpmyadmin does bring the data as 221 was included in query

SELECT id FROM `member` where parent_id in (220) && (plan!='bm' || takencf=1) && martyr!=1 && status='verified'

how can i solve this issue?

btw the only important thing to me is the $count so f it can be done in any other way like by using a single query then please tell me that

this is my first time in stack overflow... so sorry if i explained it terribly... please ask in comment if you want any other information

This is rarely a sensible idea. The normal approach is use just one query, with joins, as necessary. In general, the fewer round trips to the database the better — Strawberry, Mar 06 '20 at 23:31
@Strawberry it would be so so so great if it is possible in a single query ... can you convert it in that and post it as a answer i will happily accept it — asdfg, Mar 06 '20 at 23:38
Not as presently articulated. However, if that's something you're interested in exploring I would get rid of all of the above code, and instead see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, Mar 06 '20 at 23:45
You need to build a string of placeholders for each item you're searching for, so query looks like `WHERE parent_id IN (?)` when there's one, `WHERE parent_id IN (?, ?)` when there are two, etc. No need to bind parameters, just pass them as array to `execute()`. — miken32, Mar 07 '20 at 01:41

nbk · Answer 1 · 2020-03-08T00:11:04.487

0

Make a

SELECT GROUP_CONCAT(id)  as id FROM `$mem` 
WHERE parent_id in (?) && (plan!='bm' || takencf=1) && martyr!=1 && status='verified'

this gives you back a single field with comma separated ids, which you can use directly.

I personally would write a recursive procedure for your query, but that's me

Your code then don't need to be so coomplecated

while ($row3 = $result3->fetch_assoc())
{
    $results = $row3['id'];
    var_dump($row3['id']);
}

But as you get only one row you don't need the while loop

and

you can directly use the result and count the number of values

$result1 = substr_count( $lvl1, ",") +1; 
$lvl2 = lvlmem($lvl1);
$result2 = substr_count( $lvl2 , ",") +1; 
$lvl3 = lvlmem($lvl2);

edited Mar 08 '20 at 00:11

answered Mar 06 '20 at 23:39

nbk

45,398
8
30
47

Procedures seem appropriate for procedural problems. Straightforward queries seem more sensible otherwise. But maybe that's just me. – Strawberry Mar 06 '20 at 23:47
this is giving a bunch of error in php my admin like group cant be there _concat is not a thing only concat is... but using only concat doesnt give a single field – asdfg Mar 06 '20 at 23:50
As i explained you get back only a filed containing 1,2,3,4 so you need only $results = $row3['id']; and aölso get rid of the implode – nbk Mar 06 '20 at 23:52
@nbk brother i am saying that this query is giving error in phpmyadmin... i suppose there is a problem in this query... 9 errors were found Variable name was expected. (near "?" at position 67) An expression was expected. (near "GROUP" at position 7) Unrecognized keyword. (near "GROUP" at position 7) Unexpected token. (near "_CONCAT" at position 13) Unexpected token. (near "(" at position 21) Unexpected token. (near "id" at position 22) Unexpected token. (near ")" at position 24) Unrecognized keyword. (near "as" at position 27) Unexpected token. (near "id" at position 30) – asdfg Mar 06 '20 at 23:57
phpmyadmin is right it is a typo it must be GROUP_CONCAT and **not** GROUP _CONCAT it is only **one** word – nbk Mar 07 '20 at 00:00
@nbk ok so it worked after you removed a space – asdfg Mar 07 '20 at 00:02
@nbk that answer really made my code simpler and shorter but now how can count the no of ids in $lvl1, $lvl2, $lvl3... ?? – asdfg Mar 07 '20 at 23:50

Siva Koteswara Rao · Answer 2 · 2020-03-07T19:16:09.623

-1

you can try FIND_IN-SET()

SELECT id FROM `member` where   
 FIND_IN_SET(parent_id,("219,220"))>0    
 AND (plan!='bm' || takencf=1) AND
 martyr!=1 AND status='verified' "

edited Mar 07 '20 at 19:16

answered Mar 06 '20 at 23:38

Siva Koteswara Rao

129
1
6

The second argument to `FIND_IN_SET()` should be a string. – Barmar Mar 06 '20 at 23:51
gave me an error that operand should contain one element – asdfg Mar 06 '20 at 23:52

Using mySQL query result in making another query in PHP

2 Answers2