Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 79
  • Last Modified:

Showing data from 2 sql queries in one dropdown

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
Black Sulfur
Asked:
Black Sulfur
  • 5
  • 3
1 Solution
 
Ray PaseurCommented:
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
 
Black SulfurAuthor Commented:
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
 
Black SulfurAuthor Commented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Ray PaseurCommented:
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
 
Black SulfurAuthor Commented:
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
 
Ray PaseurCommented:
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
 
Black SulfurAuthor Commented:
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
 
Black SulfurAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now