Solved

How do I create a simple ODBC query in PHP?

Posted on 2010-09-14
9
333 Views
Last Modified: 2013-12-13
I am trying to learn PHP and I've set up an ODBC connection to my SQL Database. I'm trying to run a simple query, but when I load the following, I get an "Error in SQL" error in IE...

The table name in the datatbase in dbo_t.user and the odbc connection name is invoice; the fields are as listed below (UserName and UserID) - there are approximately 100 records in the database...(the correct username and password are in my php document)

The problem seems to lie with the if statement:

if (!$rs)
  {exit("Error in SQL");}

Any ideas?

<?php
$conn=odbc_connect('ucs invoices','username','password');
if (!$conn)
  {exit("Connection Failed: " . $conn);}
$sql="SELECT * FROM dbo_t.user";
$rs=odbc_exec($conn,$sql);
if (!$rs)
  {exit("Error in SQL");}
echo "<table><tr>";
echo "<th>UserName</th>";
echo "<th>UserID</th></tr>";
while (odbc_fetch_row($rs))
  {
  $compname=odbc_result($rs,"UserName");
  $conname=odbc_result($rs,"UserID");
  echo "<tr><td>$UserName</td>";
  echo "<td>$UserID</td></tr>";
  }
odbc_close($conn);
echo "</table>";
?>
0
Comment
Question by:Kpomilla
[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
  • 4
  • 3
  • 2
9 Comments
 
LVL 20

Expert Comment

by:Silvers5
ID: 33675011
The blank space in the DB name might be the problem
0
 
LVL 20

Expert Comment

by:Silvers5
ID: 33675043
Also did you mention the driver name and server in the code? as I do not see those in the snippet
0
 
LVL 20

Expert Comment

by:Silvers5
ID: 33675081
I thought you might be using a system DSN, for this you need to check that permissions of the webuser are set to see it.. and also you can try to remove the blank space from the DSN
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 16

Expert Comment

by:13598
ID: 33675091
Is it invoices or invoice?
the odbc connection name is invoice
$conn=odbc_connect('ucs invoices'
0
 
LVL 16

Expert Comment

by:13598
ID: 33675272
Also are you sure the table name is dbo_t.user and not dbo.t.user?
dbo is usually qualifying the table and it would have a . not a _
0
 

Author Comment

by:Kpomilla
ID: 33675481
It looks like the SQL Error has been resolved (it was the name dbo_t.user, should've been dbo.t_user), but now there's a new issue:

Now, when I open in IE, all I get returned are the headings to the fields, not the actual data.



0
 
LVL 16

Accepted Solution

by:
13598 earned 500 total points
ID: 33675615
Instead of this:
$compname=odbc_result($rs,"UserName");
  $conname=odbc_result($rs,"UserID");
  echo "<tr><td>$UserName</td>";
  echo "<td>$UserID</td></tr>";
  }

Try something like this
$UserName=odbc_result($rs,"UserName");
  $UserID=odbc_result($rs,"UserID");
  echo "<tr><td>$UserName</td>";
  echo "<td>$UserID</td></tr>";
  }
 
0
 

Author Closing Comment

by:Kpomilla
ID: 33675903
That did the trick. Thanks to everyone for your help!

Just a quick follow up - how do you designate column widths in the table?
0
 
LVL 16

Expert Comment

by:13598
ID: 33676038
$UserName=odbc_result($rs,"UserName");
 $UserID=odbc_result($rs,"UserID");
 echo "<tr><td width=100>$UserName</td>";
 echo "<td>$UserID</td width=70></tr>";
 
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Is your Office 365 signature not working the way you want it to? Are signature updates taking up too much of your time? Let's run through the most common problems that an IT administrator can encounter when dealing with Office 365 email signatures.
When crafting your “Why Us” page, there are a plethora of pitfalls to avoid. Follow these five tips, and you’ll be well on your way to creating an effective page.
In this tutorial viewers will learn how to embed an audio file in a webpage using HTML5. Ensure your DOCTYPE declaration is set to HTML5: : The declaration should display (CODE) HTML5 is supported by the most recent versions of all major browsers…
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.

752 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