I-AM-OBODO

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 😊

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.

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>'; }

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

The duplicate names I have addressed. All that's left is to know if the minus could be done in the query. Other than that, it's working just fine. Thanks a million times

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

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

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

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!

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

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

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

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);

Thanks for the addition.

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!

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

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

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

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

It's always best if you create a folder to store the files and then you save the path in the database.

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

Thank you so much. I will build on yours.

I didn't. I did just as he suggested but got same result.

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

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