Chief Delphi - PHP, MySQL, and Dropdown lists

Chief Delphi (http://www.chiefdelphi.com/forums/index.php)

- Website Design/Showcase (http://www.chiefdelphi.com/forums/forumdisplay.php?f=64)

- - PHP, MySQL, and Dropdown lists (http://www.chiefdelphi.com/forums/showthread.php?t=29275)

Raven_Writer

28-06-2004 16:07

PHP, MySQL, and Dropdown lists

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

28-06-2004 16:26

Re: PHP, MySQL, and Dropdown lists

Quote:

Originally Posted by Raven_Writer

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

Raven_Writer

28-06-2004 16:28

Re: PHP, MySQL, and Dropdown lists

Quote:

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

28-06-2004 17:37

Re: PHP, MySQL, and Dropdown lists

Quote:

Originally Posted by Raven_Writer

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

28-06-2004 17:41

Re: PHP, MySQL, and Dropdown lists

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

28-06-2004 17:46

Re: PHP, MySQL, and Dropdown lists

Quote:

Originally Posted by Raven_Writer

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

Raven_Writer

28-06-2004 19:09

Re: PHP, MySQL, and Dropdown lists

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

28-06-2004 19:23

Re: PHP, MySQL, and Dropdown lists

Quote:

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

28-06-2004 19:29

Re: PHP, MySQL, and Dropdown lists

Quote:

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?

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

28-06-2004 19:46

Re: PHP, MySQL, and Dropdown lists

Quote:

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

28-06-2004 23:11

Re: PHP, MySQL, and Dropdown lists

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 :P I just got home from work.. so.. uhh *runs into wall and falls over*

Max Lobovsky

28-06-2004 23:53

Re: PHP, MySQL, and Dropdown lists

Quote:

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

29-06-2004 08:22

Re: PHP, MySQL, and Dropdown lists

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

Raven_Writer

29-06-2004 09:21

Re: PHP, MySQL, and Dropdown lists

Quote:

Originally Posted by evulish

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

29-06-2004 13:43

Re: PHP, MySQL, and Dropdown lists

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();
?>

evulish

29-06-2004 14:57

Re: PHP, MySQL, and Dropdown lists

Shouldn't you be doing something like UPDATE belt_owner SET $belt = '$new' WHERE wrestlername = 'Trent Sinn'. Or are these same people always going to have a belt? Could you maybe return the results of describe table; for the tables you're talking about? I'm not totally positive how you're doing this. I think I kinda see.. but it's a weird db design that I can't really grasp without seeing..

Raven_Writer

29-06-2004 15:04

Re: PHP, MySQL, and Dropdown lists

Quote:

Originally Posted by evulish

My database is set up like this:

Table - Fields
---------------
admin - username, password
belt_owner -[list of belts]
news - [news stuff]
wrestler - [wrestler info]
------------------

The belt's values are who owns them. The reason for this is because a wrestler could hold more than 1 belt. I'm gonna keep it like this though (atleast for now).

I get what you're saying though. But my field is set up to enter the belt to change, the current holder, and the new holder. So, I can't really do it the way you did by specifically (sp?) saying who holds the belt.

Thanks though :)

evulish

29-06-2004 17:03

Re: PHP, MySQL, and Dropdown lists

Hrm. Okay.. so your belt_owner table has two columns.. one for the belt name and one for the owner of the belt? UPDATE belt_owner SET $belt = '$new' WHERE $belt = '$old' should be

Code:

UPDATE belt_owner SET wrestler_name = '$new' WHERE belt_name = '$belt'

I think..

(and what I meant by describe table.. was actually running 'describe table' in mysql.. so you get stuff like this:)

Code:

mysql> describe myposts;

+-------+--------------+------+-----+---------+----------------+

| Field | Type         | Null | Key | Default | Extra          |

+-------+--------------+------+-----+---------+----------------+

| title | varchar(60)  |      |     |         |                |

| date  | varchar(16)  |      |     |         |                |

| post  | mediumblob   |      |     |         |                |

| id    | mediumint(9) |      | PRI | NULL    | auto_increment |

+-------+--------------+------+-----+---------+----------------+

4 rows in set (0.09 sec)

or if the data isn't big in the table, do something like SELECT * FROM table LIMIT 3; so we can get an idea of what data you have in the table and it'll show the layout and stuff. Much easier than trying to describe you table to us :)

Raven_Writer

29-06-2004 17:30

Re: PHP, MySQL, and Dropdown lists

Quote:

Originally Posted by evulish

Hrm. Okay.. so your belt_owner table has two columns.. one for the belt name and one for the owner of the belt? UPDATE belt_owner SET $belt = '$new' WHERE $belt = '$old' should be

Code:

UPDATE belt_owner SET wrestler_name = '$new' WHERE belt_name = '$belt'

Nope, 1 column, the belt title (TV, Tag Team, etc...). The value for the column is the owner of the belt.

Quote:

(and what I meant by describe table.. was actually running 'describe table' in mysql.. so you get stuff like this:)

...

or if the data isn't big in the table, do something like SELECT * FROM table LIMIT 3; so we can get an idea of what data you have in the table and it'll show the layout and stuff. Much easier than trying to describe you table to us :)

evulish

29-06-2004 17:57

Re: PHP, MySQL, and Dropdown lists

Huh.. okay.. that's an interesting way to do it. Well, then your SQL line should have been right. Try printing it out to make sure the values are what you expect them to be.

Raven_Writer

30-06-2004 20:28

Re: PHP, MySQL, and Dropdown lists

Ok, 1 last question I think.

I got my other problems fixed so far, but now I'm having trouble showing the bio of the wrestler.

I can get it to show everything but the weight. I've been working on this for about 1.5 days, and I can't understand why it's not working:

PHP Code:


		
			
$query = mysql_query("SELECT * FROM wrestler WHERE name = '$w_name'");
   
 while($row = mysql_fetch_array($query)){
    $rfinishers = $row["finisher"];
    $rtrademarks = $row["trademark_move"];
    $rtheme_song = $row["theme_song"];
    $rheight = $row["height"];
    $rweight = $row["weight"];
    $rfaction = $row["faction"];
 }

I've checked, and rechecked the column in the table, and it's not any different that what I have above.

If anyone can help me w/ this, I will gladly appreciate it.

evulish

30-06-2004 23:02

Re: PHP, MySQL, and Dropdown lists

Just for kicks, try print_r($row); which will print the array. Make sure it's set and you have the column name correct. The code is fine, so I'd guess it's something small.

Raven_Writer

01-07-2004 08:05

Re: PHP, MySQL, and Dropdown lists

Quote:

Originally Posted by evulish

Just for kicks, try print_r($row); which will print the array. Make sure it's set and you have the column name correct. The code is fine, so I'd guess it's something small.

I tried it, and the field is empty. Everything else is filled out, but that field is empty.

All times are GMT -5. The time now is 00:29.