MySQL question

ASKER

I have tried this already, but my syntax is wrong. i was hoping someone could provide me with the correct query

Avinash Zala

what is the query u have created and what error are u getting?

Please post here.

Thanks
Addy

ASKER

I don't have the queries anymore, i deleted them and can't remember what i was trying.

Avinash Zala

ok can u post the relation ship between tables you want to use.

Thanks
Addy

ASKER

how would I do that?

@lvollmer

Here is the code that you are looking for. Try it out and let me know if it worked.

"SELECT wp_users.user_login, wp_users.user_nicename, wp_users.display_name, wp_users.user_email, 
		wp_usermeta.meta_value, wp_usermeta.meta_key,
		blog_homepage.domain
		FROM wp_users 
		LEFT JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id
		LEFT JOIN blog_homepage 
				ON SUBSTRING_INDEX(SUBSTRING_INDEX(wp_usermeta.meta_key, '_', 2), '_', -2) = blog_homepage.blog_id
		WHERE RIGHT(wp_usermeta.meta_key,12) = 'capabilities'
";

//echo $row['domain'];

ASKER

the query executed, but the 'domain' value is Null for every record

Then that means you don't have a value for the domain in the blog_homepage table. If you are unsure, I strongly suggest you take a look at the contents of the blog_homepage table to confirm this. Even better if you can post the contents of the table, I will be able to review the data and help you further.

If you don't know how to how to get the table data, here's quick instructions:
1. You will need to export the table data.
2. Assuming that you have access to phpmyadmin, click on the Database(DB) name in the left column.
3. You will then be shown all the tables on the left column and a summary of these tables on the right.
4. Click on the blog_homepage table in the left column. This will show you the table values in the right. Towards the top of these values (in the right hand column), there will be an "Export" button. Click on it.
5. Doing so will show you "View dump (schema) of table". Towards the bottom of the screen, there's a button called "Go". Click on it.
6. That's it. It shows you the structure of the table and all the values in it. Just copy and paste it here and I will review and help you out.

If you are using some other means to view your database and tables, you will need to follow the instructions given in their help manual to get the data.

Hope that helps. Let me know.

ASKER

excellent, I will do that now - thanks!

ASKER

i attached the first 15 records of the SQL - hope this helps
table.sql

Thanks for the file.

I have taken a look at it and the values exist for the domain column. Then it's possible that you are using incorrect variables names to display data. For example, I used echo $row['domain']; Now this will need to be modified according to the way you are querying the database and retrieving the values. And for that, I will need you to post me the query. I remember that you have posted the core SQL query in your question, but I would need the PHP code. It would be something like the code that I have attached.

So if you can post that code, I think we should be done with the problem (provided that there are no other problems associated with it).

$result = mysql_query( "SELECT ..... ");

while ($rows = mysql_fetch_array($result) )
{
   echo $rows['domain'];
}

ASKER

Is this correct? the file comes up blank

<?php
	require_once('wp-config.php');



$result = mysql_query( "SELECT wp_users.user_login, wp_users.user_nicename, wp_users.display_name, wp_users.user_email, 
		wp_usermeta.meta_value, wp_usermeta.meta_key,
		blog_homepage.domain
		FROM wp_users 
		LEFT JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id
		LEFT JOIN blog_homepage 
				ON SUBSTRING_INDEX(SUBSTRING_INDEX(wp_usermeta.meta_key, '_', 2), '_', -2) = blog_homepage.blog_id
		WHERE RIGHT(wp_usermeta.meta_key,12) = 'capabilities'");

while ($rows = mysql_fetch_array($result) )
{
   echo $rows['domain'];
}

 ?>

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ok I think I know why it's not giving you any results. Just run the script I gave you previously and note down the result. Then after that, remove the where clause and run the script and note down the result i.e just delete the following line from the script and note down the result:
WHERE RIGHT(wp_usermeta.meta_key,12) = 'capabilities'

If you do that, then you should be able to see the output as you wanted.

Just try that as well and let me know.

ASKER

ok I will give this a shot now

Great! I am looking forward for the results. I want us to be all happy happy :)

ASKER

on both tests, I got a blank page:

http://tinyurl.com/2659xqk

I think that something else is going different on your end. The script would have otherwise prompted us the root of the problem. If everything went properly (or even improperly), you should not get to see a blank page. Since neither is the case, maybe while during upload or when the file is uploaded, something is going wrong.

Are you sure that you have uploaded the correct file and replaced all the info that I have asked? If unsure, I suggest you do it again with another file and let me know.

ASKER

when I entered the incorrect database, it does print the error. refresh the page and you will see what I mean. The code is copied exactly and i triple checked the parameters

ASKER

