StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to properly set up a PDO connection
primarykey
Id
11369360
data
AcceptedAnswerId
11369679
AnswerCount
4
ClosedDate
CommentCount
2
CommunityOwnedDate
CreationDate
2012-07-06T20:37:42.150
FavoriteCount
73
LastActivityDate
2017-05-23T04:07:30.590
LastEditDate
2017-05-23T04:07:30.590
LastEditorUserId
4874281
OwnerUserId
1355562
ParentId
0
PostTypeId
1
Score
83
ViewCount
82958
LastEditorDisplayName
text
Body
From time to time I see questions regarding connecting to database. Most answers is not the way I do it, or I might just not get the answers correctly. Anyway; I've never thought about it because the way I do it works for me. But here's a crazy thought; Maybe I'm doing this all wrong, and if that's the case; I would really like to know how to properly connect to a MySQL database using PHP and PDO and make it easy accessible. Here's how I'm doing it: First off, here's my file structure (stripped down): <pre><code>public_html/ * index.php * initialize/ -- load.initialize.php -- configure.php -- sessions.php </code></pre> index.php At the very top, I have <code>require('initialize/load.initialize.php');</code>. load.initialize.php <pre><code># site configurations require('configure.php'); # connect to database require('root/somewhere/connect.php'); // this file is placed outside of public_html for better security. # include classes foreach (glob('assets/classes/*.class.php') as $class_filename){ include($class_filename); } # include functions foreach (glob('assets/functions/*.func.php') as $func_filename){ include($func_filename); } # handle sessions require('sessions.php'); </code></pre> I know there's a better, or more correct, way to include classes, but can't remember what it was. Haven't gotten the time to look into it yet, but I think it was something with <code>autoload</code>. something like that... configure.php Here I basically just override some php.ini-properties and do some other global configuration for the site connect.php I've put the connection onto a class so other classes can extends this one... <pre><code>class connect_pdo { protected $dbh; public function __construct() { try { $db_host = ' '; // hostname $db_name = ' '; // databasename $db_user = ' '; // username $user_pw = ' '; // password $con = new PDO('mysql:host='.$db_host.'; dbname='.$db_name, $db_user, $user_pw); $con->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ); $con->exec("SET CHARACTER SET utf8"); // return all sql requests as UTF-8 } catch (PDOException $err) { echo "harmless error message if the connection fails"; $err->getMessage() . " "; file_put_contents('PDOErrors.txt',$err, FILE_APPEND); // write some details to an error-log outside public_html die(); // terminate connection } } public function dbh() { return $this->dbh; } } # put database handler into a var for easier access $con = new connect_pdo(); $con = $con->dbh(); // </code></pre> Here I do believe there's room for massive improvement since I recently started learning OOP, and using PDO instead of mysql. So I've just followed a couple of beginners tutorials and tried out different stuff... sessions.php Beside handling regular sessions, I also initialize some classes into a session like this: <pre><code>if (!isset($_SESSION['sqlQuery'])){ session_start(); $_SESSION['sqlQuery'] = new sqlQuery(); } </code></pre> This way this class is available all over the place. This might not be good practice(?)... Anyway, this is what this approach allows me to do from everywhere: <pre><code>echo $_SESSION['sqlQuery']->getAreaName('county',9); // outputs: Aust-Agder (the county name with that id in the database) </code></pre> Inside my <code>sqlQuery</code>-class, which <code>extends</code> my <code>connect_pdo</code>-class, I have a public function called <code>getAreaName</code> which handles the request to my database. Pretty neat I think. Works like a charm So that's basically how I'm doing it. Also, whenever I need to fetch something from my DB from not within a class, I just do something similar to this: <pre><code>$id = 123; $sql = 'SELECT whatever FROM MyTable WHERE id = :id'; $qry = $con->prepare($sql); $qry -> bindParam(':id', $id, PDO::PARAM_INT); $qry -> execute(); $get = $qry->fetch(PDO::FETCH_ASSOC); </code></pre> Since I put the connection into a variable inside connect_pdo.php, I just have referring to it and I'm good to go. It works. I get my expected results... But regardless of that; I would really appreciate if you guys could tell me if I'm way off here. What I should do instead, areas I could or should change for improvement etc... I'm eager to learn...
Tags
<php><mysql><sql><class><pdo>
Title
How to properly set up a PDO connection
singulars
PostAcceptedAnswerId
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. This table or related slice is empty.
UserOwnerUserId
1. USThomasK
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. PL
 singulars
 LinkTypeLinkTypeId
 LTLinked
2. PL
 singulars
 LinkTypeLinkTypeId
 LTLinked
3. PL
 singulars
 LinkTypeLinkTypeId
 LTLinked
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
2. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 POHow to properly set up a PDO connection
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
2. VO
 singulars
 PostPostId
 POHow to properly set up a PDO connection
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
3. VO
 singulars
 PostPostId
 POHow to properly set up a PDO connection
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
CommentsPostId
1. COYou should use an autoloader instead of including _every single file_ in your application at once.
 singulars
 PostPostId
 POHow to properly set up a PDO connection
 UserUserId
 USLusitanian
2. COThis question is probably best on [codereview.se]
 singulars
 PostPostId
 POHow to properly set up a PDO connection
 UserUserId
 USMadara Uchiha

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.