Barand

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

I'll try to explain what the function is doing First, in the test data we are trying to achieve this and we are starting with two sets of numbers sign_x - "0,0,100,100,0,50,100,0,100" sign_y - "100,20,20,100,20,0,20,100,100" These numbers need to be put into (x, y) pairs. The first x with first y give (0, 100). The second x with the second y gives (0, 20), the third (100, 20) and so on. By far the easiest way to do this is with indexed arrays so we use explode to put the x and y values into two arrays $xa = Array $ya = Array ( ( [0] => 0 [0] => 100 [1] => 0 [1] => 20 [2] => 100 [2] => 20 [3] => 100 [3] => 100 [4] => 0 [4] => 20 [5] => 50 [5] => 0 [6] => 100 [6] => 20 [7] => 0 [7] => 100 [8] => 100 [8] => 100 ) ) As described inthe above figure, we first want to move to the start point (0, 100) then, after that, we want to draw lines to each successive point. Using SVG (Scalable Vector Graphics) the way to this is to create a path which has a definition of "M 0 100 L 0 20 L 100 20 L 100 100 L 0 20 L 50 0 L 100 20 L 0 100 L 100 100" where M x y means "move to x,y" and L x y means "line to x,y" The function line by line function drawImage($xcoords, $ycoords) { $xa = explode(',', $xcoords); // put x coords string into an array $ya = explode(',', $ycoords); // put x coords string 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 i'th x with the i'th 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='1' fill='none'/> </svg>"; return $im; }

When that combination is required to be unique anyway you may as well make it the primary key instead of an artificial one.

No, but every table should have a unique primary key, but not necessarily auto_incremented (for example the plan_version table above, where the primary key is the (plan_id, version_no) combination)

you posted [44, 44, 44, 45, 45, 45, 45, 45, 45, 43, 42, 41, 40, 39, 39, 39, 37, 37, 37, 37, 37, 37, 37, 39, 40, 43, 47, 47, 49, 51, 52, 52, 53, 54, 55, 57, 58, 59, 64, 68, 73, 80, 85, 89, 93, 94, 94, 95, 95, 95, 93, 91, 89, 87, 85, 83, 81, 79, 75, 75, 72, 69, 66, 63, 60, 57, 55, 52, 50, 47, 45, 43, 41, 41, 41, 123, 124, 124, 123, 122, 119, 117, 111, 105, 100, 94, 89, 85, 82, 78, 76, 75, 73, 73, 72, 72, 72, 72, 75, 77, 79, 81, 83, 83, 87, 91, 95, 101, 109, 118, 127, 137, 144, 150, 153, 155, 155, 155, 155, 155, 155, 154, 154, 153, 150, 147, 147, 145, 144, 144, 144, 144, 144, 149, 152, 155, 158, 163, 167, 170, 176, 184, 191, 197, 201, 203, 204, 205, 206, 206, 206, 205, 203, 203, 202, 202, 202, 202, 202, 202, 202, 204, 207, 210, 213, 219, 220, 222, 223, 224, 226, 228, 230, 234, 237, 242, 246, 252, 259, 269, 278, 287, 293, 301, 309, 316, 325, 333, 340, 345, 345, 343, 341, 341, 339, 335, 332, 329, 327, 327, 327, 327, 328, 328, 329, 329, 332, 332, 335, 336, 338, 339, 343, 343, 346, 346] [39, 45, 50, 58, 72, 86, 91, 97, 102, 106, 108, 111, 112, 114, 114, 112, 108, 102, 98, 92, 86, 81, 75, 69, 65, 60, 56, 55, 53, 50, 50, 48, 48, 48, 48, 48, 46, 46, 47, 48, 51, 53, 55, 58, 60, 61, 62, 66, 70, 76, 80, 84, 86, 89, 93, 94, 97, 99, 101, 102, 102, 102, 102, 102, 102, 102, 102, 102, 102, 102, 102, 102, 102, 102, 102, 72, 72, 71, 69, 68, 65, 64, 62, 61, 61, 61, 61, 61, 61, 62, 64, 64, 66, 68, 71, 76, 81, 86, 92, 94, 96, 100, 102, 102, 104, 104, 104, 104, 104, 101, 98, 94, 91, 86, 84, 80, 78, 76, 74, 73, 72, 71, 70, 69, 71, 75, 76, 82, 86, 92, 95, 98, 102, 105, 108, 108, 110, 111, 111, 111, 110, 106, 102, 98, 94, 91, 88, 84, 82, 79, 77, 74, 71, 70, 68, 68, 66, 66, 65, 64, 63, 62, 62, 59, 58, 57, 57, 56, 56, 56, 56, 58, 58, 60, 61, 63, 64, 66, 68, 68, 68, 68, 68, 65, 63, 59, 55, 51, 50, 47, 47, 48, 50, 51, 52, 55, 57, 58, 60, 59, 58, 57, 56, 55, 55, 54, 53, 52, 51, 50, 50, 48, 47, 47, 46, 46] I just copy/pasted and changed it to $xx = "44, 44, 44, 45, 45, 45, 45, 45, 45, 43, 42, 41, 40, 39, 39, 39, 37, 37, 37, 37, 37, 37, 37, 39, 40, 43, 47, 47, 49, 51, 52, 52, 53, 54, 55, 57, 58, 59, 64, 68, 73, 80, 85, 89, 93, 94, 94, 95, 95, 95, 93, 91, 89, 87, 85, 83, 81, 79, 75, 75, 72, 69, 66, 63, 60, 57, 55, 52, 50, 47, 45, 43, 41, 41, 41, 123, 124, 124, 123, 122, 119, 117, 111, 105, 100, 94, 89, 85, 82, 78, 76, 75, 73, 73, 72, 72, 72, 72, 75, 77, 79, 81, 83, 83, 87, 91, 95, 101, 109, 118, 127, 137, 144, 150, 153, 155, 155, 155, 155, 155, 155, 154, 154, 153, 150, 147, 147, 145, 144, 144, 144, 144, 144, 149, 152, 155, 158, 163, 167, 170, 176, 184, 191, 197, 201, 203, 204, 205, 206, 206, 206, 205, 203, 203, 202, 202, 202, 202, 202, 202, 202, 204, 207, 210, 213, 219, 220, 222, 223, 224, 226, 228, 230, 234, 237, 242, 246, 252, 259, 269, 278, 287, 293, 301, 309, 316, 325, 333, 340, 345, 345, 343, 341, 341, 339, 335, 332, 329, 327, 327, 327, 327, 328, 328, 329, 329, 332, 332, 335, 336, 338, 339, 343, 343, 346, 346"; $yy = "39, 45, 50, 58, 72, 86, 91, 97, 102, 106, 108, 111, 112, 114, 114, 112, 108, 102, 98, 92, 86, 81, 75, 69, 65, 60, 56, 55, 53, 50, 50, 48, 48, 48, 48, 48, 46, 46, 47, 48, 51, 53, 55, 58, 60, 61, 62, 66, 70, 76, 80, 84, 86, 89, 93, 94, 97, 99, 101, 102, 102, 102, 102, 102, 102, 102, 102, 102, 102, 102, 102, 102, 102, 102, 102, 72, 72, 71, 69, 68, 65, 64, 62, 61, 61, 61, 61, 61, 61, 62, 64, 64, 66, 68, 71, 76, 81, 86, 92, 94, 96, 100, 102, 102, 104, 104, 104, 104, 104, 101, 98, 94, 91, 86, 84, 80, 78, 76, 74, 73, 72, 71, 70, 69, 71, 75, 76, 82, 86, 92, 95, 98, 102, 105, 108, 108, 110, 111, 111, 111, 110, 106, 102, 98, 94, 91, 88, 84, 82, 79, 77, 74, 71, 70, 68, 68, 66, 66, 65, 64, 63, 62, 62, 59, 58, 57, 57, 56, 56, 56, 56, 58, 58, 60, 61, 63, 64, 66, 68, 68, 68, 68, 68, 65, 63, 59, 55, 51, 50, 47, 47, 48, 50, 51, 52, 55, 57, 58, 60, 59, 58, 57, 56, 55, 55, 54, 53, 52, 51, 50, 50, 48, 47, 47, 46, 46"; to give me two string variables (as you get from the db table), then echo drawImage($xx, $yy); to get the signature displayed. Why are you changing the function like that?

