shortcode-variables / includes / db.php

db.php in Snippet Shortcodes 2.0, at includes/db.php

293 lines 5.5 KB

Raw Download Zip

1	<?php
2
3	defined('ABSPATH') or die('Jog on!');
4
5	/**
6	* Build database table
7	*/
8	function sh_cd_create_database_table() {
9
10	global $wpdb;
11
12	$table_name = $wpdb->prefix . SH_CD_TABLE;
13
14	$charset_collate = $wpdb->get_charset_collate();
15
16	$sql = "CREATE TABLE $table_name (
17	id mediumint(9) NOT NULL AUTO_INCREMENT,
18	slug varchar(100) NOT NULL,
19	data text,
20	disabled bit default 0,
21	UNIQUE KEY id (id)
22	) $charset_collate;";
23
24	require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
25
26	dbDelta( $sql );
27	}
28
29	/**
30	* Fetch all Shortcodes
31	*
32	* @return bool
33	*/
34	function sh_cd_db_shortcodes_all() {
35
36	global $wpdb;
37
38	return $wpdb->get_results( 'SELECT * FROM ' . $wpdb->prefix . SH_CD_TABLE . ' order by slug asc', ARRAY_A );
39	}
40
41	/**
42	* Fetch a shortcode by ID (mainly used for quick lookups in admin)
43	*
44	* @param $id
45	*
46	* @return mixed
47	*/
48	function sh_cd_db_shortcodes_by_id( $id ) {
49
50	global $wpdb;
51
52	$sql = $wpdb->prepare('SELECT id, slug, data, disabled FROM ' . $wpdb->prefix . SH_CD_TABLE . ' where id = %d', $id);
53
54	return $wpdb->get_row( $sql, ARRAY_A );
55	}
56
57	/**
58	* Fetch the content of the shortcode by slug
59	*
60	* @param $slug
61	*
62	* @return null\|string
63	*/
64	function sh_cd_db_shortcodes_by_slug( $slug ) {
65
66	global $wpdb;
67
68	$sql = $wpdb->prepare('SELECT data FROM ' . $wpdb->prefix . SH_CD_TABLE . ' where slug = %s and disabled <> 1', $slug);
69
70	$row = $wpdb->get_var( $sql );
71
72	return ( false === empty( $row ) ? stripslashes( $row ) : NULL );
73	}
74
75	/**
76	* Get a Shortcode's slug from the slug ID
77	*
78	* @param $id
79	*
80	* @return bool
81	*/
82	function sh_cd_db_shortcodes_get_slug_by_id( $id ) {
83
84	global $wpdb;
85
86	$sql = $wpdb->prepare('SELECT slug FROM ' . $wpdb->prefix . SH_CD_TABLE . ' where id = %d', $id);
87
88	return $wpdb->get_var( $sql );
89	}
90
91	/**
92	* Save / Insert a shortcode
93	*
94	* @param $shortcode
95	*
96	* @return bool
97	*/
98	function sh_cd_db_shortcodes_save( $shortcode ) {
99
100	if ( false === is_admin() ) {
101	return false;
102	}
103
104	$shortcode = wp_parse_args( $shortcode, [
105	'id' => NULL,
106	'slug' => NULL,
107	'data' => NULL,
108	'disabled' => 0
109	]);
110
111	// We need either a slug or an ID
112	if ( true === empty( $shortcode['slug'] ) && true === empty( $shortcode['id'] ) ) {
113	return false;
114	}
115
116	$shortcode['disabled'] = (int) $shortcode['disabled'];
117
118	global $wpdb;
119
120	$result = false;
121
122	// Updating an existing shortcode?
123	if ( false === empty( $shortcode['id'] ) && true === is_numeric( $shortcode['id'] ) ){
124
125	// Once set, a slug cannot be updated.
126	unset( $shortcode['slug'] );
127
128	$formats = sh_cd_db_get_formats( $shortcode );
129
130	$result = $wpdb->update(
131	$wpdb->prefix . SH_CD_TABLE,
132	$shortcode,
133	[ 'id' => $shortcode['id'] ],
134	$formats,
135	[ '%d' ]
136	);
137
138	sh_cd_cache_delete_by_slug_or_key( $shortcode['id'] );
139
140	// Adding a new shortcode
141	} else {
142
143	unset( $shortcode['id'] );
144
145	// Ensure slug is santised and unique
146	$shortcode['slug'] = sh_cd_slug_generate( $shortcode['slug'] );
147
148	$formats = sh_cd_db_get_formats( $shortcode );
149
150	$result = $wpdb->insert(
151	$wpdb->prefix . SH_CD_TABLE,
152	$shortcode,
153	$formats
154	);
155
156	// It's an insert, so there should be no cache... however, just a wee sanity check in case
157	// a shortcode with the same slug previously exists.
158	sh_cd_cache_delete_by_slug_or_key( $shortcode['slug'] );
159	}
160
161	return ( false !== $result );
162	}
163
164	/**
165	* Update a shortcode's status
166	*
167	* @param $shortcode
168	*
169	* @return bool
170	*/
171	function sh_cd_db_shortcodes_update_status( $id, $status ) {
172
173	if ( false === is_admin() ) {
174	return false;
175	}
176
177	global $wpdb;
178
179	$result = $wpdb->update(
180	$wpdb->prefix . SH_CD_TABLE,
181	[ 'disabled' => $status ],
182	[ 'id' => $id ],
183	[ '%d' ],
184	[ '%d' ]
185	);
186
187	sh_cd_cache_delete_by_slug_or_key( $id );
188
189	return ( false !== $result );
190	}
191
192	/**
193	* Update a shortcode's content
194	*
195	* @param $shortcode
196	*
197	* @return bool
198	*/
199	function sh_cd_db_shortcodes_update_content( $id, $data ) {
200
201	if ( false === is_admin() ) {
202	return false;
203	}
204
205	global $wpdb;
206
207	$result = $wpdb->update(
208	$wpdb->prefix . SH_CD_TABLE,
209	[ 'data' => $data ],
210	[ 'id' => $id ],
211	[ '%s' ],
212	[ '%d' ]
213	);
214
215	sh_cd_cache_delete_by_slug_or_key( $id );
216
217	return ( false !== $result );
218	}
219
220
221	/**
222	* Delete a shortcode
223	*
224	* @param $id
225	*
226	* @return bool
227	*/
228	function sh_cd_db_shortcodes_delete( $id ) {
229
230	if ( false === is_admin() \|\| false === is_numeric( $id ) ) {
231	return false;
232	}
233
234	global $wpdb;
235
236	// Clear cached version
237	sh_cd_cache_delete_by_slug_or_key( $id );
238
239	$result = $wpdb->delete( $wpdb->prefix . SH_CD_TABLE, [ 'id' => $id ], [ '%d' ] );
240
241	return ( false !== $result );
242	}
243
244	/**
245	* For a given key value array, look up and return the expected MySQL data formats.
246	*
247	* @param $data
248	*
249	* @return array
250	*/
251	function sh_cd_db_get_formats( $data ) {
252
253	$lookup = [
254	'id' => '%d',
255	'slug' => '%s',
256	'data' => '%s',
257	'disabled' => '%d'
258	];
259
260	$formats = [];
261
262	foreach ( $data as $key => $value ) {
263
264	if ( false === empty( $lookup[ $key ] ) ) {
265	$formats[] = $lookup[ $key ];
266	}
267	}
268
269	return $formats;
270	}
271
272	/**
273	* Check if the slug already exists
274	*
275	* @param $slug
276	*
277	* @return bool
278	*/
279	function sh_cd_slug_is_unique( $slug ) {
280
281	if ( true === empty( $slug ) ) {
282	return false;
283	}
284
285	global $wpdb;
286
287	$sql = $wpdb->prepare( 'SELECT count(slug) FROM ' . $wpdb->prefix . SH_CD_TABLE . ' where slug = %s', $slug );
288
289	$row = $wpdb->get_var( $sql );
290
291	return ( empty( $row ) );
292	}
293