How to fetch data from mysql DB with string conditioning option?

Question

In my project, I want to fetch title, dep and name from DB with conditioning option is id_select.

The id_select is from BB table successfully. The code is:

$sql="select sNum from BB where id='TN001';";
$sel = $conn->query($sql);

$resSQL =  $conn->query($sql)->fetchall();

if(count($resSQL) > 0)
{              
   while($numRow=$sel->fetch(PDO::FETCH_NUM))
   {
     $systemNum[]=$numRow[0];
   }
}   
   $id_select=implode(",",$systemNum);

The content of Conditioning options "id_select" string is like:

N20180004,N20180005,N20180006,N20180007,N20180008,N20180009

And "id_select" is used in sql as conditioning option. The code is :

 $sql="select title,dep,name from  OA where sysid in ($id_select);";//works fail

The correct output of title,dep,name should be:

 ncss,hpc,Tom      //for N20180004
 ncss,cloude,Jack  //for N20180005
 ncss,market,Stock //for N20180006
 ncss,system,Stack //for N20180007
 ncss,project,Shansa //for N20180008
 ncss,administr,Go  //for N20180009

Finally, they should be stored in data array. But unlucky, it works fail with nothing return.

So, I tried another sql of:

$sql="select title,dep,name from  OA  where name='Tom';"; //works ok

and

$sql="select title,dep,name from  OA where sysid='N20180009';";//works ok

Both of them works OK along with one return record.

The totle code of PHP is:

$sql="select sNum from BB where id='TN001';";
$sel = $conn->query($sql);

 $resSQL =  $conn->query($sql)->fetchall();

 if(count($resSQL) > 0)
 {              
   while($numRow=$sel->fetch(PDO::FETCH_NUM))
  {
   $systemNum[]=$numRow[0];
   }
 }   
 $id_select=implode(",",$systemNum);

 $cdata=array();

 $sql="select title,dep,name from  OA where sysid in ($id_select);";//works fail
 //$sql="select title,dep,name from  OA  where name='Tom';"; //works ok
 //$sql="select title,dep,name from  OA where sysid='N20180009';";//works ok
   $sel=$conn->query($sql);

   while($res=$sel->fetch(PDO::FETCH_BOTH)){
     $cdata[]=$res;
   }

  $data=array();

 foreach($cdata as $k=>$v){
  $data['rows'][$k]['SNV']=$v['title'];
  $data['rows'][$k]['CV']=$v['dep'];
  $data['rows'][$k]['EV']=$v['name'];
  }

  $data['total']=$row[0];  
  echo json_encode($data);

In my project, I had to choose id_select option. Who can help me ?

Ivan86 · Accepted Answer · 2018-01-08 06:55:37Z

1

Since your $id_select values are strings add apostrophes ' to to your PHP for each row:

while($numRow=$sel->fetch(PDO::FETCH_NUM))
{
  $systemNum[] = "'".$numRow[0]."'";
}

After the $id_select=implode(",",$systemNum); the $id_select will look like this:

'N20180004','N20180005','N20180006','N20180007','N20180008','N20180009','N20180010'

From what I see everything else should then work fine.

answered Jan 8, 2018 at 6:55

Ivan86

5,7182 gold badges16 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

stack Over a year ago

Wow, it works fine. I love you, Thank you for your kindly heart

Boni · Accepted Answer · 2018-01-08 05:29:32Z

0

$sql = "SELECT title,dep,name from OA Where sysid IN (".implode(',',$id_select).")";

I think it's help u.

answered Jan 8, 2018 at 5:29

Boni

915 bronze badges

Collectives™ on Stack Overflow

How to fetch data from mysql DB with string conditioning option?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related