Tell me more ×
WordPress Answers is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.
up vote 1 down vote favorite

I'm using this code.

$blog_list = $wpdb->get_results( "SELECT " . $extra . "blog_id, last_updated FROM " . $wpdb->blogs. " WHERE public = '1' AND archived = '0' AND mature = '0' AND spam = '0' AND deleted ='0' AND UPPER(blog_name) LIKE 'A%'" . $order . " " . $limit . "", ARRAY_A );

But that LIKE statement AND UPPER(blog_name) LIKE 'A%'" not working. Can anyone give me the solution?

Thanks

Update:

Actually i'm using this code 

$where = ''; 
if ($filter) { 
$where = "AND UPPER(blog_name) LIKE '".strtoupper($filter)."%' "; 
}

So i'll replace AND UPPER(blog_name) LIKE 'A%'" with $where later

Note: I have 500+ blogs in my network.

I want to display all blog names with anchor link in a page. But I don't want to load all the blog names. So I'm trying to achieve alphabetical navigation. If the user click link "A" then the page should display Blog names starts with "A". Likewise B, C, etc.

Here is the full code if anyone interested

share|improve this question
1  
Note: Get rid of the UPPER and the strtoupper. LIKE comparisons are case-insensitive in MySQL. – Otto Feb 11 '12 at 18:38
@Otto Thanks. But i dropped this idea. Because Kaiser said The problem is that you will won't be able to search from the beginning of the string. So you'll get every blog that contains an A in it's name. I'm trying this one instead of querying mysql. I'm trying to implement it for more than 20 hours So please help me if you can. Thanks – Giri Feb 11 '12 at 18:56
Umm.. No, "A%" will indeed find all the entries starting with A. If you wanted A anywhere in the string then you'd use "%A%" instead. – Otto Feb 11 '12 at 21:53

2 Answers

up vote 3 down vote accepted

Prepare your Queries!

global $wpdb;
$wpdb->get_results( 
    $wpdb->prepare( 
        "SELECT %s blog_id, last_updated FROM %s WHERE public = '1' AND archived = '0' AND mature = '0' AND spam = '0' AND deleted ='0' AND UPPER(blog_name) LIKE '%s' %s %s ", ARRAY_A 
    )
    ,$extra
    ,$wpdb->blogs
    ,like_escape( 'A%' )
    ,$order
    ,$limit
);

Never ever send data without prepare! Also: There're some functions like like_escape() that'll help you preparing the data properly.

Still wondering why you'd want to search for something like A%…

share|improve this answer
1  
Could you please simply a) update your Q as the code is impossible to read in a comment and b) reply to @Brady answer? Thanks. – kaiser Feb 11 '12 at 14:23
@kaiser I think he is trying to search for all blogs on his network starting with the letter A. A lot of his questions lately are related to multisite where he is running a network of lots of sub sites. – Brady♦ Feb 11 '12 at 14:26
@Brady Yes you are right. I'm trying to create a alphabetical navigation. If the user click "A", i want to display only the blog names which starts with A. Like wise B,C,etc – Giri Feb 11 '12 at 14:33
2  
@Viruthagiri How about another update to the Q to reflect your real Q »_I'm trying to create a alphabetical navigation. If the user click "A", i want to display only the blog names which starts with A. Like wise B,C,etc_« – kaiser Feb 11 '12 at 14:40
1  
Yes: Go with what @Brady wrote. – kaiser Feb 11 '12 at 15:24
show 5 more comments
up vote 1 down vote

What @kaiser said plus:

In this table there is no blog_name field... It appears that your query includes some other SQL but without that we cannot tell whats going wrong.

where is blog_name

Update The only place the blog name resides is in the individual blogs options table. So to do what you are after you would have to do this:

Read out all your blog ID's that are public = 1 && achieved = 0 && mature = 0 && spam = 0 && deleted = 0. That's one query there that in your case would return about 500 records. Then with each record you would have to read each blog options table {prefix}_{ID}_options and find the row that has a option_name = 'blogname' and then check if that record begins with the letter of choice.

Maybe the above is possible in one massive MySQL query but I'd hate to be the one to put it together.

I cannot help any further. If it was me looking to do what you are after I'd look at creating a new table of my own and keep it up to date with the info I need to search. I'd use available hooks to make sure that when a new site is added/updated/deleted it would update this table I had created.

share|improve this answer
Hi Brady, Please check this pastebin – Giri Feb 11 '12 at 14:32
@Viruthagiri - You are searching the blogs table on a column called 'blog_name' which doesn't exist in this table. You need to do some JOIN magic with other tables to pull in blog_name. But looking at the tables that are available there is no single table that contains all the blogs names. – Brady♦ Feb 11 '12 at 14:39
Ah.. Can you give me some sample code? Please check this pastebin too. This code doesn't contains that filter. So it returns all blog names – Giri Feb 11 '12 at 14:48
See update I have made – Brady♦ Feb 11 '12 at 14:57
1  
I'd stick with @Brady recommendation: Do it in a stepped process for easier debugging + Save your data to a separate table. Also: You should add another answer when you're done. There you should add your code for later readers and mark it as solution 2 days later. – kaiser Feb 11 '12 at 15:25
show 4 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.