select option passing data to input fields: mySQL

mac_gyver · June 30, 2022

here's a big problem with the existing table per category. the ids are repeated, so, having an id doesn't uniquely tell you what the item is, nor does it let you find (sub)items related to the item. when someone selects an item, you cannot just store the item id and the quantity. you must also store the category_id, requiring more data storage and more code for every operation.

33 minutes ago, javanoob said:

adjusting the database

are you actually reading and getting the information in the replies? there's a post above stating how simple you can build the category and item tables from the table data you have shown us. the code to do so would look like (untested) -

<?php

// get or build a list of table information
$tables = [];
// table - is the existing table name
// name - is the item name, which unfortunately repeats part of the table name, making everything harder than necessary
// category - is the desired category name
$tables[] = ['table'=>'rifles','name'=>'rifleName','category'=>'rifle'];
$tables[] = ['table'=>'shotguns','name'=>'shotgunName','category'=>'shotgun'];
// add entries for the rest of the tables here...

// query to populate category table
$sql = "INSERT category (name) VALUE (?)";
$cat_stmt = $pdo->prepare($sql);

foreach($tables as $table)
{
	// insert the category data
	$cat_stmt->execute($table['category']);
	$cat_id = $pdo->lastInsertId();
	
	// query to populate item table
	$sql = "INSERT item (category_id, name, em, gm) SELECT $cat_id, `{$table['name']}`, em, gm FROM `{$table['table']}`";
	$stmt = $pdo->query($sql);
}

Edited June 30, 2022 by mac_gyver

javanoob · June 30, 2022

5 hours ago, mac_gyver said:

are you actually reading and getting the information in the replies?

Yes. Every character. Multiple times. I tend to write lengthy threads. It's only fair I read every character other people write and share.

I don't understand what's you mean by "categories". My database already has categories. That's all it is is categories. I based the design of my database off of phpbb3's newest forum package and WordPress's database. The only difference in those and mine is mine doesn't have a prefix to each table. Please try to explain what you mean. A roadmap image, a screenshot of one like you mean?

I shared my database file. It can easily be downloaded and edited to show me what you mean. Change one of the sheets to show me what you mean and I'll do the rest?

6 hours ago, mac_gyver said:

you cannot just store the item id and the quantity. you must also store the category_id, requiring more data storage and more code for every operation.

Did you actually look at my database? There's more on it than just an id and a quantity. I believe you it's different. I don't have a database with info on it besides the one I made to look at one, that has info on it. Share how it's different please. I don't see a difference between mine and any other I have access to to look at.

5 hours ago, mac_gyver said:

<?php

// get or build a list of table information
$tables = [];
// table - is the existing table name
// name - is the item name, which unfortunately repeats part of the table name, making everything harder than necessary
// category - is the desired category name
$tables[] = ['table'=>'rifles','name'=>'rifleName','category'=>'rifle'];
$tables[] = ['table'=>'shotguns','name'=>'shotgunName','category'=>'shotgun'];
// add entries for the rest of the tables here...

// query to populate category table
$sql = "INSERT category (name) VALUE (?)";
$cat_stmt = $pdo->prepare($sql);

foreach($tables as $table)
{
	// insert the category data
	$cat_stmt->execute($table['category']);
	$cat_id = $pdo->lastInsertId();
	
	// query to populate item table
	$sql = "INSERT item (category_id, name, em, gm) SELECT $cat_id, `{$table['name']}`, em, gm FROM `{$table['table']}`";
	$stmt = $pdo->query($sql);
}

I don't have a clue what all that is or what it's doing.

I don't see much difference in yours, mac_gyver:

$(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)
    })
})

and the one Barand posted:

    $().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)
      })
    })

I haven't tried running the mac_gyver script in my project's index.php file. I'll sigh and swat at myself for you if it just works.

javanoob · June 30, 2022

I can't get the SQL/PHP code to run. I'm not sure how to use that php/sql code. I doubt it's the code, it's how I'm trying to use it.

    Unexpected character. (near "?" at position 1)
    Unexpected character. (near "$" at position 54)
    Unexpected character. (near "[" at position 64)
    Unexpected character. (near "]" at position 65)
    Unexpected beginning of statement. (near "?" at position 1)
    Unexpected beginning of statement. (near "php" at position 2)
    Unrecognized statement type. (near "get" at position 12)

Is this all I have to do / Is this what you mean?:

And the script mac_gyver posted will work?:

$(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)    
  }) 
})

I'll attempt that on a new database.

javanoob · July 1, 2022

Result:

Fatal error: Uncaught PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'ehr_bt.category' 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, ...') #1 {main} thrown in C:\Program Files (x86)\EasyPHP-Devserver-17\eds-www\ehr_tracker\try13\mac_gyver1.php on line 26

I got Barand's suggestion to work. It just worked. I'll stick with using that.

Thank you so much for the help!

Barand · July 1, 2022

It's unfortunate, for someone who wants to minimize their code, that you have used a data model which considerably increases your code.

