Jump to content
Larry Ullman's Book Forums

Stuck: Working On An Ugly Dropdown Menu


Recommended Posts

Hi.  I've managed to dig myself down into a muddled mess of ugly dropdown code and need some help.

 

In plain english, this is what I want to happen:

I have a table called employee which has a primary key of employee_id and a table called ruh which has a primary key called ruh_id and a foreign key called employee_id.

I'm trying to make a select menu to update the employee_id on the ruh table.  I would like the dropdown to return every employee_id, first_name, last_name from employee table and then select the foreign key employee_id from ruh table for the one value that should be selected.

 

In this example, the one value should be employee_id  of 68 when ruh_id =1003 .  The end result should be a dropdown menu filled with all the employee_ids from the employee table and with 68 selected.

The pupose of this is to allow the user to update the employee_id on the ruh table with any employee_id from the employee table.

 

I hope it makes sense.  I've been trying to figure this out for many hours and am slowly going insane.

Help is appreciated.
 

I found something similar here:

http://www.larryullman.com/forums/index.php?/topic/1944-making-drop-down-date-values-sticky/?hl=selected

<?php
	require('includes/config.inc.php');
	require(MYSQL);
	$q = "SELECT e.employee_id, r.employee_id, e.first_name, e.last_name FROM employee AS e LEFT JOIN ruh AS r";
	$r = @mysqli_query ($dbc, $q);
	while ($row = mysqli_fetch_array($r, MYSQLI_NUM)) {

  $ruh_id = 1003; // 
  $employee_id = $row[0];
  }
  echo '<label>Employee</label>
  <select name="employee_id">';
  foreach ($employee_id as $value) {
    echo '<option value="' . $value . '"';
    if ($value === $ruh_id) {
      echo ' selected="selected"';
    }
    echo '>' . $value . '</option>';
  }
  echo '</select>';

?>
Link to comment
Share on other sites

Deaddog, I'm not entirely sure what you want (as I can't understand your post very well), but I think that both your query and while loop are wrong.

 

In regards to the query, you will likely need to restrict your join with some sort of condition(s) in the WHERE clause. Also, I think you want to return the ruh ID, not the employee ID, from the ruh table.

Furthermore, your while loop does not seem to be correct. As such, I can't imagine you'll get even close to what you want.

 

Again, I'm not sure exactly what you want, but I'll take a stab at it with some abbreviated code that highlights the parts of interest:

<?php
  
  // Includes here
  
  $q = "SELECT e.employee_id, r.ruh_id, e.first_name, e.last_name FROM employee, ruh WHERE e.employee_id = r.employee_id;";
  
  // Execute the query here.
  
  $ruh_id = 1003; // Is this a constant? I'm not sure what "ruh" means.
  
  echo '<label for="employee_id">Employee</label>
  
  <select id="employee_id" name="employee_id">';
  
  while ($row = mysqli_fetch_array($r, MYSQLI_NUM)) {
    
    echo '<option value="' . $row[0] . '"';
    
    if ($row[1] === $ruh_id) {
      
      echo ' selected="selected"';
      
    }
    
    echo '>' . $row[2] . ' ' . $row[3] . '</option>';
    
  }
  
  echo '</select>';

 

That's my best guess of what you want.

Please let us know.

Thanks.

  • Upvote 1
Link to comment
Share on other sites

Hi HartelySan,

Let me try to explain it better.

 

If I use the following SQL statement I don't get all the employee_ids in the the employee table:

 

SELECT e.employee_id, r.ruh_id, e.first_name, e.last_name FROM employee AS e INNER JOIN ruh AS r WHERE r.employee_id = e.employee_id;

 

You asked what the ruh_id was.  Well, I'm making an incident reporting system and the ruh_id is the report id primary key.  Not every employee will make a report, however, if a report is made, the dropdown to edit who made it must contain all the employees in the company (from the employee table) and the one employee (selected) whos report it is to be edited from the ruh table.

 

