Connecting Tech Pros Worldwide Help | Site Map
bytes > topic > php > answers

drop down php/mysql

Familiar Sight
  
Join Date: Nov 2007
Posts: 153
#1: Jan 21 '09
Hi all,

This is my first attempt at anything to do with php/mysql so any help will be greatly appreciated.

I have been set a challenge to come up with a web app to enable staff to log on to the page, select their name in a drop down box and then set their current location and time due to leave that location. Then press submit to update their record on staff database.

So far I have gotten any names in database loaded into a drop down box and have 2 further text fields to enter in location and time details. Finding a time function is for another day!

here's my code:

Expand|Select|Wrap|Line Numbers
  1. <html>
  2. <?php
  3. $connection = mysql_connect("localhost", "root", "");
  4. if(!$connection)
  5. {
  6. die("database failed " . mysql_error());
  7. }
  8. $db_select = mysql_select_db("staff_status", $connection);
  9. if(!$db_select)
  10. {
  11. die("database selection failed " .mysql_error());
  12. }
  13. echo $db_select;
  14. ?>
  15.  
  16. <form>
  17. <select>
  18. <?php 
  19. $sql="SELECT id,staff_name FROM status_staff";
  20. $result =mysql_query($sql);
  21. while ($data=mysql_fetch_assoc($result))
  22. {
  23. echo ("<option value=".$data['id'].">". $data['staff_name']."</option>"); 
  24. ?>
  25. <?php } ?>
  26. </select>
  27. </form> 
  28. <form action="drop.php" method="post">
  29.  
  30.  
  31.     <br>
  32. Location: 
  33. <input type="text" name="location">
  34. <br>
  35. Time Leaving: 
  36. <input type="text" name="time">
  37. <br>
  38. <input type="Submit">
  39.  
  40. </html>
  41.  
So far so good but for the life of me I can't find a tutorial to show me how to handle the selection. What I want this app to do is update the record of the person selected with the inputted location and time overiding the last input they did.

Any pointers please?
Markus's Avatar
Moderator
  
Join Date: Jun 2007
Location: York, England, with wolves.
Posts: 4,936
#2: Jan 21 '09

re: drop down php/mysql

Ok, the MySQL will need to be an UPDATE query. You will also need to know how to get the selected value of the dropdown (I will explain that below). However, currently, looking at your form, there is a problem: you <form> has no action or method. Not a huge issue, but it is good practice to tell your form what you want it to do. I expect the form is posting to the same page, but I don't know whether you plan on using GET or POST (or if you even know about those?). I'll assume you will use POST. Make your <form> into this:

Expand|Select|Wrap|Line Numbers
  1. <form action="<?php echo $_SERVER['PHP_SELF'];?>" method="post">
  2.  
Now when the form is submitted, all the values of the form will be in the POST array, which we can access by using the name attribute of the form elements as the array key, like so:

Expand|Select|Wrap|Line Numbers
  1. <?php
  2.  
  3. echo $_POST['element_name'];
  4.  
  5. ?>
  6.  
Overlooking your code again, I see your <select> has no name attribute - this is necessary. Otherwise, the element's value won't be available in the POST array. So add name="user" to your <select>. Note: you will have to have a name attribute for every form element.

Check this tutorial out, for help with forms & php: PHP Tutorial - Forms
Dormilich's Avatar
Moderator
  
Join Date: Aug 2008
Location: Leipzig, Germany
Posts: 3,635
#3: Jan 21 '09

re: drop down php/mysql

Quote:

Originally Posted by Markus View Post

However, currently, looking at your form, there is a problem: you <form> has no action or method. Not a huge issue, but it is good practice to tell your form what you want it to do.

it's a POST to "drop.php", line 28. there's just one <form> too many.

btw. "action" is a required attribute
Familiar Sight
  
Join Date: Nov 2007
Posts: 153
#4: Jan 21 '09

re: drop down php/mysql

so is the code so bad that it can't be made to work?
Dormilich's Avatar
Moderator
  
Join Date: Aug 2008
Location: Leipzig, Germany
Posts: 3,635
#5: Jan 21 '09

