PHP, MySQL, and Dropdown lists

[Raven_Writer]

I have a question. Is it possible to get all the fields from a table inside a MySQL database, and display 'em, one by one, inside a dropdown list? I'm pretty sure it is, but I don't want to do 3+ hr.'s of coding to find out it won't work.

[Brandon Martus]

Originally Posted by Raven_Writer

I have a question. Is it possible to get all the fields from a table inside a MySQL database, and display 'em, one by one, inside a dropdown list? I'm pretty sure it is, but I don't want to do 3+ hr.'s of coding to find out it won't work.

(Nearly) Anything is possible. You'll want to query "DESCRIBE tablename", and then use that data to create your drop down.

[Raven_Writer]

Originally Posted by Brandon Martus

(Nearly) Anything is possible. You'll want to query "DESCRIBE tablename", and then use that data to create your drop down.

Hmm...thanks a lot Brandon

I'll try that and see what happens.

[Joshua May]

Originally Posted by Raven_Writer

I have a question. Is it possible to get all the fields from a table inside a MySQL database, and display 'em, one by one, inside a dropdown list? I'm pretty sure it is, but I don't want to do 3+ hr.'s of coding to find out it won't work.

Haven't tested it, adn there are probably syntax errors, but this might work.

PHP Code:

<?php
$username = "username";
$password = "password";
$database = "database";

$link = mysql_connect(localhost,$username,$password);

@mysql_select_db($database) or die( "Unable to select database");

$result = mysql_query("SELECT * FROM table",$link) or die ("Mysql error: ".mysql_error());

$num = mysql_num_rows($result);

echo "<form name=\"mysql_test\">";
echo "<select size=\"1\" name=\"mysql\">";

$i=0;
while ($i < $num) {

$field1 = mysql_result($result,$i,"field1");

echo "<option>$field1</option>";

$i++;
}

$i=0;
while ($i < $num) {

$field2 = mysql_result($result,$i,"field2");

echo "<option>$field2</option>";

$i++;
}

echo "</select>";
echo "</form>";

mysql_close();
?>

Hopefully that gives you an idea of what I'm trying to say. It searches through the first field and each time prints the HTML code for each section of a drop down list. Then it runs through the second field and each time prints the HTML code for each section of a drop down list. You would still need to format it to fit into an HTML table if that's what you want to do. Hope this works for you.

[Raven_Writer]

Thank you

