Using explode, split, or preg_split to store and get multiple database entries

I'm trying to figure out how and which is best for storing and getting multiple entries into and from a database. Either using explode, split, or preg_split. What I need to achieve is a user using a text field in a form to either send multiple messages to different users or sharing data with multiple users by enter their IDs like "101,102,103" and the PHP code to be smart enough to grab each ID by开发者_如何转开发 picking them each after the ",". I know this is asking a lot, but I need help from people more skilled in this area. I need to know how to make the PHP code grab IDs and be able to use functions with them. Like grabbing "101,102,103" from a database cell and grabbing different stored information in the database using the IDs grabbed from that one string.

How can I achieve this? Example will be very helpful.

Thanks

If I understand your question correctly, if you're dealing with comma delimited strings of ID numbers, it would probably be simplest to keep them in this format. The reason is because you could use it in your SQL statement when querying the database.

I'm assuming that you want to run a SELECT query to grab the users whose IDs have been entered, correct? You'd want to use a SELECT ... WHERE IN ... type of statement, like this:

// Get the ids the user submitted
$ids = $_POST['ids'];
// perform some sanitizing of $ids here to make sure 
// you're not vulnerable to an SQL injection
$sql = "SELECT * FROM users WHERE ID IN ($ids)";
// execute your SQL statement

Alternatively, you could use explode to create an array of each individual ID, and then loop through so you could do some checking on each value to make sure it's correct, before using implode to concatenate them back together into a string that you can use in your SELECT ... WHERE IN ... statement.

Edit: Sorry, forgot to add: in terms of storing the list of user ids in the database, you could consider either storing the comma delimited list as a string against a message id, but that has drawbacks (difficult to do JOINS on other tables if you needed to). Alternatively, the better option would be to create a lookup type table, which basically consists of two columns: messageid, userid. You could then store each individual userid against the messageid e.g.

messageid | userid
1 | 1
1 | 3
1 | 5

The benefit of this approach is that you can then use this table to join other tables (maybe you have a separate message table that stores details of the message itself).

Under this method, you'd create a new entry in the message table, get the id back, then explode the userids string into its separate parts, and finally create your INSERT statement to insert the data using the individual ids and the message id. You'd need to work out other mechanisms to handle any editing of the list of userids for a message, and deletion as well.

Hope that made sense!

Well, considering the three functions you suggested :

• explode() will work fine if you have a simple pattern that's always the same.
  • For instance, always ', ', but never ','
• split() uses POSIX regex -- which are deprecated -- and should not be used anymore.
• preg_split() uses a regex as pattern ; and, so, will accept more situations than explode().

Then : do not store several values in a single database column : it'll be impossible to do any kind of useful work with that !

Create a different table to store those data, with a single value per row -- having several rows corresponding to one line in the first table.

I think your problem is more with SQL than with PHP.

Technically you could store ids into a single MySQL field, in a 'set' field and query against it by using IN or FIND_IN_SET in your conditions. The lookups are actually super fast, but this is not considered best practice and creates a de-normalized database.

What is nest practice, and normalized, is to create separate relationship tables. So, using your example of messages, you would probably have a 'users' table, a 'messages' table, and a 'users_messages' table for relating messages between users. The 'messages' table would contain the message information and maybe a 'user_id' field for the original sender (since there can only be one), and the 'users_messages' table would simply contain a 'user_id' and 'message_id' field, containing rows linking messages to the various users they belong to. Then you just need to use JOIN queries to retrieve the data, so if you were retrieving a user's inbox, a query would look something like this:

SELECT
    messages.*
FROM
    messages
    LEFT JOIN users_messages ON users_messages.message_id = messages.message_id
WHERE
    users_messages.user_id = '(some user id)'