Solved

PHP PDO QUERY Function and variable

Posted on 2013-10-25
15
942 Views
Last Modified: 2013-12-02
I am using a PDO and Object Oriented Programming (NEWBIE!!!!)

If I have the following function:
function Workorder()	{ 
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	
	$sql = "SELECT *
			FROM workorders
			WHERE workorder_id = '$id'";
	// CURSOR_SCROLL ALLOWS REPOSITIONING THE CURSOR - LIKE DATA_SEEK() - BUT SADLY NOT WITH MYSQL
	$pdos = $pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));

	//  START FIRST QUERY TRY GRAB EMPLOYEE ID FROM SCHEDULE			
	try {
    	$pdos->execute();
	
		$results = $pdos->fetchAll();
			if($results) {
		///////////////////////////////////////////////////////////////////
		
		}
			
	}  //  END TRY			          	
	catch(PDOException $e) {
   		echo 'ERROR: ' . $e->getMessage();
	}
	
				
	}  //  END WORKORDERS FUNCTION	

Open in new window


How do I turn those results into variable that I can use on another page, within a form?
	<table style="border-bottom:1px dotted #cccccc;margin:0px 0px 23px 0px;">
		<tr>
			<td align="left" width="250"><strong>NEW WORK ORDER</strong>:</td>
			<td align="left">DATE BOOKED:<br /><input type="text" name="date_booked" class="datepicker" value="<?php if(isset($trimmed['date_booked'])) echo $trimmed['date_booked']; ?> " /></td>
			<td align="left">HOW HEARD:<br /><input type="text" name="how_heard" size="35" maxlength="60" value="<?php if(isset($trimmed['how_heard'])) echo $trimmed['how_heard']; ?>"  /></td>
			<td align="left">REFERENCE NUMBER:<br /><input type="text" name="reference_number" size="35" maxlength="60" value="<?php if(isset($trimmed['reference_number'])) echo $trimmed['reference_number']; ?>"  /></td>
			<td align="left"></td>
		</tr>
		<tr>
			<td align="left" width="250">PACK:<br /><input type="text" name="pack" size="35" maxlength="60" value="<?php if(isset($trimmed['pack'])) echo $trimmed['pack']; ?> " /></td>
			<td align="left">PICK UP:<br /><input type="text" name="pick_up" size="35" maxlength="60" value="<?php if(isset($trimmed['pick_up'])) echo $trimmed['pick_up']; ?> " /></td>
			<td align="left">DELIVERY:<br /><input type="text" name="delivery" size="35" maxlength="60" value="<?php if(isset($trimmed['delivery'])) echo $trimmed['delivery']; ?>"  /></td>
			<td align="left">WEIGHT:<br /><input type="text" name="weight" size="35" maxlength="60" value="<?php if(isset($trimmed['weight'])) echo $trimmed['weight']; ?>"  /></td>
			<td align="left">VAN:<br /><input type="text" name="van" size="35" maxlength="60" value="<?php if(isset($trimmed['van'])) echo $trimmed['van']; ?>"  /></td>
		</tr>
			
	</table>

Open in new window


