Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

PDO, Select statement, single result 1

Status
Not open for further replies.
eraH

eraH

MIS
Dec 1, 2003
106
NZ
I've just started learning to create websites. I'm trying to have a SQL query that returns a single value, but can't figure out if there is an elegant solution to what I'm trying to achieve.
Is there a single line of code I can use to assign the SQL query result to the AccountManagers variable?
Code: 
<body>
<?php
    $db = new PDO('sqlite:.\database\siteinfo.db');
?>
<!--Choose list of companies-->
<form method="post" action="<?php echo $_SERVER['PHP_SELF'];?>">
<p>Company Name:
    <select name="CompanySelection">
        <?php
            $sql = "SELECT DISTINCT CompanyName from CustomerDetails";   
            foreach ($db->query($sql) as $row)
            {
                echo '<option value="'.$row['CompanyName'].'">'.$row['CompanyName'].'</option>';
            }
        ?>
    </select>
    <input name="CompanySelectionButton" type="submit" value="Select"/>
</p>
</form>
<?php
if ($_POST)
{
    $CompanyName=$_POST['CompanySelection'];
    [COLOR=#ff0000]$AccountManager=$db->query('SELECT AccountManager FROM CustomerDetails WHERE CompanyName='.$CompanyName);[/color]    
    echo '<h1>Customer Details - '.$CompanyName.'</h1>';
       echo '<hr />
        <table>
        <tr>
            <th>Company Name:</th>
            <td>'.$CompanyName.'</td>
            <th>Account Manager:</th>
            [COLOR=#ff0000]<td>'.$AccountManager.'</td>[/color]
        </tr>
 
Sort by date Sort by votes
Assuming you are using the Pear package. db->query returns a resource handle not the actual results so your $AccountManager variable won't contain anything useful to you.

Think of a resource handle as a pointer. Its not the data itself, but it points to wherever the data is.

You need to actually go in and fetch it. Since its a single row you don't need a while loop, and can simply access it directly:
Code: 
%res=db->query(...);
$row=db->$res->fetchRow(DB_FETCHMODE_OBJECT)
$AccountManager=$row->AccountManager;

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.
 
Upvote 0 Downvote
@vacunita: PDO is not the same as the DB PEAR package, although PDO can be installed from PECL. However of course your point remains completely correct in either scenario.

@eraH, the DSN should use forward slashes ideally. that corresponds to php standards.

for your actual query, please try rewriting as follows. note the use of placeholders which are vital for security.

Code: 
//prepare the PDO statement
$statement =$db->prepare('SELECT AccountManager FROM CustomerDetails WHERE CompanyName=?);

//check for query errors
if ($statement == false) die(print_r($db->errorinfo, true));

//assemble the parameters to be used
$params = array($_POST['CompanySelection']);

//execute the query using the prepared statement
$result = $statement->execute($params);
if ($result == false) die (print_r($statement->errorinfo, true));
list($AccountManager) = $statement->fetch(PDO::FETCH_NUM);
/*
or
$row = $statement->fetch(PDO::FETCH_OBJ);
$AccountManager =& $row->AccountManager;
*/

Personally I use an abstraction layer for PDO that I have written. this has a number of useful methods like fetchOne, insert(), save(), update() etc etc. the PDO class is easily extendable.
 
Upvote 0 Downvote
Thanks guys, yeah, I kind of know that there are lot's of security issues, because I've just started learning I just want to get the basics going.

I will try your code jpadie, thanks for both of your help so far.
 
Upvote 0 Downvote
@jpadie, perfect, thanks a lot. BTW, you were just missing a single quote at the end of line 2 of your code (if anyone else find's the post).
 
Upvote 0 Downvote
I need to repeat this code a couple of times, is there any way to do this in a foreach loop? So that at the end, you have

$AccountManager='someone';
$AccountManager2='someoneelse';
Code: 
	$ArrayDetails = array('AccountManager','AccountManager2');
	foreach ($ArrayDetails as $Details)
	{
		$statement =$db->prepare("SELECT '.$Details.' FROM CustomerDetails WHERE CompanyName=?");
		if ($statement == false) die(print_r($db->errorinfo, true));
		$params = array($_POST['CompanySelection']);
		$result = $statement->execute($params);
		if ($result == false) die (print_r($statement->errorinfo, true));
		$row = $statement->fetch(PDO::FETCH_OBJ);
		$$Details =& $row->$Details;
	}
 
Upvote 0 Downvote
why not just put both in the query?

Code: 
$statement =$db->prepare("SELECT [red]AccountManager, AccountManager2 [/red] FROM CustomerDetails WHERE CompanyName=?");
        if ($statement == false) die(print_r($db->errorinfo, true));
        $params = array($_POST['CompanySelection']);
        $result = $statement->execute($params);
        if ($result == false) die (print_r($statement->errorinfo, true));
        $row = $statement->fetch(PDO::FETCH_OBJ);
        $am = $row->AccountManager;
        $am2 = $row->AccountManager2;
 
Upvote 0 Downvote
That only makes sense if there are 2 fields in the table named AccountManager and AccounManager2.

What exactly are you trying to get from that query?

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.
 
Upvote 0 Downvote
@vacunita

check out the first few lines of the OP's last post. he IS saying that there are two columns in his DB with those names
OP} [code said:
$ArrayDetails = array('AccountManager','AccountManager2');
foreach ($ArrayDetails as $Details)
{
$statement =$db->prepare("SELECT '.$Details.' FROM CustomerDetails WHERE CompanyName=?");
[/code]
Click to expand...
 
Upvote 0 Downvote
Thanks to everyone, I understand your original code better jpadie, and just modified it slightly to do what I need. I was over thinking the solution.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

ersatz
  • Locked
  • Question
How to send 2 select from mysql database in email like tables
Replies
5
Views
593
ersatz
ersatz
Rutsagig
  • Locked
  • Question
mysql_insert_id returns 0 after SELECT query
Replies
0
Views
181
Rutsagig
Rutsagig
Swi
  • Locked
  • Question
Help on depreciated statement. 2
Replies
6
Views
221
Swi
Swi
kitackers
  • Locked
  • Question
PHP If statement always returning else condition 1
Replies
4
Views
377
kitackers
kitackers
BobMCT
  • Locked
  • Question
Old but new again - Debugging PDO prepared statements
Replies
1
Views
155
vacunita
vacunita

Part and Inventory Search

Sponsor

Back
Top