PHP/MySQL - Update array if variable does not exist

Question

I have an array with different IDs which I get from my database.

$fotos_temp_list=explode(",",$fotos_temp["fa_id"][0]);
Result: fa_id => 15,16,17,18

Now I want to update all rows inside another table with the IDs 15,16,17,18 and insert a specific '$id' into a column called 'fa_ev_id'. In this example I set:

$id=1;

Now my foreach-loop looks like this:

foreach ($fotos_temp_list as $key) {
    UPDATE images SET fa_ev_id = Concat(fa_ev_id , ',' ,'".$id."') where fa_id='".$key."' ";
}

This part is working too. Every row with my IDs (in my example: 15,16,17,18) gets updated.

Problem: When I run the foreach-loop again, the '$id' will be saved again inside the row. See here:

Question: How is it possible, that I can check if '$id' is already inside the row and if so, it gets skipped? Here is what I tried to do:

foreach ($fotos_temp_list as $key) {
    if (!in_array($id,$key)===true) {
        UPDATE fotoalbum SET fa_ev_id = Concat(fa_ev_id , ',' ,'".$id."') where fa_id='".$key."'
    }
}

I think the in_array function checks if '$id' id already inside '$key' and if it is true, the UPDATE statement is done. Therefore I added '!in_array' but it doesn´t seems to work. Does anyone has an idea what I am doing wrong? I just want to check if the '$id' is already inside my database row and if so, it should not insert the '$id' again. I appreciate any advice.

Looks more like a problem of poorly structured database. Please explain to me, why are you saving IDs in a comma separated string instead of a typical 1-to-many model? — Havenard
– Havenard, Commented Sep 12, 2018 at 16:01
what's in $fotos_temp_list at the time you run this? And why on earth are you storing multiple values inside a single field? A properly designed relational database would not do this. You should have one value per field. If you don't, it indicates a flaw in your schema design. Have you studied relational database design and normalisation at all? — ADyson
– ADyson, Commented Sep 12, 2018 at 16:01
WARNING: Whenever possible use prepared statements to avoid injecting arbitrary data in your queries and creating SQL injection bugs. These are quite straightforward to do in mysqli and PDO where any user-supplied data is specified with a ? or :name indicator that’s later populated using bind_param or execute depending on which one you’re using. — tadman
– tadman, Commented Sep 12, 2018 at 17:08

Ojo Tokunbo · Accepted Answer · 2018-09-12 16:06:13Z

1

The in_array() function in php accepts an array as its second parameter, in (!in_array($id,$key)===true), https://www.w3schools.com/php/func_array_in_array.asp. The second parameter $key, passed in here isn't an array.

You could save the datatype of fa_ev_id as json, and retrieve and json_decode the value when you are about performing an update the field to know if that $id already exists using in_array() or you could retrieve the values, expolode them to form an array and then check if it exists with in_array().

answered Sep 12, 2018 at 16:06

Ojo Tokunbo

561 silver badge5 bronze badges

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

2 Comments

Christoph C. Over a year ago

Thank you. I added your answer as the correct answer cause I ignored that $key cannot be an array. I fetched the data per json and checked with in_array() if the ID is already in. It works now. Thank you.

tadman Over a year ago

Please try to avoid linking to third-rate sites like w3schools. The official documentation is almost always far, far better, and includes community comments that improve it even more. Here's the equivalent documentation for in_array.

Collectives™ on Stack Overflow

PHP/MySQL - Update array if variable does not exist

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related