select option passing data to input fields: mySQL

[javanoob]

I'm trying to write a budget evaluator web app for a game I play. A sample of the database is as follows:

id  |       rifleName  |  em  |   gm   |
----------------------------------------------
1   | .308 Bolt action | 225  | 1350   |

2   | 7mm magnum       | 300  | 1575   |

3   | .243 LeverAction | 215   | 8725  |

I'm using a dropdown selection menu that's dynamically populated through php and mySQL:

<select class='nameItems' id="menu" name="selection">
    <option value="">Choose a Rifle</option>
  <?php foreach ($data as $row): ?>
    <option value="<?= $row['id'] ?>"><?= htmlspecialchars( $row["rifleName"] ) ?></option>
  <?php endforeach ?>
</select>

em:<input type="text" class="charge-type" name="em" value="0" disabled size="5"/>

gm: <input class="charge-type" name="gm" value="0" disabled size="5"/>

And this script to populate the input fields based on what's selected from the dropdown menu:

    $("select[name='selection']").change(function() {
         var selected = $(this).find("option:selected").val();
         $("input[name='em']").val(selected);
         
     $("select[name='selection']").change(function() {
         var selected = $(this).find("option:selected").val();
         $("input[name='gm']").val(selected);
         
    });
         
});

The script is correctly passing the `id` from the database to the input fields but I want the data from the selected `id` in the `em` and `gm` columns from the database to display in the input fields. I'm struggling to figure out how I would get the script to populate the input fields with the data from the em and gm columns. How would I do that? 

I'm extremely novice with JavaScript/jQuery is where the issue lies, I think. I've tried this: 

