Solved

Showing data from 2 sql queries in one dropdown

Posted on 2016-12-01
8
62 Views
Last Modified: 2016-12-28
I had this question after viewing SQL inner join confusion.

When editing a record, there is a dropdown for location. Ideally, I would like the location chosen in the initial insert record to be the text you see in the dropdown so when the record is updated, it doesn't change the location because it is on the default record from the SQL query. So, if it is alphabetical and when you signed up you chose United States, when you go to update the record, the dropdown should still show United States as the selected record and not the first record in the alphabet i.e.: Africa for example. This would obviously be to prevent them pressing the update button and unwillingly the database record changes from United States to Africa.

I figured out the part that I need to show the initial selected location as per below. My problem is that it only shows one record, the initial selection. Is it possible to list all the other locations beneath it in case the user does want to change their location? Or is there a better way to handle this?

echo "<select name='locations' class='selectpicker' data-title='Select Location' data-style='btn-default btn-block' data-menu-style='dropdown-blue'>";
$stmt = $link->prepare("SELECT location.location_name, service_providers.sp_location FROM service_providers INNER JOIN location ON service_providers.sp_location = location.location_id AND service_providers.service_provider_id = ?");
$stmt->bind_param("i", $_GET['id']);
$stmt->execute();
$stmt->bind_result($location_name, $sp_location);
$stmt->fetch();
$stmt->close();
echo "<option value='$sp_location'>" . sanitize($location_name) ."</option>";
echo " </select>";

Open in new window

0
Comment
Question by:Black Sulfur
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 41909243
If you expect to get multiple rows back in the results set, it would be reasonable to use the results of fetch() in an iterative way.  You can test the return value from the function.  If it's TRUE, it has loaded data into the bound variables.  If it's NULL, there is no more data to load.
http://php.net/manual/en/mysqli-stmt.fetch.php
0
 
LVL 1

Author Comment

by:Black Sulfur
ID: 41909257
I have this on the insert record dropdown which shows all locations which works fine. Not sure if I didn't explain my question properly or if I just don't understand your answer :-)

I want the initial choice to show first and then all the other records in case the user wants to change their location when editing their profile. But if they don't the initial record must be the one that updates, basically just overwriting itself.


echo "<select name='locations' class='selectpicker' data-title='Select Location' data-style='btn-default btn-block' data-menu-style='dropdown-blue'>";
	$stmt = $link->prepare("SELECT `location_name`, `location_id` FROM `location` ORDER BY `location_name` ASC");
	$stmt->execute();
	$result = $stmt->get_result();
	$numRows = $result->num_rows;
	If($numRows > 0) {
		while($row = $result->fetch_assoc()) {
			$locationId = htmlentities($row['location_id']);
			$location_name = htmlentities($row['location_name']);
			echo "<option value='$locationId'>$location_name</option>";
		}
	}
	$stmt->close();
    echo " </select>";

Open in new window

0
 
LVL 1

Author Comment

by:Black Sulfur
ID: 41909266
Let me try explain it better.. When I signup I see a long list in alphabetical order....

Afghanistan
Albania
Algeria
Andorra
Angola
Antigua and Barbuda
Argentina
Armenia
Aruba
Australia
Austria
Azerbaijan
.........................

Let's pretend I select Denmark. A few days later I decide I want to change something on my profile. The dropdown that shows my country should have Denmark already selected in the dropdown so that when I click update, it doesn't change my country to Afghanistan which is the first record in the dropdown. So, instead of the dropdown looking like the above, it should look like:

Denmark
Afghanistan
Albania
Algeria
Andorra
Angola
Antigua and Barbuda
Argentina
Armenia
Aruba
Australia
Austria
Azerbaijan

So, when the user clicks update, it selects Denmark again because that is the first record since that is pulling from the database as the selection i made when I signed up.
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 110

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 41909273
want the initial choice to show first...
As written, the SQL query will control what shows first.  If you want something different, maybe for the first option, then you might prepare a "default" first option tag and echo that right after the select tag.  There is no harm in running many queries to get the right data for your web application!  So just make a separate query to get Denmark, or whatever, and use that result.  Easy!
1
 
LVL 1

Author Comment

by:Black Sulfur
ID: 41909293
Aha. Great stuff, I got it working. Well, almost. I want to show all the records except the already selected one. At the moment it is showing as the selected one and again in the list of options. I don't want it to show in the list of the options and effectively be displayed twice. I tried a not equal to clause in my SQL statement but it doesn't seem to have the desired result.

echo "<select name='locations' class='selectpicker' data-title='Select Location' data-style='btn-default btn-block' data-menu-style='dropdown-blue'>";
$stmt = $link->prepare("SELECT location.location_name, service_providers.sp_location FROM service_providers INNER JOIN location ON service_providers.sp_location = location.location_id AND service_providers.service_provider_id = ?");
$stmt->bind_param("i", $_GET['id']);
$stmt->execute();
$stmt->bind_result($location_name, $sp_location);
$stmt->fetch();
$stmt->close();
echo "<option value='$sp_location'selected>$location_name</option>";
	
$stmt2 = $link->prepare("SELECT `location_id`, `location_name` FROM `location` WHERE `location_id` <> ?");
$stmt2->bind_param("i", $sp_location);
$stmt2->execute();
$result = $stmt2->get_result();
$numRows = $result->num_rows;
if($numRows > 0){
	while($row = $result->fetch_assoc()){
		
		$location_id = sanitize($row['location_id']);
		$location_name = sanitize($row['location_name']);
		
		echo "<option value='$location_id'>$location_name</option>";
		
	}
}
	
echo " </select>";

Open in new window

0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 41909300
Erm, what does it do that is undesirable?  Look at the generated HTML document (check it with the W3C Validator) and see what looks amiss.
0
 
LVL 1

Author Comment

by:Black Sulfur
ID: 41909302
It shows the location as selected in the dropdown and then when expanding the dropdown it shows again in the list of choices. It shouldn't do that. I suppose it isn't the end of the world but I would rather it stays as is (selected) and you don't see it again in the list of options.
0
 
LVL 1

Author Comment

by:Black Sulfur
ID: 41909308
Strange, when viewing the source it doesn't show twice. Maybe this is just a weird dropdown and it appears to be showing twice. (I didn't style it myself, it was a purchased admin theme)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question