Page 3 of 4

Re: How do I block user viewing out DB tables?

Posted: Wed Aug 28, 2013 8:40 am
by simonmlewis
So keep the current older DBConn script there, and just update the include files with PDO?

Re: How do I block user viewing out DB tables?

Posted: Wed Aug 28, 2013 8:42 am
by Celauran
You could even do both in the same include file, if it's already being included everywhere.

Re: How do I block user viewing out DB tables?

Posted: Wed Aug 28, 2013 8:45 am
by simonmlewis
I don't mean that.
We have multiple include files to run the site. Plus of course the template.
But we have one include file that is the database connection.

My question is: can I do PDO on all the internal includes first, while keeping the older style dbconn?

Or do they both have to be "PDO" ?

Re: How do I block user viewing out DB tables?

Posted: Wed Aug 28, 2013 8:53 am
by Celauran
simonmlewis wrote:My question is: can I do PDO on all the internal includes first, while keeping the older style dbconn?
Sorry, I'm not following what you mean here. What I was suggesting is setting up your dbconn (mysql_connect()) and instantiating a PDO object in the same file so that you don't need to update your include calls anywhere and have both methods available to you so you can make the transition piecemeal.

Code: Select all

define('DBHOST', 'localhost');
define('DBUSER', 'username');
define('DBPASS', 'password');
define('DBNAME', 'database');

$conn = mysql_connect(DBHOST, DBUSER, DBPASS);
if ($conn) {
	mysql_select_db(DBNAME);
}

$pdo = new PDO('mysql:host=' . DBHOST . ';dbname=' . DBNAME, DBUSER, DBPASS);
Including this file means your mysql_query calls will continue to work, while $pdo will also be available to you.

Re: How do I block user viewing out DB tables?

Posted: Wed Aug 28, 2013 8:57 am
by simonmlewis
Sorry teach, now you've lost me too.
Are you saying this script of yours does both old and new versions in the same script?
So if PDO is available, it will work with it. If the include script is old style, it will work with that too?
And once all old versions have been replaced with PDO, this will just work anyway?

Re: How do I block user viewing out DB tables?

Posted: Wed Aug 28, 2013 8:59 am
by Celauran
That's what I'm saying, yes. Your mysql_query() calls depend upon a mysql_connect() and mysql_select_db() call. We've got those covered. PDO simply requires that a PDO object be created. We're covering that, too.

Re: How do I block user viewing out DB tables?

Posted: Wed Aug 28, 2013 9:04 am
by simonmlewis
Ok - new question then.
My template closes all queries with:
mysql_close($sqlconn);
Otherwise I have countless numbers of opening and closing connections (bad).

Or can I now add a second "close" function for PDO? (which is...??).
So the template loads.
It runs TWO connections scripts.

The include files run whatever queries they want, under whichever connections is most suitable.
At the end of the page, it runs two "dbclose" scripts.

Re: How do I block user viewing out DB tables?

Posted: Wed Aug 28, 2013 9:07 am
by simonmlewis
Is it as simple as:

Code: Select all

<?php
$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
// use the connection here


// and now we're done; close it
$dbh = null;
?>

Re: How do I block user viewing out DB tables?

Posted: Wed Aug 28, 2013 9:08 am
by Celauran
It's not necessary. From the manual:
Upon successful connection to the database, an instance of the PDO class is returned to your script. The connection remains active for the lifetime of that PDO object. To close the connection, you need to destroy the object by ensuring that all remaining references to it are deleted--you do this by assigning NULL to the variable that holds the object. If you don't do this explicitly, PHP will automatically close the connection when your script ends.

Re: How do I block user viewing out DB tables?

Posted: Wed Aug 28, 2013 9:11 am
by simonmlewis
Oh - so you MUST close it.... but you don't have to.
Bit contradictory but I get it.

Re: How do I block user viewing out DB tables?

Posted: Wed Aug 28, 2013 1:09 pm
by Christopher
simonmlewis wrote:Oh - so you MUST close it.... but you don't have to.
Bit contradictory but I get it.
It doesn't say you must close it; it says you can close it -- and here is how. Typically PHP scripts do not close database connections because it is more efficient to let PHP do it during its post-execution clean-up.

Re: How do I block user viewing out DB tables?

Posted: Wed Aug 28, 2013 10:48 pm
by Eric!
While this is all good, don't forget items 1,2,3,5 and 6 that I mentioned. And here is a good reference on hashing.

Re: How do I block user viewing out DB tables?

Posted: Thu Aug 29, 2013 3:45 am
by simonmlewis
My next step, is to update my XAMPP locally. I did install the latest but all my sites died locally, so I had to install the older version.
I didn't think it would kill them tho. So that's a puzzle.
I may try again later and see if I can find out the issue.

At least from "Celauran" help, I've been able to get right into this, so much appreciation to him. And thanks to others - I also use a Close script as I was taught it best practice.
"You've opened the connection, now close it after".

Re: How do I block user viewing out DB tables?

Posted: Thu Aug 29, 2013 11:17 am
by simonmlewis
What is wrong with this code?
It's my first bigger query using PDO.

I'm using your PDO multiple connection while I go thru all the pages.

Code: Select all

$query = "SELECT id, catid, catname, uk_catname FROM products WHERE pause = 'off' AND catid IS NOT NULL GROUP BY catname ORDER BY 
catname='XL' DESC,
catname='L' DESC,
catname='M' DESC,
catname='S' DESC,
catname='XS' DESC";
$result = $pdo->query($query);
while ($row = $result->fetch(PDO::FETCH_OBJ)) {
      $categ = "$row->catname"; 
      $findcateg ="/ /"; 
      $replacecateg ="-"; 
      $categreplace = preg_replace ($findcateg, $replacecateg, $categ); 
			echo "<div class='submenu'><a href='/category/$row->catid/$categreplace'>$row->catname</a></div>";
			}

Re: How do I block user viewing out DB tables?

Posted: Thu Aug 29, 2013 2:37 pm
by Christopher
simonmlewis wrote:What is wrong with this code?
Are you getting an error or looking for a critique?

On a macro level, I would recommend separating the database code into a separate class that reads all the rows into a $rows array and returns it, then have you display code use the returned array. You will find that you will start to get code reuse if the do that.