Select row(s) using join then update mysql table

Question

Ok basically I need to select the submitted_link row in image 1 based on the tab_id from image 1 and id from image 2 (the join). After this is selected I need to update the tab_content row in image 2 for each of the id's in image 2. Below the images is what I have code wise right now.

Image 1:

Database Help http://bekustom.com/Untitled-1.jpg

Image 2:

Database Help http://bekustom.com/Untitled-2.jpg

My Code:

$sql = ("SELECT submitted_links.submitted_link, submitted_links.tab_id, users_tabs.id FROM users_tabs, submitted_links WHERE submitted_links.tab_id=users_tabs.id AND submitted_links.user_id=users_tabs.user_id");

$result = mysqli_query($db, $sql);
while($row = $result->fetch_array()) {
$rows[] = $row;
}

foreach($rows as $row) {
$tab_content[] = $row['submitted_link'];
$tab_id = $row['tab_id'];

$tab_implode = implode(",", $tab_content);
$sql2 = ("UPDATE users_tabs SET tab_content='".$tab_implode."' WHERE id='".$tab_id."'");
$result2 = $db->query($sql2);
}

What I'm getting in tab_content currently:

ID 17: http://google.com,http://twitter.com,http://google2.com
ID 18: http://google.com,http://twitter.com

Desired data in tab_content:

ID 17: http://google.com,http://google2.com
ID 18: http://twitter.com

Can be more clear regarding the final state of database that you want? — Manu, Jan 05 '14 at 13:40
The update works but does not give me the desired data into the tab_content column for each id in image 2. The tab_content column should read `http://google.com,http://google2.com` for id 17, and `http://twitter.com` for id 18 in image 2. — BeKustom, Jan 05 '14 at 13:44
Updated question to show current results in tab_content vs desired results in tab_content. — BeKustom, Jan 05 '14 at 13:47

Manu · Accepted Answer · 2014-01-05T14:41:05.440

1

You need to change your PHP code!

Try this...

$sql = "SELECT 
submitted_links.submitted_link, 
submitted_links.tab_id, 
users_tabs.id, 
GROUP_CONCAT( submitted_link ) AS all_links 
FROM 
users_tabs, submitted_links 
WHERE 
submitted_links.tab_id = users_tabs.id 
AND submitted_links.user_id=users_tabs.user_id 
GROUP BY submitted_links.tab_id";

$result = mysqli_query($db, $sql);
while($row = $result->fetch_array()) 
{
    $sql2 = "UPDATE 
             users_tabs 
             SET 
             tab_content='" . $row['all_links'] . "' 
             WHERE id='" . $row["tab_id"] . "'";

    $result2 = $db->query($sql2);
}

edited Jan 05 '14 at 14:41

answered Jan 05 '14 at 14:16

Manu

901
1
8
28

No errors being reported but it's not updating the tab_content column. – BeKustom Jan 05 '14 at 14:26
It looks correct to me logically... try to debug and see where the problem is! – Manu Jan 05 '14 at 14:30
Check what is the output of the first query.. After that check `echo $sql2` in the loop!! – Manu Jan 05 '14 at 14:32
This worked after I tracked down the issue which was just a typo: Was: `GROUP_CONCAT( submitted_links ) AS all_links` Should be: `GROUP_CONCAT( submitted_link ) AS all_links` – BeKustom Jan 05 '14 at 14:38
Ohh!! Ok.. I have updated the answer to reflect the same.! :) – Manu Jan 05 '14 at 14:41
Someone else also told me the same thing.. hahaha.. http://stackoverflow.com/questions/20933812/how-would-i-get-values-from-2-rows-into-one/20933992?noredirect=1#comment31435226_20933992 Glad to help! – Manu Jan 05 '14 at 14:44
I got another quick question @Manu I'm trying to use the following inside the while loop after the update in your code above but it only returns the last item after the last comma when I explode it instead of listing them all from the tab_content column. Any ideas? `foreach(explode(',', $row9['all_links']) as $split_links) { $fix_links = ''.$split_links.'
'; }` – BeKustom Jan 06 '14 at 02:03
Nevermind @Manu I got it here's the code: `$fix_links = ''; foreach(explode(',', $row9['all_links']) as $split_links) { $split_links = trim($split_links); $fix_links .= ''.$split_links.'
'; }` – BeKustom Jan 06 '14 at 02:26

Kumar V · Answer 2 · 2014-01-05T14:34:36.207

0

Try this query in your mysql environment. If it is worked, then no need of while , foreach loops

UPDATE users_tabs  AS c   
    join (SELECT o.tab_id, GROUP_CONCAT(o.submitted_links SEPARATOR ',' ) as  'links'  
     FROM submitted_links AS o  GROUP BY o.tab_id) as mysub_tab
    on c.id= mysub_tab.tab_id
      SET c.tab_content = mysub_tab.links

edited Jan 05 '14 at 14:34

answered Jan 05 '14 at 13:50

Kumar V

8,810
9
39
58

MySQL gave this error: You can't specify target table 'c' for update in FROM clause – BeKustom Jan 05 '14 at 13:55
This look like oracle syntax to update select. Try inner join as shown on this question: http://stackoverflow.com/questions/11247982/update-multiple-rows-using-select-statement – Jorge Campos Jan 05 '14 at 14:00
@kumar_v also generated an error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT o.tab_id, GROUP_CONCAT(DISTINCT o.submitted_links SEPARATOR ',' ) as 'li' at line 2 – BeKustom Jan 05 '14 at 14:09
@BeKustom Updated my answer again. I couldn't test due to some problem in my system. – Kumar V Jan 05 '14 at 14:14
@kumar_v Another error: #1054 - Unknown column 'o.tab_id' in 'field list' – BeKustom Jan 05 '14 at 14:27
@kumar_v Nope: #1054 - Unknown column 'o.submitted_links' in 'field list' It's all good manu's solution worked for me. Thanks for the help. – BeKustom Jan 05 '14 at 14:41
@BeKustom I tried to give best solution without using loop. But you are not trying from your side. his solution is working in my side. but not in your side means, you need to adjust fields name.. etc.. – Kumar V Jan 05 '14 at 15:04
@kumar_v Oh I tried believe me for the past 6 hours I have tried. His solution works now that I found the typo in the GROUP_CONCAT and it has since been corrected in the given solution by manu so of course it works. Have a good day bud. – BeKustom Jan 05 '14 at 15:25

Select row(s) using join then update mysql table

2 Answers2