从php中的mysql中选择一个文本字段

usersim interested how do i select a text field form my mysql database, i have a table named users with a text field called "profile_fields" where addition user info is stored. How do i access it in php and make delete it? I want to delete unvalidate people.

usersim感兴趣我如何从我的mysql数据库中选择一个文本字段,我有一个名为users的表,其中包含一个名为“profile_fields”的文本字段,其中存储了额外的用户信息。我如何在PHP中访问它并删除它?我想删除未验证的人。

PHP code

<?php
//Working connection made before assigned as $connection
$time = time();
$query_unactive_users = "DELETE FROM needed WHERE profile_fields['valid_until'] < $time"; //deletes user if the current time value is higher then the expiring date to validate
mysqli_query($connection , $query_unactive_users);
mysqli_close($connection);
?>

In phpmyadmin the field shows (choosen from a random user row):

在phpmyadmin中,该字段显示(从随机用户行中选择):

a:1:{s:11:"valid_until";i:1370695666;}

Is " ... WHERE profile_fields['valid_until'] ..." the correct way?

是“...... WHERE profile_fields ['valid_until'] ......”正确的方法?

2 个解决方案

#1

Anyway, here's a very fragile solution using your knowledge of the string structure and a bit of SUBSTRING madness:

无论如何,这是一个非常脆弱的解决方案,使用你对字符串结构的知识和一些SUBSTRING疯狂:

DELETE FROM needed WHERE SUBSTRING(
    profile_fields,
    LOCATE('"valid_until";i:', profile_fields) + 16, 
    LOCATE(';}', profile_fields) - LOCATE('"valid_until";i:', profile_fields) - 16
) < UNIX_TIMESTAMP();

But notice that if you add another "virtual field" after 'valid_until', that will break...

但请注意,如果你在'valid_until'之后添加另一个“虚拟字段”,那将会破坏......

#2

You can't do it in a SQL command in a simple and clean way. However, the string 'a:1:{s:11:"valid_until";i:1370695666;}' is simply a serialized PHP array.

您无法以简单而干净的方式在SQL命令中执行此操作。但是,字符串'a:1:{s:11:“valid_until”; i:1370695666;}'只是一个序列化的PHP数组。

Do this test:

做这个测试:

print_r(unserialize('a:1:{s:11:"valid_until";i:1370695666;}'));

The output will be:

输出将是:

Array ( [valid_until] => 1370695666 )

So, if you do the following, you can retrieve your valid_until value:

因此,如果执行以下操作,则可以检索valid_until值:

$arrayProfileData = unserialize('a:1:{s:11:"valid_until";i:1370695666;}');
$validUntil = arrayProfileData['valid_until'];

So, a solution would be to select ALL items in the table, do a foreach loop, unserialize each "profile_fields" field as above, check the timestamp, and store the primary key of each registry to be deleted, in a separate array. At the end of the loop, do a single DELETE operation on all primary keys you stored in the loop. To do that, use implode(',', $arrayPKs).

因此,解决方案是选择表中的所有项,执行foreach循环,如上所述反序列化每个“profile_fields”字段,检查时间戳,并将每个要删除的注册表的主键存储在单独的数组中。在循环结束时,对存储在循环中的所有主键执行单个DELETE操作。为此,请使用implode(',',$ arrayPKs)。

It's not a very direct route, and depending on the number of registers, it may not be slow, but it's reliable.

它不是一个非常直接的路径,并且根据寄存器的数量,它可能不会很慢,但它是可靠的。

Consider rixo's comment: if you can, put the "valid_until" in a separate column. Serializing data can be good for storage of non-regular data, but never use it to store data which you may need to apply SQL filters later.

考虑一下rixo的评论:如果可以的话,将“valid_until”放在一个单独的列中。序列化数据可以很好地存储非常规数据,但从不使用它来存储稍后可能需要应用SQL过滤器的数据。

#1