MySQL Multiple Table Update Statement

Question

I am working on a study planner. The SQL query below is supposed to run when a logged in user tries to save a study plan from the welcome page. However, the code does not do anythin. The input values just disappear upon submission with no initialisation of the $result variable, no flag changes and no any error print-outs when I run the application via NetBeans...

// Set up a flag for monitoring the individual queries
    $flag = true;

    $query = "UPDATE
    `modulecodes`
    LEFT JOIN `moduletitles` ON `moduletitles`.`modulecodeid` = `modulecodes`.`modulecodeid`
    LEFT JOIN `studyplans` ON `studyplans`.`modulecodeid` = `modulecodes`.`modulecodeid`
    LEFT JOIN `comments` ON `comments`.`modulecodeid` = `modulecodes`.`modulecodeid`
    SET
    modulecode = '$moduleCode', moduletitle = '$moduleTitle', studydate = '$moduleStudyDate', numberofstudyhours = '$moduleAllocatedHours', comment = '$studyPlanComments'
    WHERE userid = '$userid';";

    // Execute the query and put the result in the variable $result
    $result = mysqli_query($mysqli, $query);
    if (!$result) {
        $flag = false;
        echo $flag;
        echo "Error details for Result: " . mysqli_error($mysqli) . ".";
    }

...And with hard-coded values (as per below) and direct test in phpMyAdmin, it runs but comes back with 0 rows affected. (Query took 0.0069 seconds.) and no changes in the database, whatsoever. So, I feel like I haven’t structured it well. Can someone kindly guide me on how to convert the query into one with a subquery or something more efficient and reliable, please or at the least, help point out what I am doing wrong?

I'd suspect other parts of the code but the fact that a direct test in phpMyAdmin tells me, the issue is with the SQL statement, besides I have already used other parts of the code elsewhere and with another query and it runs fine.

UPDATE
    `modulecodes`
    LEFT JOIN `moduletitles` ON `moduletitles`.`modulecodeid` = `modulecodes`.`modulecodeid`
    LEFT JOIN `studyplans` ON `studyplans`.`modulecodeid` = `modulecodes`.`modulecodeid`
    LEFT JOIN `comments` ON `comments`.`modulecodeid` = `modulecodes`.`modulecodeid`
    SET
    modulecode = 'P00100', moduletitle = 'Java', studydate = '2017/04/10', numberofstudyhours = '1', comment = 'Java'
    WHERE userid = 20;

Database Code:

CREATE TABLE `comments` (
  `commentid` int(10) NOT NULL,
  `modulecodeid` int(10) NOT NULL,
  `comment` text,
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


-- --------------------------------------------------------

--
-- Table structure for table `modulecodes`
--

CREATE TABLE `modulecodes` (
  `modulecodeid` int(10) NOT NULL,
  `userid` int(10) NOT NULL,
  `modulecode` varchar(10) NOT NULL,
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `moduletitles`
--

CREATE TABLE `moduletitles` (
  `moduletitleid` int(10) NOT NULL,
  `modulecodeid` int(10) NOT NULL,
  `moduletitle` varchar(100) NOT NULL,
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `studyplans`
--

CREATE TABLE `studyplans` (
  `studyplan` int(10) NOT NULL,
  `modulecodeid` int(10) NOT NULL,
  `studydate` date NOT NULL,
  `numberofstudyhours` int(10) NOT NULL,
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------


--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `userid` int(10) NOT NULL,
  `firstname` varchar(100) NOT NULL,
  `lastname` varchar(100) NOT NULL,
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

1) use paramaterized SQL; otherwise you're open to SQL injection. — xQbert
– xQbert, Commented Apr 10, 2017 at 15:39
What's the schema of the tables involved? Will the UPDATE rewritten as a select return any rows? — Sloan Thrasher
– Sloan Thrasher, Commented Apr 10, 2017 at 15:43
@xQbert Frankly, I don’t know the first thing about what you just mentioned but I will quickly research it. Meanwhile can you please demonstrate, based on the given SQL statement? — Chizzy Meka
– Chizzy Meka, Commented Apr 10, 2017 at 15:43
@SloanThrasher I understand the first part of your question and have now included the SQL code for the DB, however, I don’t quite understand what you are asking in the second question. Can you please skim through the DB code or elaborate on your second question, please? — Chizzy Meka
– Chizzy Meka, Commented Apr 10, 2017 at 15:47
Thanks for adding the schema, but could you limit it to just the tables involved in this query??? — Sloan Thrasher
– Sloan Thrasher, Commented Apr 10, 2017 at 15:47

Sloan Thrasher · Accepted Answer · 2017-04-10 15:59:44Z

0

To test the relationships, see if this returns the rows you expect to be updated:

SELECT 
    a.`modulecode`, 
    b.`moduletitle`, 
    c.`studydate`, 
    c.`numberofstudyhours`, 
    d.`comment`
FROM `modulecodes` a
    LEFT JOIN `moduletitles` b ON b.`modulecodeid` = a.`modulecodeid`
    LEFT JOIN `studyplans` c ON c.`modulecodeid` = a.`modulecodeid`
    LEFT JOIN `comments` d ON d.`modulecodeid` = a.`modulecodeid`
WHERE `userid` = '$userid';

If any of the columns are NULL (joined table row not found), then the update will fail.

answered Apr 10, 2017 at 15:59

Sloan Thrasher

5,0383 gold badges26 silver badges44 bronze badges

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

2 Comments

Sloan Thrasher Over a year ago

@ChizzyMeka: So, did this return the rows you expected, without any nulls?

Chizzy Meka Over a year ago

Hey, thanks for checking up on me. I feel the problem is with the surrounding PHP code, so I have been trying to see where the issue is. Thank you very much for your help, though. It is well appreciated.

Collectives™ on Stack Overflow

MySQL Multiple Table Update Statement

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related