Solved

How do I create a simple ODBC query in PHP?

Posted on 2010-09-14
9
324 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
  • 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

910 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now