when I run the same code with a different query, it displays data:
http://tinyurl.com/25wkat2

Ok I tried both the links again. Here are the results:

In the first link you gave me I am now able to see the output. The Output is: Cannot select DB
This means that you have entered incorrect parameters in the details that I have asked you to enter (the hostname, password, etc.). Make sure that there are no spaces. It's definitely an issue with the details you have entered here. Please cross check carefully.

In the 2nd link you gave me, I am able to see the output. The Output is:
( I am not releasing the domain name here ) Your domain name. I see it loop for 5 times. So this means it is working here. (looks like you have entered correct information here)

You said that you are running the same code with a different query. What is the same code and what is the different query here?

ASKER

thank you for not pasting the domain name.

I purposely changed the database name on link one to something that was not right so that you could see that the file was there. I have put the correct db name in there now, and it is blank again.

So perhaps there is something wrong with the query?

Link 2 has the same code as link 1, i just used a much simpler query from another file I grabbed.

ASKER

SELECT * FROM wp_67_posts where post_type='post' and post_status = 'publish' ORDER BY post_date DESC LIMIT 5

BTW that is the query used in link 2

Ok, we are going to try something different with both the links then.

Please remove just the queries (just the main sql queries & retain the database connectivity queries) from both the files that you have uploaded and replace it with the code that I am giving you now and try and let me know what the respective output is.

