How to use PHP explode function & insert values into MYSQL

Question

I'm using the following code to grab my form's data and send to MYSQL:

$id=$_POST['id'];
$tag=$_POST['tag']; 
$race=$_POST['race']; 
$city=$_POST['city']; 
$pic=($_FILES['photo']['name']);

and inserting it into my table using:

mysql_query("INSERT INTO `people` VALUES ('$id', '$tag', '$race', '$city', '$pic')") ;

My problem is that I need to allow users to enter multiple values (separated by commas or spaces) into the 'tag' field of my form to allow for multiple people to be tagged in the same image.

So I have 2 questions. First, is it best practice to create entirely new database entries for each 'bib' tag? Or would it be better to store the comma delimited list in a single column? Secondly, I have been advised to use the explode function. However, I haven't been able to figure out how to translate this into my current INSERT query. If anyone could steer me in the right direction, I'd really appreciate it. Very new to PHP and just trying to teach myself some basics.

Thanks.

Hammerite · Accepted Answer · 2011-09-17 19:34:19Z

1

So I have 2 questions. First, is it best practice to create entirely new database entries for each 'bib' tag? Or would it be better to store the comma delimited list in a single column?

Storing a comma-separated list in a database column is generally a mistake. It will make it very hard to write queries that try to find out (for example) which images a given person has been tagged in. You should read up on database normalisation. Ordinarily what you will do in a case like this, where a given photo can contain arbitrarily many people, is have a table image_person with two columns: one column specifies the image ID, and the other column specifies a person. A row in the table corresponds to the statement "this person has been tagged in this image". This makes it much easier to query the information you have stored.

Secondly, I have been advised to use the explode function.

Assuming that you do not go for the comma-separated list approach (as I would recommend), the only place you might need to use explode in this code is if you pass the list of people to be tagged as a comma-separated list in one of the POST variables. In that case you might do something like:

$tags = explode(',', $_POST['tags']);

You could then take either of two approaches:

loop through all of the tags at an appropriate point in the code, and issue an INSERT query at each pass through the loop
construct a single INSERT query that adds all the tags at once.

Additional comment

Please read about SQL injection and make sure that you write secure code. The code you posted in your question is highly insecure.

answered Sep 17, 2011 at 19:34

Hammerite

22.5k6 gold badges76 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Chance Over a year ago

Thank you very much. That'll give me something to work on for the afternoon. Sorry...one additional question regarding your suggestion:

Chance Over a year ago

Below is my code that allows users to search for photos that have been tagged. The select a race name and type in the tag number. If I split things into 2 tables (one table with photo id/tags and the other table containing my race names and city), how will the search draw from the 2 sources? $tag = $_POST['tag']; $race = $_POST['race']; $data = mysql_query("select * from athletes where tag LIKE '%$tag%' and race LIKE '%$race%'");

Hammerite Over a year ago

I don't understand what the original table is intended to model - do rows in the original table correspond to races? to athletes? to images? It's hard for me to answer your question without understanding what the table looks like. You should probably consider asking another question, in which you explain the current design of the table(s), ask for feedback on it and ask people to explain how you would solve those problems you don't know how to solve. Either that, or spend some time looking up more information regarding database normalisation.

Astha · Accepted Answer · 2011-09-17 19:23:56Z

0

Its fine to use comma seperated tags for people if its only for one column in a table.

explode function will not be used this time. it will be used when you will extract those values from db for further process.

if comma seperated string is saved in db then when you will need it back you can do something like

$arr = explode(",",$_row['tag']);

where $_row['tag'] is your data value for tag on fetch.

answered Sep 17, 2011 at 19:23

Astha

1,7345 gold badges17 silver badges36 bronze badges

1 Comment

thegaffney Over a year ago

Wouldnt storing a list of data in one column be against database normalization? Also what if you wanted to remove just one tag from the list of tags? You would have to get the tags, do a str_replace, and insert it back into the table, if tags were a separate table, you would just need a delete statement

Priyank · Accepted Answer · 2014-09-01 13:25:52Z

0

in your $tag = $_POST['tag'];

you contains list of tags,so now you just use explode function to get these values in chunks.

for this you have to follow given code

$tag=$_POST['tag'];

$arrs=explode(", ",$tag);

foreach($arrs as $arr){

$insertTag .= $arr;

}
echo $insertTag;

mysql_query("INSERT INTO `people` VALUES ('$id', '$insertTag', '$race', '$city', '$pic')") ;

answered Sep 1, 2014 at 13:25

Priyank

3,8884 gold badges33 silver badges50 bronze badges

Collectives™ on Stack Overflow

How to use PHP explode function & insert values into MYSQL

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related