Make preselected dropdown list -2 mysql databases

Posts
983
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
November 12, 2018
- Oct 18, 2018 at 06:31 PM - Latest reply:
Posts
10849
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
November 15, 2018
- Nov 13, 2018 at 08:49 AM
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 

Your reply

8 replies

Posts
10849
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
November 15, 2018
- Oct 19, 2018 at 04:56 PM
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!
Posts
983
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
November 12, 2018
- 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 :^)
Posts
983
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
November 12, 2018
- 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. :^(
Posts
10849
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
November 15, 2018
- 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!
Respond to ac3mark
Posts
983
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
November 12, 2018
- Oct 28, 2018 at 04:46 AM
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!
Posts
10849
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
November 15, 2018
- 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....
}
Respond to BrianGreen
Posts
983
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
November 12, 2018
- Nov 12, 2018 at 07:01 PM
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 :^)
Posts
10849
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
November 15, 2018
- Nov 13, 2018 at 08:49 AM
I like Playlinda GEOGRAPHICALLY CHALLENGED®! Thanks!
Respond to BrianGreen