We help IT Professionals succeed at work.

MySQL question

lvollmer
lvollmer asked
on
I have this 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
WHERE RIGHT(wp_usermeta.meta_key,12) = "capabilities";

it will display a column called "meta_key" and it contains data like this: wp_31_capabilities, wp_15_capabilities etc

I have another table called "blog_homepage" that has two columns
blog_id
domain


blog id column has data like 31, 15

the "meta_key" column result of wp_31_capabilities is equal to the value of 31 in  the column blog_id in the table blog_home

I want to add an additional statement that matches those columns up, and displays the bloghomepage.domain column in the result set. Is this possible?


Comment
Watch Question

Avinash ZalaWeb Expert

Commented:
try this:

http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_find-in-set


Hope this helps to make your query.
Thanks
Addy

Author

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

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

Please post here.

Thanks
Addy

Author

Commented:
I don't have the queries anymore, i deleted them and can't remember what i was trying.
Avinash ZalaWeb Expert

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


Thanks
Addy

Author

Commented:
how would I do that?

Commented:
@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'];

Open in new window

Author

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

Commented:
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.

Author

Commented:
excellent, I will do that now - thanks!

Author

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

Commented:
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'];
}

Open in new window

Author

Commented:
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'];
}

 ?>

Open in new window

Commented:
Hi,
Looks like you are right but we are not hitting the nail on the spot. In order to help you faster, I have put together a script. All you would have to do is copy all of the code that I have attached, into a new blank php file (really blank with nothing in it), save it as test.php in the folder where you have your site installed.  (I am assuming that you are doing all this testing locally. Please correct me if I am wrong.). So for example, if you were accessing your website by going to:
http://localhost/mysite/index.php
then place the test.php file in mysite folder so that we can access it at:
http://localhost/mysite/test.php

Also in the script, just enter your hostname, datbase username, password and database name as I have indicated in the top 4-5 lines of the script. Just replace my dummy values with your actual values. You should be able to connect to your database and query it, if you entered the values correctly.

Once you have entered all the information as required, access it via the link. That will run and show you some output. Just tell me what you see as the output and we will take it from there.

The script won't do anything other than querying the database and tables in question to determine what we are doing is correct and to find the root of the problem. It serves no other purpose. So you can be confident about using it and telling me the results.

Let me know how it goes.
<?php
$host		 = "hostname_here"; // Put your Host name as indicated
$db_username = "username_here"; // DB username
$db_password = "password_here"; // DB  password
$db_name	 = "database_name_here"; // 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.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'");

$num_rows = mysql_num_rows($result);

if( $num_rows == 0 )
{
	echo 'No rows match the selection criteria.';	
}
else
{
	while ($rows = mysql_fetch_array($result) )
	{
	   echo $rows['domain'];
	}
}
?>	

Open in new window

Commented:
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.

Author

Commented:
ok I will give this a shot now

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

Author

Commented:
on both tests, I got a blank page:

http://tinyurl.com/2659xqk

Commented:
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.

Author

Commented:
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

Author

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

Commented:
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?

Author

Commented:
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.

Author

Commented:
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

Commented:
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 />';
	}
}

Open in new window

Author

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

there is a lot of output

Commented:
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 />';
	}
}

Open in new window

Author

Commented:
ok, refresh db-test3

Commented:
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 />';
	}
}

Open in new window

Author

Commented:
OK - the new file is up

Commented:
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.

Author

Commented:
ok, attached
sql2.txt

Commented:
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.

Author

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

Commented:
Ok, no problem. You are welcome.

Author

Commented:
here is the entire table
sql3.txt

Commented:
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.

Author

Commented:

Commented:
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 />';
	}
}

?>

Open in new window

Author

Commented:
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

Commented:
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?

Author

Commented:
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?

Author

Commented:
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

Commented:
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?

Author

Commented:
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

Open in new window

Author

Commented:
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'); ?>

Open in new window

shot.png

Commented:
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 />'; 
			}
		}
	}
?>

Open in new window

Author

Commented:
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'

Commented:
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 />'; 
			}
		}
	}
?>

Open in new window

Commented:
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.

Author

Commented:
now there is a different error

Author

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

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

Author

Commented:
no problem, don't worry about that.

it appears that there is still an error in the code

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

Commented:
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.

Author

Commented:
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 />'; 
			}
		}
	}

?>

Open in new window

Commented:
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(); ?>

Open in new window

Author

Commented:
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

Commented:
Yes, there is. You will need to use the code that you pasted in your comment ID: 32998531

You will need to use the $result query and the while loop in the page from my comment ID: 32998679. Please be forewarned that all scripts that Wordpress needs in order to process all the necessary functions must be included as well in the script. What other scripts will it need? I don't know. It depends upon what scripts the functions: get_blogs_of_user & get_blog_status are using. That's quite a bit of digging. If you can do that and include those scripts, it should then work fine.

As an alternate, why don't you try this? Just rename the index.php page in which you are running the code to something else, like experiment.php and replace the original index.php back. Then access experiment.php page via the URL, just like you would access index.php & try. That way, your site will be available and you can wait till experiment.php finishes it's execution.

Does that sound good?