Updating a 4th table using 3 different tables

Question

I've got 3 different tables and I want to update the 4th table with some specific column from each of the 3 tables, they all have a common key. I can do this from the phpmyadmin, but I want to do it using a php script. This is what I tried but it didn't work

if (isset($_GET)) { 
    $update = '';
    $count="SELECT * FROM test2   ";          
    foreach ($connect->query($count) as $row) {
         $term_total1=$row['english'];
         $sql = "UPDATE total set `f_test` ='$term_total1'  ";
         foreach ($connect->query($count) as $row) {

             echo "success<br>" . $term_total1;
         }
    }  
}else{
    echo "try another method" . mysqli_error($connect);
}

Have been trying for days now.

Repeated the same code for the other two tables but it won't work. Is it possible to do it in a single query? If Yes, then how

You do see that you have not closed the double quoted literal on line one... **Dont you?** — RiggsFolly, Apr 26 '18 at 08:59
Oh man it looks weird seeing `if` `echo` `foreach` `else` like that. — IsThisJavascript, Apr 26 '18 at 09:00
And if you're using `mysqli` you should be storing the result set `$res = $connect->query($sql)` and then using `fetch_assoc()` on the result set `while ($row = $res->fetch_assoc())` — IsThisJavascript, Apr 26 '18 at 09:02
And `$result =$connect->query ($result);` is also wrong it should be `$result =$connect->query ($sql);` — RiggsFolly, Apr 26 '18 at 09:03
While developing Add [error reporting](http://stackoverflow.com/questions/845021/how-to-get-useful-error-messages-in-php/845025#845025) to the top of your file(s) _while testing_ right after your opening PHP tag for example ` — RiggsFolly, Apr 26 '18 at 09:04
And if you are using MYSQLI also Add `ini_set('display_errors', 1); ini_set('log_errors',1); error_reporting(E_ALL); mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);` to the top of your script. This will force any `mysqli_` errors to generate an Exception that you can see on the browser and other errors will also be visible on your browser. — RiggsFolly, Apr 26 '18 at 09:04
Wouldn't `Foreach ($connect->query ($sql) as $row){` actually break the script since it's constantly executing the query? — IsThisJavascript, Apr 26 '18 at 09:06
So this isn't even the correct source? So you just expected someone here to guess the solution? And for that reason, I'm out. Edit: OP deleted his comment; it was something like "This isnt the code I just typed it up on my phone" ! — IsThisJavascript, Apr 26 '18 at 09:07
Pls overlook the typos, the real script is on my laptop, i just used my phone. — Godwin, Apr 26 '18 at 09:07
Look at what I said about using `while ($row = $res->fetch_assoc())`. Read again what RiggsFolly has said. Count your brackets aswell.. — IsThisJavascript, Apr 26 '18 at 09:19
This code is heavily different from what you originally wrote up. Do you have a unique identifier for your SQL or did you just want to mass update everything? — IsThisJavascript, Apr 26 '18 at 09:25

IsThisJavascript · Accepted Answer · 2018-04-26T09:43:19.940

0

I'm pretty sure your method of using foreach to loop the result set is incorrect. In your updated code you've also not got a unique identifier so your code is just going to mass update your table. Here's your current code fixed up so hopefully you can understand how to loop the dataset from mysqli

$count="SELECT * FROM test2";     
if($res = $connect->query($count)){
    while($row = $res->fetch_assoc()){
        $term_total1 = $row['english'];
        $sql = "UPDATE total set `f_test` = '{$term_total1}'";
        if($res2 =$connect->query($sql)){
            echo "success<br>" . $term_total1;
        }else{
            print_r($connect->error());
        }

    }
}else{
     print_r($connect->error());
}

edited Apr 26 '18 at 09:43

answered Apr 26 '18 at 09:28

IsThisJavascript

1,726
2
16
25

Yes I have a unique identifier. Just made the corrections and a fatal error pops up, uncaught error: call to member function fetch_assoc() on Boolean – Godwin Apr 26 '18 at 09:33
That means `$connect->query($count);` failed. Ensure your `$count` query is correct and make sure you actually have a database connection. Updated my code to show you have to enable debug messages – IsThisJavascript Apr 26 '18 at 09:35
Tried it without the while loop, it gets updated but only with a single value and not all the value – Godwin Apr 26 '18 at 09:39
See updated code for error reporting on second query. Edit: My bad query 2 shouldn't have a `fetch_assoc` as it's an `update` query. Updated code to reflect that. – IsThisJavascript Apr 26 '18 at 09:43
Thanks its working without errors, all the data in the column from the first table is being displayed, but the last data in the column of 'test2' is used to update the whole column of total – Godwin Apr 26 '18 at 09:56
I don't understand – IsThisJavascript Apr 26 '18 at 10:00
In the database all the rows of column f_test gets updated but only with a single value of test2. But when I echoed $term_total all the value is displayed on the browser. The value is displayed on the browser but only value value is used to update column in the database – Godwin Apr 26 '18 at 10:06
What does **your** `$sql` variable look like? – IsThisJavascript Apr 26 '18 at 10:07
Its integer. I just edited your answer by adding a snapshot of the dB – Godwin Apr 26 '18 at 10:17
I'm not sure what that edit request was for; I don't quite understand the picture. If you're still getting mass updates that's because you have no set unique identifier in `$sql` as I said once in the comments and once in my answer already. – IsThisJavascript Apr 26 '18 at 10:17
No @Emmanuel I wanted to know what your `$sql = "UPDATE total set `f_test` = '{$term_total1}'";` looked like... Did you change it like you said in your first comment `Yes I have a unique identifier. Just made the corrections` ? – IsThisJavascript Apr 26 '18 at 10:18
1

Oh sorry. Its working well now. Added a where clause, "WHERE s_I'd ='". $row['s_id']."'; – Godwin Apr 26 '18 at 10:25

Updating a 4th table using 3 different tables

1 Answers1