PHP查询到MySQL返回单值

Question

I am very new to PHP and SQL. I am a java and XML developer for android. I'm having issues here because I simply need a single value returned and cant seem to get it out of the pdo prepared query. All I'm looking for is to be returned devId "device id" from one table and insert it into another. "user_id" is the auto incremented primary and represents different users. Each row has a "devId" varchar() column. Please help me out here. I need the value for "devId" for the "user_id" i passed through. Very simple you would think to return this single value.

$query = $pdo->prepare("SELECT devId FROM accounts WHERE user_id='{$user->user_id}'");
$query->execute();
$result = $query->fetchAll();
$pdo->exec("INSERT INTO devicesTable(devId) VALUES('{$result[0]}')");

Answer 1

First, PDOStatement::fetchAll() returns a 2-dimensional array containing all the rows of the result set; second, that's not how you use a prepared statement.

The idea behind prepared statements is that when you prepare it you insert place-holders, and then when you execute you bind the place-holders to specific values, and can execute the query repeatedly with different bound values without reparsing the query. Additionally, the prepared statement automagically handles details like type-matching, quoting and escaping for you.

$stmtSel = $pdo->prepare("SELECT devId 
                        FROM accounts 
                        WHERE user_id=:id");

$stmtSel->bindParam(':id', $user->user_id, PDO::PARAM_STR);

$stmtSel->execute();

$stmtSel->bindColumn('devId', $devId);
$stmtSel->fetch(PDO::FETCH_BOUND)) 

$stmtIns = $pdo->prepare('INSERT INTO devicesTable (devId) VALUES(:devId)')
$pdo->execute(array(':devId' => $devId));

(Note this is almost the same as RiggsFolly's solution, except that mine uses two prepared statements stored in separate variables so they can both be reused.)