CODEWITHSUNDEEP
phpmysql
Robert Goodrick
Robert Goodrick

Reputation: 298

PHP query to MySQL to return single value

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]}')");

Upvotes: 0

Views: 764

Answers (3)

RiggsFolly
RiggsFolly

Reputation: 94662

If you use ->fetchAll() you are returning all the results but into an array of assoc arrays

This may be easier as you only have one result row with one column in it

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

$stmt->bindParam(':id', $user->user_id, PDO::PARAM_INT);

$stmt->execute();

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

$cnt = $pdo->exec("INSERT INTO devicesTable(devId) VALUES('$devId')");

if ( $cnt === FALSE ) {
    echo 'Nothing inserted';
    print_r($db->errorInfo());
} else {
    echo "Inserted $cnt rows";
}

Upvotes: 1

Darwin von Corax
Darwin von Corax

Reputation: 5246

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.)

Upvotes: 0

Alex
Alex

Reputation: 14618

the fetchAll() method returns an array of arrays. The first array is the rows, so $result[0], $result[1] and so on, will be associative arrays for the first, second and following rows. Then, you have to access the column by name: $result[0]['devId'] for example.

But you have to make sure that the row actually exists

if(!empty($result))
    $pdo->exec("INSERT INTO devicesTable(devId) VALUES('{$result[0]['devId']}')");

But you can write that in a single query, if you don't need the devId for anything else

"INSERT INTO devicesTable(devId) SELECT devId FROM accounts WHERE user_id='{$user->user_id}'"

Also, you are using prepared statements. You already have those, so please use parameters instead of concatenating strings.

$stmt = $pdo->prepare("INSERT INTO devicesTable(devId) SELECT devId FROM accounts WHERE user_id=:userId");
$stmt->execute([':userId'=>$user->user_id]);

Upvotes: 0

Related Questions