What I want to do is execute a quer

Apollo6 · Jun 19, 2003

What I want to do is execute a query. Then populate a dropdown box with the results. What I need to be in the dropdown is, both FirstName and LastName. When the following code runs, I get the dropdown, and it has the correct number of "spaces" but I am not seeing any names. For example, my query returns 7 people, the dropdown has 7 empty spots. What am I missing in the code...

--QUERY--

$s_emp_query = "SELECT UPEMPL.LASTNAME, UPEMPL.FIRSTNAME, UPEMPL.SSN
FROM
soinc.UPEMPL
WHERE
UPEMPL.SUPERVSR = '$s_supervisor'";
$s_result3 = odbc_exec($connect_soinc, $s_emp_query);
$s_emp_count = 0;
while (odbc_fetch_row($s_result3))
{
$s_emp_count++;
}

--DROPDOWN--

if ($s_emp_count > 0)
{
echo "<select>\n";
for ($i=0;$i<$s_emp_count;$i++)
{
$s_employees = pg_fetch_array($s_result3);
echo "<option value=$s_employees[0]>$s_employees[0]</option>\n";
}
echo "</select>\n";
}

sleipnir214 · Jun 19, 2003

Your code confuses me.

In one place you are using odbc_* function calls, in another, pg_* function calls. I strongly recommend that you use only one or the other, and the you use the most specific function family.

The resource handle is not an object. It most resembles a file handle. When you loop through the entire return, the pointer into the data is set to the end of the data.

Then you use a pg_fetch_array() fetch technique on a resource handle that is at the end of the data. The only reason at all it is outputting the options is that you're forcing the fetches by using the loop.

How about you use a program structure like:

