Page 1 of 1

php while loop with two conditions

Posted: Mon Mar 10, 2014 6:03 am
by jonnyfortis
i have two queries

the first one

Code: Select all

mysql_select_db($database_hostprop, $hostprop);
$query_rsPayment = "SELECT payment_id, payment_userid, payment_paid_timestamp, payment_amount_paid, payment_type, payment_transaction_status FROM host_payments WHERE host_payments.payment_created_timestamp LIKE '%2014%' ORDER BY payment_id DESC";
$rsPayment = mysql_query($query_rsPayment, $hostprop) or die(mysql_error());
$row_rsPayment = mysql_fetch_assoc($rsPayment);
$totalRows_rsPayment = mysql_num_rows($rsPayment);


$totalWithoutFees = 0;
$totalWithFees = 0;
and

Code: Select all

			  $weeksUserID = $row_rsPayment['payment_userid'];
			  mysql_select_db($database_hostprop, $hostprop);
$query_rsWeeks = "SELECT * FROM plus_signup WHERE userid = '$weeksUserID'";
$rsWeeks = mysql_query($query_rsWeeks, $hostprop) or die(mysql_error());
$row_rsWeeks = mysql_fetch_assoc($rsWeeks);
$totalRows_rsWeeks = mysql_num_rows($rsWeeks);
i have a table which i have simplied and it needs to show records from both queries.

Code: Select all

            <?php do { 
	$paid = $row_rsPayment['payment_amount_paid'];		
	$fees = 21;
	$totalLessFees = $paid - $fees;
	$row_rsPayment['payment_type'];
	$row_rsWeeks['prop_id'];
			?>
<table border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td>ID</td>
    <td>Customer Name</td>
    <td>Prop ID</td>
    <td>Amount </td>
  </tr>
  <tr>
    <td><?php echo $row_rsPayment['payment_id']; ?></td>
    <td><?php echo $row_rsPayment['payment_userid']; ?></td>
    <td><?php echo $row_rsWeeks['prop_id']; ?></td>
    <td><?php echo DoFormatCurrency($totalLessFees, 2, ',', '.', '£ ') ?></td>
  </tr>
</table>
  <?php 
  	$totalWithoutFees += $totalLessFees;
	$totalWithFees += $row_rsPayment['payment_amount_paid'];
	$totalFees += $lessFees;
  } while (($row_rsPayment = mysql_fetch_assoc($rsPayment)) && ($row_rsWeeks = mysql_fetch_assoc($rsWeeks))); ?>
i have tried

Code: Select all

 } while (($row_rsPayment = mysql_fetch_assoc($rsPayment)) && ($row_rsWeeks = mysql_fetch_assoc($rsWeeks)));
to include both queries but only a single results is being echoed

thanks in advance

Re: php while loop with two conditions

Posted: Mon Mar 10, 2014 6:11 am
by Celauran
Can't you use a single query using a JOIN?

Re: php while loop with two conditions

Posted: Mon Mar 10, 2014 6:14 am
by jonnyfortis
Celauran wrote:Can't you use a single query using a JOIN?
can i do it with a join? can it not be done how i have tried?

Re: php while loop with two conditions

Posted: Mon Mar 10, 2014 6:27 am
by Celauran
Your approach is effectively trying to create a JOIN after the fact. I'd definitely go with one query over two.

Re: php while loop with two conditions

Posted: Mon Mar 10, 2014 6:32 am
by jonnyfortis
Celauran wrote:Your approach is effectively trying to create a JOIN after the fact. I'd definitely go with one query over two.
ok i will see how to do this and let you know
thanks

Re: php while loop with two conditions

Posted: Mon Mar 10, 2014 6:45 am
by jonnyfortis
Celauran wrote:Your approach is effectively trying to create a JOIN after the fact. I'd definitely go with one query over two.
#

ok done it like this

Code: Select all

mysql_select_db($database_hostprop, $hostprop);
$query_rsPayment = "SELECT * FROM host_payments, plus_signup WHERE host_payments.payment_created_timestamp LIKE '%2014%' AND plus_signup.userid = host_payments.payment_userid ORDER BY host_payments.payment_id DESC";
$rsPayment = mysql_query($query_rsPayment, $hostprop) or die(mysql_error());
$row_rsPayment = mysql_fetch_assoc($rsPayment);
$totalRows_rsPayment = mysql_num_rows($rsPayment);
thanks for your advise

Re: php while loop with two conditions

Posted: Mon Mar 10, 2014 6:48 am
by Celauran
Glad you were able to get it working.

Code: Select all

SELECT * FROM host_payments, plus_signup
You should consider revising this to select only the columns you actually need rather than fetching everything from both tables.

Re: php while loop with two conditions

Posted: Mon Mar 10, 2014 6:55 am
by jonnyfortis
ok , is there a reason for this?

Re: php while loop with two conditions

Posted: Mon Mar 10, 2014 7:00 am
by Celauran
Efficiency, of course; don't fetch 20 columns if you need 3. By not specifying columns, changes to the DB that should break the application will go completely undetected and continue happily feeding you bad information.

Re: php while loop with two conditions

Posted: Mon Mar 10, 2014 7:03 am
by jonnyfortis
Celauran wrote:Efficiency, of course; don't fetch 20 columns if you need 3. By not specifying columns, changes to the DB that should break the application will go completely undetected and continue happily feeding you bad information.
ok will do thanks

Re: php while loop with two conditions

Posted: Wed Mar 12, 2014 9:08 am
by Celauran
In theory, sure. What is it you're trying to do? Why wouldn't two separate loops work?