Jump to content

Barand

Moderators
  • Posts

    24,257
  • Joined

  • Last visited

  • Days Won

    786

Everything posted by Barand

  1. Barand

    Report

    You stand a better chance of help if you tell us exactly what your problem is we know what your data looks like (table structure and test data export dump) we know what output you want.
  2. Store the field sequence number as an attribute and don't rely on its position in the array. (This would apply equally to a conventional database table where you shouldn't rely on id for position). Also, have your sequence numbers initially incrementing by 10s (or, even, 100s). If you increment by 1, changing the sequence is a problem. EG if you start with firstname lastname date_of_birth username password and you then decide you want username to be first, you have to increment 1, 2, and 3 then change 4 to 1 (and run the risk of creating illegal duplicates on the way if you are using array positions or id keys). If you started with 100, 200, 300, .... then moving the username to the top would just be a matter of changing 400 to 50.
  3. I find data attributes useful for associating inputs for the same record. I get the data-id of the select field then change other fields with the same data-id. Here's an example <php $employees = [ [ 'id'=>21, 'name'=>'Curly', 'status'=>1, 'title'=>'Sales Manager', 'salary'=>65000 ], [ 'id'=>22, 'name'=>'Larry', 'status'=>1, 'title'=>'Sales Assistant', 'salary'=>45000 ], [ 'id'=>33, 'name'=>'Mo', 'status'=>1, 'title'=>'Sales Assistant', 'salary'=>45000 ], [ 'id'=>46, 'name'=>'Tom', 'status'=>1, 'title'=>'Sales Assistant', 'salary'=>45000 ], [ 'id'=>47, 'name'=>'Dick', 'status'=>1, 'title'=>'Trainee', 'salary'=>25000 ], [ 'id'=>51, 'name'=>'Harry', 'status'=>1, 'title'=>'Trainee', 'salary'=>25000 ] ]; $tdata = ''; foreach ($employees as $e) { $tdata .= "<tr> <td>{$e['name']}</td> <td><select name='emp[{$e['id']}][status]' class='status' data-id='{$e['id']}'>" . statusOptions($e['status']) . "</td> <td><input type='text' name='emp[{$e['id']}][title]' class='title' data-id='{$e['id']}' value='{$e['title']}'></td> <td><input type='text' name='emp[{$e['id']}][salary]' class='salary' data-id='{$e['id']}' value='{$e['salary']}'></td> </tr>"; } function statusOptions($current) { $stats = [1 => "Working", 2 => "Not Working"]; $opts = "<option value=''>- select status -</option>"; foreach ($stats as $s => $desc) { $sel = $s == $current ? 'selected' : ''; $opts .= "<option $sel value='$s'>$desc</option>"; } return $opts; } ?> <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <title>Example</title> <script src="https://code.jquery.com/jquery-3.6.4.min.js"></script> <script type='text/javascript'> $(function() { $(".status").change(function() { let id = $(this).data("id") if ( $(this).val() == 2 ) { $(".title[data-id="+id+"]").attr("disabled", true) $(".salary[data-id="+id+"]").attr("disabled", true) } else { $(".title[data-id="+id+"]").attr("disabled", false) $(".salary[data-id="+id+"]").attr("disabled", false) } }) }) </script> <style type='text/css'> table { border-collapse: collapse; width: 80%; margin: 30px auto; } th { text-align: left; padding: 8px; color: white; background-color: black; } td { padding: 4px 8px; } </style> </head> <body> <h1>Example</h1> <form method='POST'> <table border='1'> <tr> <th>Name</th> <th>Status</th> <th>Job Title</th> <th>Salary</th> </tr> <?= $tdata ?> <tr><td colspan='4'><input type='submit'></tr> </table> </form> </body> </html>
  4. The keys were 0, 1. This what you have in your table. Examples... $sql2 = "INSERT INTO test_1 (field_selection) VALUES (:fids)"; $stmt2 = $pdo->prepare($sql2); $fieldIds = [0=>1, 1=>2]; $stmt2->execute([ ':fids' => json_encode($fieldIds) ]); $fieldIds = [42=>1, 57=>2]; $stmt2->execute([ ':fids' => json_encode($fieldIds) ]); // GIVES: // +----+---------------------+--------------------+ // | id | created | field_selection | // +----+---------------------+--------------------+ // | 1 | 2024-03-01 00:09:04 | [1, 2] | // | 2 | 2024-03-01 00:09:04 | {"42": 1, "57": 2} | // +----+---------------------+--------------------+
  5. What do you expect to be getting? What's in $_POST['field'] ?
  6. NOTE: I missed out a "NOT" - I edited my reply to correct.
  7. try WHERE EmpID='83201858' AND NOT (VisitType <> 'No Due' AND VisitDate ='')
  8. You should first learn the basic concepts, such as the difference between column names and column values and other attributes
  9. Some are the result of running a query from the command line but most, particularly the relationhip diagrams, are hand-typed.
  10. Now you provide it! When I think of the time I've spent building these... +--------+-----------+-----------------------------+ | cat_id | cat_name | attributes | +--------+-----------+-----------------------------+ | 1 |Membership | ["Type", "Duration"] | | 2 |Book | ["Title", "Author"] | | 3 |T-shirt | ["Size", "Colour", "Style"] | +--------+-----------+-----------------------------+
  11. You can. The trick is use a code block for consistent charater widths. EG... +------------+-------------+--------+-------+ | Field Name | Field Label | Width | Order | +------------+-------------+--------+-------+ |First Name | Given Name | 6 | 1 | |Last Name | Surname | 6 | 2 | +------------+-------------+--------+-------+ I've used json columns in the past. I have found it a good alternative to the EAV data model (PITA). For example, in my sample data below there is a product table with 3 categories of product. Each category has different attribute: Membership - type, duration Book - author, title T-shirt = size, colour, style In a conventional relational table each attribute would require its own column and most of them would be empty. The json version uses a single column. The other table was for testing arrays and accessing and joining on array elements. You would store the metadata in your database to define which attributes each category should have. +--------+-----------+-----------------------------+ | cat_id | cat_name | attributes | +--------+-----------+-----------------------------+ | 1 |Membership | ["Type", "Duration"] | | 2 |Book | ["Title", "Author"] | | 3 |T-shirt | ["Size", "Colour", "Style"] | +--------+-----------+-----------------------------+ TEST DATA CREATE TABLE `product_j` ( `product_id` int(11) NOT NULL AUTO_INCREMENT, `prod_name` varchar(45) DEFAULT NULL, `category_id` int(11) DEFAULT NULL, `attributes` json DEFAULT NULL, PRIMARY KEY (`product_id`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8; INSERT INTO `product_j` VALUES (1,'Standard Membership (12 months)',1,'{\"Type\": \"S\", \"Duration\": 12}'),(2,'Premium Membership (3 months)',1,'{\"Type\": \"P\", \"Duration\": 3}'),(3,'Brave New World',2,'{\"Title\": \"Brave New World\", \"Author\": \"Aldus Huxley\"}'),(4,'Philosophers Stone',2,'{\"Title\": \"Harry Potter and the Philosophers Stone\", \"Author\": \"JK Rowling\"}'),(5,'T-Shirt 1',3,'{\"Size\": \"M\", \"Color\": \"Red\", \"Style\": \"V-neck\"}'),(6,'T-Shirt 2',3,'{\"Size\": \"L\", \"Color\": \"White\", \"Style\": \"V-neck\"}'),(7,'T-Shirt 3',3,'{\"Size\": \"L\", \"Color\": \"Red\", \"Style\": \"V-neck\"}'),(8,'T-Shirt 4',3,'{\"Size\": \"L\", \"Color\": \"Black\", \"Style\": \"crew-neck\"}'),(9,'Goblet of Fire',2,'{\"Title\": \"Harry Potter and the Goblet of Fire\", \"Author\": \"JK Rowling\"}'); +------------+---------------------------------+-------------+------------------------------------------------------------------------------+ | product_id | prod_name | category_id | attributes | +------------+---------------------------------+-------------+------------------------------------------------------------------------------+ | 1 | Standard Membership (12 months) | 1 | {"Type": "S", "Duration": 12} | | 2 | Premium Membership (3 months) | 1 | {"Type": "P", "Duration": 3} | | 3 | Brave New World | 2 | {"Title": "Brave New World", "Author": "Aldus Huxley"} | | 4 | Philosophers Stone | 2 | {"Title": "Harry Potter and the Philosophers Stone", "Author": "JK Rowling"} | | 5 | T-Shirt 1 | 3 | {"Size": "M", "Color": "Red", "Style": "V-neck"} | | 6 | T-Shirt 2 | 3 | {"Size": "L", "Color": "White", "Style": "V-neck"} | | 7 | T-Shirt 3 | 3 | {"Size": "L", "Color": "Red", "Style": "V-neck"} | | 8 | T-Shirt 4 | 3 | {"Size": "L", "Color": "Black", "Style": "crew-neck"} | | 9 | Goblet of Fire | 2 | {"Title": "Harry Potter and the Goblet of Fire", "Author": "JK Rowling"} | +------------+---------------------------------+-------------+------------------------------------------------------------------------------+ CREATE TABLE `json_test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `jdata` json DEFAULT NULL, `role` json DEFAULT NULL, `name` varchar(45) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; INSERT INTO `json_test` VALUES (1,'{\"town\": \"Chester\", \"county\": \"Cheshire\", \"country\": 1}','[1, 2, 3]','Peter'),(2,'{\"town\": \"Tenby\", \"county\": \"Pembrokeshire\", \"country\": 3}','[1, 3]','Paul'),(3,'{\"town\": \"Lancaster\", \"county\": \"Lancashire\", \"country\": 1}','[1, 2, 4]','Mary'),(4,'{\"town\": \"Dorchester\", \"county\": \"Dorset\", \"country\": 1}','[2, 4]','Jane'),(5,'{\"town\": \"Caernarfon\", \"county\": \"Cardigan\", \"country\": 3}','[1, 2, 3, 4]','Fred'); +----+-------------------------------------------------------------+--------------+-------+ | id | jdata | role | name | +----+-------------------------------------------------------------+--------------+-------+ | 1 | {"town": "Chester", "county": "Cheshire", "country": 1} | [1, 2, 3] | Peter | | 2 | {"town": "Tenby", "county": "Pembrokeshire", "country": 3} | [1, 3] | Paul | | 3 | {"town": "Lancaster", "county": "Lancashire", "country": 1} | [1, 2, 4] | Mary | | 4 | {"town": "Dorchester", "county": "Dorset", "country": 1} | [2, 4] | Jane | | 5 | {"town": "Caernarfon", "county": "Cardigan", "country": 3} | [1, 2, 3, 4] | Fred | +----+-------------------------------------------------------------+--------------+-------+ There's also a conventional country table +------------+--------------+ | country_id | country_name | +------------+--------------+ | 1 | England | | 2 | Scotland | | 3 | Wales | | 4 | Ireland | | 5 | France | | 6 | Italy | +------------+--------------+ TEST SQL QUERIES Alternative versions of same query (JSON_UNQUOTE vs double-arrow). Join on json array element. SELECT JSON_UNQUOTE(jdata->'$.town') as Town , JSON_UNQUOTE(jdata->'$.county') as County , country_name as Country FROM json_test j JOIN country c ON c.country_id = j.jdata->'$.country' ORDER BY jdata->'$.country'; SELECT jdata->>'$.town' as Town , jdata->>'$.county' as County , country_name as Country FROM json_test j JOIN country c ON c.country_id = j.jdata->'$.country' ORDER BY jdata->'$.country'; Search on json data SELECT product_id , attributes->>'$.Color' as color , attributes->>'$.Style' as size FROM products.product_j WHERE attributes->>'$.Size' = 'L' AND category_id = 3; Update json data UPDATE product_j SET attributes = JSON_SET(attributes, "$.Style", "Turtleneck") WHERE product_id = 8; I hope this gives a flavour of using json data. I wouldn't recommend using it all the time, as it breaks normalization rules, but it has its uses.
  12. Try something like this $dir = '../test'; echo '<pre>'; foreach (glob("$dir/*.*") as $f) { printf( "%-80s %-30s<br>", $f, mime_content_type($f) ); }
  13. I am assuming that this is an AJAX/Fetch process as you reference javscript in your initial post. Is it?
  14. You don't need the ids for that. try { begin transaction insert seat booking(s) commit exit('OK') } catch (exception) { rollback exit('Error') }
  15. If they book several seats you will have several booking_ids. What will you do with them when you have them? The reason for getting the last insert id is to use it as a foreign key when you post child records (eg write invoice header then invoice item records). From your data model this isn't the case.
  16. Like this? Code <?php if ($_SERVER['REQUEST_METHOD'] == 'POST') { ## ## Process posted data ## $number = $_POST['number_entered'] ?? 0; $answer = $_POST['answer'] ?? 0; $question = $_POST['question'] ?? ''; $check = $number == $answer ? "<span class='w3-badge w3-green w3-xlarge'>&check;</span>" : "<span class='w3-badge w3-red w3-xlarge'>&times;</span>"; $output = <<<OUT <span class="qtext">$question $number</span> $check <br><br> <a href='' class='w3-button w3-indigo'>Ask me another</a> OUT; } else { ## ## Ask new question ## $rand1 = rand(1, 9); $rand2 = rand(1, 9); $randoperator = rand(0, 3); $operators = array('&times;', '&divide;', '&plus;', '&minus;'); $finalvalue = match($randoperator) { 0 => $rand1 * $rand2, 1 => handleDivide($rand1, $rand2), 2 => $rand1 + $rand2, 3 => $rand1 - $rand2 }; $question = "$rand1 {$operators[$randoperator]} $rand2 ="; $output = <<<OUT <form method='POST'> <span class="qtext">$question</span> <input type='hidden' name='question' value='$question'> <input type='hidden' name='answer' value='$finalvalue'> <input type='number' name='number_entered' placeholder='?' autofocus> <br><br> <button class='w3-button w3-indigo'>Submit answer</button> </form> OUT; } function handleDivide(&$r1, $r2) { $fv = $r1; $r1 *= $r2; return $fv; } ?> <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <title>Example</title> <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css"> <style type='text/css'> div { padding: 16px; text-align: center; } input { width: 70px; text-align: center; font-size: 20pt; } .qtext { font-size: 20pt; } </style> </head> <body> <div class='w3-blue-gray'> <h1>Do the Math</h1> </div> <div> <?= $output ?> </div> </body> </html>
  17. An easier approach is to search for those records with today's char in the recurrence column set to "1" $sql = "SELECT id , recurrence , description , begindate , enddate FROM schedule WHERE ? BETWEEN begindate AND enddate AND SUBSTRING(recurrence, WEEKDAY(?)+1, 1) AND active "; $res = $pdo->prepare($sql); $searchDate = '2024-02-28'; $res->execute([$searchDate, $searchDate]); Example outputs searchdate = Feb 26 2024 (Monday) +----+------------+-------------+------------+------------+ | id | recurrence | description | begindate | enddate | +----+------------+-------------+------------+------------+ | 39 | 1010100 | Event1 | 2023-08-31 | 3000-01-01 | +----+------------+-------------+------------+------------+ searchdate = Feb 28 2024 (Wednesday) +----+------------+-------------+------------+------------+ | id | recurrence | description | begindate | enddate | +----+------------+-------------+------------+------------+ | 39 | 1010100 | Event1 | 2023-08-31 | 3000-01-01 | | 51 | 0010000 | Event2 | 2023-08-31 | 3000-01-01 | +----+------------+-------------+------------+------------+
  18. As well as chaging the rand() ranges to 1-9 I would also change the way rand1 and rand2 are used in the case of division. allocate rand1 to the finalvalue and then multiply rand1 by rand2. This avoids none integer results. IE.. rand1 * rand2 ------------- = rand1 rand2 My code for this bit would be $rand1 = rand(1, 9); $rand2 = rand(1, 9); $randoperator = rand(0, 3); $operators = array('&times;', '&divide;', '&plus;', '&minus;'); $finalvalue = match($randoperator) { 0 => $rand1 * $rand2, 1 => handleDivide($rand1, $rand2), 2 => $rand1 + $rand2, 3 => $rand1 - $rand2 }; $question = "$rand1 {$operators[$randoperator]} $rand2 ="; echo "$question $finalvalue<br>"; function handleDivide(&$r1, $r2) { $fv = $r1; $r1 *= $r2; return $fv; }
  19. OK, I give up guessing. What is wrong with it?
  20. You are trying to do your processing when number_entered is NOT set. if(!isset($_POST['number_entered'])){ ^
  21. Rule of thumb: If all you are doing is GETting data to display then use GET If what you are doing has side-effects, such as logging in or updating a db table, use POST
  22. Try it without the above line. Specifying the call type as "JSON" has always sufficed for me.
  23. It won't be a million miles out. Try changing the last line of my code to $result = json_encode(array_values($categories));
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.