INSERT INTO, UPDATE and DELETE with add_meta_box WordPress
- SOLVED
Hi,
I would like customize media-tags plugin to fill a sql table.
So when I insert, remove or update the tags from media library, and I click on "Update" button, the system must also fill a specific table.
Indeed the system, not just insert the tag as the default, but must also fill a table called ngg_pictures with specific values.
For example:
if I add to an image the tag "shoes", the system must add the shoes tag in post-meta table as default and execute this query:
INSERT INTO wp_ngg_pictures (image_slug, post_id, galleryid, filename, description, alttext, imagedate) VALUES ('image_slug', 'post_id', '1', 'filename', 'description', 'alttext', 'imagedate');
Must have a similar behavior when I update or delete some tags (UPDATE and DELETE query).
To understand what I want, here's an example of code that I wrote (not working).
function mediatags_metaboxes() {
add_meta_box(
'tagsdiv-' . MEDIA_TAGS_TAXONOMY,
__( 'Media-Tags', MEDIA_TAGS_I18N_DOMAIN ),
'my_metabox_callback',
'attachment',
'side',
'core',
array( 'taxonomy' => MEDIA_TAGS_TAXONOMY
));
}
function my_metabox_callback ( $post, $metabox ) {
// Nextgen gallery add image to database
$post_id = $post->ID;
$result_post = mysql_query("SELECT * FROM $wpdb->posts WHERE ID = $post_id");
$result_meta = mysql_query("SELECT meta_value FROM $wpdb->postmeta
WHERE post_id = $post_id
AND meta_key = '_wp_attachment_image_alt'")
or die(mysql_error());
while($test = mysql_fetch_array($result_meta))
{
$meta_value = $test['meta_value'];
}
while($test = mysql_fetch_array($result_post))
{
$image_slug = $test['post_title'];
$galleryid = '1';
$filename = $test['post_name'];
$description = $test['post_content'];
$alttext = $test['post_content'];
$description = $test['post_content'];
$post_date = $test['post_date'];
$description = $test['post_content'];
$post_date = $test['post_date'];
mysql_query("INSERT INTO $wpdb->ngg_pictures
(image_slug, post_id, galleryid, filename, description, alttext, imagedate)
VALUES
('$image_slug', '$post_id', '$galleryid', '$filename', '$description', '$meta_value', '$post_date');")
or die(mysql_error());
}
}
The plugin:
[[LINK href="https://wordpress.org/plugins/media-tags/"]]Media Tags[[/LINK]]
I think that is necessary customize the file called mediatags_admin.php inside plugin root directory on function mediatags_metaboxes().
Answers (2)
Reigel Gallarde answers:
hi, you don't need to edit the plugin... just paste this in your functions.php file.
function create_ngg_pictures_table(){
global $wpdb;
//create the name of the table including the wordpress prefix (wp_ etc)
$search_table = $wpdb->prefix . "ngg_pictures";
//$wpdb->show_errors();
//check if there are any tables of that name already
if($wpdb->get_var("show tables like '$search_table'") !== $search_table) {
//create your sql
$sql = "CREATE TABLE ". $search_table . " (
ngg_pictures_id mediumint(12) NOT NULL AUTO_INCREMENT,
image_slug text,
post_id mediumint(12),
galleryid mediumint(12),
filename text,
description text,
alttext text,
imagedate VARCHAR (20),
UNIQUE KEY ngg_pictures_id (ngg_pictures_id));";
} else {
return; // do nothing if table already exists..
}
//include the wordpress db functions
require_once(ABSPATH . 'wp-admin/upgrade-functions.php');
dbDelta($sql);
//register the new table with the wpdb object
if (!isset($wpdb->ngg_pictures))
{
$wpdb->ngg_pictures = $search_table;
//add the shortcut so you can use $wpdb->ngg_pictures
$wpdb->tables[] = str_replace($wpdb->prefix, '', $search_table);
}
}
/**
* When the post is saved....
*
* @param int $post_id The ID of the post being saved.
*/
function attachment_fields_to_save( $post,$attachment ) {
global $wpdb;
create_ngg_pictures_table(); // check and create the table if it does not exists...
$ngg_pictures = $wpdb->prefix . "ngg_pictures";
$wpdb->show_errors();
// If this is an autosave or ajax, our form has not been submitted, so we don't want to do anything.
if (( defined( 'DOING_AUTOSAVE' ) && DOING_AUTOSAVE ) || ( defined( 'DOING_AJAX ' ) && DOING_AJAX )) {
return;
}
$post_id = $post['post_ID'];
$getpost = (array) get_post($post_id); // let's get the post cause current post data only contains some data that are changeable. like it does not have the date.
$image_slug = $post['post_title'];
$galleryid = '1';
$filename = $post['post_name'];
$description = $post['post_content'];
$alttext = $post['post_content'];
$post_date = $post['post_date'];
$wpdb->insert($ngg_pictures, array(
"image_slug" => $image_slug,
"post_id" => $post_id,
"galleryid" => $galleryid,
"filename" => $filename,
"description" => $description,
"alttext" => $alttext,
"imagedate" => $post_date
));
return $post;
}
add_action( 'attachment_fields_to_save', 'attachment_fields_to_save',11,2 );
/**
* when an attachment is deleted....
*
* @param int $post_id The ID of the attachment being deleted.
*/
function delete_attachment($postid){
global $wpdb;
$ngg_pictures = $wpdb->prefix . "ngg_pictures";
$wpdb->delete( $ngg_pictures , array( 'post_id' => $postid ) );
return $postid;
}
add_action( 'delete_attachment', 'delete_attachment' );
You might re-adjust some data or variables there for your needs...
Tested on wordpress version 4.1
This filters/actions are used in media-tags plugin.
in the file mediatags_admin.php.
we don't have to edit it, we just have to hook our functions in there..
Reigel Gallarde comments:
I have updated my answer.
Reigel Gallarde comments:
the function I did will always insert the values in the database... you may need to change it to "update"... for more $wpdb functions, visit this link ([[LINK href="http://codex.wordpress.org/Class_Reference/wpdb#UPDATE_rows"]]http://codex.wordpress.org/Class_Reference/wpdb#UPDATE_rows[[/LINK]])
the $post parameter in attachment_fields_to_save function has an array like this...
Array
(
[_wpnonce] => 590cd7667b
[_wp_http_referer] => /wordpress/wp-admin/post.php?post=1042&action=edit&message=1
[user_ID] => 1
[action] => editpost
[originalaction] => editpost
[post_author] => 2
[post_type] => attachment
[original_post_status] => inherit
[referredby] => http://localhost/wordpress/wp-admin/post.php?post=1042&action=edit&message=4
[_wp_original_http_referer] => http://localhost/wordpress/wp-admin/post.php?post=1042&action=edit&message=4
[post_ID] => 1042
[meta-box-order-nonce] => d399f2ec62
[closedpostboxesnonce] => 16ebeca34a
[post_title] => The Dark Knight Rises
[samplepermalinknonce] => 750112141b
[excerpt] => Batman
[_wp_attachment_image_alt] => The Dark Knight Rises
[content] =>
[attachment_url] => http://localhost/wordpress/wp-content/uploads/2013/03/the-dark-knight-rises.jpg
[original_publish] => Update
[save] => Update
[tax_input] => Array
(
[media-tags] => fsddsfsd,reigel,test
)
[newtag] => Array
(
[media-tags] =>
)
[advanced_view] => 1
[comment_status] => open
[ping_status] => open
[add_comment_nonce] => 2a3b5ab630
[_ajax_fetch_list_nonce] => bf43d7be7c
[post_name] => the-dark-knight-rises
[post_author_override] => 2
[woo_nonce] => c6c1e525af
[post_mime_type] => image/jpeg
[ID] => 1042
[post_content] =>
[post_excerpt] => Batman
[post_status] => inherit
)
and if you'll use get_post, you will have an object like this...
WP_Post Object
(
[ID] => 1042
[post_author] => 2
[post_date] => 2013-03-15 17:27:09
[post_date_gmt] => 2013-03-15 22:27:09
[post_content] =>
[post_title] => The Dark Knight Rises
[post_excerpt] => Batman
[post_status] => inherit
[comment_status] => open
[ping_status] => open
[post_password] =>
[post_name] => the-dark-knight-rises
[to_ping] =>
[pinged] =>
[post_modified] => 2013-03-15 17:27:09
[post_modified_gmt] => 2013-03-15 22:27:09
[post_content_filtered] =>
[post_parent] => 1031
[guid] => http://localhost/wordpress/wp-content/uploads/2013/03/the-dark-knight-rises.jpg
[menu_order] => 0
[post_type] => attachment
[post_mime_type] => image/jpeg
[comment_count] => 0
[filter] => raw
)
Let me know if you have problems.
anjx comments:
Hi Reigel,
There are some irregularity:
1. When I add a tag, the function inserts values, but when I remove the same tag from post, the function reinserts the same value, instead of remove the post from ngg_pictures.
2. I want that the function inserts values into database, only if the image not exists with the same tag. So if the tag is already inserted, the function returns nothing.
3. $filename = $post['post_name']; I would like that $filename have a url of attachment.
Thanks :)
anjx comments:
3. $filename = $post['post_name']; I would like that $filename have a url of attachment.
Example:
$filename = the-dark-knight-rises.jpg;
Reigel Gallarde comments:
I updated all the functions...
function create_ngg_pictures_table(){
global $wpdb;
//create the name of the table including the wordpress prefix (wp_ etc)
$search_table = $wpdb->prefix . "ngg_pictures";
//$wpdb->show_errors();
//check if there are any tables of that name already
if($wpdb->get_var("show tables like '$search_table'") !== $search_table) {
//create your sql
$sql = "CREATE TABLE ". $search_table . " (
ngg_pictures_id mediumint(12) NOT NULL AUTO_INCREMENT,
image_slug text,
post_id mediumint(12),
galleryid mediumint(12),
filename text,
description text,
alttext text,
tag VARCHAR(20),
imagedate datetime DEFAULT '0000-00-00 00:00:00',
UNIQUE (tag, post_id),
UNIQUE KEY ngg_pictures_id (ngg_pictures_id));";
} else {
return; // do nothing if table already exists..
}
//include the wordpress db functions
require_once(ABSPATH . 'wp-admin/upgrade.php');
dbDelta($sql);
//register the new table with the wpdb object
if (!isset($wpdb->ngg_pictures))
{
$wpdb->ngg_pictures = $search_table;
//add the shortcut so you can use $wpdb->ngg_pictures
$wpdb->tables[] = str_replace($wpdb->prefix, '', $search_table);
}
}
/**
* When the post is saved....
*
* @param int $post_id The ID of the post being saved.
*/
function attachment_fields_to_save( $post,$attachment ) {
global $wpdb;
create_ngg_pictures_table(); // check and create the table if it does not exists...
$ngg_pictures = $wpdb->prefix . "ngg_pictures";
// If this is an autosave or ajax, our form has not been submitted, so we don't want to do anything.
if (( defined( 'DOING_AUTOSAVE' ) && DOING_AUTOSAVE ) || ( defined( 'DOING_AJAX ' ) && DOING_AJAX )) {
return;
}
$post_id = $post['post_ID'];
$getpost = (array) get_post($post_id); // let's get the post cause current post data only contains some data that are changeable. like it does not have the date.
$image_slug = $post['post_title'];
$galleryid = '1';
$filename = basename($post['attachment_url']);
$description = $post['post_content'];
$alttext = $post['post_content'];
$post_date = $getpost['post_date'];
$tags = explode(',',$post['tax_input']['media-tags']);
foreach($tags as $tag) {
$tag_exists = $wpdb->get_var( "SELECT tag FROM {$ngg_pictures} WHERE tag='{$tag}' AND post_id={$post_id}" );
$data = array(
"image_slug" => $image_slug,
"galleryid" => $galleryid,
"filename" => $filename,
"description" => $description,
"alttext" => $alttext,
"imagedate" => $post_date
);
$where = array(
"post_id" => $post_id,
"tag" => $tag
);
// check if tag exists;
if ($tag_exists) {
$wpdb->update($ngg_pictures, $data, $where);
} elseif (!empty($tag)) {
$data = array_merge($data,$where);
$wpdb->insert($ngg_pictures, $data);
}
}
// delete none existing tag...
$tags = '\''.str_replace(',','\',\'',$post['tax_input']['media-tags']).'\'';
$wpdb->query("DELETE FROM {$ngg_pictures} WHERE post_id={$post_id} AND tag NOT IN ({$tags})");
return $post;
}
Key notes:
1. I have to change the table. I have added a tag column so we could save and check if tag already exists in database for a particular attachment (using post_id as reference)
2. the function will now check if tag exist then prefer to update the row. I choose to do this because, some data might have been changed, sample the image url.
3. the function will insert the data if tag did not exists in database and can also do remove.
4. $filename now has extension, just like what you wanted.
Please check and let me know if I missed something..
Reigel Gallarde comments:
forgot to paste this part... but this was not changed from the first answer I gave..
just had to paste it now.. you might get confused..
add_action( 'attachment_fields_to_save', 'attachment_fields_to_save',11,2 );
/**
* when an attachment is deleted....
*
* @param int $post_id The ID of the attachment being deleted.
*/
function delete_attachment($postid){
global $wpdb;
$ngg_pictures = $wpdb->prefix . "ngg_pictures";
$wpdb->delete( $ngg_pictures , array( 'post_id' => $postid ) );
return $postid;
}
add_action( 'delete_attachment', 'delete_attachment' );
Reigel Gallarde comments:
add_action( 'attachment_fields_to_save', 'attachment_fields_to_save',11,2 );
/**
* when an attachment is deleted....
*
* @param int $post_id The ID of the attachment being deleted.
*/
function delete_attachment($postid){
global $wpdb;
$ngg_pictures = $wpdb->prefix . "ngg_pictures";
$wpdb->delete( $ngg_pictures , array( 'post_id' => $postid ) );
return $postid;
}
add_action( 'delete_attachment', 'delete_attachment' );
Reigel Gallarde comments:
have you tried the new code?
anjx comments:
Thanks for answers Reigel Gallarde,
ngg_pictures is a table of nextgen gallery that exists already in my DB and by default don't have "tag" attribute.
So I think is necessary an update of ngg_pictures table to add this attribute.
anjx comments:
Or maybe it would be better to use the same system of tags check used by the plugin Nextgen Gallery.
From what I understand, the system tags check Nextgen Gallery uses tablle postmeta, posts and terms.
Reigel Gallarde comments:
it's impossible delete without knowing which tag to delete from ngg_pictures...
can't we alter the table?
anjx comments:
The tags are checked undirectly in table(value): terms(term_id). Now I not understand how Nextgen Gallery match term_id value with image post_id.
At my opinion the best solution isn't to change the existing table, but to use the existing check values.
Do you know how it make, please?
Reigel Gallarde comments:
ahh i see.. can you give me a link of the plugin you are using? and explain what you are trying to do? as to why we are inserting/deleting these values?
anjx comments:
The plugin link:
[[LINK href="https://wordpress.org/plugins/nextgen-gallery/"]][[/LINK]]
This plugin don't have possibility to chose and add a singles images inside Nextgen Gallery.
So I would implement a function or a plugin that allow me to add manually the choices images by tags.
So If an images have tag, this will appear inside Nextgen Gallery image list, if an image don't have any tag, this will not appear inside Nextegen Gallery.
So I would like to cross the functionality of Media Tags plugin(or similar plugin) with Nextgen Gallery plugin.
Can you help me?
anjx comments:
www.wordpress.org/plugins/nextgen-gallery/
Reigel Gallarde comments:
I don't understand. I installed ngg and it has add images function... also, I found out that ngg stores/fetch their images from it's own created folder. hmm...
attached is an screenshot of ngg menu...