Barand

What are you trying to achieve

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)

Use your browser menu. In Firefox (for example) there is a "More tools" option which gives

Just create a counter variable and increment it in the loop $total_systems = count($systems); $total_up = 0; echo "<table border='1' class='table'>"; // foreach loop to ping IP and check if alive or dead & dispaly result foreach ($systems as $ip) { unset($result); $successValue = "DOWN"; exec("ping -n 1 $ip[ip]", $result); foreach($result as $line) { if (strpos($line,$good) == TRUE){ $successValue = "UP"; ++$total_up; // increment up count } } echo "<tbody> <tr> <td>IP Address: {$ip['ip']}</td> <td>Unit Name: {$ip['name']}</td> </tr> <tr> <td>Status is: $successValue</td> <td>" . ($successValue == "UP" ? "<img src='/Images/GTick.jpg'>" : "<img src='/Images/RTick.jpg'>") . "</td> </tr> </tbody> "; } $total_down = $total_systems - $total_up; echo "<tr><th>Systems UP</th> // output totals <td>$total_up</td> </tr> <tr><th>Systems DOWN</th> <td>$total_down</td> </tr> </table> ";

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

Thsi topic has a solution so it's time to close it before it drags on aimlessly like your previous one. If you think we will let you open a topic that will continue in perpetuity while you learn PHP and associated software, think again.

In the early days of computing the interface with the computer was through punched cards and paper tape. Output was via line printers and teletype machines. All these devices had a character set limited to a few punctuation symbols, numbers and uppercase letters. Hence in the older programming languages, such as as COBOL, FORTRAN, BCPL, the convention was to use upper case (as these had to be input via devices only only having upper case charsets). They didn't use lowercase because they couldn't - we can. Since the late seventies/eighties languages like C, C++, Pascal, Java came to the fore, and with terminal input and improved print output, lowercase became the convention. Lowercase gives improved readability as the letter forms are more distinguishable than upper case. If you have a penchant for upper case, treat yourself to a Cobol compiler and go fully retro.

Te original html markup was inconsistent. I cleaned up the output too, but slightly differently. I couldn't see the point of separate tables (you can more easily guarantee consistency with a single one) echo "<table border='1' class='table'>"; // foreach loop to ping IP and check if alive or dead & dispaly result foreach ($systems as $ip) { unset($result); $successValue = "DOWN"; exec("ping -n 1 $ip[ip]", $result); foreach($result as $line) { if (strpos($line,$good) == TRUE){ $successValue = "UP"; } } echo "<tbody> <tr> <td>IP Address: {$ip['ip']}</td> <td>Unit Name: {$ip['name']}</td> </tr> <tr> <td>Status is: $successValue</td> <td>" . ($successValue == "UP" ? "<img src='/Images/GTick.jpg'>" : "<img src='/Images/RTick.jpg'>") . "</td> </tr> </tbody> "; } echo"</table>";

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?

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.

Several. Where is line 98? When you construct the array, where are those Yr/mth values coming from in the data (you have just hard-coded them) and throwing the same values into every month. You have a subarray with keys "forecast" and "actual" but you subsequently try to output them with keys "0" and "1" I would show you how to build the array but my software is useless at loading images of data into tables. (Images of code and data are as much use as chocolate teapots in this forum)

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?

How paranoid are you - how often do you intend checking the schema? What if the table disappears just after your latest check?

If a query attempts to access a table that no longer exists mysql> select * from my_test; Empty set (0.01 sec) mysql> drop table my_test; Query OK, 0 rows affected (0.18 sec) mysql> select * from my_test; ERROR 1146 (42S02): Table 'test.my_test' doesn't exist

Yes. PHP code in an .html file will not be processed. However, a .php file does not need to contain any PHP code; it can just contain HTML and will function as an .html file.

I read the version of that post before you edited it.

@ginerjm So what's this BS about your not using a class to connect with PDO? Have you read your own code?

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

Doesn't everybody who uses PDO? https://www.php.net/manual/en/pdo.construct.php

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)

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 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);

I was going to suggest combining the arrays $systems = array_combine($systems, $Sysname); then you could foreach ($systems as $ip => $name) { // ping } However, to that that you would need the same number of names as there are IPs. It would give Array ( [192.168.9.254] => Item1 [192.168.9.205] => Item2 [192.168.9.200] => Item3 [192.168.9.201] => Item4 [192.168.9.1] => Item5 [192.168.9.2] => Item6 [192.168.9.3] => Item7 [192.168.9.4] => Item8 [192.168.9.7] => Item9 [192.168.9.13] => Item10 )

Alternatively $str = 'FSOWPDHJEOSKJ3D2DF5R3FG51'; echo join('-', str_split($str, 5)); //--> FSOWP-DHJEO-SKJ3D-2DF5R-3FG51

Perhaps because there apparently is no code to show another div?