You are not logged in.

#1 2005-05-07 17:25:16

rasat
Forum Fellow
From: Finland, working in Romania
Registered: 2002-12-27
Posts: 2,293
Website

Drop-down-menu with DB table.

How to select items from a DB table with Drop-down-menu? Currently the menu is static.

<tr>
      <td width="100"></td>
      <td>Type of Product:</td>
      <td><select name="pkgtype" size="1" >
       <option value=""> -View All-</option>
       <option value="Bearing">Bearing</option>
       <option value="Piston Ring">Piston Ring</option>
       <option value="Timing Belt">Timing Belt</option>
       <option value="Valve Seal">Valve Seal</option>
       <option value="unknown">Unknown</option></select></td>
      <td></td>
    </tr>

DB type is PostgreSQL but MySQL script sample will also do.

DB = powdb
Table = product
Field = productid
Field = productname


Markku

Offline

#2 2005-05-07 18:39:04

cactus
Taco Eater
From: t͈̫̹ͨa͖͕͎̱͈ͨ͆ć̥̖̝o̫̫̼s͈̭̱̞͍̃!̰
Registered: 2004-05-25
Posts: 4,622
Website

Re: Drop-down-menu with DB table.

what language?


"Be conservative in what you send; be liberal in what you accept." -- Postel's Law
"tacos" -- Cactus' Law
"t̥͍͎̪̪͗a̴̻̩͈͚ͨc̠o̩̙͈ͫͅs͙͎̙͊ ͔͇̫̜t͎̳̀a̜̞̗ͩc̗͍͚o̲̯̿s̖̣̤̙͌ ̖̜̈ț̰̫͓ạ̪͖̳c̲͎͕̰̯̃̈o͉ͅs̪ͪ ̜̻̖̜͕" -- -̖͚̫̙̓-̺̠͇ͤ̃ ̜̪̜ͯZ͔̗̭̞ͪA̝͈̙͖̩L͉̠̺͓G̙̞̦͖O̳̗͍

Offline

#3 2005-05-07 18:59:10

rasat
Forum Fellow
From: Finland, working in Romania
Registered: 2002-12-27
Posts: 2,293
Website

Re: Drop-down-menu with DB table.

Its PHP.


Markku

Offline

#4 2005-05-07 19:07:16

cactus
Taco Eater
From: t͈̫̹ͨa͖͕͎̱͈ͨ͆ć̥̖̝o̫̫̼s͈̭̱̞͍̃!̰
Registered: 2004-05-25
Posts: 4,622
Website

Re: Drop-down-menu with DB table.

$pkgtype = strcmp(trim($_REQUEST['pkgtype']),"")==0 ? null : $_REQUEST['pkgtype'] ;

if ($pkgtype) {
@mysql_connect("localhost",$username,$password);
@mysql_select_db("powdb") or die( "Unable to select database");
$query="SELECT productid FROM product WHERE productname='$pkgtype'";
$result=mysql_query($query);
if (!$result)
 echo "Query Error";
elseif (mysql_num_rows($result) ==0)
  echo "Query returned nothing";
else {
  while ($row=mysql_fetch_assoc($result) ) {
     echo 'Result is '.$row['productid'] . '<br>';
  }
}
mysql_close();
}

if you want more values returned from the table than just the productid, you can use "SELECT * FROM ..." instead.

Then, to get the results you can use

EDIT: That is off the top of my head. if you need something more specific, let me know..


"Be conservative in what you send; be liberal in what you accept." -- Postel's Law
"tacos" -- Cactus' Law
"t̥͍͎̪̪͗a̴̻̩͈͚ͨc̠o̩̙͈ͫͅs͙͎̙͊ ͔͇̫̜t͎̳̀a̜̞̗ͩc̗͍͚o̲̯̿s̖̣̤̙͌ ̖̜̈ț̰̫͓ạ̪͖̳c̲͎͕̰̯̃̈o͉ͅs̪ͪ ̜̻̖̜͕" -- -̖͚̫̙̓-̺̠͇ͤ̃ ̜̪̜ͯZ͔̗̭̞ͪA̝͈̙͖̩L͉̠̺͓G̙̞̦͖O̳̗͍