$result = mysql_query( "SELECT wp_users.user_login, wp_users.user_nicename, wp_users.display_name, wp_users.user_email, 
		wp_usermeta.meta_value, wp_usermeta.meta_key
		FROM wp_users 
		LEFT JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id
		");

$num_rows = mysql_num_rows($result);

if( $num_rows == 0 )
{
	echo 'No rows match the selection criteria 1.';	
}
else
{
	echo 'Yes, there is data 1 here<br />';
	while ($rows = mysql_fetch_array($result) )
	{
	   echo 'Nicename is'.$rows['user_nicename'].'<br />';
	}
}


$result = mysql_query( "SELECT wp_users.user_login, wp_users.user_nicename, wp_users.display_name, wp_users.user_email, 
		wp_usermeta.meta_value, wp_usermeta.meta_key,
		blog_homepage.domain
		FROM wp_users 
		LEFT JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id
		LEFT JOIN blog_homepage 
				ON SUBSTRING_INDEX(SUBSTRING_INDEX(wp_usermeta.meta_key, '_', 2), '_', -2) = blog_homepage.blog_id
		");

$num_rows = mysql_num_rows($result);

if( $num_rows == 0 )
{
	echo 'No rows match the selection criteria 2.';	
}
else
{
	echo 'Yes, there is data 2 here<br />';
	while ($rows = mysql_fetch_array($result) )
	{
	   echo 'Nicename is'.$rows['user_nicename'].' Email is: '.$rows['user_email'].' Domain is '.$rows['domain'].'<br />';
	}
}

ASKER

ok - same domain , filename is db-test3.php

there is a lot of output

Ok let's try it with these updated queries.

Please remove the main sql queries and replace it with the code I have in attached in the 3rd link and let me know. That should give you less output and hopefully even throw more light on the root of the problem.

$result = mysql_query( "SELECT DISTINCT wp_users.user_login, wp_users.user_nicename, wp_users.display_name, wp_users.user_email, 
		wp_usermeta.meta_value, wp_usermeta.meta_key
		FROM wp_users 
		LEFT JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id
		");

$num_rows = mysql_num_rows($result);

if( $num_rows == 0 )
{
	echo 'No rows match the selection criteria 1.';	
}
else
{
	echo 'Yes, there is data 1 here<br />';
	while ($rows = mysql_fetch_array($result) )
	{
	   echo 'Nicename is'.$rows['user_nicename'].' Metakey is:'.$rows['meta_key'].'<br />';
	}
}


$result = mysql_query( "SELECT DISTINCT wp_users.user_login, wp_users.user_nicename, wp_users.display_name, wp_users.user_email, 
		wp_usermeta.meta_value, wp_usermeta.meta_key,
		blog_homepage.domain
		FROM wp_users 
		LEFT JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id
		LEFT JOIN blog_homepage 
				ON SUBSTRING_INDEX(SUBSTRING_INDEX(wp_usermeta.meta_key, '_', 2), '_', -2) = blog_homepage.blog_id
		");

$num_rows = mysql_num_rows($result);

if( $num_rows == 0 )
{
	echo 'No rows match the selection criteria 2.';	
}
else
{
	echo 'Yes, there is data 2 here<br />';
	while ($rows = mysql_fetch_array($result) )
	{
	   echo 'Nicename is'.$rows['user_nicename'].' Email is: '.$rows['user_email'].' Metakey is:'.$rows['meta_key'].' Domain is '.$rows['domain'].'<br />';
	}
}




$result3 = mysql_query( "SELECT DISTINCT wp_usermeta.meta_key, blog_homepage.domain
						FROM wp_users,  blog_homepage
						LIMIT 1000
						");

$num_rows3 = mysql_num_rows($result3);

if( $num_rows3 == 0 )
{
	echo 'No rows match the selection criteria 3.';	
}
else
{
	echo 'Yes, there is data 3 here<br />';
	while ($rows3 = mysql_fetch_array($result3) )
	{
	   echo ' Metakey is:'.$rows3['meta_key'].' Domain is '.$rows3['domain'].'<br />';
	}
}

ASKER

ok, refresh db-test3

Ok looks the like the data is not even in the DB which means that the query may work in some cases and it may break in other cases. We will take a look at the data in the individual tables to see if we can find a pattern to use for our purpose. Just delete the result3 query and it's results from the page and replace it with the code that I am giving you now and let me know.

$result3 = mysql_query( "SELECT DISTINCT wp_usermeta.meta_key
						FROM wp_users
						LIMIT 1000
						");

$num_rows3 = mysql_num_rows($result3);

if( $num_rows3 == 0 )
{
	echo 'No rows match the selection criteria 3.';	
}
else
{
	echo 'Yes, there is data 3 here<br />';
	while ($rows3 = mysql_fetch_array($result3) )
	{
	   echo ' Metakey is:'.$rows3['meta_key'].'<br />';
	}
}


$result4 = mysql_query( "SELECT DISTINCT blog_homepage.domain
						FROM  blog_homepage
						LIMIT 1000
						");

$num_rows4 = mysql_num_rows($result4);

if( $num_rows4 == 0 )
{
	echo 'No rows match the selection criteria 3.';	
}
else
{
	echo 'Yes, there is data 3 here<br />';
	while ($rows4 = mysql_fetch_array($result4) )
	{
	   echo ' Domain is '.$rows4['domain'].'<br />';
	}
}

ASKER

OK - the new file is up

It says " supplied argument is not a valid .." on line 49 which means there is something wrong with the query are we are unable to fetch the column from the table. Can you post the structure and values for the wp_users table? Just follow the same procedure as I mentioned in my comment ID: 32971150 above and post me the code. We will see what that has to say about the error.

ASKER

ok, attached
sql2.txt

Ok, so our required data exists in wp_usermeta table. Can you please post it's structure and the first 35 records? I need to match up the data with the data from blog_homepage table. So if you can include more than 35 records, that would help analyze the pattern.

Thanks.

ASKER

OK - But I will do that Monday morning because I am away from the office until then. THanks for your help so far

Ok, no problem. You are welcome.

ASKER

here is the entire table
sql3.txt

Hi,
I think there is some confusion. You posted the contents of blog_homepage table. Can you post the structure & data for wp_usermeta table ?

Thanks.

ASKER

http://tinyurl.com/2ex554m

i put it there

Ok, I have modified code and tested locally. It works now. Attached below. I have supplied instructions in file. It's very easy to follow. As long as you have data in the format of wp_31_capabilities, it will produce results.

Try it out and let me know.

<?php
//Use the following if you want to see all results irrespective of all data wether is exists or not in blog_homepage table
$result = mysql_query( "SELECT DISTINCT wp_users.user_login, wp_users.user_nicename, wp_users.display_name, wp_users.user_email, 
		wp_usermeta.meta_value, wp_usermeta.meta_key,
		blog_homepage.domain
		FROM wp_users 
		LEFT JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id
		LEFT JOIN blog_homepage 
				ON SUBSTRING(SUBSTRING(wp_usermeta.meta_key, 3), -13) = blog_homepage.blog_id
		");

//Use the following if you want to see all results only if data in blog_homepage table. I am commenting the following query. If you want to test the following query, uncomment it and comment the query above this.
/*
$result = mysql_query( "SELECT DISTINCT wp_users.user_login, wp_users.user_nicename, wp_users.display_name, wp_users.user_email, 
		wp_usermeta.meta_value, wp_usermeta.meta_key,
		blog_homepage.domain
		FROM wp_users 
		LEFT JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id
		INNER JOIN blog_homepage 
				ON SUBSTRING(SUBSTRING(wp_usermeta.meta_key, 3), -13) = blog_homepage.blog_id
		");		
*/
				

$num_rows = mysql_num_rows($result);

if( $num_rows == 0 )
{
	echo 'No rows match the selection criteria 2.';	
}
else
{
	echo 'Yes, there is data 2 here<br />';
	while ($rows = mysql_fetch_array($result) )
	{
	   echo 'Nicename is'.$rows['user_nicename'].' Email is: '.$rows['user_email'].' Metakey is:'.$rows['meta_key'].' Domain is '.$rows['domain'].'<br />';
	}
}

?>

ASKER

the second query is EXACTLY what I need - the only problem is the meta key value is a little off. Sometimes it has a value like :
{... administrator ...}
{... author...}

it kind of looks like gibberish, but that i need that data

Yes, I noticed that too. The meta key has values such as: nickname, primary_blog, source_domain, wp_1_capabilities, etc. So as you see, you might have to filter it from entering in your database if you don't need the data that is in another format than wp_1_capabilities. I am not sure which of the Wordpress routines is doing that and why.

Sorry, you can't really join using that gibberish metadata. the blog_id column in the blog_homepage table is of int type. That means you can allow only numbers in it. meta_key values such as nickname, admin, etc. are not numeric type and they cannot be joined on the blog_homepage table. There's really no other way with the current structure. The blog_id column will need to be changed in its structure & it should be changed to varchar(255) to accommodate this change.

But I must warn you that this can immensely affect your website if there are any processing checks and it may even crash your website. I am not sure what exactly can happen but the results could be not easy to live with. So I suggest against doing that unless you are willing to take the risk. But even changing the structure does not guarantee. A lot of digging may need to be done to see where the processing related to that column is being done and any source may need to be changed. Also you risk breaking this functionality if you do such core mod. So I am not favoring this and would not suggest this to you.

So, yes, there is no way we can get that data as it does not match the blog_id column value. Maybe you can request Wordpress authors to do something about it although I don't think that they might reply positively to that.

Does that answer your question?

ASKER

ok thanks - what does the meta data reference? is the value is 10 - does that mean that 10 can be translated to a wordpress ro9le like user, admin, author, etc?

ASKER

also, it seems there is a problem with the query.

the result set is saying that certain users are assigned to certain subdomains, when in fact they are not. I attached an example
problem.txt

WP says:
Metadata API Functions for retrieving and manipulating metadata of various WordPress object types. Metadata for an object is a represented by a simple key-value pair. Objects may contain multiple metadata entries that share the same key and differ only in their value.

You can read more about it at:
http://phpxref.ftwr.co.uk/wordpress/nav.html?wp-includes/index.html

Regarding the problem, can you tell me how you got the prompt, when and where? If you are running the query as I gave it to you, you shouldn't get to see something like the problem that you have indicated. So did you change anything or include some other code? What exactly has caused such a prompt to come up?

ASKER

I ran this query in phpmyadmin and exported the results to a CSV file.

I compared that user data to the wpmu-users.php file in wordpress - this file tells you what blog every user is assigned to. that is where the inconsistencies are. the query is saying users are assigned to blogs they are not assigned to,

SELECT DISTINCT wp_users.user_login, wp_users.user_nicename, wp_users.display_name, wp_users.user_email, 
		wp_usermeta.meta_value, wp_usermeta.meta_key,
		blog_homepage.domain
		FROM wp_users 
		LEFT JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id
		INNER JOIN blog_homepage 
				ON SUBSTRING(SUBSTRING(wp_usermeta.meta_key, 3), -13) = blog_homepage.blog_id

ASKER

i attached the code that generates the page I am talking about. if I could somehow extract the query that is generated by this page. i supposed i could use that as well. I also put a screenshot in there of the actual result in wordpress

<?php
require_once('admin.php');

$title = __('WordPress MU &rsaquo; Admin &rsaquo; Users');
$parent_file = 'wpmu-admin.php';

wp_enqueue_script( 'admin-forms' );

require_once('admin-header.php');

if( is_site_admin() == false ) {
	wp_die( __('You do not have permission to access this page.') );
}

if ( $_GET['updated'] == 'true' ) {
	?>
	<div id="message" class="updated fade"><p>
		<?php
		switch ($_GET['action']) {
			case 'delete':
				_e('User deleted !');
			break;
			case 'all_spam':
				_e('Users marked as spam !');
			break;
			case 'all_notspam': 
				_e('Users marked as not spam !'); 
			break; 
			case 'all_delete':
				_e('Users deleted !');
			break;
			case 'add':
				_e('User added !');
			break;
		}
		?>
	</p></div>
	<?php
}
?>

<div class="wrap" style="position:relative;">
	<?php
	$apage = isset( $_GET['apage'] ) ? intval( $_GET['apage'] ) : 1;
	$num = isset( $_GET['num'] ) ? intval( $_GET['num'] ) : 15;
	$s = wp_specialchars( trim( $_GET[ 's' ] ) );

	$query = "SELECT * FROM {$wpdb->users}";

	if( !empty( $s ) ) {
		$search = '%' . trim( $s ) . '%';
		$query .= " WHERE user_login LIKE '$search' OR user_email LIKE '$search'";
	}

	if( !isset($_GET['sortby']) ) {
		$_GET['sortby'] = 'id';
	}

	if( $_GET['sortby'] == 'email' ) {
		$query .= ' ORDER BY user_email ';
	} elseif( $_GET['sortby'] == 'id' ) {
		$query .= ' ORDER BY ID ';
	} elseif( $_GET['sortby'] == 'login' ) {
		$query .= ' ORDER BY user_login ';
	} elseif( $_GET['sortby'] == 'name' ) {
		$query .= ' ORDER BY display_name ';
	} elseif( $_GET['sortby'] == 'registered' ) {
		$query .= ' ORDER BY user_registered ';
	}

	$query .= ( $_GET['order'] == 'DESC' ) ? 'DESC' : 'ASC';

	if( !empty( $s )) {
		$total = $wpdb->get_var( str_replace('SELECT *', 'SELECT COUNT(ID)', $query) );
	} else {
		$total = $wpdb->get_var( "SELECT COUNT(ID) FROM {$wpdb->users}");
	}

	$query .= " LIMIT " . intval( ( $apage - 1 ) * $num) . ", " . intval( $num );

	$user_list = $wpdb->get_results( $query, ARRAY_A );

	// Pagination
	$user_navigation = paginate_links( array(
		'total' => ceil($total / $num),	
		'current' => $apage,
		'base' => add_query_arg( 'apage', '%#%' ),
		'format' => ''
	));
	
	if ( $user_navigation ) {
		$user_navigation = sprintf( '<span class="displaying-num">' . __( 'Displaying %s&#8211;%s of %s' ) . '</span>%s',
			number_format_i18n( ( $apage - 1 ) * $num + 1 ),
			number_format_i18n( min( $apage * $num, $total ) ),
			number_format_i18n( $total ),
			$user_navigation
		);
	}
	
	?>
	<div class="wrap">
	<h2><?php _e( $current_site->site_name ); ?> <?php _e("Users"); ?></h2>
	<form action="wpmu-users.php" method="get" class="search-form">
		<p class="search-box">
		<input type="text" name="s" value="<?php if (isset($_GET['s'])) _e( stripslashes( $s ) ); ?>" class="search-input" id="user-search-input" />
		<input type="submit" id="post-query-submit" value="<?php _e('Search Users') ?>" class="button" />
		</p>
	</form>
	</div>

	<form id="form-user-list" action='wpmu-edit.php?action=allusers' method='post'>
		<div class="tablenav">
			<?php if ( $user_navigation ) echo "<div class='tablenav-pages'>$user_navigation</div>"; ?>

			<div class="alignleft actions">
				<input type="submit" value="<?php _e('Delete') ?>" name="alluser_delete" class="button-secondary delete" />
				<input type="submit" value="<?php _e('Mark as Spammers') ?>" name="alluser_spam" class="button-secondary" />
				<input type="submit" value="<?php _e('Not Spam') ?>" name="alluser_notspam" class="button-secondary" />
				<?php wp_nonce_field( 'allusers' ); ?>
				<br class="clear" />
			</div>
		</div>

		<?php if( isset($_GET['s']) && $_GET['s'] != '' ) : ?>
			<p><a href="wpmu-blogs.php?action=blogs&amp;s=<?php echo urlencode( stripslashes( $s ) ); ?>&blog_name=Search+blogs+by+name"><?php _e('Search Blogs for') ?> <strong><?php echo stripslashes( $s ) ?></strong></a></p>
		<?php endif; ?>

		<?php
		// define the columns to display, the syntax is 'internal name' => 'display name'
		$posts_columns = array(
			'checkbox'	 => '',
			'login'      => __('Username'),
			'name'       => __('Name'),
			'email'      => __('E-mail'),
			'registered' => __('Registered'),
			'blogs'      => ''
		);
		$posts_columns = apply_filters('wpmu_users_columns', $posts_columns);
		?>
		<table class="widefat" cellspacing="0">
			<thead>
			<tr>
				<?php foreach( (array) $posts_columns as $column_id => $column_display_name) {
					if( $column_id == 'blogs' ) {
						echo '<th scope="col">'.__('Blogs').'</th>';
					} elseif( $column_id == 'checkbox') {
						echo '<th scope="col" class="check-column"><input type="checkbox" /></th>';
					} else { ?>
						<th scope="col"><a href="wpmu-users.php?sortby=<?php echo $column_id ?>&amp;<?php if( $_GET['sortby'] == $column_id ) { if( $_GET['order'] == 'DESC' ) { echo "order=ASC&amp;" ; } else { echo "order=DESC&amp;"; } } ?>apage=<?php echo $apage ?>"><?php echo $column_display_name; ?></a></th>
					<?php } ?>
				<?php } ?>
			</tr>
			</thead>
			<tbody id="users" class="list:user user-list">
			<?php if ($user_list) {
				$bgcolor = '';
				foreach ( (array) $user_list as $user) { 
					$class = ('alternate' == $class) ? '' : 'alternate';
					
					$status_list = array( "spam" => "#faa", "deleted" => "#f55" );
					
					$bgcolour = "";
					foreach ( $status_list as $status => $col ) {
						if( $user[$status] ) {
							$bgcolour = "style='background: $col'";
						}
					}

					?>

					<tr <?php echo $bgcolour; ?> class="<?php echo $class; ?>">
					<?php
					foreach( (array) $posts_columns as $column_name=>$column_display_name) :
						switch($column_name) {
							case 'checkbox': ?>
								<th scope="row" class="check-column"><input type='checkbox' id='user_<?php echo $user['ID'] ?>' name='allusers[]' value='<?php echo $user['ID'] ?>' /></th>
							<?php 
							break;

							case 'login':
								$avatar	= get_avatar( $user['user_email'], 32 );
								$edit	= clean_url( add_query_arg( 'wp_http_referer', urlencode( clean_url( stripslashes( $_SERVER['REQUEST_URI'] ) ) ), "user-edit.php?user_id=".$user['ID'] ) );
								// @todo Make delete link work like delete button with transfering users (in wpmu-edit.php)
								//$delete	= clean_url( add_query_arg( 'wp_http_referer', urlencode( clean_url( stripslashes( $_SERVER['REQUEST_URI'] ) ) ), wp_nonce_url( 'wpmu-edit.php', 'deleteuser' ) . '&amp;action=deleteuser&amp;id=' . $user['ID'] ) );
								?>
								<td class="username column-username">
									<?php echo $avatar; ?><strong><a href="<?php echo $edit; ?>" class="edit"><?php echo stripslashes($user['user_login']); ?></a></strong>
									<br/>
									<div class="row-actions">
										<span class="edit"><a href="<?php echo $edit; ?>">Edit</a></span>
										<?php /*<span class="delete"><a href="<?php echo $delete; ?>" class="delete">Delete</a></span> */ ?>
									</div>
								</td>
							<?php
							break;

							case 'name': ?>
								<td class="name column-name"><?php echo $user['display_name'] ?></td>
							<?php
							break;

							case 'email': ?>
								<td class="email column-email"><a href="mailto:<?php echo $user['user_email'] ?>"><?php echo $user['user_email'] ?></a></td>
							<?php
							break;

							case 'registered': ?>
								<td><?php echo mysql2date(__('Y-m-d \<\b\r \/\> g:i a'), $user['user_registered']); ?></td>
							<?php
							break;

							case 'blogs': 
								$blogs = get_blogs_of_user( $user['ID'], true );
								?>
								<td>
									<?php
									if( is_array( $blogs ) ) {
										foreach ( (array) $blogs as $key => $val ) {
											$path	= ($val->path == '/') ? '' : $val->path;
											echo '<a href="wpmu-blogs.php?action=editblog&amp;id=' . $val->userblog_id . '">' . str_replace( '.' . $current_site->domain, '', $val->domain . $path ) . '</a>';
											echo ' <small class="row-actions">';
											
											// Edit
											echo '<a href="wpmu-blogs.php?action=editblog&amp;id=' . $val->userblog_id . '">' . __('Edit') . '</a> | ';
											
											// View
											echo '<a '; 
											if( get_blog_status( $val->userblog_id, 'spam' ) == 1 )
												echo 'style="background-color: #f66" ';
											echo 'target="_new" href="http://'.$val->domain . $val->path.'">' . __('View') . '</a>';
											
											echo '</small><br />'; 
										}
									}
									?>
								</td>
							<?php
							break;

							default: ?>
								<td><?php do_action('manage_users_custom_column', $column_name, $user['ID']); ?></td>
							<?php
							break;
						}
					endforeach
					?>
					</tr> 
					<?php
				}
			} else {
			?>
				<tr style='background-color: <?php echo $bgcolor; ?>'> 
					<td colspan="<?php echo (int) count($posts_columns); ?>"><?php _e('No users found.') ?></td> 
				</tr> 
				<?php
			} // end if ($users)
			?> 
			</tbody>
		</table>
		
		<div class="tablenav">
			<?php if ( $user_navigation ) echo "<div class='tablenav-pages'>$user_navigation</div>"; ?>

			<div class="alignleft">
				<input type="submit" value="<?php _e('Delete') ?>" name="alluser_delete" class="button-secondary delete" />
				<input type="submit" value="<?php _e('Mark as Spammers') ?>" name="alluser_spam" class="button-secondary" />
				<input type="submit" value="<?php _e('Not Spam') ?>" name="alluser_notspam" class="button-secondary" />
				<?php wp_nonce_field( 'allusers' ); ?>
				<br class="clear" />
			</div>
		</div>
	</form>
</div>

<?php
if( apply_filters('show_adduser_fields', true) ) :
?>
<div class="wrap">
	<h2><?php _e('Add user') ?></h2>
	<form action="wpmu-edit.php?action=adduser" method="post">
	<table class="form-table">
		<tr class="form-field form-required">
			<th scope='row'><?php _e('Username') ?></th>
			<td><input type="text" name="user[username]" /></td>
		</tr>
		<tr class="form-field form-required">
			<th scope='row'><?php _e('Email') ?></th>
			<td><input type="text" name="user[email]" /></td>
		</tr>
		<tr class="form-field">
			<td colspan='2'><?php _e('Username and password will be mailed to the above email address.') ?></td>
		</tr>
	</table>
	<p class="submit">
		<?php wp_nonce_field('add-user') ?>
		<input class="button" type="submit" name="Add user" value="<?php _e('Add user') ?>" /></p>
	</form>
</div>
<?php endif; ?>

<?php include('admin-footer.php'); ?>

shot.png

I am attaching the code below, what I believe you were asking for, by mentioning that you would want to extract the query. I have modified it as needed to run.

Try it and let me know.

<?php 

$result = mysql_query("SELECT wp_users.ID, wp_users.user_login, wp_users.user_nicename, wp_users.display_name, wp_users.user_email, 
		wp_usermeta.meta_value, wp_usermeta.meta_key,
		blog_homepage.domain
		FROM wp_users 
		LEFT JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id
		");

while ($rows = mysql_fetch_array($result) )
	{
		$blogs = get_blogs_of_user( $rows['ID'], true );
							
		if( is_array( $blogs ) ) 
		{
			foreach ( (array) $blogs as $key => $val ) 
			{
				$path	= ($val->path == '/') ? '' : $val->path;
				echo '<a href="wpmu-blogs.php?action=editblog&amp;id=' . $val->userblog_id . '">' . str_replace( '.' . $current_site->domain, '', $val->domain . $path ) . '</a>';
				echo ' <small class="row-actions">';											
											
											
				// View
				echo '<a '; 
				if( get_blog_status( $val->userblog_id, 'spam' ) == 1 )
				echo 'style="background-color: #f66" ';
				echo 'target="_new" href="http://'.$val->domain . $val->path.'">' . __('View') . '</a>';											
				echo '</small><br />'; 
			}
		}
	}
?>

ASKER

that code doesn't appear to work:
http://tinyurl.com/3y52rvf

when i run that query in phpmyadmin i get this:
#1054 - Unknown column 'blog_homepage.domain' in 'field list'

Sorry, my bad. I forgot to remove the column name from the query. I have attached the corrected code.

Please try it & let me know.

<?php 

$result = mysql_query("SELECT wp_users.ID, wp_users.user_login, wp_users.user_nicename, wp_users.display_name, wp_users.user_email, wp_usermeta.meta_value, wp_usermeta.meta_key
		FROM wp_users 
		LEFT JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id
		");

while ($rows = mysql_fetch_array($result) )
	{
		$blogs = get_blogs_of_user( $rows['ID'], true );
							
		if( is_array( $blogs ) ) 
		{
			foreach ( (array) $blogs as $key => $val ) 
			{
				$path	= ($val->path == '/') ? '' : $val->path;
				echo '<a href="wpmu-blogs.php?action=editblog&amp;id=' . $val->userblog_id . '">' . str_replace( '.' . $current_site->domain, '', $val->domain . $path ) . '</a>';
				echo ' <small class="row-actions">';											
											
											
				// View
				echo '<a '; 
				if( get_blog_status( $val->userblog_id, 'spam' ) == 1 )
				echo 'style="background-color: #f66" ';
				echo 'target="_new" href="http://'.$val->domain . $val->path.'">' . __('View') . '</a>';											
				echo '</small><br />'; 
			}
		}
	}
?>

And one more thing. When you run only the query in phpmyadmin, you won't see the blogs related to the user. You will only see the users. In order to be able to see the blogs associated with the users, you will need to run the whole code in a PHP page. Only then you will be able to see something related to the blogs.

ASKER

now there is a different error

ASKER

that is fine, i can probably cut and paste that info into an excel document manually

I am sorry, I could not really understand what you meant about the excel sheet. So does it work now?

ASKER

no problem, don't worry about that.

it appears that there is still an error in the code

Did you try the revised query that I gave you? It's working on my end.

ASKER

yes > http://tinyurl.com/3y52rvf

Are you running the code in a PHP page all by itself separate from Wordpress directory? Seems like you have not include the required configuration to run the code properly.

So do this:
1. Copy the code from index.php page of WP.
2. Once you see the code within the <body></body> tags, just replace that code with the PHP code that I gave you & try it.

You will not see the error if you run the code as a part of the wordpress directory.

ASKER

here is the code I am using (minus the usernames/pw's)

It is in the same directory as index.php of the wordpress DIR. the index.php file looks like this:

<?php
/**
* Front to the WordPress application. This file doesn't do anything, but loads
* wp-blog-header.php which does and tells WordPress to load the theme.
*
* @package WordPress
*/

/**
* Tells WordPress to load the WordPress theme and output it.
*
* @var bool
*/
define('WP_USE_THEMES', true);
/** Loads the WordPress Environment and Template */
require('./wp-blog-header.php');
?>

<?php
$host		 = ""; // Put your Host name as indicated
$db_username = ""; // DB username
$db_password = ""; // DB  password
$db_name	 = ""; // Database name for

$link = mysql_connect("$host", "$db_username", "$db_password", "$db_name");
if (!$link) 
	{
    	 die("Cannot connect");
		 exit();
	}


$db_selected = mysql_select_db("$db_name", $link);
if (!$db_selected) 
	{
    	die("Cannot select DB"); 
		exit();
	}

$result = mysql_query("SELECT wp_users.ID, wp_users.user_login, wp_users.user_nicename, wp_users.display_name, wp_users.user_email, wp_usermeta.meta_value, wp_usermeta.meta_key
		FROM wp_users 
		LEFT JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id
		");

while ($rows = mysql_fetch_array($result) )
	{
		$blogs = get_blogs_of_user( $rows['ID'], true );
							
		if( is_array( $blogs ) ) 
		{
			foreach ( (array) $blogs as $key => $val ) 
			{
				$path	= ($val->path == '/') ? '' : $val->path;
				echo '<a href="wpmu-blogs.php?action=editblog&amp;id=' . $val->userblog_id . '">' . str_replace( '.' . $current_site->domain, '', $val->domain . $path ) . '</a>';
				echo ' <small class="row-actions">';											
											
											
				// View
				echo '<a '; 
				if( get_blog_status( $val->userblog_id, 'spam' ) == 1 )
				echo 'style="background-color: #f66" ';
				echo 'target="_new" href="http://'.$val->domain . $val->path.'">' . __('View') . '</a>';											
				echo '</small><br />'; 
			}
		}
	}

?>

That's just the code that I have given to you previously. That does not get you the result. It has to include the Wordpress code.

So now do this.
1. Whatever template you are currently running in Wordpress, just navigate to the template folder.
2. Make a backup copy of index.php file.
3. Then in the index.php file, just paste the code I have attached.
4. Now visit the homepage of wordpress site just like you normally do i.e. if you are trying this locally, url would be: http://localhost/wordpress/index.php. If you are trying this online and having wordpress installed in a folder called "wordpress" then navigate to www.yourdomain.com/wordpress/index.php

Do that & let me know what you see.

<?php
/**
 * @package WordPress
 * @subpackage Default_Theme
 */

get_header(); ?>


<?php 

$result = mysql_query("SELECT wp_users.ID, wp_users.user_login, wp_users.user_nicename, wp_users.display_name, wp_users.user_email, wp_usermeta.meta_value, wp_usermeta.meta_key
		FROM wp_users 
		LEFT JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id
		") or die(mysql_error());

while ($rows = mysql_fetch_array($result) )
	{
		$blogs = get_blogs_of_user( $rows['ID'], true );
							
		if( is_array( $blogs ) ) 
		{
			foreach ( (array) $blogs as $key => $val ) 
			{
				$path	= ($val->path == '/') ? '' : $val->path;
				echo '<a href="wpmu-blogs.php?action=editblog&amp;id=' . $val->userblog_id . '">' . str_replace( '.' . $current_site->domain, '', $val->domain . $path ) . '</a>';
				echo ' <small class="row-actions">';											
											
											
				// View
				echo '<a '; 
				if( get_blog_status( $val->userblog_id, 'spam' ) == 1 )
				echo 'style="background-color: #f66" ';
				echo 'target="_new" href="http://'.$val->domain . $val->path.'">' . __('View') . '</a>';											
				echo '</small><br />'; 
			}
		}
	}
?>	

<?php get_sidebar(); ?>

<?php get_footer(); ?>

ASKER

is there any pother way to execute the query? ive been trying what you suggested but my browser freezes up when the script executes and I can't tie up my themes for too long