making a model from table mySQL with PDO

Question

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.

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.

I hope I understand you correctly but I believe you are wanting to use the GROUP_CONCAT mysql function. However you are going to need to set this in your get_view function but you've specifically said you don't want too. I'm not sure if there is another way of doing it? You could try doing a sub query with GROUP_CONCAT and then only select the field if there is data present? — Ukuser32, Dec 09 '15 at 09:34
Yes @Ukuser32 it looks like this may work... could you offer up a solution, I am not sure about the implementation. Thank you — AlphaG33k, Dec 10 '15 at 23:52

score 1 · Accepted Answer · answered Dec 14 '15 at 11:15

1

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?

answered Dec 14 '15 at 11:15

Ukuser32

2,147
2
22
32

I was going to try it now however I need to use a select * because the column names may be unique from view table to view table depending on the view that it is in (maybe sub_nav_links for one and cta_content for another etc.) I would prefer to not have to have to use the names of the cols in my query as each partial view will have maybe 20-30 columns at most, some parts with a common namespace (like heading) while as stated some not. Oh and thank you for the help so far! – AlphaG33k Dec 15 '15 at 03:20
I'm not sure if thats possible? I think you're going to have to do some coding somewhere along the lines. You could compile an array with null values and then just clean it with array_filter() and only pass in the first parameter. That way you don't have to worry about null values? If you were to store all the fields in PHP from the table (Do a SELECT Columns from table type query) that might help. – Ukuser32 Dec 15 '15 at 09:12

making a model from table mySQL with PDO

1 Answers1