Offline

#5 2005-05-07 20:21:57

rasat
Forum Fellow
From: Finland, working in Romania
Registered: 2002-12-27
Posts: 2,293
Website

Re: Drop-down-menu with DB table.

Thanks. In the HTML is this correct?

<tr>
   <td width="100"></td>
       <td>Type of Product:</td>
       <td><select name="pkgtype" size="1" >
       <option value=""> -View All-</option>
       <option value="<?echo "$pkgtype"?>"><?echo "$pkgtype"?></option>
       <option value="unknown">Unknown</option></select></td>
  <td></td>
</tr>

Markku

Offline

#6 2005-05-07 20:30:45

cactus
Taco Eater
From: t͈̫̹ͨa͖͕͎̱͈ͨ͆ć̥̖̝o̫̫̼s͈̭̱̞͍̃!̰
Registered: 2004-05-25
Posts: 4,622
Website

Re: Drop-down-menu with DB table.

The original html was fine for what it seemed like you were trying to do. What are you trying to acheive with that snippet? If having the previous post value selected on page load, then it needs a few other options. if you are trying to snag all the values from the database of possible part types, and have that in a list, then no..that will not work for that either.
Maybe a more clear description of what you are trying to do will help.


"Be conservative in what you send; be liberal in what you accept." -- Postel's Law
"tacos" -- Cactus' Law
"t̥͍͎̪̪͗a̴̻̩͈͚ͨc̠o̩̙͈ͫͅs͙͎̙͊ ͔͇̫̜t͎̳̀a̜̞̗ͩc̗͍͚o̲̯̿s̖̣̤̙͌ ̖̜̈ț̰̫͓ạ̪͖̳c̲͎͕̰̯̃̈o͉ͅs̪ͪ ̜̻̖̜͕" -- -̖͚̫̙̓-̺̠͇ͤ̃ ̜̪̜ͯZ͔̗̭̞ͪA̝͈̙͖̩L͉̠̺͓G̙̞̦͖O̳̗͍

Offline

#7 2005-05-07 20:46:50

rasat
Forum Fellow
From: Finland, working in Romania
Registered: 2002-12-27
Posts: 2,293
Website

Re: Drop-down-menu with DB table.

The Drop-down-menu is used for searching a selected product name in a DB. Instead of selecting from a static HTML tag list, I want to use the records of product table as search criteria.

Example in AIR, the lists of item in drop-down-menus are all static HTML, not from DB.
http://bliss-solutions.org/archlinux/incoming/


Markku

Offline

#8 2005-05-07 20:56:33

cactus
Taco Eater
From: t͈̫̹ͨa͖͕͎̱͈ͨ͆ć̥̖̝o̫̫̼s͈̭̱̞͍̃!̰
Registered: 2004-05-25
Posts: 4,622
Website

Re: Drop-down-menu with DB table.

This should populate the list for you. Is this what you were looking for?

<?php
@mysql_connect("localhost",$username,$password);
@mysql_select_db("powdb") or die( "Unable to select database");
$query="SELECT productname FROM product";
$result=mysql_query($query);
if (!$result)
 echo "Query Error";
elseif (mysql_num_rows($result) ==0)
  echo "Query returned nothing";
else {
    echo <<<ENDTEXT
    <tr>
   <td width="100"></td>
       <td>Type of Product:</td>
       <td><select name="pkgtype" size="1" >
       <option value=""> -View All-</option>
ENDTEXT
  while ($row=mysql_fetch_assoc($result) ) {
     echo "<option value="".$row['productname']."">".$row['productname']."</option>";
  }
}
mysql_close();
?>
     <option value="unknown">Unknown</option></select></td>
  <td></td>
</tr>

