Jump to content

imgrooot

Members
  • Posts

    383
  • Joined

  • Last visited

  • Days Won

    1

Everything posted by imgrooot

  1. Do you mean like this? $entries = 10; $id = 55; $name = 'Smith'; $stmt = $db->prepare("INSERT INTO entries(id, name) VALUES(:id, :name)"); $stmt->bindParam(':id', $id); $stmt->bindParam(':user_id', $name); for($i = 1; $i <= $entries; $i++) { $result_insert = $stmt->execute(); if($result_insert == false) { $errors[] = 'There was a problem!'; } }
  2. I have my reason to use an empty form form to submit outside data. I was not asking advice on that. I simply wanted to know how to insert the same query multiple times. Below is the updated query that does not. It seems to work fine, unless you think other wise. $entries = 10; $id = 55; $name = 'Smith'; $stmt = $db->prepare("INSERT INTO entries(id, name) VALUES(:id, :name)"); for($i = 1; $i <= $entries; $i++) { $stmt->bindParam(':id', $id); $stmt->bindParam(':user_id', $name); $result_insert = $stmt->execute(); } if($result_insert == false) { $errors[] = 'There was a problem!'; }
  3. Say I have an "Entries" table. I want to submit same multiple entries using a form submission. And If I have other queries submitted in the same form, I want those quarries to be submitted only once. Is that possible to do? Here's my code. if(isset($_POST['submit'])) { $entries = 10; $id = 55; $name = 'Smith'; $insert = $db->prepare("INSERT INTO entries(id, name) VALUES(:id, :name)"); $insert->bindParam(':id', $id); $insert->bindParam(':name', $name); $result_insert = $insert->execute(); if($result_insert == false) { echo 'Fail'; } else { echo 'Success'; } } ?> <form action="" method="post"> <input type="submit" name="submit" value="SUBMIT" /> </form>
  4. Here's the code for the countdown timer. It works. At the end of the countdown, it's suppose to show the message "EXPIRED". But that message only shows once I reload the page. The countdown itself stops at 00:00:01. Is there a way to automatically show the message after that, instead of reloading the page to show it? <style> div#counter{ margin: 100px auto; width: 305px; padding:20px; border:1px solid #000000; } div#counter span{ background-color: #00CAF6; padding:5px; margin:1px; font-size:30px; } </style> <?php $target_date = '2019-01-12 05:40:00'; $timeLeft = (strtotime($target_date) - time()) * 1000; ?> <script src="javascripts/timer.js"></script> <script> $(document).ready(function(){ var timeLeft = <?php echo $timeLeft ; ?>; var timer = new Timer($('#counter'), timeLeft); if (timeLeft <= 0) { $('#counter').text('EXPIRED'); } }); </script> <div id="counter"> <span class="hour">00</span> <span class="min">00</span> <span class="sec">00</span> </div>
  5. Correct, the multiplication would do the reverse. Like this. function convertToSatoshi($value) { $SAT = $value * 100000000 ; return $SAT; } I was more worried about reversing the decimal part of the 2nd function, but I just realized that it's not needed to convert to Satoshie.
  6. Apparently it's bad to use floats when working with bitcoin. But the API i'm using doesn't really give an option to convert Bitcoin to Satoshie or vice versa. https://www.block.io/api/simple/php So I'm trying to figure out a way to do that function with PHP. Here is the code I found that converts from Satoshie to Bitcoin. My question is, how do I convert from Bitcoin to Satoshie. function convertToBTCFromSatoshi($value) { $BTC = $value / 100000000 ; return $BTC; } function formatBTC($value) { $value = sprintf('%.8f', $value); $value = rtrim($value, '0') . ' BTC'; return $value; } echo formatBTC(convertToBTCFromSatoshi(5000));
  7. I see. But no, deposits and withdrawals will never have a value less than 0.
  8. You mean if any of the users have an earning balance in the negative (e.g. -100)? No I don't think so. Users are never able to withdraw more than their balance. But there is more than one type of withdrawal and deposit and I only needed to use the two I listed.
  9. Yep, UNION ALL does the trick. Works perfectly now. Thank you so much! Here is the updated code. $listed_amount = 1000; $sql = "SELECT u.username , SUM(amount) as total FROM users u INNER JOIN ( SELECT sent_to as user_id , deposit as amount FROM earnings WHERE e_type = 1 AND status = 1 AND principal = 1 UNION ALL SELECT sent_to , -withdrawal FROM earnings WHERE e_type = 2 AND status = 1 AND principal = 0 ) tot USING (user_id) WHERE u.ref = 1 GROUP BY u.user_id HAVING total >= :listed"; $stmt = $db->prepare($sql); $stmt->bindParam(':listed', $listed_amount); $stmt->execute(); $result_stmt = $stmt->fetchAll(PDO::FETCH_ASSOC); if(count($result_stmt) > 0) { foreach($result_stmt as $row) { $get_username = $row['username']; $earning_balance = $row['total']; ?> <div> <div><?php echo $get_username; ?></div> <div><?php echo $earning_balance; ?></div> </div> <?php } } $is_true = $stmt->rowCount() > 0; if($is_true > 0) { echo 'yes'; } else { echo 'no'; }
  10. Well the e_type and principal columns are a means to distinguish the time of payments are in the table. I did double check with my original code. It seems like the issue is with the UNION part of your query. I removed the union and just retrieved the "deposit" and the number is correct. I also checked the "withdrawal" amount by replacing the deposit and that number is also correct. So the issue is the subtracting the withdrawal from the deposit through the UNION. Something is wrong with that. Are you sure it's correct?
  11. Good to know. Thanks to you I am learning more about these joins and simplifying my queries. As per your example above, it does work. The only thing is that I am getting different earning balance results from my original queries. Here is the new code based on your example. Can you check if it's correct? Also I take it "-withdrawal" is suppose to subtract from the "deposit"? $listed_amount = 1000; $sql = "SELECT u.username , SUM(amount) as total FROM users u LEFT JOIN ( SELECT sent_to as user_id , deposit as amount FROM earnings WHERE e_type = 1 AND status = 1 AND principal = 1 UNION SELECT sent_to , -withdrawal FROM earnings WHERE e_type = 2 AND status = 1 AND principal = 0 ) tot USING (user_id) WHERE u.ref = 1 GROUP BY u.user_id HAVING total >= :listed"; $stmt = $db->prepare($sql); $stmt->bindParam(':listed', $listed_amount); $stmt->execute(); $result_stmt = $stmt->fetchAll(PDO::FETCH_ASSOC); if(count($result_stmt) > 0) { foreach($result_stmt as $row) { $get_username = $row['username']; $earning_balance = $row['total']; ?> <div> <div><?php echo $get_username; ?></div> <div><?php echo $earning_balance; ?></div> </div> <?php } } $is_true = $stmt->rowCount() > 0; if($is_true > 0) { echo 'yes'; } else { echo 'no'; }
  12. So I two tables. Users and earnings. I retrieve earning balance of each user. This works fine. But I also want to find out outside of the foreach loop if any of the earning balance inside the loop is greater than the listed amount. For e.g. Say there are 10 users. The listed amount is $1,000. Only 1 of the 10 users have an earning balance of greater than $1,000. I want the statement to return true under that condition. It could even be 10/10 users that have a greater earning balance. As long as at least one of them have it, it should return true. Here is my code. How do I fix the "is_true" part to get the results I want? $get_members = $db->prepare("SELECT user_id, user_name FROM users"); $get_members->execute(); $result_members = $get_members->fetchAll(PDO::FETCH_ASSOC); if(count($result_members) > 0) { foreach($result_members as $row) { $get_user_id = $row['user_id']; $get_username = $row['username']; $get_principal = $db->prepare("SELECT deposit FROM earnings WHERE e_type = :e_type AND sent_to = :sent_to AND principal = :principal"); $get_principal->bindValue(':e_type', 1); $get_principal->bindParam(':sent_to', $get_user_id); $get_principal->bindValue(':principal', 1); $get_principal->execute(); $result_principal = $get_principal->fetchAll(PDO::FETCH_ASSOC); if(count($result_principal) > 0) { $ub = 0; foreach($result_principal as $key=>$row) { $ub+= $row['deposit']; } $p_deposit = $ub; } else { $p_deposit = 0; } $get_withdrawal = $db->prepare("SELECT withdrawal FROM earnings WHERE e_type = :e_type AND sent_to = :sent_to AND principal = :principal"); $get_withdrawal->bindValue(':e_type', 2); $get_withdrawal->bindParam(':sent_to', $get_user_id); $get_withdrawal->bindValue(':principal', 0); $get_withdrawal->execute(); $result_withdrawal = $get_withdrawal->fetchAll(PDO::FETCH_ASSOC); if(count($result_withdrawal) > 0) { $ub = 0; foreach($result_withdrawal as $key=>$row) { $ub+= $row['withdrawal']; } $t_principal = $ub; } else { $t_principal = 0; } $earning_balance = number_format($p_deposit - $t_principal); ?> <div> <div><?php echo $get_username; ?></div> <div><?php echo $earning_balance; ?></div> </div> <?php if($earning_balance > 1000) { $is_true = 1; } else { $is_true = 0; } } } if($is_true == 1) { echo 'yes'; } else { echo 'no'; }
  13. Unfortunately I have to keep the two earning types as separate. Otherwise it would be a very messy table. I can retrieve the individual earnings and combine them for each campaign listed. But the issue I have is that I need to be able to sort them from highest to lowest amount. This is a more simpler code but I have no clue on sorting them from highest to lowest, aside from from the method "Barand" mentioned. $find_campaign = $db->prepare("SELECT campaigns.campaign_id, member_earnings.deposit, guest_earnings.deposit FROM campaigns LEFT JOIN member_earnings ON campaigns.campaign_id = member_earnings.campaign_id LEFT JOIN guest_earnings ON campaigns.campaign_id = guest_earnings.campaign_id GROUP BY campaigns.campaign_id ORDER BY campaigns.campaign_id DESC LIMIT 20"); $find_campaign->execute(); $result_find_campaign = $find_campaign->fetchAll(PDO::FETCH_ASSOC); if(count($result_find_campaign) > 0) { foreach($result_find_campaign as $row) { $campaign_id = $row['campaign_id']; $get_raised_1 = $db->prepare("SELECT deposit FROM member_earnings WHERE campaign_id = :campaign_id"); $get_raised_1->bindParam(':campaign_id', $campaign_id); $get_raised_1->execute(); $result_raised_1 = $get_raised_1->fetchAll(PDO::FETCH_ASSOC); if(count($result_raised_1) > 0) { $ub = 0; foreach($result_raised_1 as $key=>$row) { $member_raised+= $row['deposit']; } } $get_raised_2 = $db->prepare("SELECT deposit FROM guest_earnings WHERE campaign_id = :campaign_id"); $get_raised_2->bindParam(':campaign_id', $campaign_id); $get_raised_2->execute(); $result_raised_2 = $get_raised_2->fetchAll(PDO::FETCH_ASSOC); if(count($result_raised_2) > 0) { $ub = 0; foreach($result_raised_2 as $key=>$row) { $guest_raised+= $row['deposit']; } } $campaign_raised = $member_raised + $guest_raised; echo $campaign_raised; } }
  14. Was away for few days. I'm back now. I changed it to what you wrote but still get the same error as before. Which is this. Fatal error: Uncaught PDOException: SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'campaign_id' in from clause is ambiguous in
  15. I meant to have it WHERE id > 1. I didn't want to show the first record. As per your example, I tried my best to replicate it using my query but so far no success. Here's the new query. Can you please fix what's wrong with it? $get_raised = $db->prepare("SELECT member_earnings.campaign_id, SUM(member_earnings.deposit) AS total_e, guest_earnings.deposit AS total_g FROM campaigns INNER JOIN member_earnings ON campaigns.campaign_id = member_earnings.campaign_id JOIN (SELECT campaign_id, SUM(deposit) as total FROM guest_earnings GROUP BY campaign_id) btots USING (campaign_id) WHERE campaigns.campaign_id > :campaign_id GROUP BY campaigns.campaign_id ORDER BY btots DESC"); $get_raised->bindValue(':campaign_id', 1); $get_raised->execute(); $result_raised = $get_raised->fetchAll(PDO::FETCH_ASSOC); if(count($result_raised) > 0) { foreach($result_raised as $row) { $total_m = $row['total_m']; $total_g = $row['total_g']; $grand_total = $row['btots']; $total_earnings = $grand_total; } }
  16. So I am trying to retrieve earnings of a campaign from two different tables and order them from highest to lowest. It works fine if I do it from a single table "member_earnings". $get_raised = $db->prepare("SELECT SUM(member_earnings.deposit) AS total_m, campaigns.campaign_id FROM campaigns INNER JOIN member_earnings ON campaigns.campaign_id = member_earnings.campaign_id WHERE campaigns.campaign_id > :campaign_id GROUP BY campaigns.campaign_id ORDER BY total_m DESC"); $get_raised->bindValue(':campaign_id', 0); $get_raised->execute(); $result_raised = $get_raised->fetchAll(PDO::FETCH_ASSOC); if(count($result_raised) > 0) { foreach($result_raised as $row) { $total_m = $row['total_m']; $total_earnings = $total_m; } } Here is my code when add another table "guest earnings". I don't get any errors. But the amount I receive from guest earnings quadruples the original amount. Also in this scenario, how would I ORDER BY considering I am retrieving numbers from two different tables? I can't use "total_m" or "total_g". Anyway to combine the two? $get_raised = $db->prepare("SELECT SUM(member_earnings.deposit) AS total_m, SUM(guest_earnings.deposit) AS total_g, campaigns.campaign_id FROM campaigns INNER JOIN member_earnings ON campaigns.campaign_id = member_earnings.campaign_id INNER JOIN guest_earnings ON campaigns.campaign_id = guest_earnings.campaign_id WHERE campaigns.campaign_id > :campaign_id GROUP BY campaigns.campaign_id ORDER BY total_m DESC"); $get_raised->bindValue(':campaign_id', 0); $get_raised->execute(); $result_raised = $get_raised->fetchAll(PDO::FETCH_ASSOC); if(count($result_raised) > 0) { foreach($result_raised as $row) { $total_m = $row['total_m']; $total_g = $row['total_g']; $total_earnings = $total_m + $total_g; } }
  17. Should * never be used even if I am retrieving all the columns in that table? So I edited the query using all three of your points above. The load time has decreased from 5 seconds down to 1-2 seconds. It's not instantaneous but much better.
  18. I am retrieving data from multiple joined tables that contain around 3,500 rows each. I am only showing limited results but the load time is the same. Like it takes 5 seconds to load the page. I don't want this same issue to continue if I am predicting over 1,000,000 rows from these tables. So what's the best way to fix this? Here is my query. $get_members = $db->prepare("SELECT users.*, approvals.*, sponsors.* FROM approvals LEFT JOIN users ON approvals.user_id = users.user_id LEFT JOIN sponsors ON approvals.user_id = sponsors.sponsored_user WHERE user_id > :user_id ORDER BY user_id DESC LIMIT 20"); $get_members->bindValue(':user_id', 0); $get_members->execute(); $result_members = $get_members->fetchAll(PDO::FETCH_ASSOC); if(count($result_members) > 0) { foreach($result_members as $row) { // show results here } } else { echo 'none'; } if(count($result_members) < 20) {} else { // show-more button here }
  19. Based on what you wrote, here is my new query. Seems to work. Need to do more testing. What you think? $get_raised = $db->prepare("SELECT SUM(deposit) AS total, campaigns.campaign_id FROM campaigns LEFT JOIN member_earnings ON campaigns.campaign_id = member_earnings.campaign_id WHERE campaigns.campaign_id > :campaign_id GROUP BY campaigns.campaign_id ORDER BY total DESC"); $get_raised->bindValue(':campaign_id', 0); $get_raised->execute(); $result_raised = $get_raised->fetchAll(PDO::FETCH_ASSOC); if(count($result_raised) > 0) { foreach($result_raised as $row) { $campaign_id = trim($row['campaign_id']); $campaign_raised = trim($row['total']); ?> <div> <?php echo $campaign_id; ?><br> $<?php echo $campaign_raised; ?> </div> <br> <?php } } else { echo 'no'; }
  20. I kind of understand what you are saying but I'm a little lost on how to integrate it into my code above. I have two queries as you can see. Can you please modify them according to your edit? That would really help. And the "trim" part is a habit. I know I shouldn't use it.
  21. My bad, I forgot a small detail. Check it out below. The earnings table have multiple rows for a single campaign id. So I don't think simply joining two tables would work. Unless if you still think otherwise, then it would be great if you can show it. $find_campaign = $db->prepare("SELECT * FROM campaigns WHERE status = :status ORDER BY campaign_id DESC LIMIT 10"); $find_campaign->bindValue(':status', 1); $find_campaign->execute(); $result_find_campaign = $find_campaign->fetchAll(PDO::FETCH_ASSOC); if(count($result_find_campaign) > 0) { foreach($result_find_campaign as $row) { $campaign_id = trim($row['campaign_id']); $campaign_goal = trim($row['campaign_goal']); $get_raised = $db->prepare("SELECT deposit FROM earnings WHERE campaign_id = :campaign_id"); $get_raised->bindParam(':campaign_id', $campaign_id); $get_raised->execute(); $result_raised = $get_raised->fetchAll(PDO::FETCH_ASSOC); if(count($result_raised) > 0) { $campaign_raised = 0; foreach($result_raised as $key=>$row) { $campaign_raised += trim($row['deposit']); } } else { $campaign_raised = 0; } //show campaign html here } }
  22. I have two tables. Table-1 is Campaigns and Table-2 is Earnings. I simply want to list the Campaigns from high to low earnings or vice versa. But I don't know how to do that since it's two separate tables. Here are the two queries I have. The query shows that the Campaigns will be listed by their campaign_id. I want to be able to order them by their earnings. How do I do that with these two queries? $find_campaign = $db->prepare("SELECT * FROM campaigns WHERE status = :status ORDER BY campaign_id DESC LIMIT 10"); $find_campaign->bindValue(':status', 1); $find_campaign->execute(); $result_find_campaign = $find_campaign->fetchAll(PDO::FETCH_ASSOC); if(count($result_find_campaign) > 0) { foreach($result_find_campaign as $row) { $campaign_id = trim($row['campaign_id']); $campaign_goal = trim($row['campaign_goal']); $get_raised = $db->prepare("SELECT deposit FROM earnings WHERE campaign_id = :campaign_id"); $get_raised->bindParam(':campaign_id', $campaign_id); $get_raised->execute(); $result_raised = $get_raised->fetchAll(PDO::FETCH_ASSOC); if(count($result_raised) > 0) { $ub = 0; foreach($result_raised as $key=>$row) { $campaign_raised = trim($row['deposit']); } } else { $campaign_raised = 0; } //show campaign html here } }
  23. This is the code for a dynamic dropdown list which i use to create a record. It works. But i find it slow when updating a record. It takes time to load the cities dropdown data. I am wondering, is there a more efficient way to do this dropdown so it loads the data more quickly? <script> function getCity(val) { $.ajax({ type: "POST", url: "../snippets/get_cities.php", data:'state_id='+val, success: function(data){ $("#city-list").html(data); } }); } function getState(val) { $.ajax({ type: "POST", url: "../snippets/get_states.php", data:'country_id='+val, success: function(data){ $("#state-list").html(data); } }); } function selectCountry(val) { $("#search-box").val(val); $("#suggesstion-box").hide(); } </script> <fieldset> <label>Location:</label><br/> <select name="country" id="country-list" onChange="getState(this.value);"> <option value="">Select Country</option> <?php $get_countries = $db->prepare("SELECT * FROM countries2"); $get_countries->execute(); $result_countries = $get_countries->fetchAll(PDO::FETCH_ASSOC); if(count($result_countries) > 0) { foreach($result_countries as $row) { $get_country_id = trim($row['id']); $get_country_name = trim($row['name']); ?><option value="<?php echo $get_country_id; ?>" <?php if($record_country_id == $get_country_id) { echo 'selected'; }; ?>><?php echo $get_country_name; ?></option><?php } } ?> </select> <select name="state" id="state-list" onChange="getCity(this.value);"> <option value="">Select State/Province</option> <?php $get_states = $db->prepare("SELECT * FROM states"); $get_states->execute(); $result_states = $get_states->fetchAll(PDO::FETCH_ASSOC); if(count($result_states) > 0) { foreach($result_states as $row) { $get_state_id = trim($row['id']); $get_state_name = trim($row['name']); ?><option value="<?php echo $get_state_id; ?>" <?php if($record_state_id == $get_state_id) { echo 'selected'; }; ?>><?php echo $get_state_name; ?></option><?php } } ?> </select> <select name="city" id="city-list"> <option value="">Select City</option> <?php $get_cities = $db->prepare("SELECT * FROM cities"); $get_cities->execute(); $result_cities = $get_cities->fetchAll(PDO::FETCH_ASSOC); if(count($result_cities) > 0) { foreach($result_cities as $row) { $get_city_id = trim($row['id']); $get_city_name = trim($row['name']); ?><option value="<?php echo $get_city_id; ?>" <?php if($record_city_id == $get_city_id) { echo 'selected'; }; ?>><?php echo $get_city_name; ?></option><?php } } ?> </select> </fieldset>
  24. Perfect. That works like a charm. Thank you.
×
×
  • 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.