New to/learning PDO

PHP
1

OK, I am trying to get my head around the PDO stuff that I need to change to for the database queries.

So, with that in mind, I have searched the internet and gone over many articles and videos on PDO. Now, I can connect with the DB, and get some data out BUT cannot figure out how to call functions and keep that data both input and output.

Here is a test piece I tried. It has to do with a database that has some “links” to where pictures are stored in our site.

Here is my connection include file:

<?PHP
	// *************************************************************************************************************
	/**
              * filename: db_connect_PDO.php";
              *
	* Connect to the Database
	*
	* Used to establish a connection to the database. Modify this page if database
	* host or login information changes.
	*
	*
	*/
	// *************************************************************************************************************

	// Connection Variables
	$hostname="localhost";
	$mysql_login="user";
	$mysql_password="pw";
	$database='ourDB';
	
	try
	{
		// Connect to the database server
		$pdo = new PDO("mysql:dbname=$database; host=$hostname", $mysql_login, $mysql_password);
		
		if ( $pdo )
		{
			echo 'connected';
			return ($pdo);
		}
		else
		{
			echo 'DB error';
			
		}
	}
	
	catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
	}
	
?>

and here is the test file I am trying to call this stuff from.

<?

?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>PDO Test 1</title>
</head>
<body>
		<div >
		<p >Ride Picture<br />

<!-- Automatic display of a ride per month   -->
			<?php
				$Img_Display = current_image();
				echo '<pre>';
				echo 'Returning<br>';
				print_r($Img_Display);
				echo '</pre>';
				echo '<br><br>Image to display: ' . $Img_Display[1] . '<br>';
				echo '<a href = "ccount/click.php?id=9">' ;
				echo '<img src="'.$Img_Display[1].'" width="180" height="150" alt="'.$Img_Display[3].'" /></a><span class="imglabel">'.$Img_Display[3].'</span>' ;
			?>
			<span style="margin: 0px auto; font-size:.8em; font-weight: 800; color: #00C; line-height:.85em; padding-top: 35px;">Click image for more Ride Pictures</span>
		</p>
		</div>
</body>


<?php

// function to get the image location from the database
function current_image() {
    // open the database
  	// Connection Variables
	include_once "inc/db_connect_PDO.php";

	$pdo = get_db_connection();
	$status = 2; //this is the status of the ride to display
	
    // now get the current image for display
    $query = $pdo->prepare("SELECT * FROM MemberRidePix WHERE Status=:Status LIMIT 1");  // check for the current picture
    $query->bindParam(":Status", $status);
	
	if ($query->execute())
	{
    //$image_data_array = $query->fetchObject();
    //return $image_data_array;
	return $query->fetchAll();
	}
   }

?>

You can see that i have some debugging parts in the test file. When I want to echo out the returned results (which should be only one row/record) I get nothing.

So, need some help in how to pass information to/from functions with the PDO tags.

Thanks
E

2

If the code you posted was verbatim then your pdo query is never happening. There is no function called get_db_connection.

You should also be aware that $stmt->fetchAll() always returns an array of database rows even with a limit of 1. So you need to peel off the first item in the array.

Finally, in most cases it’s best to return associative arrays. By default, fetchAll returns both assoc arrays and indexes arrays.

Make a new file called query.php and paste in:


<?php
error_reporting(E_ALL);

$conn = new PDO("mysql:host=localhost; dbname=DBNAME", 'USER', 'PASSWORD');
$conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
$conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC);

$sql = <<<EOT
SELECT * FROM MemberRidePix WHERE Status=:Status LIMIT 1
;
EOT;

$stmt = $conn->prepare($sql);
$stmt->execute(array('Status' => 2));
$rows = $stmt->fetchAll();
print_r($rows);

$image = $rows[0];
print_r($image);

?>

Adjust DBNAME,USER,PASSWORD to the correct values.
You can run this script from the command line using “php query.php”. It’s often easier to debug queries from the command line. Or you can run it from the browser.

After you understand how $image is formatted then you can add your html to the end of the file. Even better would be to put the html in a second file and include it. But make sure you understand the query stuff before diving into html.

3

OK, I do understand the query and the $image, I have the current site using MySQL commands which, as we all know, are now being deprecated.

So, I need to figure out how to use functions to pass the results between the html main and any function called - either in the main html file or an include.

Unless I am missing something???
E

4

Here’s a good introduction to using PDO: http://net.tutsplus.com/tutorials/php/php-database-access-are-you-doing-it-correctly/

5

In the answer I posted, I listed a few things that were just plain wrong about your code such as no definition for get_db_connection() and not specifying how you wanted the returned data. Here is my code again but wrapped in a few functions:


<?php 
error_reporting(E_ALL); 

function get_db_connection()
{
    $conn = new PDO("mysql:host=localhost; dbname=DBNAME", 'USER', 'PASSWORD'); 
    $conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION); 
    $conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE,PDO::FETCH_ASSOC); 
    return $conn;
}
function get_current_image()
{
    $conn = get_db_connection();

    $sql = <<<EOT 
SELECT * FROM MemberRidePix WHERE Status=:Status LIMIT 1 
; 
EOT; 

    $stmt = $conn->prepare($sql); 
    $stmt->execute(array('Status' => 2)); 
    $rows = $stmt->fetchAll(); 

    return $rows[0];
}

// Note that in general it's cleaner to push data into a template as opposed to pulling data from within a template
$image = get_current_image();
?>
<html>
<head><title>Roll Tide</title></head>
<body>
    <div>Link: <?php echo $image['link']; ?></div>
</body>
</html>

This is just to get started. You will of course need to add some error checking.

And take note of the error_reporting(E_ALL) line at the top of the file. Error messages are often turned off by default.