The following (working) drop down returns all the employees in employee table and this is what I want to happen. However, it lacks the ability to preselect the current employee_id foriegn key in the ruh table for that particurly ruh_id.

<?php 
require('includes/config.inc.php');
echo'<form>
<fieldset>
<legend>Test Form</legend>
<select name="employee_id">';
include(MYSQL);
$q = "SELECT employee_id, first_name, last_name FROM employee";
$r = mysqli_query($dbc, $q);
if (mysqli_num_rows($r) > 0) {
	while ($menu_row = mysqli_fetch_array($r, MYSQLI_NUM)) {
		echo '<option value="'.$menu_row[0].'">'.$menu_row[1].' ' . $menu_row[2] . '</option>\n';
		}
	}
mysqli_free_result($r);
echo '</select>
</fieldset>';
?>

I was thinking maybe I need a foreach statement.   I hope this makes it clearer.

 

Link to comment
Share on other sites

Thank you for explaining all that.

 

If you want all the employees (including employees who have not made any reports), use a left join instead of an inner join. In other words, change "INNER" in the query to "LEFT".

Also, assuming that your query is sound, you should use a while loop to get all the returned results from the DB, not a foreach loop.

 

With all that said, however, I'm having trouble understanding your implementation. Why would someone select who edited/edits a report? Wouldn't that always be automatically assigned based on who actually edited/edits a report?

I think I am misunderstanding something, but I'm not sure what.

 

Doesn't someone simply select a report to edit, and then edit it? What am I missing?

Link to comment
Share on other sites

The idea is that the person who enters the report in the db is probably not the actual employee who reports the incident.  The db entry will be done by someone in an office somewhere.  The initial report will be written on paper and submitted to management.    This system will give the office guy the chance to edit who makes the incident report in case a mistake is made on the initial entry. 

 

Anyway, I will work on the whileloop and the LEFT JOIN and get back to this post when I figure it all out.    Thanks for the help.

Link to comment
Share on other sites

Okay, I think I get it now.

So basically, the person who writes the initial report (not the person who actually enters it into the DB) is credited as being the initial creator of the report, no?

And that person is associated with the report, so that down the line, if someone wants to edit the report, they're presented with a drop-down list that contains all the employees, and by default, the initial creator is selected in the drop-down list, right?

 

If that's the case, then as I mentioned before, you definitely want to perform a left join on the employees table with the ruh table being the other table in the query (the table on the right). Also, you may want to consider using the MySQL CONCAT function to group the first and last names together.

Finally, you should definitely use a while loop to print out all the information retrieved from the DB.

 

Good luck, and please let us know whether you find success or get stuck again.

Thanks.

Link to comment
Share on other sites

Yes that's right.  The incident creator is probably someone in a workshop somewhere, but the guy entering these reports is updating into the db, sitting in a office someplace. 

 

With your help, I managed to get a working code, though maybe not as polished as it could be, it seems to do the trick.

 

Here it is:  It was the LEFT JOIN that was the key here.  I also used the CONCAT advice. 

Thanks again.

<?php 
require('includes/config.inc.php');
echo'<form>
<fieldset>
<legend>Test Form</legend>
<select name="employee_id">';
include(MYSQL);
$q = "SELECT e.employee_id, r.employee_id, CONCAT(e.first_name ,e.last_name) AS name, r.ruh_id FROM employee AS e LEFT JOIN ruh as r USING(employee_id) ORDER BY e.last_name ASC";
$r = mysqli_query($dbc, $q);
$id = 1006; //Get ruh_id for incident.
if (mysqli_num_rows($r) > 0){
	while ($menu_row = mysqli_fetch_array($r, MYSQLI_NUM)) {
			if($menu_row[3]== $id){//select the employee that made initial report.
			echo '<option value="'.$menu_row[0].'" selected="selected">'.$menu_row[2].'</option>';
			}
			if($menu_row[3] != $id) {//list all employees to allow editing, if required.
			echo '<option value="'.$menu_row[0].'">'.$menu_row[2].'</option>';
			}
		}
	}
