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, Apr 10 '17 at 15:39
What's the schema of the tables involved? Will the UPDATE rewritten as a select return any rows? — Sloan Thrasher, Apr 10 '17 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, Apr 10 '17 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, Apr 10 '17 at 15:47
Thanks for adding the schema, but could you limit it to just the tables involved in this query??? — Sloan Thrasher, Apr 10 '17 at 15:47
Example: http://stackoverflow.com/questions/6514649/php-mysql-bind-param-how-to-prepare-statement-for-update-query — xQbert, Apr 10 '17 at 15:48
The two sql statements don't match up. The second one appears to be nonsense. — Sloan Thrasher, Apr 10 '17 at 15:49
@xQbert Thank you very much for the provided link. I will explore it more once I can get the query running properly. — Chizzy Meka, Apr 10 '17 at 15:53
One possible problem is that you are trying to update potentially non-existent rows. If you can't write it without the LEFT JOINS, you should probably do separate updates on each table, inside a transaction. — Sloan Thrasher, Apr 10 '17 at 15:58
@SloanThrasher Okay, very good to know. I will try and run the select statement and let you know. Meanwhile, thank you very much for your assistance. — Chizzy Meka, Apr 10 '17 at 16:02

score 0 · Answer 1 · answered Apr 10 '17 at 15:59

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 '17 at 15:59

Sloan Thrasher

4,953
3
22
40

@ChizzyMeka: So, did this return the rows you expected, without any nulls? – Sloan Thrasher Apr 10 '17 at 16:09
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. – Chizzy Meka Apr 10 '17 at 17:18

MySQL Multiple Table Update Statement

1 Answers1