pg_connect (...);
$result = pg_query ("your query&quot

;
if (pg_num_rows($result) > 0)
{
print "<select>";
while ($avariable = pg_fetch_array ($result))
{
print '<option value="' . $avariable[0] . '">' . $avariable[0] . '<option>';
}
print '</select>';
}

The code is much simpler, does not have to loop through the data twice, and does not mix odbc and pg families of functions.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

skiflyer · Jun 19, 2003

Ok, here's what's happening... you're fetching all the rows, then you're at the end. So you go to fetch 7 more times, but you're already at the end.

I'm not quite sure why you're mixing pg_fetch_array and odbc_fetch_row though... I'll assume ODBC is correct... then what you should do is...

Code:

echo &quot;<select>\n&quot;;
while ($row = odbc_fetch_row($s_result3)) {
  echo '<option value=&quot;'.$row[0].'&quot;>'.$row[0].'</option>'.&quot;\n&quot;;
}
echo &quot;<select>\n&quot;;

and drop that $s_emp_count++ code at the top.

-Rob

Apollo6 · Jun 19, 2003

Both of you are exactly right about the mixing of the function calls. The project is actually pulling data from four different types of databases. In this specific issue, I need to be using the odbc_* functions.

You both basically sent the same solution but I went with skiflyer's suggestion but still only come back with a blank drop down (I pasted the exact post.) I can echo out the number of records that comes back from the query and that is correct.

Could my query be causing this due to pulling two fields per record and then I'm trying to put them into a dropdown that can only take 1 element? I would post the code for the dropdown but again, it's exactly skiflyer's suggestion.

Thanks for the explanation on it as well as the help. I'll keep trying.

sleipnir214 · Jun 19, 2003

It's not the two columns/one output problem. That won't matter at all.

Have you gotten rid of the code where you loop through the entire return, incrementing a counter?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

skiflyer · Jun 19, 2003

try this...

Code:

echo &quot;<pre>&quot;;
while ($row = odbc_fetch_row($s_result3)) {
  print_r($row);
  echo &quot;<hr />&quot;;
}

See what that shows you, it should print out the structure of row in such a way that you can see how to access the data.

-Rob

Apollo6 · Jun 19, 2003

I took out the code that looped and incremented a counter. I still use $s_count to determine which database returns a hit. I know get the dropdown, again with 7 spaces (7 records is correct amount). Under that I should have seen the results of $s_result3, but do not see anything. This is leading me to think I've got issues somewhere else in the script.

Here is the current code after the suggestions were applied:

--Two queries above this block of code--

$s_result1 = odbc_exec($connect_soinc, $s_query1);
$s_result2 = odbc_exec($connect_soinc, $s_query2);
$s_count=(int)odbc_result($s_result2,1);
--This $s_count will return either 1 or 0...

$s_sup_first = trim(odbc_result($s_result1, 1));
$s_sup_last = trim(odbc_result($s_result1, 2));
$s_supervisor = $s_sup_first . " " . $s_sup_last;

--QUERY--
$s_emp_query = "SELECT UPEMPL.LASTNAME, UPEMPL.FIRSTNAME
FROM
soinc.UPEMPL
WHERE
UPEMPL.SUPERVSR = '$s_supervisor'";
$s_result3 = odbc_exec($connect_soinc, $s_emp_query);

--DROPDOWN--
if ($s_count > 0)
{
echo "<select>\n";
while ($row = odbc_fetch_row($s_result3)) {
echo '<option value="'.$row[0].'">'.$row[0].'</option>'."\n";
}
echo "<select>\n";
}
echo "<pre>";
while ($row = odbc_fetch_row($s_result3)) {
print_r($row);
echo "<hr />";
}

skiflyer · Jun 19, 2003

Code:

if ($s_count > 0)
{
echo &quot;<select>\n&quot;;
while ($row = odbc_fetch_row($s_result3)) {
echo '<option value=&quot;'.$row[0].'&quot;>'.$row[0].'</option>'.&quot;\n&quot;;
}
echo &quot;<select>\n&quot;;
}

Comment that out, and see what gets printed.

-Rob

Apollo6 · Jun 20, 2003

I return nothing. That's telling me that there is a problem with my $s_result3. I just don't understand how I can count the number of records in $s_result3 but when trying to echo out the results I get blanks... Still looking at the other parts of the script. I'm fairly sure that it's not the syntax of populating the dropdown. Thanks again for the help.

Apollo6 · Jun 20, 2003

Having trouble with this dropdown issue. I'm filling a dropdown from a PostgreSQL table with no problems. The only difference is using the odbc_* functions against a Pervasive.SQL database. I've pasted the entire script if anyone out there has time to see what I'm not doing correctly.

<?
include_once("logonemp.inc&quot

;
session_start();

$connection = pg_connect ("host=$host dbname=$db user=$user
password=$pass&quot

;
if (!$connection)
{
die("Could not open connection to PostgreSQL&quot

;
}

$connect_soinc = odbc_connect("soinc","","&quot

;
if (!$connect_soinc)
{
die("Error in SOINC connection&quot

;
}

$connect_topllc = odbc_connect("TOPLLC","","&quot

;
if (!$connect_topllc)
{
die("Error in TOPLLC connection&quot

;
}

$user_id = $_SESSION['SESSION_UID'];
$ss_num = $_SESSION['SUP_SSN'];

//TOPLLC queries...
$t_query1 = "SELECT UPEMPL.FIRSTNAME, UPEMPL.LASTNAME
FROM
topllc.UPEMPL
WHERE
UPEMPL.SSN = '$ss_num'";

$t_query2 = "SELECT COUNT(*)
FROM
topllc.UPEMPL
WHERE UPEMPL.SSN = '$ss_num'";

$t_result1 = odbc_exec($connect_topllc, $t_query1);
$t_result2 = odbc_exec($connect_topllc, $t_query2);
$t_sup_first = trim(odbc_result($t_result1, 1));
$t_sup_last = trim(odbc_result($t_result1, 2));
$t_supervisor = $t_sup_first . " " . $t_sup_last;

$t_query3 = "SELECT UPEMPL.FIRSTNAME, UPEMPL.LASTNAME, UPEMPL.SSN
FROM
topllc.UPEMPL
WHERE
UPEMPL.SUPERVSR = '$t_supervisor'";
$t_result3 = odbc_exec($connect_topllc, $t_query3);

$t_count=(int)odbc_result($t_result2,1);

//SOINC queries...

$s_query1 = "SELECT UPEMPL.FIRSTNAME, UPEMPL.LASTNAME
FROM
soinc.UPEMPL
WHERE
UPEMPL.SSN = '$ss_num'";

$s_query2 = "SELECT COUNT(*)
FROM
soinc.UPEMPL
WHERE UPEMPL.SSN = '$ss_num'";

$s_result1 = odbc_exec($connect_soinc, $s_query1);
$s_result2 = odbc_exec($connect_soinc, $s_query2);
$s_count=(int)odbc_result($s_result2,1);
$s_sup_first = trim(odbc_result($s_result1, 1));
$s_sup_last = trim(odbc_result($s_result1, 2));
$s_supervisor = $s_sup_first . " " . $s_sup_last;

$s_emp_query = "SELECT UPEMPL.LASTNAME, UPEMPL.FIRSTNAME
FROM
soinc.UPEMPL
WHERE
UPEMPL.SUPERVSR = '$s_supervisor'";
$s_result3 = odbc_exec($connect_soinc, $s_emp_query);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"

http://www.w3.org/TR/xhtml1/DTD/transitional.dtd">

<html>
<head>
<title>Employee List</title>
</head>
<body bgcolor="#FFFFFF" background="background2.gif">
<form name="SubMain" action="benefit_summary.org.php" method="post"><div align="center">
<img src="tnologo.gif" /> <div align="center">
<hr width="50%" />
<div align="center"> <font color="#000000"><font size=3><strong>AVAILABLE
EMPLOYEES:</strong></font><br />
<br />
<?
if ($t_count > 0)
{
echo "<select name=employees>\n";
while ($row = odbc_fetch_row($t_result3)) {
echo '<option value="'.$row[0].'">'.$row[0].'</option>'."\n";
}
echo "</select>\n";
}
else
if (s_count > 0)
{
echo "<select name=employees>\n";
while ($row = odbc_fetch_row($s_result3)) {
echo '<option value="'.$row[0].'">'.$row[0].'</option>'."\n";
}
echo "<select>\n";
}
echo "<br />";
pg_close ($connection);
odbc_close ($connect_soinc);
odbc_close ($connect_topllc);
?>
</div>
<hr width="50%" />
<p>
<div align="center">
<input type="submit" name="submit" value="SUBMIT">
</p></form>
</body>
</html>

Apollo6 · Jun 20, 2003

Finally... For those who might want to know, after some reading, odbc_fetch_row function returns boolean. So this will not bring back a recordset. I used the following code to populate the dropdown box using odbc_* functions:

if ($s_count > 0)
{
echo "<select name=employees>\n";
while ($row = odbc_fetch_row($s_result3)) {
$firstName = odbc_result ($s_result3,'firstname');
$lastName = odbc_result ($s_result3,'lastname');
$out = $firstName . $lastName;
echo '<option value="'.$out.'">'.$out.'</option>'."\n";
}
echo "<select>\n";
}

sleipnir214 · Jun 20, 2003

How bizarre. All of the other *_fetch_row() functions in the online manual return arrays. Since I've not done any database programming using ODBC with PHP, I just assumed odbc_fetch_row() would work the same.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!

skiflyer · Jun 20, 2003

Me too, and I had done some programming with ODBC. My apologies for giving the bad advice

-Rob

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

What I want to do is execute a quer

Apollo6

Technical User

sleipnir214

Programmer

skiflyer

Programmer

Apollo6

Technical User

sleipnir214

Programmer

skiflyer

Programmer

Apollo6

Technical User

skiflyer

Programmer

Apollo6

Technical User

Apollo6

Technical User

Apollo6

Technical User

sleipnir214

Programmer

skiflyer

Programmer

Similar threads

Part and Inventory Search

Sponsor