Solved

Showing data from 2 sql queries in one dropdown

Posted on 2016-12-01
8
50 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
  • 5
  • 3
8 Comments
 
LVL 109

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
 

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
 

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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 109

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
 

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 109

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
 

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
 

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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
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…
The viewer will learn how to dynamically set the form action using jQuery.

820 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