shortcode-variables / includes / db.php

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

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