Barand

You still need the data for those items. However the data should be normalised so that instead of sevveral columns in the visit table they are moved to a separate table (pmvisit_item) with a separate row for each item. At present your data is similar to this (only some of the columns and 5 items shown for brevity) +-----+--------------+------------+------------+------------+------------+------------+------------+----------+-----------+ | id | first_name | last_name | item_1 | item_2 | item_3 | item_4 | item_5 | sign_x | sign_y | +-----+--------------+------------+------------+------------+------------+------------+------------+----------+-----------+ | 1 | Tom | Di Canari | Done | Screw loose| Done | Done |Screw loose | [1,2,3,4]| [[1,2,3,4]| | 2 | Laura | Norder | FUBAR | Done | Done | Done |Screw loose | [1,2,3,4]| [[1,2,3,4]| +-----+--------------+------------+------------+------------+------------+------------+------------+----------+-----------+ As shown in the relationship diagram in my earlier post, above this data should be stored as TABLE : pmvisit TABLE : pmvisit_item (1 row per visit/item) +-----+--------------+------------+----------+-----------+ +----------+-------+-------|-------------------------+ | id | first_name | last_name | sign_x | sign_y | | visit_id | item | pass | fail_comment | +-----+--------------+------------+----------+-----------+ +----------+-------+-------+-------------------------+ | 1 | Tom | Di Canari | [1,2,3,4]| [[1,2,3,4]| | 1 | 1 | 1 | | | 2 | Laura | Norder | [1,2,3,4]| [[1,2,3,4]| | 1 | 2 | 0 | Screw loose | +-----+--------------+------------+----------+-----------+ | 1 | 3 | 1 | | | 1 | 4 | 1 | | | 1 | 5 | 0 | Screw loose | | 2 | 1 | 0 | FUBAR | | 2 | 2 | 1 | | | 2 | 3 | 1 | | | 2 | 4 | 1 | | | 2 | 5 | 0 | Screw loose | +----------+-------+-------+-------------------------+ One way to achieve this is to use you current pmvisit table as an interface and have a script which runs regularly (daily?) to extract new data and reformat to new tables that you would use from then on for reporting, querying etc

Here's my 0.02 worth. Take out the itemXXX columns from the report. Add a clickable link to allow viewing of the items separately. Give those rows where one or more items contain a negative outcome/fault a different background color so you know which ones a worth clicking. Of course the way you have it, this means 22 separate checks. If done properly there is a single check... SELECT id , first_name , last_name , SUM(i.pass_fail=0) as `Defect Count` , ... , manager_sign_x , manager_sign_y FROM pmvisit v LEFT JOIN pmvisit_item i ON v.id = i.visit_id AND pass_fail = 0 GROUP BY v.id +-----------------------+ +-----------------+ | pmvisit | | item | +-----------------------+ +-----------------+ | id |-----+ +-------| item_no | | first_name | | | | description | | last_name | | +-----------------------+ | +-----------------+ | tech_phonenum | | | pmvisit_item | | | store_name | | +-----------------------+ | | store_num | +-----<| visit_id | | | store_address | | item_no |>-----+ | manager_name | | pass_fail (1 or 0) | | robot_num | | comment (if fail) | | checkin_time | +-----------------------+ | tech_general_comment | | checkout_time | | manager_sign_x | | manager_sign_y | +-----------------------+ Item table would contain: +----------+--------------------------------------------------------+ | item_no | description | +----------+--------------------------------------------------------+ | 1 | Bumper Tie Rod Mounting Screws | | 2 | Inspect/Clean Caster and Drv Wheel | | 3 | Inspect Bumper Damage | | 4 | Remove Motor Cble Ties / inspect frayed cables | | 5 | Inspect Screws/Parts in tray area | | 6 | Vaccum Cooling Air Inlets for PC | | 7 | Visual Cable Inspection / Connections | | 8 | 12V Power Port Inspection | | 9 | Lidar Drv Belt Inspect | | 10 | Backdoor Alignment Check | | 11 | Charging Connector Contact Condition | | 12 | Trim lwr right cover under lidar | | 13 | Tighten GO Button nut | | 14 | Align WiFi Antenna | | 15 | Center Camera Alignment Check | | 16 | Lidar Rotation Check | | 17 | Googly Eyes Check | | 18 | Cleaned Non-Optical Cover Surfaces | | 19 | Clean all Optical Imaging Components | | 20 | Inspect Dock Connector / Height / Contact Condition | | 21 | Dock and Fudicals Cleaned | | 22 | Dock and Park Tape | +----------+--------------------------------------------------------+ Dates should always be stored in yyyy-mm-dd format. Other formats cannot be successfully compared, so cannot be sorted, cannot be selected by date range and do not work with the dozens of date/time functions without prior conversion.

Nothing screams "bad design" more than a set of columns in a table like these... item_one, item_two, item_three, item_four, item_five, item_six, item_seven, item_eight, item_nine, item_ten, item_eleven, item_twlve, item_thirteen, item_fourteen, item_fifteen, item_sixteen, item_seventenn, item_eighteen, item_nineteen, item_twenty, item_twentyone, item_twentytwo, except, maybe, datetimes that are stored in "mm-dd-yyyy - hh-mm" format. If you reduce the horizontal cell padding from 60px there may be room for the date on a single line. Send me a PM with your requirements and I'll look at it.

I have been thinking about since I realised it was a problem. It seems to me there are two approaches On input The input algorithm detects the breaks and inserts a "flag" to indicate the break EG (1,2,3,4,#,5 6 7,8) On output Consecutive points are very close together. The output function could look for consecutive points more than N pixels apart and assume there is a break. The first I can do nothing about, and may be more difficult (I don't know). The second I could do, but it may not be as accurate as the first option but is worth a try as it is (probably) the easier option. As I don't have means of generating the sign_x and sign_y data myself I would need you to provide me some test data (like that used for the example in your post).

Maybe not! Take the case of John Dott. His signature is When signing, his signature has three distinct paths draws the "J" then "Doll" Crosses the "t"s so his pen leaves the paper twice. At present the sigs are assumed to be one continuous path from start to end, which probably is rarely the case. The sign_x and sign_y data needs to contain something to indicate these breaks.

Add "AND plan = X" to the WHERE clause (where X is the current user's plan) and it becomes less of a report and more a list of what the user can access. Take it a step further and add "AND article = Y" and you know if a specific article is permitted.

Why all those calls? Haven't you heard of JOINS? One query gets all the stuff that the current version of a plan is entitled to today (query from your other thread) SELECT p.plan_name , v.version_no , DATE_FORMAT(v.valid_from, '%m/%d/%Y') as `From` , DATE_FORMAT(v.valid_until, '%m/%d/%Y') as `Until` , GROUP_CONCAT(f.title ORDER BY title SEPARATOR ', ') as features FROM plan p JOIN plan_version v USING (plan_id) JOIN entitlement e USING (plan_id, version_no) JOIN feature f USING (feature_id) WHERE CURDATE() BETWEEN v.valid_from AND v.valid_until GROUP BY plan_id, version_no;

Years ago I wrote a web page (using VB with SQLServer) which showed all bus stops within 200m of your location (this could be changed by a slider) with their scheduled departure times/destinations in the next hour. This was fine out in the 'burbs where there might only be a handful of stops inside the search area. In the city centre it was a different picture with hundreds of stops and thousands of journeys. If the search radius exceed 50m it was timing out after 30secs. This was at a time when I had started using PHP but my bosses were against it (cos it wasn't Micro$oft). I migrated the required database tables so that I had a MySQL version and rewrote the VB code in PHP. Absolutely no problem in the city centre until I wound up the search radius to 2 Km. That was the last time I used VB.

Right click on the " 22 minutes ago" Select "copy link"

Yes

You're welcome. Glad to have been of help.

71 years 4 weeks tomorrow My career summary

Why? The function is doing that for you.

A single query with a join is more efficient than separate queries.

I changed the function to work with or without the brackets in that last version I posted, so it doesn't matter either way. A pretty good match between those those two image versions.

Are those two signatures in your image produced by different methods?

You just need to pass the data from the sign_x and sign_y columns in your table to the drawImage() function. In prictice, how do you want to use the function. Will you be producing lists of names and signatures?, or querying the database for an individual signature?

They affect the way the data has to be processed.

It's a json array. Try this version of the "drawImage" function with your db table function drawImage($xcoords, $ycoords) { $xa = $xcoords[0]=='[' ? json_decode($xcoords, 1) : explode(',', $xcoords); // put x coords into an array $ya = $ycoords[0]=='[' ? json_decode($ycoords, 1) : explode(',', $ycoords); // put x coords into an array $w = max($xa)+1; // get the max coord values so we know the size $h = max($ya)+1; // define the path $path = "M $xa[0] $ya[0] "; // move to the first x,y position unset($xa[0], $ya[0]); // remove the the first items from the array foreach ($xa as $i => $x) { // loop through arrays $y = $ya[$i]; // pairing the ith x with the ith y $path .= "L $x $y "; // define line to x y } // create svg object $w x $h to display image $im = "<svg width='$w' height='$h'> <path d='$path' stroke='#000' stroke-width='2' fill='none'/> </svg>"; return $im; }

Relative time reference would work. EG See my earlier post X hrs Y mins ago

@requinix when I hover the cursor over the posted time of your latest post^ I see "02/18/2020 07:28 AM". Am I seeing that because all times are Zulu time or because it is my local time (GMT)? Do you see "02/17/2020 11:28 PM"? In other words, is the visible timestamp useless for referencing if the participants are in different timezones?

Yes, that one.

I have a wonderful rectangular device on my desk. When i press on the area marked "+", a "+" appears on the screen. Similarly, pressing "-" area gives a "-". Works with letters, the long narrow area at the bottom and numbers too. You should get one. Use a monospace font like courier. Use spaces, never tabs, and paste into a code area in the forum so you get a monospaced font. For query output you can use the mysql command line interface

Q1 - not necessarily the latest, you could have future plans on file. You want the one that is current. Q2 - I have already answered in previous posts. Only use blank dates if you want to over-complicate the logic in your queries Q3 - Don't know Q4 - Don't know Q5 - Yes, trigger

It doen't output anything but processing looks OK