Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 345
  • Last Modified:

How do I create a simple ODBC query in PHP?

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
Kpomilla
Asked:
Kpomilla
  • 4
  • 3
  • 2
1 Solution
 
Michel SakrCommented:
The blank space in the DB name might be the problem
0
 
Michel SakrCommented:
Also did you mention the driver name and server in the code? as I do not see those in the snippet
0
 
Michel SakrCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
13598Commented:
Is it invoices or invoice?
the odbc connection name is invoice
$conn=odbc_connect('ucs invoices'
0
 
13598Commented:
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
 
KpomillaAuthor Commented:
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
 
13598Commented:
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
 
KpomillaAuthor Commented:
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
 
13598Commented:
$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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now