generate drop down box data from ODBC database

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
FE
Forum Commoner
Posts: 35
Joined: Fri Jul 14, 2006 5:21 am

generate drop down box data from ODBC database

Post by FE »

Pimptastic | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Hello everyone

Can someone please help me popualte a drop down box from a ODBC database and add data from a database into a text box after someone has selected a database entry?



my current code is:

Code: Select all

if (PHP_VERSION >= '5.1.0RC1')
date_default_timezone_set('Europe/London');

$pf_time = strtotime("-14 days");
$startdate = date("Y-m-d", $pf_time);

$enddate = date("Y-m-d");

$connectionstring = odbc_connect("odbcdatabasee","username","password");
$query = "SELECT * FROM database WHERE DATE (BETWEEN '$startdate' AND '$enddate')" ; 
$result = odbc_do($connectionstring, $query);

echo '<select id="sbox" onchange="document.getElementById(\'datahere\').value=this.value;">';
while($row=odbc_fetch_row($result)) {
echo '<option value="'.$row['database_pk'].'">'.$row['datbase_column'].'</option>';
}


echo '<input type="text" id="datahere" />';
when i run the page i don't get any reported errors, all i get is an empty drop down box and an empty text box

Thank in advance guys!


Pimptastic | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

it's probably because after this :

Code: Select all

echo '<select id="sbox" onchange="document.getElementById(\'datahere\').value=this.value;">'; 
while($row=odbc_fetch_row($result)) { 
echo '<option value="'.$row['database_pk'].'">'.$row['datbase_column'].'</option>'; 
}
you are forgetting to end your select tag. so it should be like this instead:

Code: Select all

echo '<select id="sbox" onchange="document.getElementById(\'datahere\').value=this.value;">'; 
while($row=odbc_fetch_row($result)) { 
echo '<option value="'.$row['database_pk'].'">'.$row['datbase_column'].'</option>'; 
} 
echo "</select>";
FE
Forum Commoner
Posts: 35
Joined: Fri Jul 14, 2006 5:21 am

Post by FE »

cheers fo the reply infolock, it probley is the reason why its not working!

sory for not reading the post on how to post code, my mistake and rushing again

cheers guy i will try again at work tomrrow :!:
FE
Forum Commoner
Posts: 35
Joined: Fri Jul 14, 2006 5:21 am

Post by FE »

its still not working, maybe ignore the code above as i think its terribly wrong

can someone tell me how to read data from an ODBC database and produce a dropdown box from a database column? and then auto fill in 7 text boxes relating to the value selected from the drop down box?

e.g select customer ID, then customers address would be auto filled in

cheers guys
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

I would try executing that query in the console itself and verify the query is actually returning a result. Otherwise, I see no problem with this code.

May also want to verify your connection. maybe it's not even connecting.
FE
Forum Commoner
Posts: 35
Joined: Fri Jul 14, 2006 5:21 am

Post by FE »

Hello

I am sure this code:

Code: Select all

if (PHP_VERSION >= '5.1.0RC1') 
date_default_timezone_set('Europe/London'); 

$pf_time = strtotime("-14 days"); 
$startdate = date("Y-m-d", $pf_time); 

$enddate = date("Y-m-d"); 

$connectionstring = odbc_connect("odbcdatabasee","username","password"); 
$query = "SELECT * FROM database WHERE DATE (BETWEEN '$startdate' AND '$enddate')" ; 
$result = odbc_do($connectionstring, $query);
Works as I used the echo command during debugging to see if the variables hold any values, I then checked it against the database results, and it produced the correct results but after numerous attempts I cannot get the results into a drop down box!! Any more help is definitely appreciated!!
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Start simple. Trash the idea of a drop down box (for the moment) and try echoing out the while loop to see if the query is doing anything. And throw in some error checking to be on the safe side.

Code: Select all

<?php
if (PHP_VERSION >= '5.1.0RC1')
{
    date_default_timezone_set('Europe/London');
}

$startdate = date("Y-m-d", strtotime("-14 days"));
$enddate = date("Y-m-d");

if (!$connectionstring = odbc_connect("odbcdatabasee","username","password"))
{
    die('Could not connect to my database...');
}

$query = "SELECT * FROM database WHERE DATE (BETWEEN '$startdate' AND '$enddate')" ;
if(!$result = odbc_do($connectionstring, $query))
{
    die('There was a database error in the query: ' . odbc_errormsg());
}

while ($row = odbc_fetch_row($result))
{
    echo '<p>Testing out a data value: ' . $row['database_pk'] . '</p>';
}
?>
FE
Forum Commoner
Posts: 35
Joined: Fri Jul 14, 2006 5:21 am

Post by FE »

this is the actual code i used when testing:

Code: Select all

<?php
if (PHP_VERSION >= '5.1.0RC1') 
{ 
    date_default_timezone_set('Europe/London'); 
} 

$startdate = date("Y-m-d", strtotime("-14 days")); 
$enddate = date("Y-m-d"); 

if (!$connectionstring = odbc_connect("SageLine50v11","manager","")) 
{ 
    die('Could not connect to my database...'); 
} 

$query = "SELECT * FROM GRN_ITEM WHERE DATE (BETWEEN '$startdate' AND '$enddate')" ; 
if(!$result = odbc_do($connectionstring, $query)) 
{ 
    die('There was a database error in the query: ' . odbc_errormsg()); 
} 

while ($row = odbc_fetch_row($result)) 
{ 
    echo '<p>Testing out a data value: ' . $row['GRN_NUMBER'] . '</p>'; 
} 
?>
i just want to make sure that GRN_NUMBER is correct, GRN_NUMBER is the primary key column from the database or something else

i tested the above code and got 8 x "Testing out a data value: " rows on the page
FE
Forum Commoner
Posts: 35
Joined: Fri Jul 14, 2006 5:21 am

Post by FE »

i just changed the code to: (odbc_result_all)

Code: Select all

<?php
while ($row = odbc_result_all($result)) 
{ 
    echo '<p>Testing out a data value: ' . $row['ITEM_NUMBER'] . '</p>'; 
} 
?>
and i got results and column names from the database inbetween the desired date ranges, showing that the SQL commands are correct

and at the bottom i got:
Testing out a data value:

No rows found
after further debugging i thik i have found the error, the below code:

Code: Select all

echo '<p>Testing out a data value: ' . $row['ITEM_NUMBER'] . '</p>';
searches for results in a row, i need the drop down box to be populated with data from a column, does anyone know how to change this?
Post Reply