+---------------------------------------------------------------+             +--------------------------------------------------+
|                                                               |             |                                                  |
|                         YOUR DATA                             |             |                      MY DATA                     |
|                                                               |             |                                                  |
+---------------------------------------------------------------+             +--------------------------------------------------+

rifles                                                                        item
+----+------------------+------+------+                                       +----+---------------------+------+------+--------+
| id | rifleName        | em   | gm   |                                       | id | itemname            | em   | gm   | cat_id |
+----+------------------+------+------+                                       +----+---------------------+------+------+--------+
|  1 | .308 Bolt action |  225 | 1350 |                                       |  1 | .308 Bolt action    |  225 | 1350 |      1 |
|  2 | 7mm magnum       |  300 | 1575 |                                       |  2 | 7mm magnum          |  300 | 1575 |      1 |
|  3 | .243 LeverAction |  215 | 8725 |                                       |  3 | .243 LeverAction    |  215 | 8725 |      1 |
+----+------------------+------+------+                                       |  4 | 6ft Longbow         |  135 | 1120 |      2 |
bows                                                                          |  5 | 5ft Flatbow         |  235 | 1345 |      2 |
+----+-------------+------+------+                                            |  6 | Recurve             |  215 | 2525 |      2 |
| id | bowName     | em   | gm   |                                            |  7 | Purdy 12 bore       |  135 | 1120 |      3 |
+----+-------------+------+------+                                            |  8 | Beretta 12 bore     |  235 | 1345 |      3 |
|  1 | 6ft Longbow |  135 | 1120 |                                            |  9 | Small bore rook gun |  215 | 2525 |      3 |
|  2 | 5ft Flatbow |  235 | 1345 |                                            | 10 | Glock 9mm           |  125 | 1050 |      4 |
|  3 | Recurve     |  215 | 2525 |                                            | 11 | Colt .38            |  200 | 1175 |      4 |
+----+-------------+------+------+                                            | 12 | Derringer .22       |  115 | 3125 |      4 |
shotguns                                                                      +----+---------------------+------+------+--------+
+----+---------------------+------+------+
| id | shotgunName         | em   | gm   |                                    category
+----+---------------------+------+------+                                    +--------+----------+
|  1 | Purdy 12 bore       |  135 | 1120 |                                    | cat_id | cat_name |
|  2 | Beretta 12 bore     |  235 | 1345 |                                    +--------+----------+
|  3 | Small bore rook gun |  215 | 2525 |                                    |      1 | Rifles   |
+----+---------------------+------+------+                                    |      2 | Bows     |
pistols                                                                       |      3 | Shotguns |
+----+---------------+------+------+                                          |      4 | Pistols  |
| id | pistolName    | em   | gm   |                                          +--------+----------+
+----+---------------+------+------+
|  1 | Glock 9mm     |  125 | 1050 |
|  2 | Colt .38      |  200 | 1175 |
|  3 | Derringer .22 |  115 | 3125 |
+----+---------------+------+------+


