Page 1 of 1

Select statement How to use two select in one phpfile

Posted: Mon Jul 06, 2009 11:51 am
by peter162in
I have two php files. one is calender.php which generates calender
the other one is week.php which is below.When I click date in calender.php it passes the date to week.php
my table is in mysql ,name testdb. it contain three columns( date item and price.)
After getting date

date item price

eg.
2009-01-20 xerox paper 90
2009-01-20 transparency cover 12
2009-01-13 xerox paper 100
2009-01-13 transparency cover 14

end result should be

Query should select price as on 2009-01-20 for item xerox paper then take data on 2009-01-13( week before for same item xerox paper) then find the difference.

xerox paper 90( amount as on 2009-01-20) -100 (amount as on 2009-01-20) = 10 ( This I want to find out)


I am not a professional programmer, I learn programming using internet, reading books and asking experts.
MY CODE LOOK LIKE THIS

***** Please use the [codde=php] tag when posting PHP *****

Code: Select all

< php 
 
 
include "dbcon1.php"; 
 
$date1=$_GET['date']; // this is initial date 2009-01-20 
 
 
echo"$date1". "<br />"; 
 
$difference = strtotime ( '-1 week' , strtotime ( $date1 ) ) ; // getting week ago 
 
$date2=date ( 'Y-m-d' , $difference ) . "<br />\n" ; //this is week ago in the required format 2009-01-13 
 
 
 
 
$sql = "SELECT item, price FROM table1 WHERE (((table1.date)='$date1')) "; //getting price on 2009-01-20 
 
$sql1 = "SELECT price FROM table1"."WHERE (((table1.date)='$date2'))"; getting price on 2009-01-13 
 
$result = mysql_query($sql) 
or die(mysql_error()); 
$result2 = mysql_query($sql1) 
or die(mysql_error()); 
 
 
$header=<<<EOD 
 
 
<h2><center>price variation - <font color="red" size="6"> </center></h2> 
<table width="70%" border="1" cellpadding="2" 
cellspacing="2" align="center"> 
<tr> 
<th>DATE</th> 
<th>ITEM</th> 
<th>PRICE</th> 
<th>DIFFERENCE</th> 
<th>PERCENT</th> 
 
</tr> 
 
EOD; 
$details = ''; 
 
while ($row = mysql_fetch_array($result2)) 
{ 
 
$price2 = $row['price']; 
} // 
 
echo "$price2"; 
 
while ($row = mysql_fetch_array($result)) 
 
{ 
// $date = $row['date']; 
$item = $row['item']; 
$price = $row['price']; 
$price1 = $price2; // 
 
$gain =($price-$price1); // this is what i exaclty required. 
$percentage=(($gain*100)/$price1); 
 
$details .=<<<EOD 
 
<tr> 
 
<td>$date</td> 
<td><font color="red" size="4"> $price</td> 
<td> $price11</td> 
 
<td><font color="red" size="4"> $gain</td> 
<td><font color="blue" size="4"> $percentage</td> 
 
</tr> 
EOD; 
} 
 
$footer ="</table>"; 
 
$output =<<<ALL 
$header 
$details 
$footer 
ALL; 
 
echo $output; 
 
?>
Please help IT is not working.

Re: Select statement How to use two select in one phpfile

Posted: Mon Jul 06, 2009 12:07 pm
by Christopher
It is not clear exactly what result you want, but you can select multiple dates with OR:

Code: Select all

$sql = "SELECT item, price FROM table1 WHERE (table1.date='$date1') OR (table1.date='$date2')";

Re: Select statement How to use two select in one phpfile

Posted: Tue Jul 07, 2009 12:51 am
by peter162in
Thank you sir,

The code working fine.But Icannot able to use the result for calcuation.After taking out the result I want difference between price to be calculated. being price as one variable I cannot use this query.I want price on first date as one variable and price on other date as variable2. How to do a query. Please help.


SELECT item, price FROM table1 WHERE (table1.date='$date1') OR

select item , price as price1 from (table1.date='$date2')";

something similar


Thanking you in advance.

Re: Select statement How to use two select in one phpfile

Posted: Tue Jul 07, 2009 1:57 am
by Christopher
If there is only one price record per date, then the query I posted will return two records. Do your calculations using the price fields in the two records.

Re: Select statement How to use two select in one phpfile

Posted: Tue Jul 07, 2009 10:14 am
by peter162in
Thank you sir,
The query is working . It gives two records also. How I diffrentiate these records. It coming on single page. Being price as one variable and it takes out data for both dates together. Help Please.

Re: Select statement How to use two select in one phpfile

Posted: Tue Jul 07, 2009 1:16 pm
by Christopher
You could sort the two records by the date field. That would put them in chronological order:

Code: Select all

$sql = "SELECT item, price FROM table1 WHERE (table1.date='$date1') OR (table1.date='$date2') ORDER BY table1.date";

Re: Select statement How to use two select in one phpfile

Posted: Thu Jul 09, 2009 11:26 am
by peter162in
Thank you sir,

price is being single variable , It Is impossible to call that variable for mathmatical function. I want to reduce price on date1 to date2. This query is working. But It cannot differentiate these prices on different dates eventhough It lists seperately.
any clue hot to do it. I stcuk here.
Peter

Re: Select statement How to use two select in one phpfile

Posted: Thu Jul 09, 2009 11:54 am
by Christopher
If there are two rows returned then there are two prices -- one in each row array.

Re: Select statement How to use two select in one phpfile

Posted: Sat Jul 11, 2009 10:40 am
by peter162in
If there is any way to calculate this from same table

price on day one -price on day 2

$sql = "SELECT * FROM testdb WHERE (((testdb.date)='$date1')) OR (((testdb.date)='$date2')) ";


if ($date=$date1)
$price2=$price;
else $price3=$price;

My sql or PHP
I think I have to use msacess only. My sql is not capable for permoming this operation

$sql = "SELECT item, price FROM testdb WHERE (((testdb.date)='$date1')) or SELECT price as price1 FROM testdb WHERE (((nse.date)='$date2'))";

How we differntiate Price on day one to price on day2 in mysql
sorry to be in a forum

Re: Select statement How to use two select in one phpfile

Posted: Sat Jul 11, 2009 10:49 am
by peter162in
THE OUTPUT

item price weekb4price diff percent
xerox 100 90 10 ??
Paper 90 100 -10 ??