How can I update two unrelated mysql tables with a php script?

Question

How can I update two unrelated mysql tables with a php script? I use something like this. If i use two different $sql statements like $sql1 $sql2 its not working.

// mysql update row with matched pid
    $result1 = mysql_query("UPDATE users SET users.p = users.p - '$p', users.n = users.n -'$n'  WHERE users.uid = $uid");
    $result2 = mysql_query("UPDATE names SET names.p = names.p +  '$p', names.n = names.n + '$n'  WHERE names.p_id = $p_id");
    // check if row inserted or not
    if ($result1 && $result2) 
   {
        // successfully updated
        $response["success"] = 1;
        $response["message"] = "successfully updated.";

        // echoing JSON response
        echo json_encode($response);
    }

What doesn't work? Any errors? Does one work and not the other? I am assuming that you have actually instantiated your db connection. — Lance, Jan 23 '15 at 07:38
Yes the connection is ok, both are not working, I read that I should use a transaction?! how can I use it here? — , Jan 23 '15 at 07:45
http://stackoverflow.com/questions/2708237/php-mysql-transactions-examples — Lance, Jan 23 '15 at 07:48
You can check error if occur like [this](https://eval.in/250562). and please let me know the error — Faruk Omar, Jan 23 '15 at 07:50
You no need to use '$p' , you just use users.p - $p if the value is number — Faruk Omar, Jan 23 '15 at 07:54
No $p= intval($_POST['p']); this is ok . you need to change use users.p - '$p' to use users.p - $p in your update sql — Faruk Omar, Jan 23 '15 at 08:55

score 0 · Answer 1 · answered Jan 23 '15 at 07:45

0

You subtract string from string??? users.p = users.p - '$p'

answered Jan 23 '15 at 07:45

Дмитрий Чистик

608
5
16

I want to subtract an integer from a posted param (is it a string hmm ) in android How I can converted to integer to make the subtraction? – Jan 23 '15 at 07:48

Faruk Omar · Accepted Answer · 2015-01-23T09:05:05.973

0

first use users.p - $p instead of users.p - '$p' second return mysql error if occur

$p= intval($_POST['p']);
$n= intval($_POST['n']);

parse data like above

<?php
// mysql update row with matched pid
$result1 = mysql_query("UPDATE users SET users.p = users.p - $p, users.n = users.n -$n  WHERE users.uid = $uid");
$result2 = mysql_query("UPDATE names SET names.p = names.p +  $p, names.n = names.n + $n  WHERE names.p_id = $p_id");
// check if row inserted or not
if ($result1 && $result2) {
   // successfully updated
    $response["success"] = 1;
    $response["message"] = "successfully updated.";

    // echoing JSON response
    echo json_encode($response);
} else {

   // failed update
    $response["success"] = 0;
    $response["message"] = "Mysql error is : " . mysql_error();

    // echoing JSON response
    echo json_encode($response);
}
?>

edited Jan 23 '15 at 09:05

answered Jan 23 '15 at 08:59

Faruk Omar

1,173
2
14
22

Its not updating any table, In android logcat the parameters passed look like these 01-23 12:32:15.771: D/Params Values(20812): [p_id=3, p=0, n=1] I am using utf8 as parsing but I get JSONException: End of input at character 0 of – Jan 23 '15 at 10:49
That means mysql generate error, you will find it in your response variable message index where success will be 0 – Faruk Omar Jan 23 '15 at 12:15
It worked thank you I just echo json_encode($response, JSON_UNESCAPED_UNICODE); at the end – Jan 23 '15 at 12:24
It works and like this if (mysqli_query($conn, $sql) && mysqli_query($conn, $sql2) ) { $response["success"] = 1; $response["message"] = "successfully updated."; // echoing JSON response echo json_encode($response,JSON_UNESCAPED_UNICODE); } else { $response["success"] = 0; $response["message"] = "not successfully updated."; // echoing JSON response echo json_encode($response,JSON_UNESCAPED_UNICODE); } – Jan 23 '15 at 12:26

How can I update two unrelated mysql tables with a php script?

2 Answers2