Previous Topic Next Topic

Make preselected dropdown list -2 mysql databases

Solved/Closed
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 - Updated on Oct 19, 2018 at 09:37 AM
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 - Nov 27, 2018 at 03:44 PM
Hi All,

Im stuck again - just when I think I know something I prove I dont!

Im looking to create a drop down list which is pre-selected from date in 2 mysql data bases. This MUST be coded in procedural PHP.

What I have is 2 databases. One (called "Users") holds the names of people and a corresonding ID number thus:

ID .......... Name
1 ........... Alan
2 ........... Brian
3 ........... Chris
4 ........... Denise

Another table (called "Data") has a few columns, one of which is called "User_ID". These user ids are related to the "Users" table. If the column has a 1 in it the that row was inserted by Alan.

What I want is a dropdown menu on my web page (which is written in PHP) where if a user has input the row of data in the "Data" database then the name of that user comes pre-selected in the dropdown menu. So if the row in "Data" has 3 in the "User_ID" column, then "Chris" is shown pre-selected in the dropdown menu.

What I have so far (and its the closest I have got in 5 days of struggling) is this code (assume I am connected to the databases):

select name="tracking" id="current_user"
<?php

$row = mysqli_query($conn, "SELECT * FROM Data");
$result = mysqli_query($conn, "SELECT ID, Name FROM Users");

while($row=mysqli_fetch_array($result)) {

$selected = (($value["ID"]) === ($row["ID"]) ? 'selected' : '');
$UserID = (($value["ID"]) === ($row["ID"]) ? $value["Name"] : '');

echo '<option value='.$UserID.' '.'select= '.$selected.'>'$row['Name']'</option>';

}

?>
</select>


Can anyone help me make this work please? It gives the wrong name - it always returns the last name on the list regardless of the line in the "Data" database.

Any help would be appreciated.

Related:

3 responses

Answer 1 / 3
Blocked Profile
Oct 19, 2018 at 04:56 PM
You have to build a query with a join.

Try something like this:

SELECT name.Data, id.users
FROM Data
INNER JOIN Users ON Data.ID=Users.ID;


Inner joins brings only data that matches and exists ON BOTH table.

Left Join brings data from the Left table, and only the matching from the right.

Right Join brings all from right table, and only the matching from left.


I like Playlinda GEOGRAPHICALLY CHALLENGED®! Thanks!
0
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 149
Oct 23, 2018 at 03:51 PM
Thanks ac3mark,

Im still having trouble with this. I had forgotten about joins and need to relearn this. I'm sure you have put me on the right track but I need to experiment a bit. Have a coke while you wait :^)
0
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 149
Oct 25, 2018 at 09:38 AM
Ok Im admitting defeat. When I try an inner join the drop down menu is populated with all the enteries that have been inputted so there are about 100 items in the dropdown which has the 4 users names attached to each one. I just want the 4 users names to appear. Im struggling to get this in to my PHP code. :^(
0
Blocked Profile
Updated on Oct 25, 2018 at 05:00 PM
Use Distinct Users.

Select DISTINCT will only return One per user!

I assumed you were building the query already using the user list. Sorry for making an ASS out of ME!
0
Answer 2 / 3
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 149
Oct 28, 2018 at 04:46 AM
Ok, I have found why my first aproach doesn't work. It seems the 
$selected = (($value["ID"]) === ($row["ID"]) ? 'selected' : '');
$UserID = (($value["ID"]) === ($row["ID"]) ? $value["Name"] : '');

lines keep looping when in a while loop until the last item in the array is looked at. Therefore the value of the variable will always be the value of the last entry in the array.

I have only entered this finding in the hope it helps others in the same situation - sorry no fix yet.

ac3mark - I havent had chance to look into your last suggestion. I will get onto it next week - unfortunately the wife wants to go on holiday!
0
Blocked Profile
Oct 30, 2018 at 05:02 PM
Well first of all, do not chop the two into separate Queries, it is one. So it would be like this: 

$row="SELECT DISTINCT name.Data, id.users FROM Data INNER JOIN Users ON Data.ID=Users.ID;"
 while($row=mysqli_fetch_array($result)) {
echo '<option value='.$row[0].' '.'select= '.$selected.'>'$row[1]'</option>';


Where 0 and 1 are the index numbers returned by the Query. Index[0]=Name.date and index[1]=id.users as indicated by the join in the SQL!


If this does not produce what you are asking, then you will need to build one array on Users. Build a loop of Queries that will return one row per user. Then you will run another query of what "items" are attached to that user, storing them in another multidimensional array. So, the first array will be of:

Select Distinct users from user table
result
Mary
Jorge
Fred

Those returned values are stored into an array of users:
Users(0)=Mary
Users(1)=Jorge
Users(2)=Fred

Now, when you query based on that element, you build another array for just that specific user:
"Select ticket_numbers where user = " & USER(0) & ";"
Return values are something like:
"3456"
"4321"
"8976"

Which represents the ticket numbers in this example. So when those are returned, we load them into another array called tickets. This array has two values to it, the user, and the ticket. So, up to know we have:
Ticket(0)="USER(0),3456"
Ticket(1)="USER(0),4321"
Ticket(2)="USER(0),8976"


After the second loop has finished, you can output the link as in:

Build a loop: 

echo '<option value='Ticket(0)[0]'>'Ticket(0)[1]'</option>';




I found this most basic that describe in PHP. I am an ASP, VBA, and C++ guy. The theory is still the same! 

$data = array();
while($row = mysql_fetch_assoc($result))
{
   $data[$row['id']]['name'] = $row['name'];
   // whatever else you want to do at this time....
}
0
Answer 3 / 3
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 149
Nov 12, 2018 at 07:01 PM
Thanks ac3mark,

Your solution put me on to the correct track... I think. Im sure my code can be improved (a lot), but at least it works.

This is what I have ... 
<?php
$data1 = "SELECT ID, Name, FROM Users
INNER JOIN Data
ON Data.Data_input_by=Users.ID
WHERE Data.id = $ticket_number";

$result1 = mysqli_query($conn, $data1);
$value1=mysqli_fetch_assoc($result1);
?>

<select name="tracking2" id="current_user2">

<?php
$data2 = "SELECT * FROM Users";
$result2 = mysqli_query($conn, $data2);

while($value2=mysqli_fetch_assoc($result2)) {
	IF ($value1['ID'] != $value2['ID']) {
		echo "<option value='{".$value2['ID']."}'>".$value2['Name']."</option>";
	} ELSE {
		echo "<option selected value='{".$value2['ID']."}'>".$value2['Name']."</option>";
	}
}
?>
</select>

You now get a case of whatever beer/spirit you like. Thanks again for your time and advice. It is very much appreciated :^)
0
Blocked Profile
Updated on Nov 13, 2018 at 08:50 AM
I like Playlinda GEOGRAPHICALLY CHALLENGED®! Thanks!
0
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 149
Nov 17, 2018 at 12:16 PM
2 cases are on its way to my house. Get yourself round here before its gone!
0
Blocked Profile
Nov 19, 2018 at 04:25 PM
LOL! Thanks sir!
0
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 149
Nov 27, 2018 at 03:44 PM
mmmmm - nice this - I can see why you like it!
0