Barand

Your tables have too many keys. See my diagram above. A city only needs FK link to its parent state. A state has a FK link to its parent country so from city you know its state then from state you know its country. Example SELECT country_name , state_name , city_name FROM city ct JOIN state s ON ct.state_id = s.id JOIN country c ON s.country_id = c.id ORDER BY country_name, state_name, city_name;

Do you have a data structure to support that process? For example +-------------------+ | country | +-------------------+ | id |-----+ | country_name | | +-------------------+ +-------------------+ | | state | | +-------------------+ | | id |-----+ +----<| country_id | | | state_name | | +-------------------+ +-------------------+ | | city | | +-------------------+ | | id |-----+ +-----<| state_id | | | city_name | | +-------------------+ +-------------------+ | | area | | +-------------------+ | | id | +-----<| city _id | | area_name | +-------------------+

Hijacking someone else's topic aside for now, define "Not working".

First you are going to have to bear some criticism of your code DO NOT connect to the server every time you call a function. Connect once at the top of index.php an pass the connection in the function calls. Connections are slow and you will overburden your server with connections You don't need 3 function calls to get the status totals - one will suffice You don't execute() after query(), execute() is for prepared statements. Don't put a closing ?> at end of included php files Lecture over, here's how I would do it (skeleton) index.php <?php require 'SqlConn.php'; $SC = 0; $FC = 0; // get status counts and store counts in an array $statusCounts = JC($conn); // pass $conn to function // output status counts echo "<h2>Completed : {$statusCounts['Completed']}</h2>"; echo "<h2>In-Progress : {$statusCounts['In-Progress']}</h2>"; echo "<h2>On-Hold : {$statusCounts['ON_HOLD']}</h2>"; // check critical systems CriticalSystem( $SC, $FC ); // output system success counts countupdown($SC, $FC); ?> functions.php <?php function JC($conn) // pass $conn to function { $sql = "Select Status , Count(Status) as total from EXCELMACRO...Jobs$ group by Status "; $stmt = $conn->query($sql); $resultCompleted = $stmt->fetchAll(); return array_column($resultCompleted, 'total', 'Status'); // return array of totals bt status } function CriticalSystem( &$SC, &$FC ) // pass count variables by reference { //array for critical devices $systems = array( array('ip' => '192.168.9.254', 'name' => 'Tech Swtich'), array('ip' => '192.168.9.205', 'name' => 'Printer'), array('ip' => '192.168.9.200', 'name' => 'Sales Printer'), array('ip' => '192.168.9.201', 'name' => 'Admin Printer'), array('ip' => '192.168.9.1', 'name' => 'KVM'), array('ip' => '192.168.9.2', 'name' => 'Office Data 24 Port'), array('ip' => '192.168.9.3', 'name' => 'Office Data 48 Port'), array('ip' => '192.168.9.4', 'name' => 'Office Voice 48 Port'), array('ip' => '192.168.9.7', 'name' => 'Warehouse Switch'), array('ip' => '192.168.9.13', 'name' => 'Foundry Canteen Switch'), ); //Result to search for to give success result $good = "Received = 1"; //troubleshooting to see if being re-freshed //echo "<h1>Site Status ".date("h:i:s")."</h1>"; echo "<table border='0' 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'>" ."<span style='color:#000000'>". $SC++ ."</span>" : "<img src='/Images/RTick.jpg'>" . "<span style='color:#000000'>".$FC++ ."</span>"). "</td> </tr> </tbody> "; } echo"</table>"; $FC = count($systems) - $SC; } function countupdown($countup, $countdown) // pass counts to function { echo "<center>"; echo "<br></br>"; echo "<img src='/Images/GTick.jpg'>". " " . "Systems Online = ".$countup; echo "<br></br>"; echo "<img src='/Images/RTick.jpg'>". " ". "Systems Offline = ".$countdown; echo "</center>"; }

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

The two most likely scenarios are these A ) the functions are called independantly <?php CriticalSystem(); // call 1st function countupdown(); // call 2nd function function CriticalSystem() { // function definition } function countupdown() { // function definition } ?> B ) the second function is called from within the first function <?php CriticalSystem(); // call 1st function function CriticalSystem() { // function definition countupdown(); // call 2nd function } function countupdown() { // function definition } ?> Which of these applies will determine how the values are passed.

Why are you converting $countup and $countdown to arrays. (you can't echo an array) Is you criticalSystems() function being called several times for different sets of systems? Problem is, I am only seeing a couple of snippets of code with no context as to how and where they are called, which doesn't help me. Conventional wisdom says "Forget that the global keyword exists". (It can cause more problems than it solves.) Pass values as function arguments.

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