1: <?php
2: /*
3: * The MIT License
4: *
5: * Copyright 2017 David Schoenbauer <dschoenbauer@gmail.com>.
6: *
7: * Permission is hereby granted, free of charge, to any person obtaining a copy
8: * of this software and associated documentation files (the "Software"), to deal
9: * in the Software without restriction, including without limitation the rights
10: * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
11: * copies of the Software, and to permit persons to whom the Software is
12: * furnished to do so, subject to the following conditions:
13: *
14: * The above copyright notice and this permission notice shall be included in
15: * all copies or substantial portions of the Software.
16: *
17: * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
18: * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
19: * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
20: * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
21: * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
22: * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
23: * THE SOFTWARE.
24: */
25: namespace DSchoenbauer\Sql\Command;
26:
27: use DSchoenbauer\Sql\Exception\ExecutionErrorException;
28: use DSchoenbauer\Sql\Where\WhereStatementInterface;
29: use PDO;
30: use PDOStatement;
31:
32: /**
33: * retrieves data from a PDO connected resource
34: * @author David Schoenbauer <dschoenbauer@gmail.com>
35: */
36: class Select implements CommandInterface
37: {
38:
39: private $table;
40: private $fields = ['*'];
41: private $data = [];
42: private $fetchStyle = \PDO::FETCH_ASSOC;
43: private $fetchFlat = false;
44: private $defaultValue = [];
45:
46: use WhereTrait;
47:
48: /**
49: * retrieves data from a PDO connected resource
50: * @param string $table name of the table that houses the data
51: * @param array $fields optional default value: empty array - defines which
52: * fields are returned if no fields defined a star will be used
53: * @param WhereStatementInterface $where optional default value: null -
54: * object used to limit the returned results
55: * @param integer $fetchStyle optional default value: PDO::FETCH_ASSOC -
56: * sets how the PDO statement will return records
57: * @param boolean $fetchFlat optional default value: false - true will
58: * return one record, false will return all records
59: * @param mixed $defaultValue optional default value: empty array -
60: * value to be returned on query failure
61: * @return Select the select object responsible for retrieving records
62: * @since v1.0.0
63: */
64: public function __construct(
65: $table,
66: $fields = [],
67: WhereStatementInterface $where = null,
68: $fetchStyle = \PDO::FETCH_ASSOC,
69: $fetchFlat = false,
70: $defaultValue = []
71: ) {
72:
73:
74: $this->setTable($table)
75: ->setFields($fields)
76: ->setWhere($where)
77: ->setFetchStyle($fetchStyle)
78: ->setFetchFlat($fetchFlat)
79: ->setDefaultValue($defaultValue);
80: }
81:
82: /**
83: * Runs a query and returns the result of the SQL
84: * @param PDO $pdo a PDO connection object
85: * @return mixed with return the result set as defined by fetchStyle
86: * @throws ExecutionErrorException on SQL error with the message of the exception
87: * @since v1.0.0
88: */
89: public function execute(PDO $pdo)
90: {
91: try {
92: $stmt = $pdo->prepare($this->getSql());
93: $this->statementExecute($stmt, $this->getWhereData());
94: $data = $this->fetchData(
95: $stmt,
96: $this->getFetchFlat(),
97: $this->getFetchStyle()
98: );
99: $this->setData($data ?: $this->getDefaultValue());
100: return $this->getData();
101: } catch (\Exception $exc) {
102: throw new ExecutionErrorException($exc->getMessage());
103: }
104: }
105:
106: /**
107: * Runs the sql statement inserting the query's data
108: * @param PDOStatement $stmt PDO statement of a prepared SQL statement
109: * @param array $whereData data to be used to fill out a where statement
110: * @return bool true query succeeds, false there was an error executing the query
111: * @since v1.0.0
112: */
113: protected function statementExecute(PDOStatement $stmt, array $whereData)
114: {
115: if (count($whereData) > 0) {
116: return $stmt->execute($whereData);
117: }
118: return $stmt->execute();
119: }
120:
121: /**
122: * Fetches the data from PDO resource
123: * @param PDOStatement $stmt PDO statement of a prepared SQL statement
124: * @param bool $fetchFlat true returns one record, false returns all records
125: * @param integer $fetchStyle a \PDO::FETCH_* variable defining the format of
126: * the returned object
127: * @return array returns the results of the query
128: * @since v1.0.0
129: */
130: protected function fetchData(PDOStatement $stmt, $fetchFlat, $fetchStyle)
131: {
132: if ($fetchFlat) {
133: return $stmt->fetch($fetchStyle);
134: }
135: return $stmt->fetchAll($fetchStyle);
136: }
137:
138: /**
139: * returns a PDO SQL string that has parameter syntax
140: * @return string
141: * @since v1.0.0
142: */
143: public function getSql()
144: {
145: $sqlTemplate = "SELECT %s FROM %s %s";
146: $fieldsCompiled = implode(',', $this->getFields());
147: return trim(sprintf(
148: $sqlTemplate,
149: $fieldsCompiled,
150: $this->getTable(),
151: $this->getWhereStatement()
152: ));
153: }
154:
155: /**
156: * acts as a cache to house ran queries
157: * @param array $data data to be stored
158: * @return Select for method chaining
159: * @since v1.0.0
160: */
161: public function setData(array $data)
162: {
163: $this->data = $data;
164: return $this;
165: }
166:
167: /**
168: * Acts as a cache array that holds the data returned from a query
169: * @return mixed
170: * @since v1.0.0
171: */
172: public function getData()
173: {
174: return $this->data;
175: }
176:
177: /**
178: * retrieves the table with which you wish to select from
179: * @return string table with which you wish to select from
180: * @since v1.0.0
181: */
182: public function getTable()
183: {
184: return $this->table;
185: }
186:
187: /**
188: * defines a table with which you wish to append to
189: * @param string $table a table with which you wish to append to
190: * @return Select for method chaining
191: * @since v1.0.0
192: */
193: public function setTable($table)
194: {
195: $this->table = $table;
196: return $this;
197: }
198:
199: /**
200: * Returns the fields to be returned, if no fields defined all fields are returned
201: * @return array
202: * @since v1.0.0
203: */
204: public function getFields()
205: {
206: return $this->fields;
207: }
208:
209: /**
210: * Defines the fields to be returned, if no fields defined all fields are returned
211: * @param array $fields
212: * @return Select for method chaining
213: * @since v1.0.0
214: */
215: public function setFields(array $fields = null)
216: {
217: $this->fields = $fields ?: ["*"];
218: return $this;
219: }
220:
221: /**
222: * defines how data is returned
223: * @return int $fetchStyle one of the PDO::FETCH_*
224: * @since v1.0.0
225: */
226: public function getFetchStyle()
227: {
228: return $this->fetchStyle;
229: }
230:
231: /**
232: * used to define how data is returned
233: * @param int $fetchStyle one of the PDO::FETCH_*
234: * @return Select for method chaining
235: * @since v1.0.0
236: */
237: public function setFetchStyle($fetchStyle)
238: {
239: $this->fetchStyle = $fetchStyle;
240: return $this;
241: }
242:
243: /**
244: * sets if one or many records will be returned.
245: * @return bool true for one record, false for all records
246: * @since v1.0.0
247: */
248: public function getFetchFlat()
249: {
250: return $this->fetchFlat;
251: }
252:
253: /**
254: * sets if one or many records will be returned.
255: * @param boolean $fetchFlat optional default value: false - true will
256: * return one record, false will return all records
257: * @return Select for method chaining
258: * @since v1.0.0
259: */
260: public function setFetchFlat($fetchFlat = true)
261: {
262: $this->fetchFlat = $fetchFlat;
263: return $this;
264: }
265:
266: /**
267: * Value to be returned if no data is found or query fails
268: * @return mixed return the value used when the query returns false
269: * @since v1.0.0
270: */
271: public function getDefaultValue()
272: {
273: return $this->defaultValue;
274: }
275:
276: /**
277: * Value to be returned if no data is found or query fails
278: * @param mixed $defaultValue optional default value: empty array - value to
279: * be returned on query failure
280: * @return Select for method chaining
281: * @since v1.0.0
282: */
283: public function setDefaultValue($defaultValue = [])
284: {
285: $this->defaultValue = $defaultValue;
286: return $this;
287: }
288: }
289: