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>";
?>
KpomillaAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
13598Connect With a Mentor Commented:
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
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
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
 
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
All Courses

From novice to tech pro — start learning today.