Jump to content

Barand

Moderators
 View Profile  See their activity

  • Posts

    24,563

  • Joined

  • Last visited

  • Days Won

    822

 Content Type 

Everything posted by Barand

  1. Barand
    Perhaps https://dev.mysql.com/doc/refman/8.0/en/tutorial.html
  2. Barand

    Report

    Barand replied to KAVIYA's topic in MySQL Help

    By the way @KAVIYA, I think you should get together with @Senthilkumar and join forces. His database name, column names and query contents and use of varchar for almost everything bear an amazing similarity to yours.
  3. Barand

    Report

    Barand replied to KAVIYA's topic in MySQL Help

    It helps if all the months that you searching for are the same. I can't see any data for 2024-04 in marketing_data table, so it gives this when I combine all
  4. Barand

    Report

    Barand replied to KAVIYA's topic in MySQL Help

    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.
  5. Barand
    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.
  6. Barand
    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>
  7. Barand
    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} | // +----+---------------------+--------------------+
  8. Barand
    What do you expect to be getting? What's in $_POST['field'] ?
  9. Barand
    NOTE: I missed out a "NOT" - I edited my reply to correct.
  10. Barand
    try WHERE EmpID='83201858' AND NOT (VisitType <> 'No Due' AND VisitDate ='')
  11. Barand
    You should first learn the basic concepts, such as the difference between column names and column values and other attributes
  12. Barand
    Some are the result of running a query from the command line but most, particularly the relationhip diagrams, are hand-typed.
  13. Barand
    Do you have php v8?
  14. Barand
    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"] | +--------+-----------+-----------------------------+
  15. Barand
    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.
  16. Barand
    Try something like this $dir = '../test'; echo '<pre>'; foreach (glob("$dir/*.*") as $f) { printf( "%-80s %-30s<br>", $f, mime_content_type($f) ); }
  17. Barand
    I am assuming that this is an AJAX/Fetch process as you reference javscript in your initial post. Is it?
  18. Barand
    You don't need the ids for that. try { begin transaction insert seat booking(s) commit exit('OK') } catch (exception) { rollback exit('Error') }
  19. Barand
    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.
  20. Barand
    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>
  21. Barand
    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 | +----+------------+-------------+------------+------------+
  22. Barand
    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; }
  23. Barand
    OK, I give up guessing. What is wrong with it?
  24. Barand
    You are trying to do your processing when number_entered is NOT set. if(!isset($_POST['number_entered'])){ ^
  25. Barand
    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
×
×
  • 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.