imgrooot

January 14, 2019

7 hours ago, Barand said:

You only need to bind once.

You are checking only the last insert

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

January 14, 2019

10 hours ago, ginerjm said:

I see a form with no inputs, just a button to submit. Then I see a block of code that builds a query and executes it to insert a record with a static id and static name, neither of which comes from any form. And it happens just once.

Despite your poorly written post I'm concerned that you have a poorly designed methodology here as well. And where are these other "quarries" (a place to find rocks?) that you want to have executed as well?

From your post I half-expected some kind of input form with values that you then wanted to repeatedly post to a database using a unique key for each. Quite an easy exercise. That was not what you presented.

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

January 13, 2019

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>

January 11, 2019

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>

January 10, 2019

2 hours ago, requinix said:
This is what converts.
function convertToBTCFromSatoshi($value) {
    $BTC = $value / 100000000 ;
    return $BTC;
}
Do I need to say anything more?

2 hours ago, ginerjm said:

I may regret asking this but - are you sure about that? Is going from a to b involves division, wouldn't the reverse involve multiplication?

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.

January 9, 2019

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

June 4, 2018

5 hours ago, Barand said:
I didn't mean the balances. I was asking if any the values that you are totalling (deposits or withdrawal values) have a negative value. Are there any deposits of -X or any withdrawals of -Y?

Does this find any?
SELECT COUNT(*)
FROM earnings
WHERE (deposit < 0) OR (withdrawal < 0) 

I see. But no, deposits and withdrawals will never have a value less than 0.

June 4, 2018

2 hours ago, Barand said:

Does your data have any deposit or withdrawal values that are negative in the earnings table?

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.

June 4, 2018

2 hours ago, Barand said:

try changing "UNION" to "UNION ALL" in case there are deposits and withdrawals for the same amounts.

Also, LEFT JOIN needs only be an INNER JOIN.

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

June 3, 2018

32 minutes ago, Barand said:

All I can say is my query was correct when run against my test tables withe given criteria. I cannot verify that yours is correct without

your data

knowing the business requirement and conditions (such as the meaning of values in e_type and pricipal columns)

knowing the predicted results from that data

But you should be able to.

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?

June 3, 2018

10 hours ago, Barand said:
Avoid running queries inside loops - it drains resources. Use joins instead and run a single query. The loop then becomes unnecessary.

If you must run prepared queries inside loops do not prepare the statement and bind inside the loop. Do those first. Inside the loop you just change the values and execute.
$listed_amount = 1000;

$sql = "SELECT u.username
             , SUM(amount) as total
        FROM user u 
            LEFT JOIN
            (
                SELECT sent_to as user_id
                     , deposit as amount
                FROM earnings
                WHERE e_type = 1 AND principal = 1
                UNION
                SELECT sent_to
                     , -withdrawal
                FROM earnings
                WHERE e_type = 2 AND principal = 0
            ) tot USING (user_id)
        GROUP BY u.user_id
        HAVING total >= :listed";
$stmt = $db->prepare($sql);
$stmt->execute( [':listed' => $listed_amount] );

$is_true = $stmt->rowCount() > 0;

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

June 3, 2018

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

May 11, 2018

You could try this alternative approach. (The subquery effectively puts the member and guest earnings into a single temporary table)

SELECT c.campaign_id
     , SUM(CASE etype WHEN 'M' THEN deposit ELSE 0 END) as member_earnings
     , SUM(CASE etype WHEN 'G' THEN deposit ELSE 0 END) as guest_earnings
     , SUM(deposit) as total
FROM
     campaigns c
     LEFT JOIN (
               SELECT campaign_id
                    , deposit
                    , 'M' as etype
               FROM member_earnings
               UNION
               SELECT campaign_id
                    , deposit
                    , 'G' as etype
               FROM guest_earnings
               ) earns USING (campaign_id)
GROUP BY c.campaign_id
ORDER BY total DESC

Holy cow that actually worked. Thanks a million!!!

May 11, 2018

i would store all the earnings in one table, with a member/guest 'type' column (or perhaps you are already storing a value that distinguishes between members and guests?)

if you only want the total per campaign, the query structure you already have would produce the correct result.

if you also want to retrieve and display separate earning values for members and guests, you would just add conditional logic in the SELECT term to SUM() the two 'type' values separately.

if at any point you just want to query for member earnings or for guest earnings, rather than to query against a separate table for each, you would just add the appropriate condition to the WHERE term.

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

May 11, 2018

SELECT ... , guest_earnings.deposit AS total_g ...
should be
SELECT ... , btots.total as total_g ...
Your earnings table has now been condensed into a table subquery (alias btots)

(Mea culpa - I should have put btots.total in my example)

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

May 5, 2018

I see you are still doing the redundant "WHERE id > 0".