Your soul would be in mortal peril. As with so many things

The same way that the function does in the first two lines function drawImage($xcoords, $ycoords) { $xa = explode(',', $xcoords); // create array from $xcoord string $ya = explode(',', $ycoords); // create array from $ycoord string $w = max($xa)+10; $h = max($ya)+10; $path = "M $xa[0] $ya[0] "; unset($xa[0], $ya[0]); foreach ($xa as $i => $x) { $y = $ya[$i]; $path .= "L $x $y "; } $im = "<svg width='$w' height='$h' > <path d='$path' stroke='#000' fill='none'/> </svg>"; return $im; } example $x = "1, 2, 3, 4, 5"; // comma separated string $arr = explode (', ', $x); // explode into array elements splitting on ", " echo '<pre>', print_r($arr, 1), '</pre>'; // view the array /* result : Array ( [0] => 1 [1] => 2 [2] => 3 [3] => 4 [4] => 5 ) */

That's what ids are for (row identification) "Some point" is version 8. https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html Yes, I agree.

Probably best to leave it as it is then; it works (if it ain't bust, don't fix it)

I prefer to use ids as foreign keys and not data attribute values (such as dates, names etc) So does NOT NULL You would validate end > start on processing the input. The method I proposed for entering new versions would ensure contiguous logical date sequences. The valid_from / valid_until dates give you a history. (see my data and query examples) That is up to you. If you assign a member to a particular plan/version then you get that problem. Alternatively assign them to a plan and the use the "current" version. Prices for each plan would also have valid_from / valid_to dates. For new members, get the current price. (SELECT price FROM plan_price WHERE CURDATE() BETWEEN valid_from and valid_to) For renewals (grandfathering) get the price that applied on the date_joined (SELECT price FROM plan_price WHERE date_joined BETWEEN valid_from and valid_to)

They would still be a strings of comma-separated values. Changing to column type TEXT just increases the maximum storage capacity of each column to 65500 (which should be sufficient to hold the longest signature with several thousand points.) Normalising the data would allow you store the data as a series of (x,y) points which, to me, seems more logical as that is how the data is used. At the moment you have a huge block of x coordnates and another of y coordinates. Storing it this way way would be done as part of your transfer from the phone app. Question: Does the app put the data directly into the mysql db in your current format or is there some sort if interface process between the two?

You only need to query once $res = $conn->query("SELECT userid, fname, lname, sign_x, sign_y FROM winuser_demo"); $conn->query("SELECT userid, fname, lname, sign_x, sign_y FROM winuser_demo"); // <--- REMOVE THIS LINE

I get NOTE: the 2 values you posted are arrays. The function expects strings of comma-separated values. If you prefer to pass arrays like those the query can be modified. You should also consider normalizing the data ... +-------------+ | user | +-------------+ | id |--------+ +-------------+ | firstname | | | signature | | lastname | | +-------------+ +-------------+ +--------<| user_id | | seq | | x | | y | +-------------+

Change the column type of the sign_x and sign_y columns to TEXT instead of varchar(N). That should give sufficient capacity.