re: drop down php/mysql

if you apply Markus' improvements, then certainly not.
Familiar Sight
  
Join Date: Nov 2007
Posts: 153
#6: Jan 21 '09

re: drop down php/mysql

Thanks Dormilich for swift reply

Here is code with suggestions added:

Expand|Select|Wrap|Line Numbers
  1. <html>
  2. <?php
  3. $connection = mysql_connect("localhost", "root", "");
  4. if(!$connection)
  5. {
  6. die("database failed " . mysql_error());
  7. }
  8. $db_select = mysql_select_db("staff_status", $connection);
  9. if(!$db_select)
  10. {
  11. die("database selection failed " .mysql_error());
  12. }
  13. echo $db_select;
  14. ?>
  15.  
  16. <form action="<?php echo $_SERVER['PHP_SELF'];?>" method="post">
  17. <select name="user">
  18. <?php 
  19. $sql="SELECT id,staff_name FROM status_staff";
  20. $result =mysql_query($sql);
  21. while ($data=mysql_fetch_assoc($result))
  22. {
  23. echo ("<option value=".$data['id'].">". $data['staff_name']."</option>"); 
  24. ?>
  25. <?php } ?>
  26. <?php 
  27.  
  28. echo $_POST['staff_name']; 
  29.  
  30. ?>
  31. </select>
  32. </form> 
  33. <form action="drop.php" method="post">
  34.  
  35.  
  36.     <br>
  37. Location: 
  38. <input type="text" name="location">
  39. <br>
  40. Time Leaving: 
  41. <input type="text" name="time">
  42. <br>
  43. <input type="Submit">
  44.  
  45. </html>
  46.  
  47.  
I know I'm out of my depth here but if I was to get it working I could understand/learn from reading the working code.

Any further instructions please?
Dormilich's Avatar
Moderator
  
Join Date: Aug 2008
Location: Leipzig, Germany
Posts: 3,635
#7: Jan 21 '09

re: drop down php/mysql

there's still work to do...
minor details:
- line 23, you should html-quote the attribute values
Expand|Select|Wrap|Line Numbers
  1. echo '... value="' . $var . '" ...';
  2. // or
  3. echo "... value=\"" . $var . "\" ...";
  4. // giving
  5. ... value="content_of_$var" ...
- line 24–26, replace by } (the <?php and ?> remove themselves)

