PHP query to MySQL to return single value

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

RiggsFolly · Accepted Answer · 2016-01-14 02:45:08Z

1

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";
}

edited Jan 14, 2016 at 2:45

answered Jan 14, 2016 at 2:25

RiggsFolly

94.7k22 gold badges108 silver badges152 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

RiggsFolly Over a year ago

And another one. Its late I should be in bed

Robert Goodrick Over a year ago

It's not inserting anything. PARAM_STR doesnt help

Robert Goodrick Over a year ago

$stmt->bind_param(':id', $user->user_id, PDO::PARAM_STR); $stmt->execute();$stmt->bindColumn('devId', $devId); $pdo->exec("INSERT INTO devicesTable(devId) VALUES('$devId')");

RiggsFolly Over a year ago

I fixed the bind_param it should be bindParam

Darwin von Corax · Accepted Answer · 2016-01-14 02:40:38Z

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

Alex · Accepted Answer · 2016-01-14 02:29:13Z

0

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]);

answered Jan 14, 2016 at 2:29

Alex

14.7k5 gold badges45 silver badges60 bronze badges

6 Comments

Robert Goodrick Over a year ago

Ive got one last need for this system AlexanderMP

Robert Goodrick Over a year ago

when user logs in to access account one thing passed is $user->devId. Basically in java I would write if (bannedDeviceArrayFromDeviceTable.contains($user->devId){ refuse entry basically}.

Robert Goodrick Over a year ago

how to write in php...does deviceTable contain "$user->devId"

Alex Over a year ago

you could use the php in_array() function. I know, right? Going from a pretty OOP-centered language like Java, to an environment where you have to run global functions for that. Ewww. php.net/manual/en/function.in-array.php buuut PHP is great and fun nevertheless, if you use it properly.

Alex Over a year ago

Oh, you wanted in SQL? In that case it's SELECT CASE IF :userDevId IN (SELECT devId FROM devicesTable) THEN 1 ELSE 0 END. :userDevId is a parameter you have to assign in PHP.

|

Collectives™ on Stack Overflow

PHP query to MySQL to return single value

3 Answers 3

4 Comments

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related