phpmysqlpdomustachemustache.php

making a model from table mySQL with PDO


I am developing an application for fun, and I am purposefully NOT using any frameworks aside from mustache for PHP and JS, etc on either the client or server side of the modest application.

Each view has a table which holds all data used for each one. I wish to execute a query that selects each column in the table and makes it available to my mustache template.

I do NOT want to change methods in order to access any particular column at runtime. fetchAll gives me a ton of null fields in the data returned that I do not want while fetch() gives me a nice JSON structure containing all of the proper data aside from a column containing more than one row of data only shows for the first row/instance.

Here is my PHP method:

public function get_view($view_name , $out_type = "raw"){

                // Col names may vary from view table to view table

                $statement = $this->prep_sql("SELECT * FROM `$view_name`");

                $statement->execute(array());

                $view_data = $statement->fetch(); // I know fetchAll will work for arrays but how then will my mustache template bind to data returned from columns with more than one row?

                return (strtoupper($out_type) === "JSON" ? json_encode($view_data) :  $view_data);

            }

What I want is to ignore any fields with null or empty as if they do not exist and form an array with columns in the database with multiple rows of data. I also realize that using fetch for single instances like a page title and fetchAll would work for multiple rows however I want to eliminate this.

This is what my table <code>view_data_main</code> looks like:


In order for the templates to bind correctly the data output must look similar to:

 {
     module_name: 'main', 
     module_title: 'Main', 
     module_images: ['http://...', 'http://...', 'http://...'], 
     module_scripts: ['http://...','http://...','http://...',]
}

Instead with fetchAll I get

[{
    module_name: 'main',
    module_title: 'Main',
    module_images: 'http://...', // 1
    module_scripts: 'http://...' // 1
  }, {
    module_name: null,
    module_title: null,
    module_images: 'http://..', // 2
    module_scripts: 'http://..' // 2
}];

I am aware SELECT * is not a traditional way to select all of your view data however the number of cols in each view table will be less than 100 max and no tables besides view tables will be accessed using a wildcard select. That said along with the dynamic col names from view to view mean ALOT less code to write. I hope that this doesnt offend anybody :)

Thank you all kindly for the help.


Solution

  • Is this what you are after??

    CREATE TABLE view_data_main (
        module_name VARCHAR(30),
        module_title VARCHAR(30),
        module_images VARCHAR(30),
        module_scripts VARCHAR(30)
    )
    
    INSERT INTO `view_data_main` (module_name,module_title,module_images,module_scripts) VALUES 
    ('welcome','main','http://www.test.com','http://www.test2.com'),
    (NULL,NULL,'http://www.test.com','http://www.test2.com'),
    (NULL,NULL,'http://www.test.com','http://www.test2.com')
    
    SELECT module_name,module_title,GROUP_CONCAT(module_images),GROUP_CONCAT(module_scripts)
    FROM `view_data_main`
    WHERE module_images IS NOT NULL AND module_scripts IS NOT NULL
    GROUP BY CONCAT(module_images,',',module_scripts)
    

    Having re-read the question I don't think the subquery is necessary unless I'm missing something?