· 6 years ago · May 08, 2019, 04:50 PM
1<?php
2/**
3* File Name: ssql_functions.php
4* Author: James Flournoy
5*/
6
7 /** My function to display the simple_campus table sorted by id */
8function showCampusById(){
9
10 /** Required to make use of the wpdb Class */
11 global $wpdb;
12
13 /** Query the database */
14 $query = $wpdb->prepare('SELECT * FROM %1$s ORDER BY %2$s ASC', 'simple_campus', 'id');
15 $campuses = $wpdb->get_results($query);
16
17 /** Check for results */
18 if(!empty($campuses)) :
19 echo '<p><table>
20 <tr><th>id</th><th>campus_name_abbr</th></tr>';
21
22 /** Echo each campus id and campus_name_abbr */
23 foreach ( $campuses as $campus ){
24 echo "<tr><td>$campus->id</td><td>$campus->campus_name_abbr</td></tr>";
25 };
26
27 echo '</table></p>'."\n\n";
28 endif;
29}
30
31 /** My function to display the simple_campus table sorted by campus_name_abbr */
32function showCampusByName(){
33
34 /** Required to make use of the wpdb Class */
35 global $wpdb;
36
37 /** Query the database */
38 $query = $wpdb->prepare('SELECT * FROM %1$s ORDER BY %2$s ASC', 'simple_campus', 'campus_name_abbr');
39 $campuses = $wpdb->get_results($query);
40
41 /** Check for results */
42 if(!empty($campuses)) :
43 echo '<p><table>
44 <tr><th>id</th><th>campus_name_abbr</th></tr>';
45
46 /** Echo each campus id and campus_name_abbr */
47 foreach ( $campuses as $campus ){
48 echo "<tr><td>$campus->id</td><td>$campus->campus_name_abbr</td></tr>";
49 };
50
51 echo '</table></p>'."\n\n";
52 endif;
53}
54
55 /** Function to display my tables with prefix 'simple' */
56function showTables(){
57
58 /** Required to use WPDB class */
59 global $wpdb;
60 $count = 0;
61
62 /** Query the database for all tables */
63 $my_tables = $wpdb->get_results("SHOW TABLES",ARRAY_N);
64
65 /** Counts tables with prefix 'simple' */
66 foreach ( $my_tables as $table ){
67 $str = $table[0];
68 $length = strlen($str);
69 $prefix = substr($str, 0, $length-7);
70
71 if ($prefix == "simple"){
72 $count = $count + 1;
73 }
74 }
75 /** Echos the number of tables to the content */
76 echo "Table count: $count</br>";
77
78 /** Echos the names tables with prefix 'simple' to the content */
79 foreach ( $my_tables as $table ){
80 $str = $table[0];
81 $length = strlen($str);
82 $prefix = substr($str, 0, $length-7);
83
84 if ($prefix == "simple"){
85 echo "$table[0]</br>";
86 }
87 }
88}
89
90 /** My function to show the users */
91function showUsers(){
92
93 /** Required to use WPDB class */
94 global $wpdb;
95 $count = 0;
96
97 /** Query the database for users */
98 $myquery = "SELECT * FROM $wpdb->users";
99 $users = $wpdb->get_results( $myquery );
100
101
102 /** Counts the users */
103 foreach ( $users as $user ) {
104 $count = $count + 1;
105 }
106
107 /** Displays the user count */
108 echo "User count: $count</br>";
109
110 /** Displays all the user names */
111 foreach ( $users as $user ) {
112 echo "{$user->user_login}</br>";
113 $count = $count + 1;
114 }
115}
116
117 /** My function to show the campuses */
118function showCampus(){
119
120 /** Required to use WPDB class */
121 global $wpdb;
122 $count = 0;
123
124 /** Query the database for all rows in the simple_campus table, sorted by campus_name_abbr */
125 $myquery = "SELECT * FROM simple_campus ORDER BY campus_name_abbr ASC";
126 $campuses = $wpdb->get_results($myquery);
127
128 /** Counts the campuses */
129 foreach ($campuses as $campus) {
130 $count = $count + 1;
131 }
132
133 /** Displays the count */
134 echo "Campus count: $count</br>";
135
136 /** Displays the campuses as a list */
137 foreach ($campuses as $campus) {
138 echo "$campus->campus_name_abbr ";
139 }
140}
141
142 /** My function to select a campuses and display its id and abbreviation */
143function selectCampus(){
144
145 /** Required to make use of the wpdb Class */
146 global $wpdb;
147
148 /** Query the database */
149 $query = $wpdb->prepare('SELECT * FROM %1$s ORDER BY %2$s ASC', 'simple_campus', 'campus_name_abbr');
150 $results = $wpdb->get_results($query);
151
152 /** Check for $results */
153 if(!empty($results)) :
154
155 /** Loop through the $results and add each as a dropdown option */
156 $options = '';
157 foreach($results as $result) :
158
159 $options.= sprintf("\t".'<option value="%1$d">%2$s</option>'."\n", $result->id,$result->campus_name_abbr);
160
161 endforeach;
162
163 /** Output the dropdown */
164 echo '<select id="my-select">'."\n";
165 echo $options;
166
167 /** Using JavaScript to identify and display the selection in drop down */
168 echo '</select>
169 <button onclick="campusSelect()">Try it</button>
170 <p><table>
171 <tr><th>id</th><th>campus_name_abbr</th></tr>
172 <tr><td id="selection-id"></td><td id="selection-campus-abbr"></td></tr>
173 </table></p>'."\n\n";
174 endif;
175}
176
177 /** My function to enter a campus name for adding */
178function addCampus() {
179
180 /** First calls showCampus() to display existing campuses */
181 showCampus();
182
183 /** Using JavaScript to identify controls, sends input to $_POST, and links to campus-added page on click */
184 echo '<form action="../campus-added" method="post">Campus Abbreviation: <input type="text" name="campus-name" autofocus><input type="submit"></form>';
185
186}
187
188 /** My function to add the campus */
189function campusAdded(){
190
191 /** Required to make use of the wpdb Class */
192 global $wpdb;
193
194 /** If the data (campus_name_abbr) is received is parsed and formatted */
195 if ( isset($_POST['campus-name'])) {
196
197 $newCampus = $_POST['campus-name'];
198 $newCampus = preg_replace( "/[^a-z]/i", "", $newCampus );
199 $newCampus = strtoupper("$newCampus");
200
201 /** Requiring campus_name_abbr be less than 12 and at least 1 character */
202 if ( (strlen($newCampus) > 12) || (strlen($newCampus) < 1) ) {
203
204 echo 'Entry should be at least one and no more than 12 letters';
205
206 } else {
207
208 /** Query the database */
209 $wpdb->insert(
210 simple_campus,
211 array(
212 'campus_name_abbr' => "$newCampus",
213 )
214 );
215
216 /** Links back to addCampus() so the user can quickly add another */
217 addCampus();
218
219 /** Identifies the new campus by id and campus_name_abbr */
220 echo "</br>$newCampus inserted into row $wpdb->insert_id";
221 }
222 }
223}
224
225 /** My function to add the modify a campus */
226function modifyCampus(){
227
228 /** Required to make use of the wpdb Class */
229 global $wpdb;
230
231 /** Query the database */
232 $query = $wpdb->prepare('SELECT * FROM %1$s ORDER BY %2$s ASC', 'simple_campus', 'campus_name_abbr');
233 $results = $wpdb->get_results($query);
234
235 /** Check for $results */
236 if(!empty($results)) :
237
238 /** Loop through the $results and add each as a dropdown option */
239 $options = '';
240 foreach($results as $result) :
241
242 $options.= sprintf("\t".'<option value="%1$d">%2$s</option>'."\n", $result->id,$result->campus_name_abbr);
243
244 endforeach;
245
246 /** Output the dropdown */
247 echo '<select id="my-select">'."\n";
248 echo $options;
249 echo '</select><button onclick="campusSelectValue()">Try it</button><p id="selection-id"></p>'."\n\n";
250 endif;
251}
252
253 /** My function make the simple_campus table */
254function makeCampusTable() {
255
256 /** Required to make use of the wpdb Class */
257 global $wpdb;
258 $charset_collate = $wpdb->get_charset_collate();
259
260 /** Format the query */
261 $sql = "CREATE TABLE IF NOT EXISTS simple_campus (
262 id int(11) NOT NULL AUTO_INCREMENT,
263 campus_name_abbr varchar(12) NOT NULL,
264 PRIMARY KEY (id)
265 ) $charset_collate;";
266
267 /** Required to make use of the dbDelta() function */
268 require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
269
270 /** Query the database */
271 dbDelta( $sql );
272}
273
274 /** My function make the simple_campus table */
275function dropCampusTable() {
276
277 /** Required to make use of the wpdb Class */
278 global $wpdb;
279
280 /** Format the query */
281 $sql = "DROP TABLE IF EXISTS simple_campus";
282
283 /** Query the database */
284 $wpdb->query( $sql );
285}
286?>