CODEWITHSUNDEEP
phpmysqlpdo
Byakugan
Byakugan

Reputation: 981

How to make prepared statement using PDO in advanced queries?

I would like to ask for help with converting my mysql_* query to prepared statement using PDO technology. There are many of them which I cannot find on the internet how to solve them properly - mostly advanced ones like this one for example:

mysql_query("SELECT * FROM pet_auction JOIN people ON (pet_auction.pet=people.guid) 
            LEFT OUTER JOIN login.account ON (pet_auction.winner=login.account.id)
            WHERE active=1 AND seller=$userid ORDER BY id DESC");

How to succesfully convert it to PDO STMT using these?:

$people = new PDO("mysql:host=localhost;dbname=people", "myuser", "mypass");
$login = new PDO("mysql:host=localhost;dbname=login", "myuser", "mypass");

Thank you all I rather will not try else it would be false because i tested already ... I have no idea how to convert LEFT OUTER JOIN and multiple databases together.

Upvotes: 1

Views: 1049

Answers (2)

Mike Mackintosh
Mike Mackintosh

Reputation: 14237

You do not need to open a pdo object for each database. Just give myuser grant access to both login and people databases. Then query like so: 

$dbh = new PDO("mysql:host=localhost;dbname=people", "myuser", "mypass");
$stmt= $dbh->prepare("SELECT * FROM pet_auction JOIN people ON (pet_auction.pet=people.guid) 
        LEFT OUTER JOIN login.account ON (pet_auction.winner=login.account.id)
        WHERE active=1 AND seller=:userid ORDER BY id DESC");
$stmt-> execute(array(':userid' => $userid));
$variable = $stmt->fetch(PDO::FETCH_ASSOC);

Upvotes: 2

Mike
Mike

Reputation: 781

$dbh = new PDO($dsn, "myuser", "mypass");
$select = $dbh->prepare("SELECT * FROM `table`");
$select -> execute();
$variable = $select->fetch(PDO::FETCH_ASSOC);

where $dsn is a string containing 'mysql:dbname=racerost_reekris_db;host=localhost'

the query can contain any mySQL query including joins.

Upvotes: 0

Related Questions