Categories: php, sql, arrays, json, codeigniter

How to use iterated MySQL queries and form a json encoded result?

2 answers

I have a problem when executing multiple SQL into an array and then display to JSON data, this is my code:

$sql0="SELECT id,               company_id,               class_name        FROM cc_class_room        WHERE company_id='CO191125310002'";  $res=$this->db->query($sql0); $data=$res->result_array();  $i=0;  foreach($data as $row ) {     $sql1="SELECT COUNT(user_id) member FROM cc_class_room_user WHERE class_id='".$row['company_id']."' GROUP BY class_id";     $res1=$this->db->query($sql1);      $responce->rows[$i]['id']=$row['id'];     $responce->rows[$i]['cell']=array("company_id"=>$row['company_id'],"class_name"=>$row['class_name'],"member"=>$row['member']);     $i++; }  return json_encode($responce); 

When I did this I got an error:

Undefined index: member

The second SQL can't execute, how do I execute sql to array, without join/union the SQL? This is display JSON that I want json

This is an application built on CodeIgniter.

All answers to this question, which has the identifier 61220400

The best answer:

You haven't fetched the member value from the second query. You need to fetch that into a different array so you can add it to your output data:

foreach($data as $row ) {     $sql1="SELECT COUNT(user_id) member FROM cc_class_room_user WHERE class_id='".$row['company_id']."'";     $res1=$this->db->query($sql1);     $row1=$res1->result_array();     $responce->rows[$i]['id']=$row['id'];     $responce->rows[$i]['cell']=array("company_id"=>$row['company_id'],"class_name"=>$row['class_name'],"member"=>$row1[0]['member']);     $i++; }    

Note that you don't need to use a counter in your loop, you can put all the data together in an array and push it in one line:

$responce[] = array('id' => $row['id'],                     'cell' => array("company_id" => $row['company_id'],                                     "class_name"=>$row['class_name'],                                      "member"=>$row1[0]['member']                                    )                     ); 

I'm late to the party, but this is important to share...

About your error, it looks like you forgot to declare the result set in the loop like you did for the $data before the loop.

$res1=$this->db->query($sql1);  // $res1 is never used again! 

You could have just written the count directly into your new data structure like this:

'member' => $res1->getRowArray['member'] 

You should not be making iterated trips to the server when you can do all of the work in a single trip. You can use a subquery in the SELECT clause -- this will make just as many individual queries on the tables as in your original script, but this does it all in one trip. This is better practice and your teacher should not be telling you otherwise.

Also, since you are scripting your application in CodeIgniter, you should fully embrace the active record syntax. If your incoming company_id value is user-supplied, then the following snippet will actually help you to write a stable, secure, and portable query.

Raw MySQL SELECT with subquery: ( demo)

SELECT id,        class_name,        (SELECT COUNT(1)         FROM cc_class_room_user         WHERE class_id = company_id) AS members FROM cc_class_room WHERE company_id = 'CO191125310002' 

SELECT with subquery via CodeIgniter's Active Record:
(I have tested this locally to be successful/trustworthy)

$companyId = 'CO191125310002';  $subquery = $this->db     ->select('COUNT(1)')     ->from('cc_class_room_user')     ->where('class_id = company_id')     ->get_compiled_select();  $resultSet = $this->db     ->select("id, class_name, ({$subquery}) AS members")     ->from('cc_class_room')     ->where('company_id', $companyId)     ->get()     ->result_array(); 

I'll also say that there may be better alternative queries which can be executed, but to be completely confident, I'd need to have access to your table schema to run some tests.

No matter which way you choose to query the database, the preparation of your results, IMO, is most cleanly written like this:

$results = []; foreach ($resultSet as $row) {     $results[] = [         'id' => $row['id'],         'cell' => [             'company_id' => $companyId,  // no reason to query for what you already know             'class_name' => $row['class_name'],             'members' => $row['members'],  // I am using plural because it makes better sense for a counted value         ],     ]; } return json_encode(['rows' => $results], JSON_PRETTY_PRINT); 

Last questions

how do i remove the switch on my home screen?
how to edit the JS date and time to update atuomatically?
How to utilize data stored in a multidimensional array
Powermockito not mocking URL constructor in URI.toURL() method
Android Bluetooth LE Scanner only scans when phone's Location is turned on in some devices
docker wordpress container can't connect to mysql container
How can I declare a number in java that is more than 64-bits? [duplicate]
Optaplanner solutionClass entityCollectionProperty should never return null error when simple JSON object passed to controller
Anylogic, get the time a pedestrain is in a queue
How do I fix this syntax issue with my .flex file?
Optimizing query in PHP
How to find the highest number of a column and print two columns of that row in R?
Ideas on “Error: Type is referenced as an interface from”?
JCIFS SmbFile.exists() and SmbFile.isDirectory() return false when it exists and I can listFiles()
PHP total order
Laravel booking system design
neural net - undefined column selected
How to indicate y axis does not start from 0 in ggplot?
Fragments in backStack
Spinner how to change the data