I would like to replace the : <?php if(isset($trimmed etc...
with the variable from the function.

Any hints, clues, etc...???
0
Comment
Question by:rgranlund
  • 6
  • 5
  • 4
15 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
PDO does not use PHP data strings in the queries.  There's a lot of deep background understanding you need to have in order to understand this.  But if you follow the design of the PDO examples in this article it can work out OK for you.  
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

Pay particular attention to prepare() and bindparam()
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
I suppose I should also add that the PHP function() encapsulates data.  What that means is that any data you want to use inside the function must be defined in the function or passed into the function as an argument.  

One of the terrible problems with PHP is that by default it will not tell you if your script relies on an undefined variable!  Without this knowledge, the function posted above will malfunction and PHP won't tell you why.  To get that knowledge, add the following statement to the top of all of your PHP scripts.

error_reporting(E_ALL);

In the WorkOrder() function posted above, $pdo and $id are undefined variables.

You're also going to benefit greatly from the idea of coding standards.  You really want to be lining up your code neatly so the control structures stand out and the logic is easy to follow.  PHP doesn't care, of course (some languages do), so you're doing that part of the programming task for the benefit of the humans, not the machines.  The humans in question here include anyone who might need to read your code some day in the future.  And that includes you, if you're away from the task for more than a few hours.
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
You are wrapping your database call in a function, so that function needs to 'return' your data. You also need to get out of the habit of doing things the old 'mysql' way. Do not concatenate variables into a query string. Use named parameters and pass in the data separately. Your function is also relying on a variable called $id, but it is not set 'in-scope'. Your function should look something like this:

function Workorder($id = 0)	{ 
	//connect to your DB
	$conn = new PDO("mysql:host=localhost;dbname=yourDB", "username", "password");

	//prepare the query with a named parameter - :id
	$sql = "SELECT column1, column2, column3 FROM workorders WHERE workorder_id = :id LIMIT 1";
	$workorders = $conn->prepare($sql);

	//execute the query, passing in the named parameter value
	$workorders->execute(array('id' => $id));
	
	//get the results
	$results = $workorders->fetch();

	//return the results from your function
	return $results;
}

Open in new window

And then you would call it like this:

<?php
$id = 7;
$myResult = Workorder($id);
//now you have the data in a variable called $myResult
var_dump($myResult);
?>

Open in new window

0
 
LVL 7

Author Comment

by:rgranlund
Comment Utility
So, on the page where I am including the function do I do this?

function Workorder()	{  //  METHOD
	$id = $_GET['id'];				
	$pdo = new PDO("............");
	
	
	$sql = "SELECT *
			FROM workorders
			WHERE workorder_id = '$id'";

	$workorders = $pdo->prepare($sql);

	// EXECUTE QUERY
	try {
    	$workorders->execute(array('i' => $id));
		
	//  GET RESULTS
	$results = $workorders->fetch();
	
	//  RETURN THE RESULTS FROM THE FUNCTION
	return $results;			
	}  //  END TRY			          	
	catch(PDOException $e) {
   		echo 'ERROR: ' . $e->getMessage();
	}
	
				
	}  //  END WORKORDERS FUNCTION	

Open in new window


Then on the page that I am including the function???
	$workorder = new ShowWorkorder();
	$workorder->Workorder($id);
	$myResult = Workorder($id);
	var_dump($myResults);

Open in new window


Or do I need to include that in the function?
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
We would need to see the ShowWorkOrder class to know how to answer the question.  Why are you using a null input to the constructor?  If there is no input to the constructor, you might want to leave the () off, instead of raising a doubt and a question in the minds of everyone who reads your code! :-)

If you want to learn a bit more about OOP, PHP has a fairly good write-up of the way it implements object-oriented design here:
http://php.net/manual/en/language.oop5.php

And one of our EE colleagues has a pair of good articles on this site.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_2626-Beginning-Object-Oriented-Programming-in-PHP.html
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_2631-Advanced-Object-Oriented-Programming-in-PHP.html
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
You're still not using named parameters correctly. Named parameters in a query are variables preceded by a colon (without quotes), so to use it in your query you do this;

//create a query string with a parameter named 'id'
$sql = "SELECT * FROM workorders WHERE workorder_id = :id";

//prepare the query
$workorders = $pdo->prepare($sql);

//Your query is now expecting (demanding!) a parameter named 'id'.
//You do this by passing an array to the execute() method and that array must have a key called id
//In this line, the array contains a key called id (that matches your named parameter)
//and has the value of $id, which has been passed as an argument to your Workorder() function
$workorders->execute( array('id' => $id) );

Open in new window

Now, to use this in your page, you would create a new instance of your class, and then call the Workorder method, passing in the value that you want to insert into your query:

//create a new instance
$workorder = new ShowWorkorder;
//assign the output of the Workorder method to a variable (passing in the id as an argument)
$myResult = Workorder($_GET['id']);
//examine the variable
var_dump($myResults);

Open in new window

In your function code above you are creating the $id variable by grabbing $_GET['id']. Don't do this. Pass it in to the function. Declare your function like this:

function Workorder($id = 0) {
...
}

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Scan this article for PDO - Prepare a Query and look at the code sample.  It shows exactly how you do this!
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 7

Author Comment

by:rgranlund
Comment Utility
@Ray; Here is the entire thing:
<?php
class ShowWorkorder {

	
	
	function Workorder()	{  //  METHOD
	$id = $_GET['id'];				
	$pdo = new PDO("");
	
	
	$sql = "SELECT *
			FROM workorders
			WHERE workorder_id = :id";
	// CURSOR_SCROLL ALLOWS REPOSITIONING THE CURSOR - LIKE DATA_SEEK() - BUT SADLY NOT WITH MYSQL
	$workorders = $pdo->prepare($sql);

	// EXECUTE QUERY
	try {
    	$workorders->execute(array('id' => $id));
		
	//  GET RESULTS
	$results = $workorders->fetch();
	
	//  RETURN THE RESULTS FROM THE FUNCTION
	return $results;
	
			
	}  //  END TRY			          	
	catch(PDOException $e) {
   		echo 'ERROR: ' . $e->getMessage();
	}
	
				
	}  //  END WORKORDERS FUNCTION	

	//$workorder = new ShowWorkorder();
	//$workorders->Workorders();

}  //  END CLASS
?>

Open in new window


@Chis;

A HUGE Light bulb just turned on.  I understand what is going on now in a much more clear light.
0
 
LVL 7

Author Comment

by:rgranlund
Comment Utility
When I do this, I get an error  on line 4
 Call to undefined function workorder()
//create a new instance
$workorder = new ShowWorkorder;
//assign the output of the Workorder method to a variable (passing in the id as an argument)
$myResult = Workorder($_GET['id']);
//examine the variable
var_dump($myResults);

Open in new window

0
 
LVL 7

Author Comment

by:rgranlund
Comment Utility
When I write it like this, it works.  Is this ok if it is written this way?
<?php
	$workorder = new ShowWorkorder();
//assign the output of the Workorder method to a variable (passing in the id as an argument)
	$myResults = $workorder->Workorder($_GET['id']);
//examine the variable
	var_dump($myResults);

?>

Open in new window

0
 
LVL 7

Author Comment

by:rgranlund
Comment Utility
OK, and in closing:  It all works great now.
When I have the following:
<?php
	$workorder = new ShowWorkorder();
//assign the output of the Workorder method to a variable (passing in the id as an argument)
	$mr = $workorder->Workorder($_GET['id']);
//examine the variable
	echo $mr['shippers_full_name'];
?>

Open in new window

When I echo I get the correct name.
Now I have a form.  Is there a simple way to place this into the form with using:
echo $mr['shippers_full_name'];  ???

<td align="left" width="350">SHIPPERS FULL NAME:</td>
<td><?php echo $mr['shippers_full_name']; ?> </td>

Open in new window

Is there a simpler way to echo the shippers_full_name ???
Just wondering.
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
Right. When you create an instance of an object, then you need to call the methods on that object:

//create a new instance of the ShowWorkorder class
$workorder = new ShowWorkorder;

//this will generate an error (you are not calling a method on the $workorder object)
$myResult = Workorder($_GET['id']);

//this is the right way to do it - call the Workorder() method on the $workorder instance!
$myResult = $workorder->Workorder($_GET['id']);

Open in new window

Having said that, the code above is passing an argument to the Workorder() method ($_GET['id']), so you need to make sure the Workorder method can handle that argument, so you define it in your class like this:

class ShowWorkorder {

//Create the Workorder method so it receives an argument, and puts that argument into the $id variable. If no argument is passed, then $id will default to 0.
function Workorder($id = 0) {
...
}

}

Open in new window

Your continuing journey in programming will no doubt give you plenty of 'light bulb' moments - we've all had them :)
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
Echoing out the values is about as simple as it gets!

One thing I would consider though (and this is just a personal preference). As you're now moving into OOP coding, you may want to pull the results from your database as objects, rather than arrays.

Currently, when you call fetch(), you retrieve the data as an array, so you end up with:

$mr['shippers_full_name']

A more OOP way of doing things is to pull the data out as an object and then you have:

$mr->shippers_full_name

To do that, just set the default fetch mode, after you've created your database connection:

$pdo = new PDO('mysql:host=localhost;dbname=yourDb', 'user', 'password');
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);