mysqli_free_result($r);
echo '</select>
</fieldset>';
?>

Link to comment
Share on other sites

I think I figured it out now.  The solution is probably the PHP equivalent of fixing the car with duct tape, but it really seems to work this time.

Making the JOIN was too advanced for me, so I simply made two separate queries.  The ruh_id 1006 on line 6 will be the get value depending on what the user selects.  So on the live site, that bit of code will look like this:

$q2 = "SELECT employee_id FROM ruh WHERE ruh_id = $id";

Otherwise the test code looks like this:

<legend>Test Form</legend>
<select name="employee_id">';
include(MYSQL);
$q = "SELECT employee_id, CONCAT(first_name ,last_name) FROM employee";
$r = mysqli_query($dbc, $q);
$q2 = "SELECT employee_id FROM ruh WHERE ruh_id = '1006'";
$r2 = mysqli_query($dbc, $q2);
$menu_row_selected = mysqli_fetch_array($r2, MYSQLI_NUM);
if (mysqli_num_rows($r) > 0){
	while ($menu_row = mysqli_fetch_array($r, MYSQLI_NUM)) {
			echo '<option value="'.$menu_row[0].'"'; 
			if ($menu_row_selected[0]== $menu_row[0]) echo 'selected'; else echo '';
			echo '>'.$menu_row[1].'</option>';
			}
		}
mysqli_free_result($r);
echo '</select>
Link to comment
Share on other sites

Hi HartleySan,

It is working now with the two queries, confirmed.   

 

Margaux,

I did use the CONCAT as you advised.  That part looks like this:

CONCAT_WS(' ', first_name, last_name)

Because I am using MYSQLI_NUM:

