Mysql query LIKE not working

1 Views

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?

Read More

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

Post Views: 1

Related posts

Leave a Reply

2 comments

  1. 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,
    $wpdb->esc_like( '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%…

  2. 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.