+---------------------------------------------------------------+             +--------------------------------------------------+
|                                                               |             |                                                  |
|                         YOUR CODE                             |             |                      MY CODE                     |
|                                                               |             |                                                  |
+---------------------------------------------------------------+             +--------------------------------------------------+

        A separate block of code for each category                            Single block of code for all categories
                                                                              (For 20 categories that's a 95% reduction in coding time)
        
        If you add 4 more categories you have to write                        If I add more categories the code remains unchanged
        4 more code blocks

My code's output

image.png.e69859d8f6d0b66bc0bc58ff95d7abf6.png

My code for you to try

<?php
require 'db_inc.php';             // contains db credentials and connection function code
$pdo = pdoConnect('javanoob');    // connect setting javanoob as default db
                                  
             // YOU WILL NEED YOUR OWN PDO CONNECT CODE
                                  
                                  
################################################################################
##                                                                            ##
##  Handle AJAX request when + button is clicked                              ##
##                                                                            ##
################################################################################

if (isset($_GET['ajax'])) {
    if ($_GET['ajax'] == 'newrow')  {
        exit( tableItemRow($pdo, $_GET['catid']) );
    }
}

################################################################################
##                                                                            ##
##  Query db item table to get all the categrories for the output table       ##
##                                                                            ##
################################################################################

$res = $pdo->query("SELECT id
                         , itemname
                         , em
                         , gm
                         , cat_id
                         , cat_name
                    FROM item i
                         JOIN
                         category c USING (cat_id)
                    WHERE cat_id IN (1,2,3,4)
                    ORDER BY cat_id, itemname          
                   ");
$data = [];
foreach ($res as $r) {
    if (!isset($data[$r['cat_id']])) {
        $data[$r['cat_id']] = [ 'catname' => $r['cat_name'], 'items' => [] ]; 
    }
    $data[$r['cat_id']]['items'][] = array_slice($r, 0, 4);
}


################################################################################
##                                                                            ##
##  Loop through the data array to build the output table                     ##
##                                                                            ##
################################################################################

$tdata = '';
foreach ($data as $cid => $cdata) {
    $tdata .= "<tbody data-cat='$cid'>
                  <tr>
                    <td class='cat-title' colspan='4'>
                        {$cdata['catname']}
                        <div class='addmore' data-cat='$cid'><i class='fa fa-plus'></i></div>
                    </td>
                  </tr>
                  <tr>      
                    <td><select class='item-menu' name='item[]' onchange='item_menu_changed(this)'>
                          <option value='' data-em='0' data-gm='0'>Choose from {$cdata['catname']}</option>
              ";
    foreach ($cdata['items'] as $row) {
        $tdata .= "<option value={$row['id']} data-em='{$row['em']}' data-gm='{$row['gm']}'>" . htmlspecialchars( $row["itemname"] ) . "</option>";
   }
   $tdata .= "</select></td>
                <td class='em ca'>0</td>     
                <td class='gm ca'>0</td>     
                </tr>
            </tbody>";           
}


/*******************************************************************************
* generate html for additional output row
* 
* @param PDO $db
* @param int $cat_id
*/
function tableItemRow(PDO $db, $cat_id)
{
    $res = $db->prepare("SELECT id
                             , itemname
                             , em
                             , gm
                             , cat_name
                        FROM item i
                             JOIN
                             category c USING (cat_id)
                        WHERE cat_id = ?
                        ORDER BY itemname          
                       ");
    $res->execute([$cat_id]);
    $opts = '';
    $data = $res->fetchAll();
    foreach ($data as $row) {
        $opts .= "<option value={$row['id']} data-em='{$row['em']}' data-gm='{$row['gm']}'>" . htmlspecialchars( $row["itemname"] ) . "</option>";
    }
    $rowdata = "<tr>      
                    <td><select class='item-menu' name='item[]' onchange='item_menu_changed(this)'>
                    <option value='' data-em='0' data-gm='0'>Choose from {$data[0]['cat_name']}</option>"
              . $opts
              . "</select></td>
                <td class='em ca'>0</td>     
                <td class='gm ca'>0</td>     
                </tr>";
    return $rowdata; 
}

?>
<!--#ffa500-->
<!DOCTYPE html>
<html lang='en'>
<head>
<title>sample</title>
<meta http-equiv='Content-Type' content='text/html; charset=utf-8'>
<link href="https://stackpath.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet">
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script type='text/javascript'>
    $().ready( function() {
        
        $(".addmore").click( function() {
            let cid = $(this).data("cat")
            $.get (
                "",
                {"ajax":"newrow", "catid":cid},
                function(resp) {
                    $("tbody[data-cat="+cid+"]").append(resp)
                },
                "TEXT"
            )
        })
    })
    
    function item_menu_changed(menu)
    {
        //
        // update values in current row
        //
        let therow = $(menu).parent().parent()
        let em = parseInt($(menu).find("option:selected").data("em"))
        let gm = parseInt($(menu).find("option:selected").data("gm"))
        $(therow).find(".em").html(em)
        $(therow).find(".gm").html(gm)
        //
        // calculate totals
        //
        let totem = 0
        let totgm = 0
        
        $(".em").each(function(k,v) {
            totem += parseInt($(v).html())
        })
        $(".gm").each(function(k,v) {
            totgm += parseInt($(v).html())
        })
        $("#totem").html(totem)
        $("#totgm").html(totgm)
        
    }
</script>
<style type='text/css'>
    body {
        font-family: calibri, sans-serif;
    }
    select {
        background-color: black;
        color: white;
        font-size: 14pt;
        width: 100%;
        padding: 4px;
        border: none;
    }
    table {
        background-color: black;
        color: white;
        border-collapse: collapse;
        border-color: #ffa500;
        margin: 50px auto;
    }
    th {
        font-size: 20pt;
        color: #ffa500;
    }
    td {
        font-size: 14pt;
    }
    .cat-title {
        background: linear-gradient(to right top, #ffa500, black);
        font-size: 20pt;
        font-weight: 600;
        color: black;
        padding: 4px 2px 4px 16px;
        width: 600px;
    }
    .ca {
        text-align: center;
    }
    .addmore {
        width: 20px;
        height: 20px;
        display: inline-block;
        padding: 2px;
        text-align: center;
        font-size: 14pt;
        background: linear-gradient(to right top, #666, #FFF, #666);
        color: black;
        border: 1px solid #ffa500;
        float: right;
    }
</style>
</head>
<body>
<table border='1'>
    <tr><th>Items</th><th>em</th><th>gm</th><th>&plus;</th></tr>

    <?= $tdata ?>

    <tbody>
        <tr style='border-top: 8px solid #ffa500;'>
            <th>Totals</th>
            <td class='ca' id='totem'>0</td>     
            <td class='ca' id='totgm'>0</td>
            <td>&nbsp;</td>     
        </tr>
    </tbody>
</table>
</body>
</html>

and the SQL code to create the test data tables

--
-- Table structure for table `category`
--
DROP TABLE IF EXISTS `category`;
CREATE TABLE `category` (
  `cat_id` int(11) NOT NULL AUTO_INCREMENT,
  `cat_name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`cat_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
INSERT INTO `category` VALUES (1,'Rifles'),(2,'Bows'),(3,'Shotguns'),(4,'Pistols');

--
-- Table structure for table `item`
--
DROP TABLE IF EXISTS `item`;
CREATE TABLE `item` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `itemname` varchar(50) DEFAULT NULL,
  `em` int(11) DEFAULT NULL,
  `gm` int(11) DEFAULT NULL,
  `cat_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_item_cat_id` (`cat_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;
INSERT INTO `item` VALUES (1,'.308 Bolt action',225,1350,1),(2,'7mm magnum',300,1575,1),(3,'.243 LeverAction',215,8725,1),(4,'6ft Longbow',135,1120,2),(5,'5ft Flatbow',235,1345,2),(6,'Recurve',215,2525,2),(7,'Purdy 12 bore',135,1120,3),(8,'Beretta 12 bore',235,1345,3),(9,'Small bore rook gun',215,2525,3),(10,'Glock 9mm',125,1050,4),(11,'Colt .38',200,1175,4),(12,'Derringer .22',115,3125,4);

javanoob · July 2, 2022

10 hours ago, Barand said:

It's unfortunate, for someone who wants to minimize their code, that you have used a data model which considerably increases your code.

Yes. It is. Thank you! I tried so hard to get mac_gyver's suggestion to work, I'm sure it does work, but I wasn't able to get it working with a database no matter what I tried. I don't understand what the categories thing that keeps being mentioned is. I very much appreciate all the help and input, from everyone that attempts helping!

To shorten my code I've resorted to making separate files out of each section:

table_header.php

<html>
<head></head>

<body>
<table><tr><td>
<table border="1" border-color="orange" id="items_table"><tr>
<thead>
<tr><th colspan="3" class="category_top">Items</th></tr>
<th>Categories</th> <th> em</th><th>gm</th><th style="border:none;"></th>
</thead>

rifles.php

<!-- Rifles -->
<div>
<tr><td colspan="3" class="categoryth"><span class="text">Rifles</span></td><td><button type="button">+</button></td></tr>
</tr><tr>
<td><select id="rmenu" name="selection">
   <?php $data = $pdo->query("SELECT id, rifleName, em, gm FROM rifles ORDER BY id");
        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 class="cal_em" type="text" id="rem" value="0" disabled size="5"/></td>
<td><!-- Rifle GM--><input type+"text" id="rgm" 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() {
        $("#rmenu").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>
<!-- End Rifles -->
</div>

shotguns.php

<div>
<tr><td colspan="3" class="categoryth">Shotguns</td><td><button type="button">+</button></td></tr>
</tr>
<tr><td>
<select id="smenu" name="selection">
  <?php 
    $shotguns = $pdo->query("SELECT id, shotgunName, em, gm FROM shotguns ORDER BY id");
    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  class="cal_em" 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>
</tr>

and so on.....I'm using includes to put it all together (example....this isn't the actual file it was one I grabbed quick to example):

index.php

<?php include ('secFol/c.php'); ?>
<html>
<head>
<link rel="stylesheet" href="style/page.css"></link> 
<link rel="stylesheet" href="style/items.css"></link> 
</head>
<body>
<table border="0" width="100%">
<tr><td><?php include ('pages/rifles.php');?></td>
<tr><td><?php include ('pages/bows.php');?></td>
<tr><td><?php include ('pages/shotguns.php');?></td>
<tr><td><?php include ('pages/optics.php');?></td></tr>
</table>
</body>
</html>

I understand and I'm thinking to myself doing it this way is only making it more difficult but my OCD gets upset if my code isn't nice, neat, organized, and as few lines as possible. I get bothered by having to scroll through a 90 mile long file of code that's the same thing (slightly different) over and over again.

10 hours ago, Barand said:

My code's output

Awesome!! You got the totals working! Thank you! That was my next task I was probably going to have a hard time figuring out that I'll be asking about.
The plus buttons I intend to duplicate each div so users can add an additional item of that type, because players often do that - buy more than one thing of each item type. That's another thing I'll be figuring out. I have a script that duplicates the div but...getting the totals to work with that, I assume, is going to be a difficult task for me to figure out on my own.

I'll copy what you did and use it! Thank you so very much!

And now I finally see what you and mac_gyver mean:

 item
 +----+---------------------+------+------+--------+
 | id | itemname            | em   | gm   | cat_id |
 +----+---------------------+------+------+--------+
 |  1 | .308 Bolt action    |  225 | 1350 |      1 |
 |  2 | 7mm magnum          |  300 | 1575 |      1 |
 |  3 | .243 LeverAction    |  215 | 8725 |      1 |
 |  4 | 6ft Longbow         |  135 | 1120 |      2 |
 |  5 | 5ft Flatbow         |  235 | 1345 |      2 |
 |  6 | Recurve             |  215 | 2525 |      2 |
 |  7 | Purdy 12 bore       |  135 | 1120 |      3 |
 |  8 | Beretta 12 bore     |  235 | 1345 |      3 |
 |  9 | Small bore rook gun |  215 | 2525 |      3 |
 | 10 | Glock 9mm           |  125 | 1050 |      4 |
 | 11 | Colt .38            |  200 | 1175 |      4 |
 | 12 | Derringer .22       |  115 | 3125 |      4 |
 +----+---------------------+------+------+--------+

 category
 +--------+----------+
 | cat_id | cat_name |
 +--------+----------+
 |      1 | Rifles   |
 |      2 | Bows     |
 |      3 | Shotguns |
 |      4 | Pistols  |
 +--------+----------+

I knew whatever you and mac meant was a better way of doing it. I wasn't translating what, exactly, you mean. This explains it!
I can and will do that. I don't mind doing any amount of work if it results in things working better, and less code. I'll fix my .ods file and make a new database structure so it's like that. Thanks!

Edited July 2, 2022 by javanoob

javanoob · July 2, 2022

I get this when I try to run Barand's code:

Notice: Undefined index: em in index.php on line 77

Notice: Undefined index: gm in index.php on line 77

The error above repeats a bunch of times. The table displays on the page under the errors and the dropdown menu's populate but, of course since it can't find em and gm it doesn't do it's thing to the input fields. The em and gm columns and fields are there in the database. It finds everything else, the database is connected, it finds 'itemname' but not other fields in that same table.

Line 77 is:

 $tdata .= "<option value={$row['id']} data-em='{$row['em']}' data-gm='{$row['gm']}'>" . htmlspecialchars( $row["itemname"] ) . "</option>";

The weird thing is if I press the add row button it adds a row and menu (it's slow to do it like it's struggling for some reason but works). The menu it adds populates the input fields correctly.

How in the world is a row it's adding working but the row that displays by it self doesn't????
I tried sorting it out but I'm swinging blind.

Barand · July 2, 2022

The code I posted is working quite happily for me with the data I also posted. The original line 77 is not the one you indicate therefore some changes have been made to my code (warranty voided )

Also that line occurs twice in the code

In the main body when building the table (line 67)
In the function that is called when the + button is clicked to add a new row. (line 100)

From the output image it would appear to be the first that failed as the ".308 bolt action" is added by the function call and appears to be working. To know why it is now failing I would need to see all the lines (from your new version) preceding the error line, particularly the query and processing of the query results. (I suspect the query has been changed)

javanoob · July 2, 2022

The line numbers are moved forward because I added my db credentials directly to the file.

https://nwdb.42web.io/barand_rocks/ (it's a free host, might have security certificate issues until it verifies.)

Here's a video showing what I did to run it.

Edited July 2, 2022 by javanoob

Barand · July 2, 2022

The line with your error (line 77) is at line 66 in that video. I also notice it isn't accumulating the totals anymore, so I'd still need to see your code from line 1 to line 77 plus the <script> section.

This is how it works for me - view sample

javanoob · July 3, 2022

Here's line 1 to the end of </script> :

<?php
require 'connect.php';             // contains db credentials and connection function code
$pdo = pdoConnect('barands_code');              // YOU WILL NEED YOUR OWN PDO CONNECT CODE
                                  
             // YOU WILL NEED YOUR OWN PDO CONNECT CODE
                                  
                                                                               
################################################################################
##                                                                            ##
##  Handle AJAX request when + button is clicked                              ##
##                                                                            ##
################################################################################

if (isset($_GET['ajax'])) {
    if ($_GET['ajax'] == 'newrow')  {
        exit( tableItemRow($pdo, $_GET['catid']) );
    }
}

################################################################################
##                                                                            ##
##  Query db item table to get all the categrories for the output table       ##
##                                                                            ##
################################################################################

$res = $pdo->query("SELECT id
                         , itemname
                         , em
                         , gm
                         , cat_id
                         , cat_name
                    FROM item i
                         JOIN
                         category c USING (cat_id)
                    WHERE cat_id IN (1,2,3,4)
                    ORDER BY cat_id, itemname          
                   ");
$data = [];
foreach ($res as $r) {
    if (!isset($data[$r['cat_id']])) {
        $data[$r['cat_id']] = [ 'catname' => $r['cat_name'], 'items' => [] ]; 
    }
    $data[$r['cat_id']]['items'][] = array_slice($r, 0, 4);
}


################################################################################
##                                                                            ##
##  Loop through the data array to build the output table                     ##
##                                                                            ##
################################################################################

$tdata = '';
foreach ($data as $cid => $cdata) {
    $tdata .= "<tbody data-cat='$cid'>
                  <tr>
                    <td class='cat-title' colspan='4'>
                        {$cdata['catname']}
                        <div class='addmore' data-cat='$cid'><i class='fa fa-plus'></i></div>
                    </td>
                  </tr>
                  <tr>      
                    <td><select class='item-menu' name='item[]' onchange='item_menu_changed(this)'>
                          <option value='' data-em='0' data-gm='0'>Choose from {$cdata['catname']}</option>
              ";
    foreach ($cdata['items'] as $row) {
        $tdata .= "<option value={$row['id']} data-em='{$row['em']}' data-gm='{$row['gm']}'>" . htmlspecialchars( $row["itemname"] ) . "</option>";
   }
   $tdata .= "</select></td>
                <td class='em ca'>0</td>     
                <td class='gm ca'>0</td>     
                </tr>
            </tbody>";           
}


/*******************************************************************************
* generate html for additional output row
* 
* @param PDO $db
* @param int $cat_id
*/
function tableItemRow(PDO $db, $cat_id)
{
    $res = $db->prepare("SELECT id
                             , itemname
                             , em
                             , gm
                             , cat_name
                        FROM item i
                             JOIN
                             category c USING (cat_id)
                        WHERE cat_id = ?
                        ORDER BY itemname          
                       ");
    $res->execute([$cat_id]);
    $opts = '';
    $data = $res->fetchAll();
    foreach ($data as $row) {
        $opts .= "<option value={$row['id']} data-em='{$row['em']}' data-gm='{$row['gm']}'>" . htmlspecialchars( $row["itemname"] ) . "</option>";
    }
    $rowdata = "<tr>      
                    <td><select class='item-menu' name='item[]' onchange='item_menu_changed(this)'>
                    <option value='' data-em='0' data-gm='0'>Choose from {$data[0]['cat_name']}</option>"
              . $opts
              . "</select></td>
                <td class='em ca'>0</td>     
                <td class='gm ca'>0</td>     
                </tr>";
    return $rowdata; 
}

?>
<!--#ffa500-->
<!DOCTYPE html>
<html lang='en'>
<head>
<title>sample</title>
<meta http-equiv='Content-Type' content='text/html; charset=utf-8'>
<link href="https://stackpath.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet">
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script type='text/javascript'>
    $().ready( function() {
        
        $(".addmore").click( function() {
            let cid = $(this).data("cat")
            $.get (
                "",
                {"ajax":"newrow", "catid":cid},
                function(resp) {
                    $("tbody[data-cat="+cid+"]").append(resp)
                },
                "TEXT"
            )
        })
    })
    
    function item_menu_changed(menu)
    {
        //
        // update values in current row
        //
        let therow = $(menu).parent().parent()
        let em = parseInt($(menu).find("option:selected").data("em"))
        let gm = parseInt($(menu).find("option:selected").data("gm"))
        $(therow).find(".em").html(em)
        $(therow).find(".gm").html(gm)
        //
        // calculate totals
        //
        let totem = 0
        let totgm = 0
        
        $(".em").each(function(k,v) {
            totem += parseInt($(v).html())
        })
        $(".gm").each(function(k,v) {
            totgm += parseInt($(v).html())
        })
        $("#totem").html(totem)
        $("#totgm").html(totgm)
        
    }
</script>
<style type='text/css'>

I did one of these

when I realized I should be editing line 2 instead of removing it.
I'm not positive what I should be entering as the $pdoConnect('code');
That may be all I have whoopsa-futzed now?

When I reload it with what I think it should be Opera GX and Chrome say:

Quote

This page isn’t working

nwdb.42web.io is currently unable to handle this request.

HTTP ERROR 500

With $pdo = pdoConnect('barands_code'); included FireFox loads a blank, white, page.

Local WAMP says:

Quote

Fatal error: Uncaught Error: Call to undefined function pdoConnect() in index.php:17
Stack trace: #0 {main} thrown in ndex.php on line 17

Line 17 is just a } that matches up with and open {.

Everything's all the same everywhere I'm trying to run it. This is the local WAMP's db the code I posted is from:

javanoob · July 3, 2022

8 hours ago, Barand said:

This is how it works for me - view sample

The only thing I can think of is I have the $pdoConnect('code'); wrong.

<?php
require 'connect.php';             
$pdo = pdoConnect('barands_code');

I didn't change anything else. 'barands_code' is the name of the database.

The error is on line 3 now. So...I have that wrong. What am I supposed to have $pdoConnect('HERE');?

Barand · July 3, 2022

8 hours ago, javanoob said:

when I realized I should be editing line 2 instead of removing it.
I'm not positive what I should be entering as the $pdoConnect('code');

My 'db_inc.php' contains connection credentials and defines the pdoConnect() function. The next line calls pdoConnect() to connect and store the conection in $pdo.

If your "connect.php" creates the connection and stores it in $pdo then all you need is

require 'connect.php';

You don't need the $pdo = pdoConnect('barands_code');

Apart from that, the code looks OK. I am still curious why your error message reports line 77 when that line is 67. Are you sure your running the right script?

javanoob · July 3, 2022

How do I define pdoConnect()? I didn't do that. This is my connect.php file (local WAMP):

<?php
$servername = "localhost";
$dbport = '3306';
$dbuser = 'root';
$ddbname = 'barands_code';
$bpass = '';

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();
    }    
?>

That's all of it. There's nothing else in it.

2 hours ago, Barand said:

I am still curious why your error message reports line 77 when that line is 67

I added these 10 lines to the file. That's what pushed 67 to 77.

$servername = "localhost";
$dbport = '3306';
$dbuser = 'root';
$ddbname = 'barands_code';
$bpass = '';

catch(PDOException $e)
    {
    echo "Connection failed: " . $e->getMessage();
    }

The line 77 error went away when I started using the connect.php file.
The only line being reported as erroring now is line 3 which is the $pdoConnect('db_name'); line.

2 hours ago, Barand said:

Are you sure your running the right script?

As sure as I can be. Every time I tried doing this differently I made a new folder. I compared the code I have to the code you posted here in this thread side by side line for line.

I'll run them in WinMerge if my connect.php isn't missing something it needs.

Edited July 3, 2022 by javanoob

Barand · July 3, 2022

1 hour ago, javanoob said:

I added these 10 lines to the file. That's what pushed 67 to 77.

That's what confused me - your video of exactly what you did didn't show that bit. They aren't necessary as that is what your included code does.

You don't need pdoConnect(). Your "connect.php" defines the $pdo connection varaiable.

My db_inc.php contains

const HOST     = 'localhost';                                                            
const USERNAME = '????';                                                                 
const PASSWORD = '????';                                                                 
const DATABASE = 'test');               // default db                                    
                                                                                         
function pdoConnect($dbname=DATABASE)                                                    
{                                                                                        
    $db = new PDO("mysql:host=".HOST.";dbname=$dbname;charset=utf8",USERNAME,PASSWORD);  
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);                        
    $db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);                   
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);                                
    return $db;                                                                          
}                                                                                        
                                                                                         
function myConnect($database=DATABASE)                                                   
{                                                                                        
    mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);                             
    $db = mysqli_connect(HOST,USERNAME,PASSWORD,$database);                              
    $db->set_charset('utf8');                                                            
    return $db;                                                                          
}

I use the pdoConnect() and myConnect() functions to create a connection as I have dozens of databases and, depending on who I am helping, I may need a PDO or a mysqli connection. This lets me use the same include file yet allows flexibility in my connections.

javanoob · July 4, 2022

That's what I was missing. It works now. Thank you!

Is it possible to design the layout like this with how the code is now?:

I'd like to fill the page if that's possible with how the code is now. I'll try making separate php files and use <? include 'file_name.php' ?> to do that.
If I can't be done with the code as it is that's fine. It works. I'm so very extremely grateful for the help! Thank you so very much, Barand!!!

It does need to calculate totals and balance from user input.
That and a [ - ] button to remove rows that are added are my next attempt at trying to write javaScript.

Barand · July 4, 2022

4 hours ago, javanoob said:

Is it possible to design the layout like this with how the code is now?:

Not with that code, but you can by employing the same techniques. You have now changed the structure of your data so the code structure also needs to change.

Your whole existing data model needs revising. Up to now we have had a only keyhole view of your data so this has not been possible here. If you get the data model correct, the subsequent coding becomes a lot easier. Your priority is to get the data correct, then you get down to coding.

Before, your categories were

Rifles
Pistols
Bows
Hats
Jackets
etc

Now those have become subcategories of higher level categories

Weapons
Ammunition
Clothing
Accessories
etc

Other considerations are...

There is an obvious relationship between weapon and ammunition - that needs to be reflected in the data model, as do any other relationships.
Attributes of items in the clothing categories may be different from, say, weapons (What are you storing other than itemname, em and gm?)

How will your model handle those?

javanoob · July 5, 2022

22 hours ago, Barand said:

What are you storing other than itemname, em and gm?

· Scopes for the weapons.
- Each weapon doesn't mount the same scope. Some weapons have 4 available scopes they can mount, some have 2, some have 8 and they're not all the same scope sets for every weapon. There are 42 different optics in the game including binoculars, spotting scopes, and range finding binoculars.
- Vice versa for the scopes them selves. Same thing though.

This weapon can mount 5 different scopes. This one can only mount 2 different scopes that are available. Click on one of the scopes on either of those pages then click on this scope to see the weapons it can be mounted to. It's not all perfectly the same.

It would be a nice to have a feature that a weapon populated a selection menu of the scopes that fit.

22 hours ago, Barand said:

How will your model handle those?

I was considering giving every item that has an accessory an accessory id (acs_id) and making a different table for that:

I'm working on putting that data together. I'm thinking if I make a table of sets with scope_set_id's and a table of individual scopes with a scope_id's to handle that it should work out. Suggestions welcome and appreciated.

Edited July 5, 2022 by javanoob

Barand · July 5, 2022

Rifles and scopes are a typical many-to-many relationship where a rifle can have many types of scope and a scope can be used on many types of rifle.

The way to handle these is with an intermediate table associating rifle ids with compatible scope ids

item                                                               rifle_scope
 +----+---------------------+------+------+--------+                +----------------+---------------+
 | id | itemname            | em   | gm   | cat_id |                | rifle_id       | scope_id      |
 +----+---------------------+------+------+--------+                +----------------+---------------+
 |  1 | .308 Bolt action    |  225 | 1350 |      1 |                |       1        |     104       |
 |  2 | 7mm magnum          |  300 | 1575 |      1 |                |       1        |     106       |
 |  3 | .243 LeverAction    |  215 | 8725 |      1 |                |       1        |     107       |
 .                                                 .                |       2        |     105       |
 .                                                 .                |       3        |     106       |
 |104 | Scope A             |  135 | 1120 |      5 |                |       3        |     107       |
 |105 | Scope B             |  235 | 1345 |      5 |                +----------------+---------------+
 |106 | Scope C             |  215 | 2525 |      5 |                
 |107 | Scope D             |  135 | 1120 |      5 |                
 +----+---------------------+------+------+--------+

To get a menu list of scopes for rifle #1, say, you would

SELECT scp.id
     , scp.itemname
     , scp.em
     , scp.gm
FROM item rfl 
     JOIN
     rifle_scope rs ON rfl.id = rs.rifle_id
     JOIN
     item scp ON rs.scope_id = scp.id
WHERE rfl.id = 1

+-----+----------+------+------+
| id  | itemname | em   | gm   |
+-----+----------+------+------+
| 104 | Scope A  |  135 | 1120 |
| 106 | SCope C  |  215 | 2525 |
| 107 | Scope D  |  135 | 1120 |
+-----+----------+------+------+

Probably a similar setup would apply to ammunition

javanoob · July 5, 2022

Thanks! I was confident you'd know of a way to do that.
I think I found a script that'll populate the 'scope' menus based on what rifle's selected. I want to get the database completed with all the items in it before I give it a try.

javanoob · July 5, 2022

55 minutes ago, Barand said:

+----+---------------------+------+------+--------+                +----------------+---------------+
 | id | itemname            | em   | gm   | cat_id |                | rifle_id       | scope_id      |
 +----+---------------------+------+------+--------+                +----------------+---------------+
 |  1 | .308 Bolt action    |  225 | 1350 |      1 |                |       1        |     104       |
 |  2 | 7mm magnum          |  300 | 1575 |      1 |                |       1        |     106       |
 |  3 | .243 LeverAction    |  215 | 8725 |      1 |                |       1        |     107       |
 .                                                 .                |       2        |     105       |
 .                                                 .                |       3        |     106       |
 |104 | Scope A             |  135 | 1120 |      5 |                |       3        |     107       |
 |105 | Scope B             |  235 | 1345 |      5 |                +----------------+---------------+
 |106 | Scope C             |  215 | 2525 |      5 |                
 |107 | Scope D             |  135 | 1120 |      5 |                
 +----+---------------------+------+------+--------+

Doesn't that require indexes so it knows where to look? Or is that what the rfl.id = rs.rifle_id in the sql is doing?

59 minutes ago, Barand said:

Probably a similar setup would apply to ammunition

There's only a few different prices for ammo. That's what I intended the a_em and a_gm columns in the database and the number input like this is for under Ammo Qty on the front-end table.

I already have the columns filled in. It's not difficult to delete the whole column (it's excel/calc) if it's better to do as you suggest.

Barand · July 5, 2022

30 minutes ago, javanoob said:

Doesn't that require indexes so it knows where to look? Or is that what the rfl.id = rs.rifle_id in the sql is doing?

The query joins to the item table twice; with alias "rfl" and alias "scp"

The first join ON rfl.id = rs.rifle_id matches thos rifle_scope records where the rifle ids match.

The second join is from rifle_scope to scp table (ON rs.scope_id = scp.id) to find the scope items that match those found by the first join.

[edit]...

PS You need to analyse your data and nomalize it into a relarional structure. Look at the relationships between each of the entities.

The are formal steps to go through (Google normalize data) but there is rough-and-ready method in my sql tutorial (see my sig)

javanoob · July 6, 2022

Thanks! I'll give that a try. Learning from that SQL link is on my to do list.

Some items can only be bought with EM. For those items I have an X appearing in the gm field. The X messes up the totals calculation.
How can that be prevented?

let X = 0;?

Barand · July 6, 2022

Store gm = NULL in your db for those items. (set default to NULL and don't give them a value)

Display the gm value as ( $row['gm'] ?? 'X' )

(BTW, what do em and gm mean?)

Barand · July 7, 2022

Here is a fuller fix for those NULL gm values showing changes

image.png.b275aa68e2bb0d66722a92d5f203c8e4.png

Output

image.png.75f1446f77163e75c21bbaedc74b0ae0.png

Data

mysql> select * from item where cat_id=4;
+----+---------------+------+------+--------+
| id | itemname      | em   | gm   | cat_id |
+----+---------------+------+------+--------+
| 10 | Glock 9mm     |  125 | 1050 |      4 |
| 11 | Colt .38      |  200 | 1175 |      4 |
| 12 | Derringer .22 |  115 | NULL |      4 |          NULL gm value
+----+---------------+------+------+--------+

Sign In

select option passing data to input fields: mySQL

Recommended Posts

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

Popular Posts

Barand

dodgeitorelse3

ginerjm

Posted Images

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information