while ($menu_row = mysqli_fetch_array($r, MYSQLI_NUM)

I dropped the alias "name".  This is also working.

 

Since all the code is working, for now, I will move forward with the project, however it's definitely an issue I will try to improve later.  If I do figure out a way to make the dropdown work with one query, I will post the answer.  

 

Of course if the answer just happens to magically appear as a post somewhere on the forums, I'll take that too.  Hint, Hint  :)

 

Thanks for the help.

Link to comment
Share on other sites

I couldn't get this query to work on a similar situation where I have a table for users and a table for reviews. A user can have one, many or no reviews but a review will belong to only one user. When I ran a query similar to the above using left join

$q = "SELECT u.user_id, r.review_id, CONCAT_WS(' ', u.first_name,u.last_name) 
AS name FROM users AS u LEFT JOIN reviews AS r ON(u.user_id = r.user_id) 
ORDER BY u.last_name ASC";

a row was returned for each row in the user table and for each row in the review table - so several users were returned multiple times which is not what I want.

 

Then I remembered that an outer join will return all records in the second table if there is a match in the first table which of course there is. So a join won't work but I'm wondering if there is a way to get the info using only one query, perhaps with a subquery or a conditional? I dont think DISTINCT will help because all the review ids are unique. Any thoughts?

 

I mentioned both CONCAT and using joins in previous posts.

I only mentioned CONCAT in my previous post because the OP was using CONCAT and not CONCAT_WS which may have not given the results he wanted. 

Link to comment
Share on other sites

Well, yeah, because you have to also provide a specific report ID to get the exact one you're looking for.

As an example, I whipped together the following DB real quick.

Please copy and paste the SQL into phpMyAdmin or whatever to test it.

 

-- phpMyAdmin SQL Dump
-- version 3.5.2.2
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Apr 20, 2013 at 10:54 PM
-- Server version: 5.5.27
-- PHP Version: 5.4.7
 
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
 
 
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
 
--
-- Database: `reports_db`
--
CREATE DATABASE `reports_db` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `reports_db`;
 
-- --------------------------------------------------------
 
--
-- Table structure for table `reports`
--
 
CREATE TABLE IF NOT EXISTS `reports` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `content` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=13 ;
 
--
-- Dumping data for table `reports`
--
 
INSERT INTO `reports` (`id`, `user_id`, `content`) VALUES
(1, 1, 'D''oh!'),
(2, 2, 'Lots of cleaning to do.'),
(3, 2, 'And my husband is a mess.'),
(4, 4, 'Too many things to write about.'),
(5, 4, 'I am so smart!'),
(6, 4, 'My idiot brother is a failure.'),
(7, 6, 'I am the walrus.'),
(8, 6, 'My wife is Yoko Ono.'),
(9, 7, 'I''m still rockin'' it!'),
(10, 9, 'Ringo''s the name. Drumming''s my game!'),
(11, 10, 'I am the most famous Japanese baseball player in the world.'),
(12, 11, 'I have written a lot of books. Which one should I write next?');
 
-- --------------------------------------------------------
 
--
-- Table structure for table `users`
--
 
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(80) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=12 ;
 
--
-- Dumping data for table `users`
--
 
INSERT INTO `users` (`id`, `name`) VALUES
(1, 'Homer Simpson'),
(2, 'Marge Simpson'),
(3, 'Bart Simpson'),
(4, 'Lisa Simpson'),
(5, 'Maggie Simpson'),
(6, 'John Lennon'),
(7, 'Paul McCartney'),
(8, 'George Harrison'),
(9, 'Ringo Starr'),
(10, 'Ichiro Suzuki'),
(11, 'Larry Ullman');
 
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

 

In the above DB, there is a users table with IDs and names (just one name column for simplicity), and a reports table with its own ID, as well as a user ID column, which connects to the users table, and a column with the actual report content. The key is that any user in the users table can have zero, one or many reports in the reports table associated with them.

 

Now, if you run the following query, you'll get what you want (I think):

SELECT u.name, r.id FROM users AS u LEFT JOIN reports AS r ON u.id = r.user_id AND r.id = 3;

 

Note that the r.id value of 3 is completely arbitrary, and that's the value that should be provided to the query by the PHP script.

Please play around with the above DB and query, and I think you'll see it gives both of you what you want.
  • Upvote 1
Link to comment
Share on other sites

Thanks HartleySan, that worked. I was getting stuck on the syntax of specifying review(ruh)_id and did not know it could be specified in the ON clause. I have been looking for awhile for some good mysql learning resources to take my learning to a more advanced level  - if you know of any that you'd recommend please post. Thanks again!

Link to comment
Share on other sites

HartelySan,

That was an excellent post.  I have done your tutorial and it worked perfect in phpmyadmin.  It's very late, but I will incorporate the query in my live script as soon as I get the time, probably tomorrow.

Link to comment
Share on other sites

Here is the working code, using one query:

<label>Type:</label>
<select name="ruh_type_id">';
$q = "SELECT t.ruh_type_id, r.ruh_id, t.type_name FROM ruh_type AS t LEFT JOIN ruh AS r ON t.ruh_type_id = r.ruh_type_id AND r.ruh_id=$id";  //$id = r.ruh_id
$r = mysqli_query($dbc, $q);
if (mysqli_num_rows($r) > 0){
	while ($menu_row = mysqli_fetch_array($r, MYSQLI_NUM)) {
			echo '<option value="'.$menu_row[0].'"'; 
			if ($menu_row [1]== $id ) echo 'selected'; else echo '';
			echo '>'.$menu_row[2].'</option>';
			}
		}
mysqli_free_result($r);
echo '</select>';
Link to comment
Share on other sites

Cool. Glad it works.

A couple of comments:

 

1) Your label should have a for attribute, and the select element should have an ID of the same name.

 

2) I'd echo ' selected="selected"', not just 'selected'. For one, you should add a space at the beginning, and also, just 'selected' *may* cause some issues on some browsers.

Link to comment
Share on other sites

 Share

×
×
  • Create New...