WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] Aggregating Usermeta From Blogs To Custom Table (9 posts)

  1. kirkward
    Member
    Posted 1 year ago #

    I am working to build a plugin or an MySQL query to aggregate some blog and user meta into a table created by a plugin. The plugin developer has advised me that the table is a flat table, and I can add data to it.

    I am using code I found through web search (Google) and have verified that I am retrieving the data I want to use by echoing it to screen.

    I have slightly modified the code that displayed the data in an attempt to insert that data into the plugin table wp_store_locator.

    It doesn't work. And as you can understand, because I am posting here, I haven't got the faintest clue on how to make corrections.

    Any suggestions?

    Thanks,
    Kirk

    <?php
    $bcount = get_blog_count();
    
    global $wpdb;
    $blogs = $wpdb->get_results($wpdb->prepare("SELECT * FROM $wpdb->blogs WHERE spam = '0' AND deleted = '0' and archived = '0' and public='1'"));
    if(!empty($blogs)){
        ?><ul class="menu"><?php
        foreach($blogs as $blog){
            $details = get_blog_details($blog->blog_id);
            if($details != false){
                $addr = $details->siteurl;
                $name = $details->blogname;
                $blognum = $details->blog_id;
                if(!(($blog->blog_id == 1)&&($show_main != 1))){
                    ?>
    
                    <li class="menu-item<?php if($counter == get_current_blog_id()){ echo ' current-menu-item';}?>">
                        Blog ID = <?php echo $blognum; ?><br />
                        Blog URL = <?php echo $addr; ?><br />
                        Blog Name = <?php echo $name;?><br />
    <?php
    $user_id_from_email = get_user_id_from_string( get_blog_option($blognum, 'admin_email'));
    $current_site_admin = get_userdata($user_id_from_email);
    
    $kw_fname = (get_user_meta($current_site_admin, 'first_name', true));
    $kw_lname = (get_user_meta($current_site_admin, 'last_name', true));
    $kw_admin_email = get_bloginfo ( 'admin_email' );
    $kw_practice_name = (get_user_meta($current_site_admin, 'practicename', true));
    $kw_office_phone (get_user_meta($current_site_admin, 'officephone', true));
    $kw_office_city = (get_user_meta($current_site_admin, 'officecity', true));
    $kw_office_street = (get_user_meta($current_site_admin, 'officestreet', true));
    $kw_office_state = (get_user_meta($current_site_admin, 'officestateprovince', true));
    $kw_office_zip = (get_user_meta($current_site_admin, 'officepostcode', true)); 
    
                    </li>
    
                    <?php
                }
            }
        }
        ?></ul><?php
    }
    
    $data = array(
        'sl_id' => $blognum,
        'sl_store' => $kw_practice_name,
        'sl_address' => $kw_office_street,
        'sl_city' => $kw_office_city,
        'sl_state' => $kw_office_state,
        'sl_zip' => $kw_office_zip,
        'sl_country' => ,
        'sl_url' => $addr,
        'sl_image' => ,
        'sl_private' => NULL,
        'sl_neat_title' => NULL,
        'sl_pages_url' => NULL,
        'sl_pages_on' => NULL,
        'sl_lastupdated' => current_time('mysql', 1),
        'sl_option_value => NULL
    );
    
    $wpdb->insert(wp_store_locator, $data);
    
    ?>
  2. linux4me2
    Member
    Posted 1 year ago #

    One thing that strikes me right off is that you have some errors in your array declaration for these two lines:

    $data = array(
        'sl_country' => ,
        'sl_image' => ,
    );

    I'm not sure what data type those fields are in your wp_store_locator table, but you'll need to have values there, not just a space, or PHP will choke. For example, if the country field is a VARCHAR field and you don't have a value you should pass either an empty string (two single quotes, no space) or NULL if NULL is allowed for the field in the database. Same thing for the image field, depending on the data type.

    I'd fix that first and see how it works.

  3. kirkward
    Member
    Posted 1 year ago #

    It still did not update the table.

    I thought I had read somewhere that if the table defined the field types, then that would reset the datatype correctly once it was inserted.

    The field types are:

    Field 	Type
    	sl_id     	mediumint(8)
    	sl_store 	varchar(255)
    	sl_address 	varchar(255)
    	sl_address2 	varchar(255)
    	sl_city 	varchar(255)
    	sl_state 	varchar(255)
    	sl_zip   	varchar(255)
    	sl_country 	varchar(255)
    	sl_latitude 	varchar(255)
    	sl_longitude 	varchar(255)
    	sl_tags 	mediumtext
    	sl_description 	text
    	sl_email 	varchar(255)
    	sl_url  	varchar(255)
    	sl_hours 	varchar(255)
    	sl_phone 	varchar(255)
    	sl_image 	varchar(255)
    	sl_private 	varchar(1)
    	sl_neat_title 	varchar(255)
    	sl_linked_postid 	int(11)
    	sl_pages_url 	varchar(255)
    	sl_pages_on 	varchar(1)
    	sl_lastupdated 	timestamp
    	sl_fax  	varchar(255)
    	sl_option_value 	longtext

    Looking at this for the first time, I just noticed that the sl_id field is an autoincrement, which means that it is going to fail as soon as I try to insert something into that field. I was trying to use the blog number as a way of keeping the blogs and the maps in sync with the blogs.

  4. kirkward
    Member
    Posted 1 year ago #

    Okay ... some progress.

    I removed the sl_id from the data array.

    The table was updated, with new rows, but no data was inserted. I noted that the first field, sl_store, was blank, and all the others where I attempted to enter data contained NULL. I did enter 'USA' as the value for sl_country, and it was properly inserted. I used the single apostrophes around USA, and am wondering if it will be okay to put them around the php variables.

    More to come.

  5. linux4me2
    Member
    Posted 1 year ago #

    Yes, trying to insert the blog id in an autoincrement field would cause a problem. You could add a field of type integer and add it there, though if you wanted it.

    You shouldn't need quotes arount the variables; in fact, if you put them there, they won't be interpreted as variables, but as values.

    If the other values are blank, but a record is created, I don't think you have an issue with the query anymore, but with no data in your variables. I'd echo them at each step until you find out where the problem lies. For example, if $user_id_from_email is empty, you're going to get nothing from there on.

  6. kirkward
    Member
    Posted 1 year ago #

    I have added code to echo each of the fields before the wpdb query is run and am using the same variable I echo as values in the array.

    I put the quotes around the variables, and yes, they were interpreted as data, and inserted. Removed the quotes, still didn't work, so I added the names of all the fields, along with quote marks to represent an empty field.

    Now my stuff isn't working again.

    I'll be back when I at least get it to where it echoes the data to the screen.

    Sigh.

  7. linux4me2
    Member
    Posted 1 year ago #

    I don't know if this could be causing a problem or not, but you do have extra parentheses in some of your varable statements:
    $kw_fname = (get_user_meta($current_site_admin, 'first_name', true));
    should really be:
    $kw_fname = get_user_meta($current_site_admin, 'first_name', true);
    For form's sake, you could change that easily and see what happens.

  8. kirkward
    Member
    Posted 1 year ago #

    Progress.

    One record added out of six test records.

    Still experimenting ... but here is the current version of the code:

    <?php
    $bcount = get_blog_count();
    
    global $wpdb;
    $blogs = $wpdb->get_results($wpdb->prepare("SELECT * FROM $wpdb->blogs WHERE spam = '0' AND deleted = '0' and archived = '0' and public='1'"));
    if(!empty($blogs)){
        ?><ul class="menu"><?php
        foreach($blogs as $blog){
            $details = get_blog_details($blog->blog_id);
            if($details != false){
                $addr = $details->siteurl;
                $name = $details->blogname;
                $blognum = $details->blog_id;
                if(!(($blog->blog_id == 1)&&($show_main != 1))){
                    ?>
    
                    <li class="menu-item<?php if($counter == get_current_blog_id()){ echo ' current-menu-item';}?>">
                        Blog ID = <?php echo $blognum; ?><br />
                        Blog URL = <?php echo $addr; ?><br />
                        Blog Name = <?php echo $name;?><br />
    <?php
    $user_id_from_email = get_user_id_from_string( get_blog_option($blognum, 'admin_email'));
    $current_site_admin = get_userdata($user_id_from_email);
    ?>
    <?php $kw_fname = get_user_meta($current_site_admin, 'first_name', true); ?>
    First Name: <?php echo $kw_fname ?><br />
    <?php $kw_lname = get_user_meta($current_site_admin, 'last_name', true); ?>
    Last Name: <?php echo $kw_lname ?><br />
    <?php $kw_admin_email = get_bloginfo ( 'admin_email' ); ?>
    Admin Email: <?php echo $kw_admin_email ?><br />
    <?php $kw_practicename = get_user_meta($current_site_admin, 'practicename', true); ?>
    Practice Name: <?php echo $kw_practicename ?><br />
    <?php $kw_officephone = get_user_meta($current_site_admin, 'officephone', true); ?>
    Office Phone: <?php echo $kw_officephone ?><br />
    <?php $kw_officecity = get_user_meta($current_site_admin, 'officecity', true);?>
    Office City: <?php echo $kw_officecity ?><br />
    <?php $kw_officestreet = get_user_meta($current_site_admin, 'officestreet', true); ?>
    Office Address: <?php echo $kw_officestreet ?><br />
    <?php $kw_officestateprovince = get_user_meta($current_site_admin, 'officestateprovince', true); ?>
    Office State: <?php echo $kw_officestateprovince ?><br />
    <?php $kw_officepostcode = get_user_meta($current_site_admin, 'officepostcode', true); ?>
    Office Zip: <?php echo $kw_officepostcode ?><br /><br /><br />
    
                    </li>
    
                    <?php
                }
            }
        }
        ?></ul><?php
    }
    
    $data = array(
        'sl_store' => $kw_practicename,
        'sl_address' => $kw_officestreet,
        'sl_address2' => '',
        'sl_city' => $kw_officecity,
        'sl_state' => $kw_officestateprovince,
        'sl_zip' => $kw_officepostcode,
        'sl_country' => 'USA',
        'sl_latitude' => '',
        'sl_longitude' => '',
        'sl_tags' => '',
        'sl_description' => '',
        'sl_email' => '',
        'sl_url' => $addr,
        'sl_hours' => '',
        'sl_phone' => '',
        'sl_image' => '',
        'sl_private' => NULL,
        'sl_neat_title' => NULL,
        'sl_linked_postid' => NULL,
        'sl_pages_url' => NULL,
        'sl_pages_on' => NULL,
        'sl_lastupdated' => current_time('mysql', 1),
        'sl_fax' => '',
        'sl_option_value => NULL
    );
    
    $wpdb->insert(wp_store_locator, $data);
    ?>

    I'm thinking that I have the wpdb code placed outside of the foreach loop. Now to find it.

  9. kirkward
    Member
    Posted 1 year ago #

    Okay, got it finally.

    Moved the $wpdb array and query code to above the tag and apparently that put it inside the foreach loop. All sample data was added to the data base.

    Thank you!

    Thank you!

    Thank you!

Topic Closed

This topic has been closed to new replies.

About this Topic