Solved

Showing data from 2 sql queries in one dropdown

Posted on 2016-12-01
8
5 Views
Last Modified: 2016-12-01
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Black Sulfur
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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
Comment Utility
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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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 …
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now