0

A lot of thought process went before I decided to post this question. Trying to explain my problem in a simplified format.

I have 2 tables in my mySQL table, one of which is the users and the other one is the questions and answers related to that user.

Simplified Example:

Users

| id | name   | registered_on       |
|----|--------|---------------------|
| 1  | Aaron  | 2017-02-01 00:01:02 |
| 2  | Baron  | 2017-02-01 01:01:02 |
| 3  | Chiron | 2017-02-01 02:01:02 |

Answer Keys

| id | user_id | keyword | value   | created_on          |
|----|---------|---------|---------|---------------------|
| 1  | 1       | gender  | Male    | 2017-02-01 00:01:02 |
| 2  | 1       | age     | 24      | 2017-02-01 00:01:02 |
| 3  | 2       | gender  | Male    | 2017-02-01 00:01:02 |
| 4  | 2       | age     | Unknown | 2017-02-01 00:01:02 |
| 5  | 3       | gender  | God     | 2017-02-01 00:01:02 |

I hope the relation above is clear. So what I wish to achieve is to create a CSV report like this

| name   | gender | age     | registered_on       |
|--------|--------|---------|---------------------|
| Aaron  | Male   | 24      | 2017-02-01 00:01:02 |
| Baron  | Male   | Unknown | 2017-02-01 01:01:02 |
| Chiron | God    | NULL    | 2017-02-01 02:01:02 |

As my research suggests, this can be done in the following ways :

  • Prepared Statements (Cannot use because CodeIgniter does not Support)
  • Paging (The vertical table is the problem)
  • MySQL Pivot Tables (But with Dynamic column names -- feels complicated!)
  • Any other better way that I do not know of

I am thinking about paging but am yet to figure out how it could be used in the case of vertical tables. I would like it if any of you guys have faced the same problem or have some meaningful suggestions! Any help is appreciated!

Arcanyx
  • 852
  • 10
  • 25
  • Why not have a look at CI's documentation for pagination? https://codeigniter.com/user_guide/libraries/pagination.html – Kisaragi Feb 01 '17 at 14:23
  • For this one report you could try [cranking up the php memory limit](http://stackoverflow.com/a/22283701/3585500) or [use PDO and a prepared statement](http://stackoverflow.com/a/31098651/3585500) to get by. – ourmandave Feb 01 '17 at 14:28
  • https://www.codeigniter.com/user_guide/database/results.html?highlight=unbuffered_row#CI_DB_result::unbuffered_row – Atural Feb 01 '17 at 15:02
  • @Kisaragi - Pagination won't be possible since I am generating a CSV report. – Arcanyx Feb 02 '17 at 04:54
  • @ourmandave - Cranking up memory is my last option - since I am focussing on the optimization of the app. PDO looks good , will look into it – Arcanyx Feb 02 '17 at 04:56
  • @sintakonte - will look into unbuffered row – Arcanyx Feb 02 '17 at 04:57

1 Answers1

0

If I understand well your problem, you want a CSV file with all user informations available using the answer keys as columns?

Why not doing this with 2 queries?

First, you can select all different keys in your answer table (select distinct keyword...) Then, create your query with joins looping on your results :

$this->db->select('u.id, u.name, u.registered_on');
$this->db->from('users u');
foreach($keywords as $i_key => $s_keyword){
    $this->db->join('answers_table at'.$i_key, 'at'.$i_key.'.user_id = u.id', 'left');
    $this->db->select('at'.$i_key.'.value as '.$s_keyword);
}
$a_users = $this->db->get()->result();
VeZoul
  • 500
  • 6
  • 19
  • It is a nice suggestion. However, as I said ,I have around 60000 rows in my table and processing that much data runs me out of memory in CodeIgniter's database driver. So I pretty much think paging is my only way out.. and I am already doing what u have suggested :P – Arcanyx Feb 01 '17 at 13:18
  • Not sure the problem comes from the query itself. The memory limit must be when generating CSV file. How do you process that part? – VeZoul Feb 01 '17 at 13:25
  • `Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 256 bytes) in /var/www/html/secretprojectuwouldntwannaknow/system/database/drivers/mysqli/mysqli_result.php on line 168` -- The data I am dealing with has more columns than in the simplified example above.... – Arcanyx Feb 01 '17 at 13:31
  • In that case, maybe doing a count first, then divide that count by 5000 and loop that number of time with `$this->db->limit()` – VeZoul Feb 01 '17 at 13:35
  • Thank you for the suggest, trying that out at the moment ! – Arcanyx Feb 02 '17 at 04:58