"Be conservative in what you send; be liberal in what you accept." -- Postel's Law
"tacos" -- Cactus' Law
"t̥͍͎̪̪͗a̴̻̩͈͚ͨc̠o̩̙͈ͫͅs͙͎̙͊ ͔͇̫̜t͎̳̀a̜̞̗ͩc̗͍͚o̲̯̿s̖̣̤̙͌ ̖̜̈ț̰̫͓ạ̪͖̳c̲͎͕̰̯̃̈o͉ͅs̪ͪ ̜̻̖̜͕" -- -̖͚̫̙̓-̺̠͇ͤ̃ ̜̪̜ͯZ͔̗̭̞ͪA̝͈̙͖̩L͉̠̺͓G̙̞̦͖O̳̗͍

Offline

#9 2005-05-08 08:46:02

rasat
Forum Fellow
From: Finland, working in Romania
Registered: 2002-12-27
Posts: 2,293
Website

Re: Drop-down-menu with DB table.

Nice, this was what I wanted. Only change what was needed is double quote mark (") to single (') to enable echoo. Here is the PgSQL version and works fine.

<?php 
$conn = pg_connect("dbname=$database host="."$location"." user="."$username"." password="."$password");
if (!$conn) die ("Could not connect PgSQL");
$query="SELECT productname FROM product ORDER BY productname ASC";
$result = pg_query($conn, $query);
if (!$result)
 echo "Query Error";
elseif (pg_num_rows($result) ==0)
  echo "Query returned nothing";
else {
echo "
   <tr>
   <td width='100'></td>
       <td>Type of Product:</td>
       <td><select name='pkgtype' size='1' >
       <option value=''> -View All-</option>
";
  while ($row=pg_fetch_assoc($result) ) {
     echo "<option value="".$row['productname']."">".$row['productname']."</option>";
  }
}
pg_close();
?>
     <option value="unknown">Unknown</option></select></td>
  <td></td>
</tr>

Markku

Offline

#10 2005-05-08 09:29:28

cactus
Taco Eater
From: t͈̫̹ͨa͖͕͎̱͈ͨ͆ć̥̖̝o̫̫̼s͈̭̱̞͍̃!̰
Registered: 2004-05-25
Posts: 4,622
Website

Re: Drop-down-menu with DB table.

8)


"Be conservative in what you send; be liberal in what you accept." -- Postel's Law
"tacos" -- Cactus' Law
"t̥͍͎̪̪͗a̴̻̩͈͚ͨc̠o̩̙͈ͫͅs͙͎̙͊ ͔͇̫̜t͎̳̀a̜̞̗ͩc̗͍͚o̲̯̿s̖̣̤̙͌ ̖̜̈ț̰̫͓ạ̪͖̳c̲͎͕̰̯̃̈o͉ͅs̪ͪ ̜̻̖̜͕" -- -̖͚̫̙̓-̺̠͇ͤ̃ ̜̪̜ͯZ͔̗̭̞ͪA̝͈̙͖̩L͉̠̺͓G̙̞̦͖O̳̗͍

Offline

#11 2005-05-09 09:30:58

rasat
Forum Fellow
From: Finland, working in Romania
Registered: 2002-12-27
Posts: 2,293
Website

Re: Drop-down-menu with DB table.

In solution for Form Action discussed in:
http://bbs.archlinux.org/viewtopic.php?t=12273
.... where to add "$_REQUEST" in below sample  so that <form action="index.php" method="POST"> can read the drop-down-menu variable "$pkgtype"?

if ($pkgtype=$pkgtype) {
    $prod="AND productname = '$pkgtype'";
}

Markku

Offline

#12 2005-05-09 10:02:50

cactus
Taco Eater
From: t͈̫̹ͨa͖͕͎̱͈ͨ͆ć̥̖̝o̫̫̼s͈̭̱̞͍̃!̰
Registered: 2004-05-25
Posts: 4,622
Website

Re: Drop-down-menu with DB table.