major details:
- the values from the first form are never sent (there's no submit button for that form). you should make only one <form> which will process the results. I guess it's the second one. remember, only the values from inside the <form> are submitted (you may have multiple form elements on a side, but only the one whose submit is pressed actually sends data).
Familiar Sight
  
Join Date: Nov 2007
Posts: 153
#8: Jan 21 '09

re: drop down php/mysql

thanks again but just don't understand what you are saying to do.

I have never asked anyone on the site to show me with my variables, but I just don't think ill get it any other way.

If you can't do that for me, thanks again, I'll just stick to java!
Dormilich's Avatar
Moderator
  
Join Date: Aug 2008
Location: Leipzig, Germany
Posts: 3,635
#9: Jan 21 '09

re: drop down php/mysql

in the end, the html code should look like this (more or less):
Expand|Select|Wrap|Line Numbers
  1. <html>
  2.  
  3. <form action="drop.php" method="post">
  4.    <select name="user">
  5.      <option value="ID1">NAME1</option>
  6.      <option value="ID2">NAME2</option>
  7.      <option value="ID3">NAME3</option>
  8.  // ...
  9.   </select>
  10.   <br>
  11. Location: 
  12.    <input type="text" name="location">
  13.    <br>
  14. Time Leaving: 
  15.    <input type="text" name="time">
  16.    <br>
  17.    <input type="Submit" value="send">
  18. </form>
  19.  
  20. </html>
in the final version, drop lines 13 and 28 (this is nothing for the eyes of the user, only for debugging)

the file "drop.php" has to handle the database update.

EDIT: the problem of your code does not come from the PHP side, it's the HTML code that's not right.
Familiar Sight
  
Join Date: Nov 2007
Posts: 153
#10: Jan 21 '09

re: drop down php/mysql

Dormilich, thank you! I now know what the meaning of the word post means!

So after a bit of copying and pasting (and learning) I have 2 files which look like they should do what im trying.

here's insert1.php

Expand|Select|Wrap|Line Numbers
  1. <html>
  2. <?php
  3. $connection = mysql_connect("localhost", "root", "");
  4. if(!$connection)
  5. {
  6. die("database failed " . mysql_error());
  7. }
  8. $db_select = mysql_select_db("staff_status", $connection);
  9. if(!$db_select)
  10. {
  11. die("database selection failed " .mysql_error());
  12. }
  13. echo $db_select;
  14. ?>
  15.  
  16. <form action="insert2.php" method="post">
  17. <select name="user">
  18. <?php 
  19. $sql="SELECT id,staff_name FROM status_staff";
  20. $result =mysql_query($sql);
  21. while ($data=mysql_fetch_assoc($result))
  22. {
  23. echo ("<option value=".$data['id'].">". $data['staff_name']."</option>");
  24. ?>
  25. </select>
  26. <br>
  27. Location: <input type="text" name="location">
  28. Time Leaving: <input type="text" name="time">
  29.  
  30. <input type="Submit">
  31.  
  32.  
  33.  
  34. </form> 
  35. </html>
  36.  
I have tried to stay with importing the names from database to show in dropdown box as this was working and has been my only success today:(




Here's insert2.php



Expand|Select|Wrap|Line Numbers
  1.  
  2. <?php
  3.  
  4. $connection = mysql_connect("localhost", "root", "");
  5. if(!$connection)
  6. {
  7. die("database failed " . mysql_error());
  8. }
  9. $db_select = mysql_select_db("staff_status", $connection);
  10. if(!$db_select)
  11. {
  12. die("database selection failed " .mysql_error());
  13. }
  14. echo $db_select;
  15.  
  16. $name = $_POST['staff_name']; 
  17. $location = $_POST['location'];
  18. $time = $_POST['time'];
  19. $query2 = "INSERT INTO status_staff VALUES ('$name','$location','$time')";
  20. mysql_query($query2);
  21. mysql_close();
  22. ?>
  23.  
Am i getting closer to the concept of html/php/mysql?

Edit:
Dormilich's Avatar
Moderator
  
Join Date: Aug 2008
Location: Leipzig, Germany
Posts: 3,635
#11: Jan 21 '09

re: drop down php/mysql

Quote:

Originally Posted by brendanmcdonagh View Post

Am i getting closer to the concept of html/php/mysql?

yes


Quote:

Originally Posted by brendanmcdonagh View Post

Expand|Select|Wrap|Line Numbers
  1. echo ("<option value=".$data['id'].">". $data['staff_name']."</option>");

this could be improved to
Expand|Select|Wrap|Line Numbers
  1. echo "<option value=\"", $data['id'], "\">", $data['staff_name'], "</option>";
(printing " to the html too and executing a bit faster)
Quote:

Originally Posted by brendanmcdonagh View Post

Here im getting a parse error on line 30 which no matter what i do won't go away but I think it's a syntax error more than me not having a clue !!

which is line 30, the display is currently a bit strange here....
EDIT: fixed now, I'll have a second look
Familiar Sight
  
Join Date: Nov 2007
Posts: 153
#12: Jan 21 '09

re: drop down php/mysql

sorry, ignore line 30 error, i was looking at wrong page, doh!
Dormilich's Avatar
Moderator
  
Join Date: Aug 2008
Location: Leipzig, Germany
Posts: 3,635
#13: Jan 21 '09

re: drop down php/mysql

Quote:

Originally Posted by brendanmcdonagh View Post

Expand|Select|Wrap|Line Numbers
  1. $name = $_POST['staff_name'];

should be
Expand|Select|Wrap|Line Numbers
  1. $name = $_POST['user'];
second, but nonetheless important: never trust user input! never! you can sanitize the input using mysql_real_escape_string()
Familiar Sight
  
Join Date: Nov 2007
Posts: 153
#14: Jan 21 '09

re: drop down php/mysql

I can't thank you enough, I have it doing what it should!!!!!!!!!!
Markus's Avatar
Moderator
  
Join Date: Jun 2007
Location: York, England, with wolves.
Posts: 4,936
#15: Jan 21 '09

re: drop down php/mysql

I go out to walk the dog, and I come back to another happy customer.

Brendan, just a note, you should really check out these sites (they helped me from n00b to being able to basic-intermediate stuff).

Introduction to PHP

PHP Tutorial - Introduction
Familiar Sight
  
Join Date: Nov 2007
Posts: 153
#16: Jan 21 '09

re: drop down php/mysql

thanks for them sites, i 'll get some more education I just sometimes go into stuff feet first!

I have one more request for help on this thread...

Expand|Select|Wrap|Line Numbers
  1. <form action="insert2.php" method="post">
  2. <select name="user">
  3. <?php 
  4. $sql="SELECT id,staff_name FROM status_staff";
  5. $result =mysql_query($sql);
  6. while ($data=mysql_fetch_assoc($result))
  7. {
  8. echo "<option value=\"", $data['id'], "\">", $data['staff_name'], "</option>"; 
  9. }
  10. ?>
  11. <br>
  12. Location: <input type="text" name="location">
  13. <br>
  14. Time Leaving: <input type="text" name="time">
  15.  
  16. <input type="Submit">
  17.  
my understanding is the above form is sending 3 variables to....

Expand|Select|Wrap|Line Numbers
  1. $name = $_POST['user']; 
  2. $location = $_POST['location'];
  3. $time = $_POST['time'];
  4. $query2 = "update status_staff set staff_location = $location, set time_leaving = $time where staff_name = $name";
  5.  
  6. mysql_query($query2);
  7.  
But it's not adding the input. I know it's talking to database because before i tried update i had insert working but don't want duplicates.

Anyone got any time left to help me finish this last thing>?
Dormilich's Avatar
Moderator
  
Join Date: Aug 2008
Location: Leipzig, Germany
Posts: 3,635
#17: Jan 21 '09

re: drop down php/mysql

according to the manual (MySQL :: MySQL 5.1 Reference Manual :: 12.2.11 UPDATE Syntax) you need only one SET command. second, your input is neither quoted (necessary for strings) nor escaped (mysql_real_escape_string(), remember?)
Markus's Avatar
Moderator
  
Join Date: Jun 2007
Location: York, England, with wolves.
Posts: 4,936
#18: Jan 21 '09

re: drop down php/mysql

When it comes to writing queries, the best way to do it (for readability's sake) we capitalise any reserved words (UPDATE, SET, INSERT, etc), use back-ticks (`) on table names, column names, etc.

Like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.     `Column_Name`
  3. FROM
  4.     `Table_Name`
  5. WHERE
  6.     `Column_4` = '{$var}'
  7.  
Off-topic, but I think it deserves a mention.
Dormilich's Avatar
Moderator
  
Join Date: Aug 2008
Location: Leipzig, Germany
Posts: 3,635
#19: Jan 21 '09

re: drop down php/mysql

@Markus: backtick is unicode number 96 (U+0060) ?
Familiar Sight
  
Join Date: Nov 2007
Posts: 153
#20: Jan 21 '09

re: drop down php/mysql

done some troubleshooting and i can see what is causing the problem but can't see the solution.

Expand|Select|Wrap|Line Numbers
  1. while ($data=mysql_fetch_assoc($result))
  2. {
  3. echo "<option value=\"", $data['id'], "\">", $data['staff_name'], "</option>"; 
I ve done an echo of the $name variable once the following has been assigned

Expand|Select|Wrap|Line Numbers
  1. $name = $_POST['staff_name']; 
and it's showing as empty but the other variables posted are being sent across??
Familiar Sight
  
Join Date: Nov 2007
Posts: 153
#21: Jan 21 '09

re: drop down php/mysql

done it guys, thanks for all your help.

Brendan
Reply

« previous question | next question »

Similar PHP bytes

/bytes/development

/bytes/it

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,327 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved