Menu
Ask a question Report

Make preselected dropdown list -2 mysql databases [Solved]

Posts
999
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
August 11, 2019
 -
Programming Ask a question Report
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.

See more 

3 replies

Report Respond to ac3mark
Posts
12936
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
September 3, 2019
 1342
0
Thank you
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!
Report Respond to BrianGreen
BrianGreen
Posts
999
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
August 11, 2019
 195 -
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 :^)
Report Respond to BrianGreen
BrianGreen
Posts
999
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
August 11, 2019
 195 -
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. :^(
Report Respond to ac3mark
ac3mark
Posts
12936
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
September 3, 2019
 1342 -
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!
Respond to ac3mark
Report Respond to BrianGreen
Posts
999
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
August 11, 2019
 195
0
Thank you
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!
Report Respond to ac3mark
ac3mark
Posts
12936
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
September 3, 2019
 1342 -
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....
}
Respond to BrianGreen
Report Respond to BrianGreen
Posts
999
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
August 11, 2019
 195
0
Thank you
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 :^)
Report Respond to ac3mark
ac3mark
Posts
12936
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
September 3, 2019
 1342 -
I like Playlinda GEOGRAPHICALLY CHALLENGED®! Thanks!
Report Respond to BrianGreen
BrianGreen
Posts
999
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
August 11, 2019
 195 -
2 cases are on its way to my house. Get yourself round here before its gone!
Report Respond to ac3mark
ac3mark
Posts
12936
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
September 3, 2019
 1342 -
LOL! Thanks sir!
Report Respond to BrianGreen
BrianGreen
Posts
999
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
August 11, 2019
 195 -
mmmmm - nice this - I can see why you like it!
Respond to BrianGreen
Reply Ask a question