Open in new window

Now the fetch() method (and others) will return objects instead of arrays :)
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
Comment Utility
I might do it something like this.  Obviously I cannot test this since I don't have your database, but it looks acceptable.

<?php // RAY_temp_rgranlund.php
error_reporting(E_ALL);
class ShowWorkorder
{
    public function __construct($db_host, $db_name, $db_user, $db_word)
    {
        // OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
        $dsn = "mysql:host=$db_host;dbname=$db_name";
        try
        {
            $this->pdo = new PDO($dsn, $db_user, $db_word);
        }
        catch(PDOException $exc)
        {
            var_dump($exc);
            trigger_error('NO PDO Connection', E_USER_ERROR);
        }
        // ACTIVE THIS TO SHOW THE PDO CONNECTION OBJECT
        // var_dump($pdo);
    }
    public function Workorder($id)
    {
        // INITIALIZE THE QUERY STRING AND PREPARE IT IN THE PDO OBJECT
        $sql  = "SELECT * FROM workorders WHERE workorder_id = :id LIMIT 1";
        $pdos = $this->pdo->prepare($sql);

        // USING THE PREPARED QUERY, BIND THE VARIABLE
        $pdos->bindParam(':id', $id, PDO::PARAM_STR);

        // TRY THE PREPARED QUERY WITH THE BOUND DATA AND CATCH THE EXCEPTION
        try
        {
            $pdos->execute();
        }
            catch(PDOException $exc)
        {
            var_dump($exc);
            trigger_error($exc->getMessage(), E_USER_ERROR);
        }

        // DID WE FIND ANYTHING?
        $num  = $pdos->rowCount();
        if (!$num) return FALSE;

        // RETURN THE ROW
        $row = $pdos->fetchAll(PDO::FETCH_OBJ);
        return $row;
    }
}

// INSTANTIATE THE OBJECT
$swo = new ShowWorkOrder($db_host, $db_name, $db_user, $db_word);

// GET A VALUE FROM THE WORKORDER() METHOD
$obj = $swo->WorkOrder($_GET['id']);

// SHOW WHAT HAPPENED
var_dump($obj);

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Thanks for the points and thanks for using EE, ~Ray
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
This tutorial demonstrates how to identify and create boundary or building outlines in Google Maps. In this example, I outline the boundaries of an enclosed skatepark within a community park.  Login to your Google Account, then  Google for "Google M…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now