i dont know what you are trying to get in that snipped.
I looks like you are comparing something to itself..very odd.

As a note, $_REQUEST is best used for testing only, or in limited instances. You can cause yourself security issues if you use it in some instances..

Lets say you are posting a variable to a page, using http_post. If someone adds that same variable to a get addendum to the url, then it might override the post value (depending on the order of things set in php.ini EGPCS. Normally this is ok, because post follows get for php variable assignment. Still, relying on a user setting can sometimes cause issues..

php has $_POST and $_GET for use with their respective elements. I would recommend using them over $_REQUEST, unless it doesn't really matter where the data comes from. Sometimes just using $_REQUEST is easier, especially if you envision other people querying the php page from disperate sources.

Back to your issue. If  you want to just use $pkgtype, then simply assign it earlier in the script.

$pkgtype=strcmp(trim($_POST['pkgtype']),"")==0 ? null : $_POST['pkgtype'];

Then you can later just check to see if $pkgtype==null and then do things with the value when it is not..
tongue

hope this helps..


"Be conservative in what you send; be liberal in what you accept." -- Postel's Law
"tacos" -- Cactus' Law
"t̥͍͎̪̪͗a̴̻̩͈͚ͨc̠o̩̙͈ͫͅs͙͎̙͊ ͔͇̫̜t͎̳̀a̜̞̗ͩc̗͍͚o̲̯̿s̖̣̤̙͌ ̖̜̈ț̰̫͓ạ̪͖̳c̲͎͕̰̯̃̈o͉ͅs̪ͪ ̜̻̖̜͕" -- -̖͚̫̙̓-̺̠͇ͤ̃ ̜̪̜ͯZ͔̗̭̞ͪA̝͈̙͖̩L͉̠̺͓G̙̞̦͖O̳̗͍

Offline

#13 2005-05-09 10:29:26

rasat
Forum Fellow
From: Finland, working in Romania
Registered: 2002-12-27
Posts: 2,293
Website

Re: Drop-down-menu with DB table.

Got the point!
To make it simple, this what I did.

$pkgtype = $_POST['pkgtype'];

if ($pkgtype=$pkgtype) {
    $prod="AND productname = '$pkgtype'";
}

Markku

Offline

#14 2005-05-09 19:47:16

cactus
Taco Eater
From: t͈̫̹ͨa͖͕͎̱͈ͨ͆ć̥̖̝o̫̫̼s͈̭̱̞͍̃!̰
Registered: 2004-05-25
Posts: 4,622
Website

Re: Drop-down-menu with DB table.

I still don't know why you are setting
$pkgtype=$pkgtype in your if statement.


first off, it should be == for a test, second, what benefit is it to compare something to itself?
Maybe you want
if ($pkgtype!="") {


"Be conservative in what you send; be liberal in what you accept." -- Postel's Law
"tacos" -- Cactus' Law
"t̥͍͎̪̪͗a̴̻̩͈͚ͨc̠o̩̙͈ͫͅs͙͎̙͊ ͔͇̫̜t͎̳̀a̜̞̗ͩc̗͍͚o̲̯̿s̖̣̤̙͌ ̖̜̈ț̰̫͓ạ̪͖̳c̲͎͕̰̯̃̈o͉ͅs̪ͪ ̜̻̖̜͕" -- -̖͚̫̙̓-̺̠͇ͤ̃ ̜̪̜ͯZ͔̗̭̞ͪA̝͈̙͖̩L͉̠̺͓G̙̞̦͖O̳̗͍

Offline

#15 2005-05-10 03:57:50

rasat
Forum Fellow
From: Finland, working in Romania
Registered: 2002-12-27
Posts: 2,293
Website

Re: Drop-down-menu with DB table.

cactus wrote:

I still don't know why you are setting
$pkgtype=$pkgtype in your if statement.

To not fillup this thread with detail dev work, I have copied your last question in Usercb:
http://user-contributions.org/forums/us … .php?t=166


Markku

Offline

Board footer

Powered by FluxBB