Jump to content

I-AM-OBODO

Members
  • Posts

    439
  • Joined

  • Last visited

Contact Methods

  • AIM
    funkyfel
  • MSN
    funkyfela
  • Yahoo
    funkyfela
  • Skype
    chidi.james

Profile Information

  • Gender
    Male
  • Location
    Nigeria
  • Interests
    Computer, Playing Video Games, Learning

Recent Profile Visitors

6,938 profile views

I-AM-OBODO's Achievements

Advanced Member

Advanced Member (4/5)

1

Reputation

1

Community Answers

  1. 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. 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. 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
  6. 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
  7. 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
  8. 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
  9. 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!
  10. 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
  11. 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
  12. 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
  13. 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);
  14. Thanks for the addition.
  15. 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!
×
×
  • 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.