Solved

How do I create a simple ODBC query in PHP?

Posted on 2010-09-14
9
328 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
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 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…

810 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