draxq02664
2017-09-25 12:51
浏览 33
已采纳

将wordpress数据库反序列化以查询数据

I have a mysql database that looks like this

+----+----------+---------+----------------------------------+---------------------+
| id | field_id | user_id |              value               |    last_updated     |
+----+----------+---------+----------------------------------+---------------------+
|  1 |        1 |       1 | admin                            | yyyy-mm-dd hh:mm:ss |
|  3 |        5 |       1 | a:1:{i:0;s:2:"18";}              | yyyy-mm-dd hh:mm:ss |
|  4 |        1 |       2 | testuser1                        | yyyy-mm-dd hh:mm:ss |
|  5 |        5 |       2 | a:2:{i:0;s:2:"19";i:1;s:2:"18";} | yyyy-mm-dd hh:mm:ss |
+----+----------+---------+----------------------------------+---------------------+

I understand that a normal sql query will not be suitable so instead I need to pull all the data into php to then sort through it.

What I want is to get any user_id that has a number, lets say "19" in field_id 5. In that example, the array should read "2". Or I could search for "18" in field_id 5 and the array would return "1,2".

To get the database, I am using the following

<?php
global $wpdb;
$table_name = $wpdb->prefix . "bp_xprofile_data";
$retrieve_data = $wpdb->get_results( "SELECT * FROM $table_name" );

$strDB = maybe_unserialize( $retrieve_data);
echo print_r($strDB, true);
?>

Which returns:

Array ( [0] => stdClass Object ( [id] => 1 [field_id] => 1 [user_id] => 1 [value] => admin [last_updated] => 2017-09-21 12:38:20 ) [1] => stdClass Object ( [id] => 3 [field_id] => 5 [user_id] => 1 [value] => a:1:{i:0;s:2:"18";} [last_updated] => 2017-09-21 12:38:20 ) [2] => stdClass Object ( [id] => 4 [field_id] => 1 [user_id] => 2 [value] => testuser1 [last_updated] => 2017-09-23 01:43:50 ) [3] => stdClass Object ( [id] => 5 [field_id] => 5 [user_id] => 2 [value] => a:2:{i:0;s:2:"19";i:1;s:2:"18";} [last_updated] => 2017-09-23 01:43:50 ) ) 

I can't work out how I can then sort through this data. I tried to find sections of string but this was not working.

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • doushanlv5184 2017-09-25 14:46
    已采纳

    You should be able to use the LIKE comparison on the 'value' field, e.g.

    SELECT * FROM $table_name AND value LIKE '%9%'
    

    The difficulty with searching for a number is that LIKE will also return partial matches, so a query for 9 would also return 19, 91, 192 etc.

    However, based on the values getting surrounded by double quoted in the serialised string, you should be able to search for the exact value by including the double quotes in the search string, e.g. "9".

    Adding that into the code in your question, we get:

    global $wpdb;
    
    $num_to_find = 19; /* or change to whatever number you need */
    $field_id_to_check = 5; /* or change to whatever number you need */
    
    $table_name = $wpdb->prefix . "bp_xprofile_data";
    
    $user_ids = $wpdb->get_results( 
         $wpdb->prepare( 
            "SELECT user_id FROM $table_name 
                     WHERE field_id = %d AND value LIKE '%%\"%d\"%%'", 
            $field_id_to_check, /*this param replaces the 1st %d in the query */
            $num_to_find /*this param replaces the 2nd %d in the query */
        )
    );
    print_r($user_ids);
    

    Note: because the query includes a variable and I don't know where its coming from, I've used $wpdb->prepare to sanitise the variable.

    That's not tested, but I believe it should work!

    已采纳该答案
    打赏 评论
  • dshqd84261 2017-09-25 13:10

    Well, first rule – you should not do this. But if there is good reason, consider using such query for searching in index-based arrays

    SELECT * FROM $table_name WHERE value REGEXP '.*;s:[0-9]+:"19".*'
    

    Here we search on the column value the value "19" as you did on the example with a regex. Regards.

    打赏 评论

相关推荐 更多相似问题