Android PHP PDO MySQL UPDATE Syntax Error

Question

I'm Johnny. I'm Japanese Man. PHP-PDO is Hard!

add(14:24) SQLSTATE[42000]: Syntax error or access violation: 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 ' 名前（カナ）, 生年月日, 郵便番号, 都道府県コード, 住所1,' at line 1

UPDATE SQL ERROR is

SQLSTATE[42000]: Syntax error or access violation: 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 '' at line 1

enter code here

function InsertUpdate($pdo, $tbname, $stmt) {

    if (isset($_POST['callName'])) {
        if ($callName = $_POST['callName']) {
            if ($callName === 'callPer') $tbname = 'Customer';
            if ($callName === 'callCar') $tbname = 'CarInfo2';
        }
    } else {
        echo "Confirmationからの送信";
    }

    $stmt = $pdo->query('DESCRIBE '.$tbname);

    $tabCount = (is_numeric($_POST['車両判別区分']) ? (int)$_POST['車両判別区分'] : 0);

    while ($row = $stmt->fetch()) {

        $key = $row['Field'];

        if ($row['Extra'] === 'auto_increment') {

            $idcol = $key;

            if ($id = (is_numeric($_POST[$key]) ? (int)$_POST[$key]: 55)) {
                //$id = (int)$_POST[$key];
                echo "成功？";
                echo "id = $id"."\r\n";
            }

            $cols = array();
            $data = array();

        } else {

            if (!isset($_POST[$key])) continue;

            $cols[] = $key;
            $upds[] = $key;
            $data[] = $_POST[$key];
        } 

    } // while終了


/////////////////////////////////////////////////////////
/////////////THIS IS ERROR !!! HELP StackOverFlow!!!!!///
/////////////////////////////////////////////////////////
    if (isset($id)) {

        $stmt = $pdo->prepare('SELECT * FROM '.$tbname.' WHERE '.$idcol.' = ?');
        $stmt->execute(array($id));

        $stmt = $pdo->prepare('UPDATE ' . $tbname . ' SET ' . implode(', ', $upds) . ' WHERE ' . $idcol . ' = ?');
        array_push($data, $id);
        $stmt->execute($data);

    } else {

        $stmt = $pdo->prepare('INSERT INTO '.$tbname.'('.$idcol.','.implode(',', $cols).') VALUES ('.implode(',', array_fill(0, count($cols)+1, ' ? ')).')');

        array_unshift($data, $id);
        $stmt->execute($data);
    }

    if ($stmt->rowCount() != 1) echo 'error';


    if (isset($_POST['regId'])) {
        $regid = $data[10];
        $apikey = "APIKEY";

        $rq = new HTTP_Request("https://android.googleapis.com/gcm/send");
        $rq -> setMethod(HTTP_REQUEST_METHOD_POST);
        $rq -> addHeader("Authorization", "key=".$apikey);
        $rq -> addPostData("registration_id", $regid);
        $rq -> addPostData("collapse_key", "1");
        $rq -> addPostData("data.message", $_POST['regId']);

        if (!PEAR::isError($rq -> sendRequest()))
            $rq -> getResponseBody();
        else
            print "\nError has occurred";

    } else {
        echo "こちらはPdoTest.phpです。GCM接続できません";
    }

    for ($i = 0; $i < count($colname); $i++) 
        echo "\r\ncolname = {$colname[$i]}\r\n";

    echo "\r\n\r\nPHP送信が完了しました\r\n";
}

Try using double quotes instead of single quotes for your queries. You might have to do certain modifications to the ones already inside them. If possible, always try to use double quotes to wrap your queries with. I.e.: ("UPDATE $tbname SET '" . implode(', ', $upds) . "' WHERE '" . $idcol . "' = ?") — Funk Forty Niner
Are you sure you need such a complex automated query builder? — Your Common Sense
Thank Comment at fred and YourCommon. but "UPDATE $tbname SET '" . implode(', ', $upds) . "' WHERE '" . $idcol . "' = ?" is Error ;D — JohnnyHubble

Your Common Sense Your Common Sense · Accepted Answer · 2014-05-03T05:08:58

Your problem is not actually with PDO but with basic string operations. You wrote a program to create your SQL query dynamically, and it failed. It is not PDO failed but your program. To fix it, you have to check the result. The result of this is not a PDO statement but just a string. An SQL string. And this very result you have to check to see what's wrong with it and eventually fix the code.

Thus, instead of writing your program directly in prepare(), better store the result in a variable, and echo this variable in case of error.

Android PHP PDO MySQL UPDATE Syntax Error

1 Answers