Jump to content

I-AM-OBODO

Members
  • Posts

    439
  • Joined

  • Last visited

Posts posted by I-AM-OBODO

  1. 1 hour ago, kicken said:

    Assuming mysql, Your tables may be using the MyISAM engine which does not support transactions.  You should be using InnoDB unless you have a specific reason not to.

    I Guest that's why! Funny though! Was using InnoDB before now. Had to switch cos a functionality I needed will only be possible with MyISAM and now another function can only work in InnoDB. 😆 

    Guess that's life! Nothing and no one has everything. Just gotta do with what's paramount. 

    Thanks 😊 

  2. 44 minutes ago, requinix said:

    Maybe a silly question, but are you sure the data from that first query is being entered? It's not possible that you had identical data in the table and so it seems like it worked?

    I am very very sure the data are not identical and the query from the first query is entered. I did that several times. I am baffled myself. 

  3. Hello all.

    I am playing with transaction and i noticed something. I deliberately omitted txn in my second mysql statement. the first statement went through i.e the data were stored regardless of the error thrown by transaction: why was it so? why did the query execute? was thinking no entry is supposed to pass through should there be any error with transaction?

    below my code

    try {
        
        $pdo->beginTransaction();
    
        $sql = "INSERT INTO tbl_cont_asess_records (
        txn,
        subj_id,
        test_score,
        exam_score
    
        ) VALUES (
        ?,
        ?,
        ?,
        ?
        )";
    
        $stmt = $pdo->prepare($sql);
    
        foreach(array_column($subject_data,'id') as $key)
        {
          $stmt->execute([
            $txn,
            $key,
            $post['test_score'][$key],
            $post['exam_score'][$key]
            
          ]);
          
        }
    
        $sql2 = "INSERT INTO tbl_cont_asess (
        txn,
        sch_session,
        sch_term,
        ward_id, 
        class_id, 
        staff_id
    
        ) VALUES (
        ?,
        ?,
        ?,
        ?,
        ?,
        ?
        )";
        $stmt2 = $pdo->prepare($sql2);
    
          $stmt2->execute([
            
            $current_session,
            $current_term,
            $_GET['id'],
            $class_id,
            $staff_id
            
          ]);
    
        $pdo->commit();
    
        echo '<div class="alert alert-success text-center text-dark">Scores Saved SUCCESSFULLY!</div>';
    
      }catch (PDOException $e) {
      
        $pdo->rollBack();
    
        echo '<div class="alert alert-danger text-center">Score Not Saved. Problem Occurred!</div>';
    
    }

     

  4. I've been able to do the minus from within the query now. After everything, the working query is

    SELECT t1.customer_id, t1.customer_name,
    COALESCE(t2.total_sales, 0) as total_sales,
    COALESCE(t3.amt_paid, 0) as amt_paid,
    COALESCE(t2.total_sales, 0) - COALESCE(t3.amt_paid, 0) as to_balance
    FROM customers t1
    LEFT JOIN 
    (
    SELECT customer_id,
    SUM(total_price) as total_sales
    FROM tbl_sales
    GROUP BY customer_id
    ) t2 USING (customer_id)
    LEFT JOIN
    (
    SELECT customer_id,
    SUM(amt_paid) as amt_paid
    FROM tbl_sales_total
    WHERE status = 0
    GROUP BY customer_id
    ) t3 USING (customer_id)
    HAVING to_balance <> 0
    ORDER BY t1.customer_name ASC;

    Thanks @Barand

  5. 9 minutes ago, I-AM-OBODO said:

    It worked fine as expected. Thank you so much. But one other thing is with these joins, how can i use it to get same result but without specifying the customer? When I removed the where clause, it throws an error.

    I also know I can get the difference between total_sales and amt_paid by first getting the values of each and then minus it. But I am curious to know if it can be achieved using the mysql query? If possible, how can it be done?

    Thanks so much as always!

    Besides, how can one know when to use the multi level joins you used? Joins so complicated. 

     

    Thanks

    I have seen why it was throwing error. Its cos of values that are 0. And number_format use with value 0 is deprecated. I have resolved the error issue but the problem now is that the grouping does not working in the final execution of the query. It brings out the new multiple times. How can I fix this. 

    Thanks

  6. It worked fine as expected. Thank you so much. But one other thing is with these joins, how can i use it to get same result but without specifying the customer? When I removed the where clause, it throws an error.

    I also know I can get the difference between total_sales and amt_paid by first getting the values of each and then minus it. But I am curious to know if it can be achieved using the mysql query? If possible, how can it be done?

    Thanks so much as always!

    Besides, how can one know when to use the multi level joins you used? Joins so complicated. 

     

    Thanks

  7. Hello all.

    please what could be the reason i am not getting the desired result when i do a JOIN.

    This query gives the desired result
     

    //calculate profit
    $stmt = $pdo->query("
    SELECT 
    SUM(total_cost) AS total_cost,
    SUM(total_sales) AS total_sales
    FROM tbl_sales
    WHERE customer_id = 489639
    AND status = 0
    
    ");
    
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    $total_sales  = $row['total_sales'];
    $total_cost   = $row['total_cost'];
    $profit = $total_sales - $total_cost;
    
    //calculate balance
    
    $stmt = $pdo->query("
    SELECT 
    SUM(amt_paid) AS amt_paid
    FROM tbl_sales_total
    WHERE customer_id = 489639
    AND status = 0
    
    ");
    
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    $amt_paid = $row['amt_paid'];
    $balance = $total_sales - $amt_paid;
    
    echo "<strong>Total Profit Realised From Customer: ₦".$profit."</strong>";
    echo "<br><strong>Total Unpaid Balance: ₦".$unpaid_bal."</strong>";

    But when i do a join it gives a wrong result.

    $stmt = $pdo->query("
    SELECT *,
    SUM(t2.total_price) AS total_sales,
    SUM(t3.amt_paid) AS amt_paid
    FROM customers t1
    LEFT JOIN tbl_sales t2 ON t1.customer_id = t2.customer_id
    LEFT JOIN tbl_sales_total t3 ON t1.customer_id = t3.customer_id
    WHERE t1.customer_id = 489639
    AND t3.status = 0                     
    
    ");
    
    WHILE($row = $stmt->fetch(PDO::FETCH_ASSOC)){
    
    echo   '<tr>
    <td>'.$row["customer_name"].'</td>
    <td>'.$row["total_sales"].'</td>
    <td>'.$row["amt_paid"].'</td>
    </td>
    </tr>';
    }

    Please what did i do wrong and how do i get the desired result.

     

    Thanks

  8. 16 hours ago, gizmola said:

    In general, this would be called provisioning. 

    For the most part, this requires that your application have an underlying architecture that supports this.

    In terms of DNS, you can set up a wildcard DNS entry for *.myapp.com.

    Internally, your application needs code that accepts a request, examines the requested url, extracts the subdomain, and executes the routing accordingly, or you can also have a rewrite that will look for subdomains other than 'www' and rewrite those to https://myapp.com/shop1.

    When a new user creates a store, you will run provisioning code in your application that does any required setup (make new database user, create database with new user assigned rights, save credentials in user profile configuration file or in database.)  There are strengths and weaknesses to each approach so you have to consider the tradeoffs and security implications of each approach.  For example, you could just use the primary database access user, and not create a separate database user for each customer database.  There isn't one right answer for each application.  

    Thanks for the response. You've shed more light on the topic for me. I will do more research on provisioning. At least I know the name of what I will be researching about! 

    Though I know all about creating a subdomain for them and setting up the database base etc. But I want it automated without me doing a thing. They should just fill form and voilà! It created.

    Thanks for the tips!

  9. 12 hours ago, requinix said:

    I'll go ahead and say that typically

    one does not do that. Because the users on your site are not actually setting up a web app.

    When you signed up on PHPFreaks, you got an account. There's a "page" for you at https://forums.phpfreaks.com/profile/63588-i-am-obodo/ and you can create content in your personal space (so to speak).
    None of that came with actual system accounts. You don't have a database dedicated to you, let alone a database account.

    Because your ability to do those things is part of our application. You are not, in fact, setting up your own application. You're simply using ours.

    If you literally want people to set up applications and write code that lets them connect to databases, that's one thing. But that's not what it sounds like you're describing.

    What you're describing is basically like what happens on many other ecommerce sites: you sign up, you get a "store" you may or may not be able to customize (be that a URL or subdomain), and you start selling items. As a user, you don't want to spend your time dealing with databases, or installing WordPress, or updating extensions, or writing HTML, or really doing anything else except for putting items up for sale and fulfilling orders.

    Thanks for your reply. But you don't get what I am trying to convey cos I might have not conveyed it proper. But your explanation is way different from what I am asking of! I know all about my account with with phpfreak ans all that cos I have built app and websites with such privileges and user areas but this is totally different from my question. Gizmola has a better understanding for my question. Provisioning is word for it. I have perused through an web app that does what I want and I know it's quite different from what I'm used to. I can manually set up each account I know. I also know that I can create a subdomain for for each store and create different database for each of them. I know all these. But it's totally different from the question I asked. 

    On this Web app that I want to copy, I visited, filled my choice name and filled some forms based on my own likings and what the form requires. After filling the form and submit, a subdomain was created automatically for me with all the basic database settings so that when I am logged in, I can complete the setting and my profile as per my requirements.

    Hope you get a better picture and know that it's way different from what you said or the analogy between me creating an account on phpfreaks and all that.

    Thanks for the response though. I appreciate it

  10. Hello guys.

    I really don't know what to use as a topic and my question might no be clear enough cos I might lack adequate words to put it together but I will try by using examples anyhow.

    My question is

    Like during joomla or WordPress installation, the processes one has to undergo e.g filling the company/application name, database name, admin username and password, color scheme etc. 

    In like manner, one has a web app hosted. How to enable different user create their own account and setup their database etc

    For example I have a web app hosted at https://myapp.com

    A user can setup their shop on my app https://myapp.com/shop1 or shop1.myapp.com

    Hope I tried to make it clear enough

    Thanks

  11. 17 hours ago, mac_gyver said:

    if both arms_name_long and arms_name_short must be unique, you would want to detect which one or both are duplicates and display a separate and specific message in the appropriate span tag(s).

    as to how to do this, your database design must enforce uniqueness. both of those database table columns should be defined as unique indexes. you would then just attempt to insert the row of data and in the exception error handling, detect if a duplicate index error number (1062) occurred. It is at this point where you would build and execute a SELECT query to find which of the values are duplicates.

    here's a laundry list of things you should/should not be doing in this code -

    1. the for='...' attribute in the <label ...> tag must match the corresponding form field's id='...' attribute OR if you put the closing </label> tag after the form field, you can leave out the for='...' attribute entirely and if not used for anything else, leave out the id='...' attribute.
    2. for problems like item #1, you need to validate your resulting web pages at validator.w3.org
    3. any value you output in a html context needs to have htmlentities() applied to it to help prevent cross site scripting.
    4. if you use the null coalescing operator (??), it will simplify things like the value='...' attribute logic - value='<?=htmlentities($_POST['arms_long_name']??'',ENT_QUOTES)?>'
    5. the ids you use in a database context should be generated via autoincrement primary index columns, not using php random numbers. if you did have some need to generate unique random numbers and store them in a database table, you would need to insure uniqueness by defining the column holding them as a unique index and perform a duplicate index error check as described above for the arms_name_long and arms_name_short values.
    6. you should keep the form data as a set in a php array variable, then operate on elements in this array variable throughout the rest of the code.
    7. you should trim all input data before validating it, mainly so that you can detect if values where all white-space characters. once you do item #6 on this list, you can trim all the data at once using a single php array function.
    8. you should apply any ucwords() and strtoupper() modification to values only when you output them.
    9. the value you output in the form field value='...' attribute should be the trimmed, validated value, not the raw field value.
    10. when you make the database connection, you need to - set the character set to match your database table's character set, set the emulated prepared query setting to false (you want to run real prepared queries whenever possible), and set the default fetch mode to assoc (so that you don't need to specify it in each fetch statement.)
    11. i'm assuming that the ALTER TABLE... query is temporarily in the code and will be removed? btw - the ->query() method call executes the query. you don't need an additional ->execute() call and i'm not sure what doing so in this case will accomplish.
    12. if you use simple ? prepared query place holders and simply supply an array of input values to the ->execute([...]) call, it will greatly reduced the amount of typing you have to do for each sql query.
    13. as already discussed, you would first attempt to insert the data, then in the exception error handling, detect if the query produced a duplicate index error number. if it did, you would then execute a SELECT query to find which column(s) contain the duplicate values. for any other error number, you would simply rethrow the exception and let php handle it.
    14. you should not output raw database statement errors on a live/public server, as this gives hackers useful information when they internationally do things that trigger errors. if you only catch and handle duplicate (and out of range) database query exceptions in your code, and let php catch and handle all other database exceptions, php will 'automatically' display/log the raw database statement errors the same as its error related settings are setup to do for php errors.

    Thank you for your responds and list of dos/don'ts. Will take note of them. But still didn't solve my problem. If I don't use a modal for the form, I can check for duplicate and display the error. The problem I am having is displaying the duplicate error inside the modal.

    As for ids, they are required for the modal to work. The id passes values of the form to the database. That's how the code for the modal was built.

    The alter table is temporal.

    No. I am not outputting the database errors live.

    I am not really a fan of placeholders.

    I have a function that trims inputs for validation.

    Good to know about the ucword/strtoupper. But is there a reason why it should be on output and not during save? Just curious to know.

    Overall, the observations are very useful and I have learned a few things that I will adapt to.

    If, I can't get a way to display the errors inside the modal, I guess I will just change the design and not use modal for the form.

    Thanks

  12. hello all.

    i dont know if to post this here or javascript/ajax section. if its not the right place, please let me know so i can ask in the right place.

    i am trying out saving to db via modal form. i learned the basics of using ajax to save data with the modal. so far, i can perform a CRUD via modal but the problem i am having is displaying duplicate entry error inside the modal. I have tried so many ways and the closest i come is displaying error only if a field is duplicate cos i use same trigger as my validation error notice. I'd be glad if i am shown the way to get it to display the error or a better way of getting it done. PS: I want the errors displayed inside the modal. I want the database query error to display where the success message is displayed (i.e on the modalMessage div)

    Thanks

    My Modal

    <div class="modal fade" id="armsModal" data-bs-backdrop="static" tabindex="-1" aria-hidden="true">
    <div class="modal-dialog modal-dialog-centered" role="document">
    <div class="modal-content">
    <div class="modal-header modal-bg">
    <h5 class="modal-title w-100 text-center mb-3" id="exampleModalLabel4">Add School Arms</h5>
    <button
    type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
    </div>
    
    <form id="submitForm" class="myForm">
    <div class="modal-body">
      <div id="modalMessage"></div>
          <div class="mb-3">
          <label for="armsname" class="form-label">Arms FullName:</label>
          <input type="text" class="form-control" id="arms_long_name" name="arms_long_name" autocomplete="off" value="<?php if(isset($_POST['arms_long_name'])){ echo $_POST['arms_long_name']; } ?>">
          <span id="longNameError" class="text-danger"></span>
          </div>
          
    
          <div class="mb-3">
              <label for="armsshort" class="form-label">Arms ShortName:</label>
              <input type="text" class="form-control" id="arms_short_name" name="arms_short_name" autocomplete="off" value="<?php if(isset($_POST['arms_short_name'])){ echo $_POST['arms_short_name']; } ?>">
              <span id="shortNameError" class="text-danger"></span>
          </div>
    </div>
    <div class="modal-footer modal-bg">
    <button type="button" class="btn btn-outline-light btn-sm" data-bs-dismiss="modal">
    Close
    </button>
    <button type="submit" class="btn btn-dark btn-sm">Submit</button>
    </div>
    </form>
    </div>
    </div>
    </div>

    My script

    <script>
      //Modal
    $('#submitForm').submit(function(event) {
            event.preventDefault();
            
            $("#armsModal").on("hidden.bs.modal", function() {
            $('#longNameError').text('');
            $('#shortNameError').text('');
            $("#submitForm")[0].reset();
            });
    
            $('#armsModal').on('hidden.bs.modal', function () {
            // Clear form fields
            $('#submitForm')[0].reset();
    
            // Clear error messages
            $('.invalid-feedback').text('');
            });
            
            // Get form data
            var formData = {
                'arms_long_name': $('#arms_long_name').val(),
                'arms_short_name': $('#arms_short_name').val()
            };
            
            // AJAX request
            $.ajax({
                type: 'POST',
                url: 'school-arms-action.php',
                data: formData,
                dataType: 'json',
                encode: true
            })
            .done(function(data) {
                if (!data.success) {
                    if (data.errors.arms_long_name) {
                        $('#longNameError').text(data.errors.arms_long_name);
                    }
                    if (data.errors.arms_short_name) {
                        $('#shortNameError').text(data.errors.arms_short_name);
                    }
                }else{
      
    
                        modalMessage.innerHTML = '<div class="alert alert-success text-center text-black">ARMS SAVE SUCCESSFUL!</div>';
       
                        setTimeout(function() {
                            window.location.href = 'school-arms';
                        }, 2000); // 2 seconds delay                
                }
            });
        });
    </script>

     

    My school-arms-action.php

    $response = array('success' => false, 'errors' => array());
    
    if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    
        $arms_long_name = ucwords($_POST['arms_long_name']);
        $arms_short_name = strtoupper($_POST['arms_short_name']);
        $arms_id = mt_rand(100, 999);
    
        // Validation
        if (empty($arms_long_name)) {
            $response['errors']['arms_long_name'] = 'Arms LongName is Required.';
        }
        if (empty($arms_short_name)) {
            $response['errors']['arms_short_name'] = 'Arms ShortName is Required.';
        }
    
    
        // If no errors, proceed to submission
        if (empty($response['errors'])) {
    
        try {
    
            $pdo = new PDO("mysql:host=localhost;dbname=db_name", "username", "password");
            $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
            $table=$pdo->query("ALTER TABLE tbl_school_arms AUTO_INCREMENT = 1");
            $table->execute();
    
        $stmt = $pdo->prepare("
    
        SELECT * 
        FROM tbl_school_arms 
        WHERE arms_name_long = :arms_name_long 
        OR arms_name_short = :arms_name_short
    
        ");
    
        $stmt->bindParam(':arms_name_long', $arms_long_name, PDO::PARAM_STR);
        $stmt->bindParam(':arms_name_short', $arms_short_name, PDO::PARAM_STR);
        $stmt->execute();
        $existingEntry = $stmt->fetch(PDO::FETCH_ASSOC);
    
        if ($existingEntry) {
            //This is what i used but not the right thing i want
             $response['errors']['arms_long_name'] = 'Duplicate Entry';
    
        } else {
    
        // Perform database operations using PDO
        $stmt = $pdo->prepare("
    
        INSERT INTO tbl_school_arms (arms_id, arms_name_long, arms_name_short) 
        VALUES (:arms_id, :arms_name_long, :arms_name_short)");
    
        $stmt->bindParam(":arms_id", $arms_id);
        $stmt->bindParam(":arms_name_long", $arms_long_name);
        $stmt->bindParam(":arms_name_short", $arms_short_name);
        $stmt->execute();
    
        if($stmt->rowCount()){
    
        $response['success'] = true;
    
        }
     
    }
    } catch (PDOException $e) {
                echo "Error: " . $e->getMessage();
            }
        }  
    
    }
    
    echo json_encode($response);

     

  13. 23 hours ago, Phi11W said:

    Almost always - Yes. 

    The only exception is when you only ever access or retrieve the whole value, commas and all, as a single unit via some other identifier (perhaps to supply that value to some application code that expects it in that "shape"). 

    But ...  as soon as you decide you want to pick that value apart, to extract part of the value or, worse, to find rows by part of that value, then you must reconsider its storage and "re-shape" it something that your database can better work with. 

    Databases are generally:

    • Really good at finding small pieces of "stuff" and bolting them together, but
    • Really rubbish at taking big chunks of "stuff" and pulling them apart. 

    Why do you think string manipulation functions are so "under-developed" in most DBMSes, compared to most programming languages? 
    It's just not what they're built to do. 

    Regards, 
       Phill W. 

    Thanks for the addition. 

  14. On 9/14/2023 at 10:17 AM, Barand said:

    Yes, positively evil.

    You can easily display them like that on output but don't store them like that. For example...

    mysql> select * from project;
    +----+---------------+-----------+------------+
    | id | project_name  | client_id | start_date |
    +----+---------------+-----------+------------+
    |  1 | Project Alpha |         4 | 2022-12-01 |
    |  2 | Proect Beta   |         2 | 2023-01-15 |
    |  3 | Project Gamma |         4 | 2023-03-01 |
    |  4 | Project Delta |         1 | 2023-03-20 |
    +----+---------------+-----------+------------+
    
    
    mysql> select client_id
        ->      , group_concat(project_name separator ', ') as projects
        -> from project
        -> group by client_id;
    +-----------+------------------------------+
    | client_id | projects                     |
    +-----------+------------------------------+
    |         1 | Project Delta                |
    |         2 | Proect Beta                  |
    |         4 | Project Alpha, Project Gamma |
    +-----------+------------------------------+

     

    Thank you so much. I've fixed the database as suggested and after doing so, I realised that there was no need for me using find_in_set again! Had to do a join with group_contact. All is working as expected now. 

    Thank you very much!

  15. 21 hours ago, Barand said:

    You are grouping by a comma-separated string value. Where's the sense in that?

    I'll come back when you have a normalized database and I don't have to sieve through that dog's breakfast.

    Does this means that storing comma separated arrays into a database is wrong practice? 

    Cos for instance, I want to list all the classes that offers a particular subject in one column, I feel using an array would be appropriate. 

    I'm just asking just to know better.

    Thanks

  16. On 9/11/2023 at 2:09 PM, Barand said:

    Sorry, I forgot to explain your probems

    1. There are no values in the class_id column that matche a column value in subj_levels column ("58100" != "58100, 47270")
    2. For find_in_set() to work, the values should be comma-separated, not comma-space-separated.

    Thanks once again. I was able to get it working as wanted but the problem i am having now is that it is omitting the general subject. I guess FIND_IN_SET only finds and displays what is in the set and disregards what is not found in the set. To make myself clearer, when all the classes offer a subject, it means the subject is termed "General".

    $stmt=$pdo->query("
    
    SELECT *,
    GROUP_CONCAT(t2.class_name_small ORDER BY t2.class_id) classLevel
    FROM tbl_school_subjects t1
    INNER JOIN  tbl_classes t2
    ON FIND_IN_SET(t2.class_id, t1.subj_levels) > 0
    GROUP BY t1.subj_levels                         
    
      ");
    
    
    WHILE($row=$stmt->fetch(PDO::FETCH_ASSOC)){
    
      if($row['subj_levels'] == "General"){
        $class_name = "General";
      }else{
        $class_name = $row['classLevel'];                              
    
      }
    
      echo '<tr>
      <td></td>
      <td>'.$row['subj_code'].'</td>
      <td>'.$row['subj_name'].'</td>
      <td>'.$row['subj_abbr'].'</td>
      <td>'.$row['subj_short_name'].'</td>                          
      <td>'.$class_name.'</td>

    All the general column is totally ignored. How can i make them show up in the list.

     

    thanks

  17. 5 hours ago, Barand said:

    Sorry, I forgot to explain your probems

    1. There are no values in the class_id column that matche a column value in subj_levels column ("58100" != "58100, 47270")
    2. For find_in_set() to work, the values should be comma-separated, not comma-space-separated.

    Thank you very much. Lemme work on the code again. Will feed you back.

  18. Hello all,
    In a column in my table is store an array 58100, 47270, 95437, 52652 which represents
    in table1
    class_id,     class_name
    58100        JSS
    47270         PRY
    95437        SSS

    in table2
    subjects,         subj_levels         
    English            58100, 47270, 95437
    Maths            58100, 47270
    Physics            47270, 95437


    I have two problems

    Problem One
    when i do a select with join, instead of getting JSS, PRY, SSS as result, i am getting only JSS and the other values not showing up.

    $stmt=$pdo->query("
    SELECT t1.subj_name, t1.subj_levels, t2.class_id
    FROM  tbl_school_subjects t1
    LEFT JOIN tbl_classes t2 ON t1.subj_levels = t2.class_id
    
    ");
    
    WHILE($row=$stmt->fetch(PDO::FETCH_ASSOC)){
    
    echo '<tr>
    <td>'.$row['subj_name'].'</td>                          
    <td>'.$row['class_name_small'].'</td>
    <td>';
    }

    Problem Two
    when i do a select find_in_set, i get no result.

    $ids = $_GET['id'];
    $stmt = $pdo->query("
    SELECT *
    FROM tbl_school_subjects
    WHERE FIND_IN_SET($ids, subj_levels) > 0
    
    ");

    what could be the problem?

    Thanks

  19. 45 minutes ago, I-AM-OBODO said:

    Thank you so much. I will build on yours.

     

    5 hours ago, mac_gyver said:

    if you are not posting the actual code that you are having a problem with, the replies you get may not have anything to do with the problem.

    here's example code (tested with faked data from the SELECT query) showing most of the points that i made -

    <?php
    
    // initialization
    
    // recursive trim function
    function _trim($val)
    {
    	if(is_array($val))
    	{
    		return array_map('_trim',$val);
    	} else {
    		return trim($val);
    	}
    }
    
    session_start();
    
    // make database connection here...
    
    $table_name = 'your_table';
    
    $error = []; // an array to hold user/validation errors
    $post = []; // an array to hold a trimmed working copy of the form data
    
    // get all subject data
    $sql = "SELECT id, subject_name FROM tbl_subjects_secondary";
    $stmt=$pdo->query($sql);
    $subject_data = $stmt->fetchAll();
    
    
    // post method form processing
    if($_SERVER['REQUEST_METHOD'] === 'POST')
    {
    	// trim all the input data at once
    	$post = _trim($_POST);
    	
    	// loop over the subject ids and validate the corresponding form data
    	foreach(array_column($subject_data,'id') as $key)
    	{
    		if($post['test_score'][$key] === '')
    		{
    			$error['test_score'][$key] = "Test Score Field is Required";
    		}
    		if($post['exam_score'][$key] === '')
    		{
    			$error['exam_score'][$key] = "Exam Score Field is Required";
    		}
    	}
    	
    	// if no errors, use the submitted data
    	if(empty($error))
    	{
    		$sql = "INSERT INTO $table_name (
    		subject_id,
    		test_score,
    		exam_score
    		) VALUES (
    		?,
    		?,
    		?
    		)";
    		$stmt = $pdo->prepare($sql);
    
    		foreach(array_column($subject_data,'id') as $key)
    		{
    			$stmt->execute([
    				$key,
    				$post['test_score'][$key],
    				$post['exam_score'][$key]
    			]);
    			
    			// note: error handling for this query is not included with this example code
    		}
    	}
    	
    	// if no errors, success
    	if(empty($error))
    	{
    		// if you want to display a one-time success message, store it in a session variable, then test, display, and clear that variable in the html document
    		$_SESSION['success_message'] = 'You have successfully inserted the subject scores';
    		// redirect to the exact same url of the current page to cause a get request - PRG Post, Redirect, Get.
    		die(header("Refresh:0"));
    	}
    	
    }
    
    // get method business logic - get/produce data needed to display the page
    
    // get all subject data - located above the post method form processing since it is also used by the validation logic
    
    
    // html document
    ?>
    <!DOCTYPE html>
    <html lang="en-US">
    	<head>
    		<meta charset="utf-8">
    		<title>Multi-row Insert Example</title>
    	</head>
    	<body>
    
    <?php
    // display and clear any success message
    if(isset($_SESSION['success_message']))
    {
    	echo '<p>'.$_SESSION['success_message'].'</p>';;
    	unset($_SESSION['success_message']);
    }
    ?>
    
    <?php
    // display the form
    ?>
    <form method="post">
    <table class="table table-borderless">
    <thead>
    <tr>
    <th>SN</th>
    <th>Subject</th>
    <th>Continuous Assessment Score</th>
    <th>Examination Score</th>
    </tr>
    </thead>
    <tbody>
    <?php
    $i = 1;
    foreach($subject_data as $row)
    {
    	?>
    	<tr>
    	<th><?= $i++ ?></th>
    	<td><input type="text" class="form-control border-0" readonly value="<?=$row['subject_name']?>"></td>
    	<td><input type="number" name="test_score[<?=$row['id']?>]" class="form-control" value="<?=htmlentities($post['test_score'][$row['id']]??'',ENT_QUOTES)?>"><span style="color: red; font-size: .8em;"><?= $error['test_score'][$row['id']]??'' ?></span></td>
    	<td><input type="number" name="exam_score[<?=$row['id']?>]" class="form-control" value="<?=htmlentities($post['exam_score'][$row['id']]??'',ENT_QUOTES)?>"><span style="color: red; font-size: .8em;"><?= $error['exam_score'][$row['id']]??'' ?></span></td>
    
    	</tr>
    	<?php
    }
    ?>
    <tr>
    <td></td><td></td>
    <td colspan="2"><button type="submit" class="btn btn-primary w-50">Save</button></td>
    </tr>
    </tbody>
    </table>
    </form>
    </body>
    </html>

    this produces valid markup, validation per field, and repopulates the field values upon a validation error.

    I really learnt new stuffs from your list of to dos... and your code. thank you

  20. 4 hours ago, mac_gyver said:

    if you are not posting the actual code that you are having a problem with, the replies you get may not have anything to do with the problem.

    here's example code (tested with faked data from the SELECT query) showing most of the points that i made -

    <?php
    
    // initialization
    
    // recursive trim function
    function _trim($val)
    {
    	if(is_array($val))
    	{
    		return array_map('_trim',$val);
    	} else {
    		return trim($val);
    	}
    }
    
    session_start();
    
    // make database connection here...
    
    $table_name = 'your_table';
    
    $error = []; // an array to hold user/validation errors
    $post = []; // an array to hold a trimmed working copy of the form data
    
    // get all subject data
    $sql = "SELECT id, subject_name FROM tbl_subjects_secondary";
    $stmt=$pdo->query($sql);
    $subject_data = $stmt->fetchAll();
    
    
    // post method form processing
    if($_SERVER['REQUEST_METHOD'] === 'POST')
    {
    	// trim all the input data at once
    	$post = _trim($_POST);
    	
    	// loop over the subject ids and validate the corresponding form data
    	foreach(array_column($subject_data,'id') as $key)
    	{
    		if($post['test_score'][$key] === '')
    		{
    			$error['test_score'][$key] = "Test Score Field is Required";
    		}
    		if($post['exam_score'][$key] === '')
    		{
    			$error['exam_score'][$key] = "Exam Score Field is Required";
    		}
    	}
    	
    	// if no errors, use the submitted data
    	if(empty($error))
    	{
    		$sql = "INSERT INTO $table_name (
    		subject_id,
    		test_score,
    		exam_score
    		) VALUES (
    		?,
    		?,
    		?
    		)";
    		$stmt = $pdo->prepare($sql);
    
    		foreach(array_column($subject_data,'id') as $key)
    		{
    			$stmt->execute([
    				$key,
    				$post['test_score'][$key],
    				$post['exam_score'][$key]
    			]);
    			
    			// note: error handling for this query is not included with this example code
    		}
    	}
    	
    	// if no errors, success
    	if(empty($error))
    	{
    		// if you want to display a one-time success message, store it in a session variable, then test, display, and clear that variable in the html document
    		$_SESSION['success_message'] = 'You have successfully inserted the subject scores';
    		// redirect to the exact same url of the current page to cause a get request - PRG Post, Redirect, Get.
    		die(header("Refresh:0"));
    	}
    	
    }
    
    // get method business logic - get/produce data needed to display the page
    
    // get all subject data - located above the post method form processing since it is also used by the validation logic
    
    
    // html document
    ?>
    <!DOCTYPE html>
    <html lang="en-US">
    	<head>
    		<meta charset="utf-8">
    		<title>Multi-row Insert Example</title>
    	</head>
    	<body>
    
    <?php
    // display and clear any success message
    if(isset($_SESSION['success_message']))
    {
    	echo '<p>'.$_SESSION['success_message'].'</p>';;
    	unset($_SESSION['success_message']);
    }
    ?>
    
    <?php
    // display the form
    ?>
    <form method="post">
    <table class="table table-borderless">
    <thead>
    <tr>
    <th>SN</th>
    <th>Subject</th>
    <th>Continuous Assessment Score</th>
    <th>Examination Score</th>
    </tr>
    </thead>
    <tbody>
    <?php
    $i = 1;
    foreach($subject_data as $row)
    {
    	?>
    	<tr>
    	<th><?= $i++ ?></th>
    	<td><input type="text" class="form-control border-0" readonly value="<?=$row['subject_name']?>"></td>
    	<td><input type="number" name="test_score[<?=$row['id']?>]" class="form-control" value="<?=htmlentities($post['test_score'][$row['id']]??'',ENT_QUOTES)?>"><span style="color: red; font-size: .8em;"><?= $error['test_score'][$row['id']]??'' ?></span></td>
    	<td><input type="number" name="exam_score[<?=$row['id']?>]" class="form-control" value="<?=htmlentities($post['exam_score'][$row['id']]??'',ENT_QUOTES)?>"><span style="color: red; font-size: .8em;"><?= $error['exam_score'][$row['id']]??'' ?></span></td>
    
    	</tr>
    	<?php
    }
    ?>
    <tr>
    <td></td><td></td>
    <td colspan="2"><button type="submit" class="btn btn-primary w-50">Save</button></td>
    </tr>
    </tbody>
    </table>
    </form>
    </body>
    </html>

    this produces valid markup, validation per field, and repopulates the field values upon a validation error.

    Thank you so much. I will build on yours.

  21. I guess i'd have to change the way the form fields are displayed. instead of getting the subjects from the database, i guess i will just create them in a form, with this way, the validation will/should work! still beats me why it cant validate though!

    Thanks all

  22. 21 hours ago, mac_gyver said:

    here's my typical laundry list of points for the posted code -

    1. you should not attempt to detect if a submit button is set. there are cases where it won't be. just detect if a post method form was submitted.
    2. you should trim all input data before validating it.
    3. you should validate all inputs separately, storing validation errors in an array using the field name as the main array index. since you have a set of inputs for each field name, you would also use the $key as a second index when storing validation errors.
    4. by validating the inputs separately, you can eventually get your logic attempting to display the errors adjacent to the corresponding field to work.
    5. after the end of the validation logic, if there are no errors (the $error array will be empty), use the submitted data.
    6. $table_name - if you are creating a bunch of different tables to hold this data, you should instead have one table with a column that identifies what is different about each set of data.
    7. the subject column in the table holding the submitted data should be the subject id, not the subject name.
    8. you should prepare the query once, before the start of the looping.
    9. it will be a poor user experience if you produce an alert for every row that gets inserted. you should instead store any result message in an array using the $key as the array index, then test/use the contents of this array, after the end of the looping.
    10. for this query, the data will be inserted unless there is a query error. most query errors are due to programming mistakes that won't occur once you have tested and debugging your application. you should use exceptions for database statement error handling. this is now the default  setting in php8+. if this query can produce errors due to duplicate or out of range user submitted values, you would catch the database exception from it, test if the error number is for something that your code is handling, and setup error messages (add to the $error array) letting the user know what was wrong with the data that they submitted. for all other error numbers, just rethrow the exception and let php handle it.
    11. since this data is a set, this INSERT query should be part of a transaction, that will get rolled back if there are any errors for any of the inserts.
    12. you need to validate the resulting web pages at validator.w3.org, e.g. you can put a html table inside a form and you can put a form inside a single html table cell, but you cannot spread a form out inside a html table. an empty action='' attribute is not valid html5. just leave the action attribute out to get the form to submit to the same page it is on.
    13. the code producing the output is using 'subject_name', not 'subjects' and you would not test if it isset() to echo it, since this is the whole point of this code. it must be set or you have a programming mistake somewhere.
    14. there should be a subject id, and this should be used when storing the submitted data. you would either have a separate hidden array field for the ids or you would use the ids as the index for the existing array fields.
    15. if you set the default fetch mode to assoc when you make the database connection, you won't have to specify it in each fetch statement.

     

    Thanks. In the code proper, i did validation for the inputs. i did not include the validation while posting. some things you mention are done in the code proper but overall i learnt some interesting things from your list. thanks again

×
×
  • 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.