What I mean here though (I dunno if you stated this, I'm really tired today so...), is something like this:

Inside a table (named "Bob"), there's 4 fields ("Hi", "I", "Love", "ChiefDelphi"). what I want to do is search through Bob, and for each field, I want to put each into a dropdown list. So, the choices would be like: "Hi", then next one is "I", then "Love", finally "ChiefDelphi".

[Joshua May]

Originally Posted by Raven_Writer

Thank you

What I mean here though (I dunno if you stated this, I'm really tired today so...), is something like this:

Inside a table (named "Bob"), there's 4 fields ("Hi", "I", "Love", "ChiefDelphi"). what I want to do is search through Bob, and for each field, I want to put each into a dropdown list. So, the choices would be like: "Hi", then next one is "I", then "Love", finally "ChiefDelphi".

Yeah, that's what I got from it. You would just change the "field1" "field2" etc to "Hi" "I" "Love" "Chiefdelphi"

[Raven_Writer]

After trying your code, I realized it's not exactly what I'm looking for. I'm trying to directly output the fields, not the field values.

[Joshua May]

Originally Posted by Raven_Writer

After trying your code, I realized it's not exactly what I'm looking for. I'm trying to directly output the fields, not the field values.

I'm confused, do you mean that you're just outputting the names of the fields? What exactly is contained in the drop-down lists?

[Raven_Writer]

Originally Posted by HHSJosh

I'm confused, do you mean that you're just outputting the names of the fields? What exactly is contained in the drop-down lists?

Well, I figured I'd do that part inputing the stuff I wanted manually.

But a new question has arisen.

My question now is, I need to get the value from the field after clicking on the submit button. Here's my code:

PHP Code:

<?
// more code above and below this snipplet
 
$belt = $_POST["belt"];
 
<form action="admin_cp.php?user=<?echo("$user"); ?>" method="POST">
<b>Belt To Change:</b> 
<select size="1" name="belt"> 
<option>Heavyweight</option> 
<option>TV</option> 
<option>Cruiserweight</option>
<option>No Limits</option>
<option>Tag Team</option>
</select>
</form>
<br><br>
<input type=hidden name=admin_login>
<input type="submit" name=updated value="Update Belt">
</form>
 
<?
if($belt){
$query = mysql_query("SELECT $belt FROM belt_owner");
$holder = mysql_fetch_array($query);
?>
 
<form action="#">
<input type=text value="<? echo("$holder"); ?>">
</form>

So, let's say the user chooses TV, and the belt owner's name is Ted. I'd like it to output "Teddy" into the input box.

Mainly, I just need to select the correct belt from the table, and output who owns it so far.

I don't mean to be seeming like I'm asking how to do this whole thing, I've just been working on this for the whole day, and it's starting to get underneath my skin here.

** PS: I know it sounds like wrestling here, I'm helping a friend on a summer project **

[Joshua May]

Originally Posted by Raven_Writer

Well, I figured I'd do that part inputing the stuff I wanted manually.

But a new question has arisen.

My question now is, I need to get the value from the field after clicking on the submit button. Here's my code:

So, let's say the user chooses TV, and the belt owner's name is Ted. I'd like it to output "Teddy" into the input box.

Mainly, I just need to select the correct belt from the table, and output who owns it so far.

I don't mean to be seeming like I'm asking how to do this whole thing, I've just been working on this for the whole day, and it's starting to get underneath my skin here.

** PS: I know it sounds like wrestling here, I'm helping a friend on a summer project **

Here's a link to a news posting script that I wrote.

NOTE: This news script cannot be used directly, there are still a few errors.

If you look at the code under the if($action == "edit") section, you will see where a form is outputted. The following is a line of code from that, the name of the variable is modified for clarity:

PHP Code:

echo "<input type=\"text\" size=\"20\" name=\"name\" value=\"".$name."\">\n"; 


Using the "value" part of the HTML input tag, you can output data into an input box. Just use some PHP code previously to get the data from the table. Then, you can allow the user to rewrite the data in the input box, and press the Submit button, you can then use more PHP code to write the new value into the MySQL database. I think this should cover it.

[evulish]

So you have a table of just people that own belts? Interesting. I'd have just made a new row in your wrestler table (I'm assuming you have one) named like belt_owned. Then you could have done something like SELECT DISTINCT wrestler_name,belt_owned FROM wrestlers WHERE belt_owned IS NOT NULL. But maybe I'm missing something. And I didn't even answer your question. Or was it already answered. I got so lost in that post that I don't even remember the problem I just got home from work.. so.. uhh *runs into wall and falls over*

[Max Lobovsky]

Originally Posted by Raven_Writer

Well, I figured I'd do that part inputing the stuff I wanted manually.

In case you change your mind and do it the cool way (dynamically), Brandon was right when he said you could use "DESCRIBE tablename" which would output all the info about a table's fields, but a quick check at the awesomest php site: www.php.net/manual reveals there is an easier to use function: mysql_list_fields. Here's a link to its page on php.net http://www.php.net/manual/en/functio...ist-fields.php

[Brandon Martus]

Ahh yes -- I knew there was a function, just couldn't find it in the 3-second attention span I allotted myself ..

[Raven_Writer]

Originally Posted by evulish

So you have a table of just people that own belts? Interesting. I'd have just made a new row in your wrestler table (I'm assuming you have one) named like belt_owned. Then you could have done something like SELECT DISTINCT wrestler_name,belt_owned FROM wrestlers WHERE belt_owned IS NOT NULL. But maybe I'm missing something. And I didn't even answer your question. Or was it already answered. I got so lost in that post that I don't even remember the problem I just got home from work.. so.. uhh *runs into wall and falls over*

After thinking about it for a lil' bit, I'm pretty sure I'll do something like that. Thanks

max: Thanks also The problem w/ it though, is that when I choose the item...nothing happens period (which could be because I'm not good at using dropdown lists).

[Raven_Writer]

Ok, a new problem and I've been trying to fix it for the past 2 hours. What I'm trying to do is update the database by finding the belt in the table "belt_owner", and updating who owns it. I can connect, and get the current owners of it. My problem is trying to change the ownership of it.

There's a belt ("TV"), and the current holder in the database is "Trent Sinn". Whenever I try to change ownership of the belt (like, I enter my name as the new owner), nothing happens. It goes to the page it's supposed to, but doesn't update the table, or display a warning.

PHP Code:

/** FORM TO GET Title, New and Old Holder **/
<form action="update_list.php" method="POST">
<b>Belt:</b> <input type=text name=belt><br>
<b>Old Holder:</b> <input type=text name=old><br>
<b>New Holder:</b> <input type=text name=new><br><br>
<input type=submit name=get_holder value="Update Belt List">
</form>
 
/** UPDATE_LIST.PHP **/
<? require("conn.php"); 
$name = isset($_SESSION['admin_user']);
$get_holder = isset($_POST["get_holder"]);
$old      = isset($_POST["old"]);
$new      = isset($_POST["new"]);
$belt = isset($_POST["belt"]);

/*...*/
 
if(!$old){ $old = ""; }
if(!$new){ $new = ""; }
if(($get_holder) && ($belt != "")){
 if($belt != "Tag Team"){
  $query = mysql_query("UPDATE belt_owner SET $belt = '$new' WHERE $belt = '$old'");
  
  if($query != FALSE){
   echo("Successfully updated database.");
  } else{
   echo("Unable to update database.  Reason: ". mysql_error());
  }
 } else{ ?>
  <form action="update_tag_teams.php" method="POST">
  <b>Member #1:</b> <input type=text name=mem1><br>
  <b>Member #2:</b> <input type=text name=mem2><br><br>
  
  <center>(or)</center><br><br>
  
  <b>Team Name:</b> <input type=text name=name><br>
  <input type=submit name=new_team value="Update Tag Team Titles">
  </form>
<?}
}
mysql_close();
?>