var selected = $(this).find("option:selected").val('<?= $row['em'] ?>`);

in line 2 of the script but it didn't work. I've tried a bunch of different things. I've been at this one thing, for one menu of what's going to be around 20 menu's (different categories of items) for about a month now. I'd really appreciate help getting this to work so I can move forward with the project. Thank you!

[Barand]

Use data attributes for the options

<?php 

$data = [
            [ 'id' => 1, 'rifleName' => '.308 Bolt action', 'em' => '225', 'gm' => '1350' ],
            [ 'id' => 2, 'rifleName' => '7mm magnum',       'em' => '300', 'gm' => '1575' ],
            [ 'id' => 3, 'rifleName' => '.243 LeverAction', 'em' => '215', 'gm' => '8725' ]
        ];

    
?>

<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script type='text/javascript'>
    $().ready(function() {
        
        $("#menu").change( function() {
            let em = $(this).find("option:selected").data("em")
            let gm = $(this).find("option:selected").data("gm")
            $("#em").val(em)
            $("#gm").val(gm)
        })
    })
</script>

<select class='nameItems' id="menu" name="selection">
    <option value="">Choose a Rifle</option>
  <?php 
        foreach ($data as $row) {
            echo "<option value={$row['id']} data-em='{$row['em']}' data-gm='{$row['gm']}'>" . htmlspecialchars( $row["rifleName"] ) . "</option>";
        } 
   ?>
</select>

em:<input type="text" class="charge-type" name="em" id="em" value="0" disabled size="5"/>

gm: <input class="charge-type" name="gm" id="gm" value="0" disabled size="5"/>

[javanoob]

There's something like 30,000 items in 20 or more categories. I'm trying to avoid writing all that out manually (again, I made the database). I'd like the code file to be short and minimal. Is there not a way to make the script I have print the price (in the "em" and "gm" columns) from the database row the selected ID is from into the input fields? 

[Barand]

27 minutes ago, javanoob said:

Is there not a way to make the script I have print the price (in the "em" and "gm" columns) from the database row

Your script won't as it never refernces those database columns.

I was under the impression that is exactly what my script is doing.

[javanoob]

Your suggestion doesn't access or need a database. It gets it's data from the array in the code file. The script I shared doesn't access the database either. That's what I'd like it to do, what I'm searching and asking for help with: some way of getting the em and gm from the database to display in the input fields without having an excessively long code file. Putting all the data in the code file would be painful task to complete and I'm not sure it would run well. 

I'm hoping I'm only missing a little snippet of php or jquery/JavaScript I haven't found yet. 

I spent several years collecting and organizing the data from the game to create the database. News that it can't be done the way I'm trying to do it isn't news I want to hear. I'll accept it if that's the case but it won't be a happy day.  

Edited June 28, 2022 by javanoob

[dodgeitorelse3]

Why not query your database and put it all in an array like the one Barand showed you. Very little code doing it that way.

Edited June 28, 2022 by dodgeitorelse3
Typo

[Barand]

I don't have your database table so I used the array to simulate the data that would be returned from a query so I could test the code.

    $data = $pdo->query("SELECT id
                              , riflename
                              , em
                              , gm
                         FROM rifles 
                         ORDER BY rifleName    
                        ");
   foreach ($data as $row) {
       echo "<option value={$row['id']} data-em='{$row['em']}' data-gm='{$row['gm']}'>" . htmlspecialchars( $row["rifleName"] ) . "</option>";
   } 

Not so difficult, was it?

[javanoob]

I wasn't aware Barand's suggestion was simulating the database. It wasn't mentioned that it was a simulation. I thought he (she?) was suggesting to write it all out like that. Writing all that out is ouchie.

Edited June 28, 2022 by javanoob

[javanoob]

2 minutes ago, Barand said:

I don't have your database table so I used the array to simulate the data that would be returned from a query so I could test the code.

    $data = $pdo->query("SELECT id
                              , riflename
                              , em
                              , gm
                         FROM rifles 
                         ORDER BY rifleName    
                        ");
   foreach ($data as $row) {
       echo "<option value={$row['id']} data-em='{$row['em']}' data-gm='{$row['gm']}'>" . htmlspecialchars( $row["rifleName"] ) . "</option>";
   } 

Not so difficult, was it?

I wasn't aware that was a simulation. You didn't mention that. I'll try this! It's the answer and way of doing if I can manage to get it working! Thank you!

[javanoob]

That works great, Barand! Thank you so much!

I am having an issue with the text in the dropdown selection's text area not showing up though. It seems to be there, but it's not showing up. 

 

<select id="menu" name="selection">
    <option value="">Choose a Rifle</option>
  <?php 
        foreach ($data as $row) {
            echo "<option value={$row['id']} data-em='{$row['em']}' data-gm='{$row['gm']}'>" . htmlspecialchars( $row["rifleName"] ) . "</option>";
        } 
   ?>
</select>

em:<input type="text" class="charge-type" name="em" id="em" value="0" disabled size="5"/>

gm: <input class="charge-type" name="gm" id="gm" value="0" disabled size="5"/>

The screenshot is without any CSS styling. I tried styling the selection box making the background black and including CSS `color: white;` to try making the text white to see what it did. When I did that the spaces are still there as if the text is there but....it's not there.

It's doing as shown in the screenshot in every browser I have except in firefox the background of the text area is black for some reason. Without any CSS styling it. 

What...um....?? LOL!

There's no css:

<html>
<head>

</head>
<body>

<?php 
$servername = "localhost";
$dbport = '3306';

I thought FireFox might be doing that because I'm using win10's dark theme. I changed it to default, closed all my windows, re-opened firefox and it's still the same. No text in the menu. 

[ginerjm]

rifleName is incorrect

That's what happens when you use multiple cases....

[javanoob]

7 minutes ago, ginerjm said:

rifleName is incorrect

LMFAO!!! That fixed it. Thank you! I didn't spot that. 

[ginerjm]

HtH     oops - I meant HTH

[javanoob]

For the life of me, I've been trying to for decades, I cannot get a grip on JavaScript. 

What am I doing wrong that I can't get the other menus working? It's the same thing, repeated. I tried renaming everything making it individual with the bows menu. No go. The shotgun and bows menu don't work. There's other categories I'd like to add and do this with. Then I'll be looking in to how to add all the values from the em and gm columns in the html table together to get a total that I'll probably be wrestling with too

<html>
<head>
<style>
body {
    background: black;
    color: white;
}
input {
    background: black;
    color: white;
    border: 1px solid orange;
    text-align: center;
}
select, selection {
    background: black;
    color: white;
    border: 1px solid orange;
}
table {
    border: 1px solid gray;
    border-collapse: collapse;
}
</style>
</head>
<body>

<?php 
$servername = "localhost";
$dbport = '3306';
$dbname = 'ehr_tracker';
$dbuser = 'root';
$dbpass = '';

try {
    $pdo = new PDO("mysql:host=$servername;dbname=$dbname;charset=utf8;'", $dbuser, $dbpass,
    array(PDO::ATTR_EMULATE_PREPARES => false,PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
    }
catch(PDOException $e)
    {
    echo "Connection failed: " . $e->getMessage();
    }
$smt = $pdo->prepare('select id, rifleName, em, gm From rifles');
$smt->execute();
$data = $smt->fetchAll();    

$data = $pdo->query("SELECT id
                              , rifleName
                              , em
                              , gm
                         FROM rifles 
                         ORDER BY rifleName    
                        ");
$bows = $pdo->query("SELECT bow_id
                              , bowName
                              , bow_em
                              , bow_gm
                         FROM bows 
                         ORDER BY bowName    
                        ");
$ShotGuns = $pdo->query("SELECT id
                              , shotgunName
                              , em
                              , gm
                         FROM shotguns 
                         ORDER BY shotgunName    
                        ");
        
?>

<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script type='text/javascript'>
    $().ready(function() {
        
        $("#menu").change( function() {
            let em = $(this).find("option:selected").data("em")
            let gm = $(this).find("option:selected").data("gm")
            $("#em").val(em)
            $("#gm").val(gm)
    // Bows        
        $("#bow_menu").change( function() {
            let bow_em = $(this).find("option:selected").data("bow_em")
            let bow_gm = $(this).find("option:selected").data("bow_gm")
            $("#bow_em").val(bow_em)
            $("#bow_gm").val(bow_gm)
        })
      })
    })
</script>


<table border="1"><tr>
<th>Items</th> <th> em</th><th>gm</th><th>+</th>
</th>
<tr>


<!-- Rifles -->
<td colspan="2">Rifles</td><td></td><td><button type="button">+</button></td></tr>
</tr>
<tr>
<td>
<select id="menu" name="selection">
    <option value="#">Choose a Rifle</option>
  <?php 
        foreach ($data as $row) {
            echo "<option value={$row['id']} data-em='{$row['em']}' data-gm='{$row['gm']}'>" . $row["rifleName"] . "</option>";
        } 
   ?>
</select></td>

<td><!-- Rifle EM--><input type="text" id="em" value="0" disabled size="5"/></td>

<td><!-- Rifle GM--><input type+"text" id="gm" value="0" disabled size="5"/></td>
<td>+</td>
</tr>

<!-- Bows -->
<tr>
<td colspan="2">Bows</td><td></td><td><button type="button">+</button></td></tr>
</tr>
<tr>
<td>
<select id="bow_menu" name="selection">
    <option value="1">Choose a Bow</option>

  <?php 
        foreach ($bows as $row) {
            echo "<option value={$row['bow_id']} data-em='{$row['bow_em']}' data-gm='{$row['bow_gm']}'>" . $row["bowName"] . "</option>";
        } 
   ?>
</select>
</td>

<td><!--Bow EM--><input type="text" id="bow_em" value="0" disabled size="5"/></td>

<td><!--Bow GM--><input type="text" id="bow_gm" value="0" disabled size="5"/></td>
</tr>

<!-- ShotGuns -->
<tr>
<td colspan="2">Shotguns</td><td></td><td><button type="button">+</button></td></tr>
</tr>
<tr>
<td>
<select id="menu" name="selection">
    <option value="#">Choose a Shotgun</option>

  <?php 
        foreach ($ShotGuns as $row) {
            echo "<option value={$row['id']} data-em='{$row['em']}' data-gm='{$row['gm']}'>" . $row["shotgunName"] . "</option>";
        } 
   ?>
</select>
</td>

<td><!--Bow EM--><input type="text" class="charge-type" name="em" id="em" value="0" disabled size="5"/></td>

<td><!--Bow GM--><input class="charge-type" name="gm" id="gm" value="0" disabled size="5"/></td>
</table>
</body>
</html>

 

[mac_gyver]

writing out, copy/pasting/overtyping, code for every possible value/category is not programming, that's you being a human-keyboard-macro. you are not using the computer as a tool to accomplish a task, the computer is using you.

your database design should have a category table, with id and name columns. this would establish category_ids. you should have an item (or similarly named) table with id, category_id, and name columns. you would then just use one query to get the data that you want and loop over it to produce the output. to add new categories or add items under a category, you would just insert the data into the appropriate table(s), and the code would 'automatically' use the new values without you needing to touch the code.

you would take a similar Don't Repeat Yourself (DRY) approach in the html and javascript, where you would dynamically build the output sections by looping over the data, putting each select/option menu and em/gm fields in a 'container', that one instance of general-purpose javascript would operate on to populate the correct em/gm fields when there's a change in the  corresponding select/option menu.

[mac_gyver]

see the following example, using the above points (tested with fake data) -

<?php

// initialization

$servername = "localhost";
$dbport = '3306';
$dbname = 'ehr_tracker';
$dbuser = 'root';
$dbpass = '';

// when you make the connection, also set the default fetch mode to assoc, so that you don't need to specify it in each fetch statement
// there's no good reason for you to catch a connection error, just let php catch it.
// also, don't output the raw database errors, as these only help hackers.
// likewise, a connection error is a fatal problem and code should stop executing in order to prevent follow-on errors.
$pdo = new PDO("mysql:host=$servername;dbname=$dbname;charset=utf8;'", $dbuser, $dbpass,
    array(PDO::ATTR_EMULATE_PREPARES => false,PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
	
// get method business logic - get/produce data needed to display the page

// the following assumes you want all category data,
// if not add a WHERE clause to get just the categories you want
$sql = "SELECT c.name category_name, i.id, i.name, i.em, i.gm
	FROM items i
	JOIN category c ON i.category_id = c.id
	ORDER BY c.name, i.name";
$stmt = $pdo->query($sql);
// fetch the data, indexing/pivoting it by the first column selected - category_name
$item_data = $stmt->fetchAll(PDO::FETCH_GROUP);

/*
// fake some data
$data = [];
$data[] = ['id'=>'1', 'name'=>'r1', 'em'=>'10', 'gm'=>'12', 'category_name'=>'rifle'];
$data[] = ['id'=>'2', 'name'=>'b1', 'em'=>'20', 'gm'=>'22', 'category_name'=>'bow'];
$data[] = ['id'=>'3', 'name'=>'b2', 'em'=>'25', 'gm'=>'27', 'category_name'=>'bow'];
$data[] = ['id'=>'4', 'name'=>'s1', 'em'=>'30', 'gm'=>'32', 'category_name'=>'shotgun'];
$data[] = ['id'=>'5', 'name'=>'s2', 'em'=>'33', 'gm'=>'34', 'category_name'=>'shotgun'];
$data[] = ['id'=>'6', 'name'=>'s3', 'em'=>'37', 'gm'=>'38', 'category_name'=>'shotgun'];

// index/pivot the fake data like the FETCH_GROUP would produce
$item_data = [];
foreach($data as $row)
{
	$item_data[ $row['category_name'] ][] = $row;
}
//echo '<pre>'; print_r($item_data); echo '</pre>';
*/

// html document starts here...
?>
<html>
<head>
<style>
body {
    background: black;
    color: white;
}
input {
    background: black;
    color: white;
    border: 1px solid orange;
    text-align: center;
}
select, selection {
    background: black;
    color: white;
    border: 1px solid orange;
}
table {
    border: 1px solid gray;
    border-collapse: collapse;
}
</style>
</head>
<body>

<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script>
$(document).ready(function() {
	$(".populate_em_gm").change( function() {
		let em = $(this).find("option:selected").data("em")
		let gm = $(this).find("option:selected").data("gm")
		$(this).closest('tr').find('.em').val(em)
		$(this).closest('tr').find('.gm').val(gm)
	})
})
</script>

<table border="1">
<tr><th>Items</th><th>em</th><th>gm</th><th>+</th><tr>
<?php
foreach($item_data as $category_name=>$arr)
{
	$cn = ucfirst($category_name);
	echo "<tr><td colspan='2'>{$cn}s</td><td></td><td><button type='button'>+</button></td></tr>";
	echo "<tr><td>";
	echo "<select class='populate_em_gm' name='$category_name'>";
	echo "<option value=''>Choose a $cn</option>";
	foreach($arr as $row)
	{
		echo "<option value='{$row['id']}' data-em='{$row['em']}' data-gm='{$row['gm']}'>{$row['name']}</option>";
	}
	?>
	</select></td>
	<td><input type="text" class="em" value="0" disabled size="5"/></td>
	<td><input type="text" class="gm" value="0" disabled size="5"/></td>
	<td>+</td>
	</tr>
<?php
}
?>
</table>
</body>
</html>

 

[javanoob]

Thanks! Doing them all like this seems to be working.

<!-- Rifles -->
</div>
<tr>
<td colspan="2">Rifles</td><td></td><td><button type="button">+</button></td></tr>
</tr>
<tr>
<td>
<select id="menu" name="selection">
    <option value="1" selected>Choose a Rifle</option>
  <?php $data = $pdo->query("SELECT id, rifleName, em, gm FROM rifles ORDER BY rifleName");
        foreach ($data as $row) {
        echo "<option value={$row['id']} data-em='{$row['em']}' data-gm='{$row['gm']}'>" . $row["rifleName"] . "</option>";
        } 
   ?>
</select></td>
<td><!-- Rifle EM--><input type="text" id="rem" value="0" disabled size="5"/></td>
<td><!-- Rifle GM--><input type+"text" id="rgm" value="0" disabled size="5"/></td>
<td>+</td>
</tr>
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script type='text/javascript'>
    $().ready(function() {
        $("#menu").change( function() {
            let em = $(this).find("option:selected").data("em")
            let gm = $(this).find("option:selected").data("gm")
            $("#rem").val(em)
            $("#rgm").val(gm)
      })
    })
</script>
</div>
<!-- End Rifles -->

 

 

<!-- Shotguns -->
<div>
<tr><td colspan="2">Shotguns</td><td></td><td><button type="button">+</button></td></tr>
</tr>
<tr><td>
<select id="smenu" name="selection">
    <option value="#">Choose a Shotgun</option>
  <?php 
    $shotguns = $pdo->query("SELECT id, shotgunName, em, gm FROM shotguns ORDER BY shotgunName");
    foreach ($shotguns as $row) {
        echo "<option value={$row['id']} data-em='{$row['em']}' data-gm='{$row['gm']}'>" . $row["shotgunName"] . "</option>";
        } 
   ?>
</select>
</td>
<td><!--Shotgun EM--><input type="text" id="sem" value="0" disabled size="5"/></td>
<td><!--Shotgun GM--><input type="tect" id="sgm" value="0" disabled size="5"/></td>
</tr>
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script type='text/javascript'>
    $().ready(function() {
        $("#smenu").change( function() {
            let em = $(this).find("option:selected").data("em")
            let gm = $(this).find("option:selected").data("gm")
            $("#sem").val(em)
            $("#sgm").val(gm)
      })
    })
</script>
</div>
<!-- End Shotguns -->

They are all the same in the database. id, [something]Name, em, gm.

I was hoping when I get it all together I would be able to put the script in one file and the db connection info in another. I'd like to keep the code as minimal as possible. Wishful thinking?

[javanoob]

46 minutes ago, mac_gyver said:

see the following example, using the above points (tested with fake data) -

<?php

// initialization

$servername = "localhost";
$dbport = '3306';
$dbname = 'ehr_tracker';
$dbuser = 'root';
$dbpass = '';

// when you make the connection, also set the default fetch mode to assoc, so that you don't need to specify it in each fetch statement
// there's no good reason for you to catch a connection error, just let php catch it.
// also, don't output the raw database errors, as these only help hackers.
// likewise, a connection error is a fatal problem and code should stop executing in order to prevent follow-on errors.
$pdo = new PDO("mysql:host=$servername;dbname=$dbname;charset=utf8;'", $dbuser, $dbpass,
    array(PDO::ATTR_EMULATE_PREPARES => false,PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
	
// get method business logic - get/produce data needed to display the page

// the following assumes you want all category data,
// if not add a WHERE clause to get just the categories you want
$sql = "SELECT c.name category_name, i.id, i.name, i.em, i.gm
	FROM items i
	JOIN category c ON i.category_id = c.id
	ORDER BY c.name, i.name";
$stmt = $pdo->query($sql);
// fetch the data, indexing/pivoting it by the first column selected - category_name
$item_data = $stmt->fetchAll(PDO::FETCH_GROUP);

/*
// fake some data
$data = [];
$data[] = ['id'=>'1', 'name'=>'r1', 'em'=>'10', 'gm'=>'12', 'category_name'=>'rifle'];
$data[] = ['id'=>'2', 'name'=>'b1', 'em'=>'20', 'gm'=>'22', 'category_name'=>'bow'];
$data[] = ['id'=>'3', 'name'=>'b2', 'em'=>'25', 'gm'=>'27', 'category_name'=>'bow'];
$data[] = ['id'=>'4', 'name'=>'s1', 'em'=>'30', 'gm'=>'32', 'category_name'=>'shotgun'];
$data[] = ['id'=>'5', 'name'=>'s2', 'em'=>'33', 'gm'=>'34', 'category_name'=>'shotgun'];
$data[] = ['id'=>'6', 'name'=>'s3', 'em'=>'37', 'gm'=>'38', 'category_name'=>'shotgun'];

// index/pivot the fake data like the FETCH_GROUP would produce
$item_data = [];
foreach($data as $row)
{
	$item_data[ $row['category_name'] ][] = $row;
}
//echo '<pre>'; print_r($item_data); echo '</pre>';
*/

// html document starts here...
?>
<html>
<head>
<style>
body {
    background: black;
    color: white;
}
input {
    background: black;
    color: white;
    border: 1px solid orange;
    text-align: center;
}
select, selection {
    background: black;
    color: white;
    border: 1px solid orange;
}
table {
    border: 1px solid gray;
    border-collapse: collapse;
}
</style>
</head>
<body>

<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script>
$(document).ready(function() {
	$(".populate_em_gm").change( function() {
		let em = $(this).find("option:selected").data("em")
		let gm = $(this).find("option:selected").data("gm")
		$(this).closest('tr').find('.em').val(em)
		$(this).closest('tr').find('.gm').val(gm)
	})
})
</script>

<table border="1">
<tr><th>Items</th><th>em</th><th>gm</th><th>+</th><tr>
<?php
foreach($item_data as $category_name=>$arr)
{
	$cn = ucfirst($category_name);
	echo "<tr><td colspan='2'>{$cn}s</td><td></td><td><button type='button'>+</button></td></tr>";
	echo "<tr><td>";
	echo "<select class='populate_em_gm' name='$category_name'>";
	echo "<option value=''>Choose a $cn</option>";
	foreach($arr as $row)
	{
		echo "<option value='{$row['id']}' data-em='{$row['em']}' data-gm='{$row['gm']}'>{$row['name']}</option>";
	}
	?>
	</select></td>
	<td><input type="text" class="em" value="0" disabled size="5"/></td>
	<td><input type="text" class="gm" value="0" disabled size="5"/></td>
	<td>+</td>
	</tr>
<?php
}
?>
</table>
</body>
</html>

Thank you! I can only write the script once? I'll play around with your sample here and test that out. Very much appreciated! I think I may have miscommunicated how many categories there are or I'm mis-reading "//the following assumes I want all category data". 
I'm confused on that actually. Yes, I will be using all the category data but not in the same `<select>` menu. 

I made the tables separate as you see on the left because each one is going to have it's own `select` menu:

The shorter the code can be, the less I have to repeat code, the better. I agree! And as I said I'd rather have the JS in it's own file all together. I'd rather not publish all the code in one file. Thank you very much again, for the help and advice! I saw your //comment about the database stuff. I'll apply what you suggested to that too. 

Thanks!

[mac_gyver]

the code i posted is a data-driven design. it takes the input data that is supplied to it and dynamically produces as many select/option menus as there are categories in the data. the javascript is also general-purpose and will work as is regardless of how many different select/option menus there are. there's no need to touch any of the actual code, unless there's a mistake that needs to be fixed or an actual change is desired.

if you comment out the database specific code, and un-comment the fake data code, you can try this by itself to see how it works.

[javanoob]

18 hours ago, mac_gyver said:

the code i posted is a data-driven design. it takes the input data that is supplied to it and dynamically produces as many select/option menus as there are categories in the data. the javascript is also general-purpose and will work as is regardless of how many different select/option menus there are. there's no need to touch any of the actual code, unless there's a mistake that needs to be fixed or an actual change is desired.

if you comment out the database specific code, and un-comment the fake data code, you can try this by itself to see how it works.

I get this error when trying to run what you shared.

Fatal error: Uncaught PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'ehr_tracker.items' doesn't exist in C:\Program Files (x86)\EasyPHP-Devserver-17\eds-www\ehr_tracker\try13\mac_gyver1.php:26 Stack trace: #0 C:\Program Files (x86)\EasyPHP-Devserver-17\eds-www\ehr_tracker\try13\mac_gyver1.php(26): PDO->query('SELECT c.name c...') #1 {main} thrown in C:\Program Files (x86)\EasyPHP-Devserver-17\eds-www\ehr_tracker\try13\mac_gyver1.php on line 26

How you did all that is beyond what I understand so, right, I didn't touch it. The database doesn't have a table named `items`. The items aren't all in one table they're in separate tables. I did it that way because it made the most sense to me. One table per category. One dropdown menu per item category. I did consider putting all of the items in one table called 'items' like that but I don't know how to make the database have a tree structure doing it that way needs. I'm not aware we can make a mySQL database have a tree structure like that. Is it possible to make the database that way? Even if it was possible to do it that way I'm not sure that would be an ideal way of setting it up for this. 

I don't know enough php (and can't find a reference that's of any help to _me_) to edit how you wrote the code you shared so it works. 

This project is based off of the layout map below. The roadmap below is based off of another spreadsheet program I made I'm trying to make a web app version of. It's easier for the game's community to access a web app than a spreadsheet program. The spreadsheet program I made won't run on google sheets or MS office "live" because I made it with Libre Calc. Not compatible or, yeah, I would just upload the spreadsheet program to MS live or google sheets and be done. 

This is the database. I import it as a file through phpMyAdmin to convert it to mySQL:
https://drive.google.com/drive/u/0/folders/16StspK1rSDKGDgKaEEuA9SBiP6FrY3Hw

 

[javanoob]

20 hours ago, mac_gyver said:

if you comment out the database specific code, and un-comment the fake data code, you can try this by itself to see how it works.

I've done that. Works great! I'll see if I can figure out how to get it working with the database I have. 

I don't understand this:
 

$sql = "SELECT c.name category_name, i.id, i.name, i.em, 
    FROM items i
    JOIN category c ON i.category_id = c.id
    ORDER BY c.name, i.name";
$stmt = $pdo->query($sql);

can that be changed to something like:

$sql = "SELECT id, rifleName, em, gm, 
    FROM rifles i
    JOIN category c ON i.category_id = c.id
    ORDER BY c.name, i.name";
$stmt = $pdo->query($sql);

and work for the rifles category and menu? I'm completely lost (at the moment) as to how you did that. Brain farts due to my limited knowledge of all this. I'm figuring it out slowly. I'm very grateful for all the help! 

Edited June 30, 2022 by javanoob

[javanoob]

I can't edit my post above anymore. It doesn't work with my database. I'll work on it a bit to see what I have to change with both the code and the database to get it working that way. I moved a ways forward with the project with how Barand suggested doing it. Your suggestion mac_gyver, seems like a great way of doing it. Hard drive space filling up! lol

[mac_gyver]

23 hours ago, mac_gyver said:

see the following example, using the above points

one of the points was properly organizing the data in the database -

On 6/28/2022 at 7:10 PM, mac_gyver said:

your database design should have a category table, with id and name columns. this would establish category_ids. you should have an item (or similarly named) table with id, category_id, and name columns. you would then just use one query to get the data that you want and loop over it to produce the output

which brings up the programming motto - a good database design supports good code design.

you can build the category and item tables from your existing tables by using a dynamically built INSERT ... SELECT query and some code to get and loop over all the table names.

[javanoob]

3 hours ago, mac_gyver said:

one of the points was properly organizing the data in the database -

which brings up the programming motto - a good database design supports good code design.

you can build the category and item tables from your existing tables by using a dynamically built INSERT ... SELECT query and some code to get and loop over all the table names.

Why can't code be adjusted to a database instead of the database being adjusted to the code? Seems to be a lot more work that way. Or is it less work to change the database? I'm going to struggle with changing the database really hard because I don't see how to create it like this, is this the kind of layout for the database you mean?:

• Categories
  • Rifles
    • Rifle 1
      • ammo
        • Rifle 1 ammo type 1
        • Rifle 1 ammo type 2
        • rilfe 1 ammo type 3
      • scopes
        • r1 scope posibility 1
        • r1 scope possibility 2
        • 3
        • 4
        • 5
        • 6
        • 7
        • 6
      • Species allows
        • 1
        • 2
        • 3
        • 4Rifle 2
    • Rifle 2
      • ammo
        • Rifle 2 ammo type 1
        • Rifle 2 ammo type 2
        • rilfe 2 ammo type 3
      • scopes
        • r2 scope posibility 1
        • r2 scope possibility 2
        • 3
        • 4
        • 5
        • 6
        • 7
        • 6
      • Species allows
        • 1
        • 2
        • 3
        • 4
  • Bows
    • bow 1
      • ammo
        • 1
        • 2
        • 3
      • scope/sight
        • 1
        • 2
        • 3
        • 4
      • animals allows to shoot
        • 1
        • 2
        • 3
        • 4
    • Bow 2
      • ammo
        • 1
        • 2
        • 3
      • scope/sight
        • 1
        • 2
        • 3
        • 4
      • animals allows to shoot
        • 1
        • 2
        • 3
        • 4

The way I have the database already is pretty much set up that way. It's just categories. I excluded making a "categories" main category and putting everything in it because this part of the project only has the categories. 

Why can't the code be adjusted without everything being in a main category?

I'd be interested to learn how to make sub-categories in a database like that. Please share how to do that! That would be cool, and useful. I don't agree it needs to be structured like that for this project but others, I do agree. 

Edited June 30, 2022 by javanoob

[javanoob]

55 minutes ago, javanoob said:

Why can't code be adjusted to a database instead of the database being adjusted to the code?

I have to repeat that question because holy boat load of re-doing a boat load of work I already did collecting and organizing the data. That process took a few years to do. The database is already categories. Doing it the way you suggest, mac_gyver, adjusting the database to the script, is a sh*t ton of work that will take me several days or weeks to do when it seems a few-line script or adjusting the script you posted can be created in a few minutes.
Ouch to all that work. Why can't it be done the way I already have the database?