Mysql Select and Insert into another table with Php foreach loop

Question

I have a created a new table (colours) which has id and hex as columns, all existing ids are kept in the pics table along with the path of the picture, which will go into the new colours table with the many hex values. I am using a class to extract the prominent colours from the picture as array of hex values.

This is the code so far:

$data = $conn->query('SELECT id,pic_path FROM pics WHERE pic_id = 1231');
while($row = $data->fetch()) {
    $image = new ColorsOfImage('images/'.$data['pic_path']);
    $colors = $image->getProminentColors();

    foreach($colors as $key => $val)
    {
        $sql = "INSERT INTO colours (pic_id,colour) VALUES (:pic_id,:colour)";
        $q = $conn->prepare($sql);
        $q->execute(array(':pic_id'=>$data['pic_id'],':colour'=>$val));
    }
}

But I get this error:

Fatal error: Cannot use object of type PDOStatement as array

This is how it should look like this once it's populated:

sample fields

@Dagon please elaborate?

Abu Nooh
– Abu Nooh

2015-05-21 22:47:04 +00:00
Commented May 21, 2015 at 22:47 — Abu Nooh
– Abu Nooh, Commented May 21, 2015 at 22:47

castis · Accepted Answer · 2015-05-21 22:43:43Z

2

You used $data['pic_id'] when what you wanted was $row['pic_id'].

answered May 21, 2015 at 22:43

castis

8,2144 gold badges45 silver badges66 bronze badges

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

1 Comment

Abu Nooh Over a year ago

Wow! That was a daft oversight! Thanks

score 1 · Accepted Answer · 2015-05-21 22:52:20Z

1

This could be done with one single query, no php, no loop required

INSERT INTO colours (pic_id,colour)
SELECT id,pic_path FROM pics
WHERE pic_id = 1231

full details: https://dev.mysql.com/doc/refman/5.1/en/insert-select.html

edited May 21, 2015 at 22:52

answered May 21, 2015 at 22:48

user557846

2 Comments

Abu Nooh Over a year ago

Oh I see, I only used pic_id to test, really I would use something like WHERE pic_id >1231, would that work?

user557846 Over a year ago

i only know one way to find out

Collectives™ on Stack Overflow

Mysql Select and Insert into another table with Php foreach loop

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related