If a record in one table matches 4 records then you will get values in that second table added 4 times. The way around it is to use a subquery to get the totals from the second table and join to that subquery. Something like this
SELECT a.id
     , SUM(a.amount) as tot_a
     , b.total as tot_b
FROM tableA a
     JOIN (
          SELECT id
               , SUM(amount) as total
          FROM tableB
          GROUP BY id
          ) btots  USING (id)
GROUP BY id 

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

May 5, 2018

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

May 3, 2018

Don't use user.* etc. Specify just the columns you actually need.

Left JOINS are slow. Unless you really need them, use INNER JOIN instead.

Make sure you have indexes specified on approvals.user_id and sponsors.sponsored_user

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.

May 3, 2018

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
}

April 26, 2018

the following is the SELECT query syntax definition, with the relevant parts in red -

the FROM table_references part is where any JOIN tables and join conditions go. JOINs (there are a handful of types, which you can research on the web or in the mysql database documentation to find out about) are used to get data from related tables or even within the same table. the two tables you have are related through the campaign_id columns. if you only want results that have entries in both tables (you can join as many tables as needed), you would use just a JOIN. if you want results from the 1st table, regardless of any entries in the 2nd table, you would use a LEFT JOIN (this would correspond to having a campaign without any earnings row(s)).

i recommend that you copy the relevant parts from the syntax definition above and try to fill in the information based on your tables. one thing that will help simplify the query is to use alias names for the tables. you can use alias names of 'c' for the campaigns table, and 'e' for the earnings table. the alias names are defined when you list the tables names in the FROM table_references part of the query. any place you reference a column in the query, use the table_alias_name.column (even when the table part is not necessary.) this will serve to help document what the query is trying to accomplish and force you to reference the columns from the correct table (important if you have same name columns in different tables with different meanings.)

to get you started, here is the FROM table_references part for your tables using a LEFT JOIN -
FROM campaigns AS c LEFT JOIN earnings AS e ON c.campaign_id = e.campaign_id
note: the AS keyword is optional (once you understand aliases, you can leave the AS out). when you have the same name columns in both tables and you are only matching column values, with no other conditions, instead of the ON keyword syntax shown, you can use USING(campaign_id)

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

April 26, 2018

you would add SUM(deposit) AS total (the AS keyword is optional) to the SELECT term, add GROUP BY campaign_id after the WHERE status ... term, and use ORDER BY total DESC

btw - any trimming of the data should have occurred before it was inserted/updated, not when it is retrieved and unless you are dynamically inputting the status value, you might as well just put a literal 1 in the query or create a VIEW that only references data with status = 1.

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.

April 25, 2018

JOIN the two tables using the campaign_id columns. You can then select the columns you need from both tables in a single query and ORDER BY deposit DESC

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

April 25, 2018

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

January 9, 2018

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>

January 7, 2018

If you join the same table multiple times, you need to give the joins an alias so you can distinguish between them.

SELECT sentBy.*, guest.*, member_earnings.* 
FROM member_earnings
LEFT JOIN users as sentBy ON member_earnings.sent_by = sentBy.user_id
LEFT JOIN users as guest ON member_earnings.guest_id = guest.user_id
WHERE record_id = :record_id AND status = :status 
ORDER BY earning_id DESC LIMIT 20

Perfect. That works like a charm. Thank you.

Sign In

imgrooot

Posts

Joined

Last visited

Days Won

Content Type

Profiles

Forums

Posts posted by imgrooot

Is there a way to submit same entry multiple times on a single form submit?

Is there a way to submit same entry multiple times on a single form submit?

Is there a way to submit same entry multiple times on a single form submit?

Need a bit of help with the countdown timer.

Is there a way to convert Bitcoin to Satoshie and vice versa?

Is there a way to convert Bitcoin to Satoshie and vice versa?

Need help finding if the statement is true or not inside foreach loop

Need help finding if the statement is true or not inside foreach loop

Need help finding if the statement is true or not inside foreach loop

Need help finding if the statement is true or not inside foreach loop

Need help finding if the statement is true or not inside foreach loop

Need help finding if the statement is true or not inside foreach loop

How would you use SUM twice in a single query from two separate tables?

How would you use SUM twice in a single query from two separate tables?

How would you use SUM twice in a single query from two separate tables?

How would you use SUM twice in a single query from two separate tables?

How would you use SUM twice in a single query from two separate tables?

I am noticing major load time in retrieving results from large tables

I am noticing major load time in retrieving results from large tables

Is there a proper way to do this query?

Is there a proper way to do this query?

Is there a proper way to do this query?

Is there a proper way to do this query?

Is there a proper way to create a dynamic countries/states/cities dropdown so it doesn't slow down?

How would you join two columns in the same table?

Browse

Activity

Important Information