Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to pull the results with ODBC_RESULT in a PHP Variable

Posted on 2012-04-13
5
Medium Priority
?
960 Views
Last Modified: 2012-06-27
Hello all,

I am trying to figure out a problem that I am having with this PHP section of code.

<?php
require_once("dbinfo.php");
$connection = odbc_connect( $connection_string, $U, $P ); 

//GET THE WORKGROUP OF THE LOGGED ON USER
$USERID = strtoupper($_POST['USER']);
            $sql = "SELECT FACILITY,WORKGROUP FROM CLUSER WHERE USERID = '$USERID'  ";
            $result = odbc_exec($connection, $sql); 
            $res_rows = odbc_result_all($result,'FACILITY');

// Want result to be stored as a variable
$FACILITY = $res_rows;
                        echo $res_rows['FACILITY'];
                                            
// echo the variable
                       echo $FACILITY;
 
?>

Open in new window


When I run this in a debugger I get the following:

Globals
Name                  Value            Type
$Facility                0                  long
$res_rows             0                  long
$result                  3                  odbc result  

I have tried ODBC_RESULT as well with the same end result.
Problem. I notice when i remove in the SQL statement the variable '$USERID' that the debugger pulls information for both ODBC_RESULT and ODBC_RESULT_ALL.  Why is the variable causing this issue.

This code is used to validate a user based on the facility and Workgroup so that they are able to view information dependent on those items.  

I have been all over PHP.net and a few other places but to no avail.  Any help on this would be great.

Thanks,

sqlspider
0
Comment
Question by:Sqlspider
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 84

Accepted Solution

by:
Dave Baldwin earned 2000 total points
ID: 37845799
odbc_result_all($result,'FACILITY'); is not what you want.  odbc_result_all() returns an HTML table with the results and the second param is supposed to be an attribute for the table.  http://us3.php.net/manual/en/function.odbc-result-all.php  You probably want...

$res_rows = odbc_fetch_array($result); // followed by
$FACILITY = $res_rows['FACILITY'];

Here is an example of a simple page that is working on my computer to access an SQL 2005 EXPRESS database table.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
 "http://www.w3.org/TR/html4/loose.dtd">

<html>
<head>
<title>PHP ODBC</title>
</head>
<body>
<h1>PHP ODBC</h1>
<?php
// MS SQL server 2005 using PHP 5.2 or lower and php_odbc.dll driver
// DB configuration
$serverName = "DBSERVER\SQLEXPRESS";     // Your database server instance
$dbuser = "Yourusername";      // Your db username
$dbpass = "Yourpassword";      // Your db password
$dbname = "Yourdatabase";      // Your database name
$dbtable = "Yourtablet";  // the table we're using

$conn = odbc_connect("Driver={SQL Server Native Client 10.0};Server=$serverName;Database=$dbname;", $dbuser, $dbpass);
	
if($conn == 0)
	 {
	 die('Unable to connect to the ODBC datasource.');
	 }
 
$idresult = odbc_exec($conn,"SELECT DisplayName, ent_num FROM $dbtable WHERE ent_num = 7");
$idrows = odbc_num_rows($idresult);
$arr = odbc_fetch_array($idresult);
echo $serverName." - ";
echo $arr['DisplayName'];
	 
// Close the ODBC connection.
odbc_close($conn);
?>

</body>
</html>

Open in new window

0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 37846163
I think DaveBaldwin has your central issue well in hand here.  The online PHP man pages document the functions so it is always worth reading them to be sure the script passes the correct parameters and tests the function return values to see if the function worked as expected.  It looks to me like you want these functions in this order:
http://php.net/manual/en/function.odbc-exec.php
http://php.net/manual/en/function.odbc-errormsg.php
http://php.net/manual/en/function.odbc-fetch-row.php
http://php.net/manual/en/function.odbc-result.php

You can use var_dump() to print out the values of data elements, including returns from function calls.  Often this can give you a strong hint as to what the server is doing with your code and data.

You might want to add some sanity checks to protect your data base from the external post data in a query.  Also, if you expect one row, you might want to add LIMIT 1 to the query, to avoid a table scan.
0
 

Author Comment

by:Sqlspider
ID: 37855634
Ok thanks for the responses give me a few hours to catch up I have been under the weather with allergies. Love the season hate the pollen.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 37855785
Yeah, we've got that pollen here, too.  No rain in weeks.  All the cars are turning green.  Hope you're feeling better soon!
0
 

Author Comment

by:Sqlspider
ID: 37856467
Ok as always you guys are worth your weight in gold.  I changed my test code to the following:
<?php

require_once("dbinfo.php");

//GET THE WORKGROUP OF THE LOGGED ON USER
$USERID = strtoupper($_POST['USER']);
[b]$DBTABLE = "[db].[dbo].[CLUSER]";            [/b]
$RESULT = odbc_exec($CONNECTION,"SELECT FACILITY,WORKGROUP FROM [b]$DBTABLE [/b]WHERE USERID = '$USERID' ");

if($CONNECTION == 0)
     {
     die('Unable to connect to the ODBC datasource.');
     }


$idrows = odbc_num_rows($RESULT);
$arr = odbc_fetch_array($RESULT);
           
// echo the variable            
ECHO $arr ['FACILITY'];
ECHO $arr ['WORKGROUP']
?>

Open in new window


I tried to run the script as is, with the proper odbc functions but it was not until I added the $dbtable = variable did I finally get the results for the odbc functions to echo.  Also before addding the $dbtable variable I removed my $USERID variable from the sql select and it ran with results.  I did do as you suggested Ray and that helped with the Limit 1.  I just did not think I needed to make a variable for the table.  Odd, but still learning.  One day when I grow up I will be as good as you guys .

Again thanks for the input.  Sorry for the delay on getting back to the forum to see your comments.  

Dave good show on your comment that put me on the right track. Debugging this PHP was giving me grief.
Now I have to correct the rest of the PHP's that I have inherited.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In threads here at EE, each comment has a unique Identifier (ID). It is easy to get the full path for an ID via the right-click context menu. However, we often want to post a short link within a thread rather than the full link. This article shows a…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

636 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