Select statement How to use two select in one phpfile

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
peter162in
Forum Newbie
Posts: 12
Joined: Mon Jul 06, 2009 11:37 am

Select statement How to use two select in one phpfile

Post 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.
Last edited by peter162in on Tue Jul 07, 2009 12:38 am, edited 3 times in total.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

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

Post 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')";
(#10850)
peter162in
Forum Newbie
Posts: 12
Joined: Mon Jul 06, 2009 11:37 am

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

Post 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.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

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

Post 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.
(#10850)
peter162in
Forum Newbie
Posts: 12
Joined: Mon Jul 06, 2009 11:37 am

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

Post 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.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

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

Post 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";
(#10850)
peter162in
Forum Newbie
Posts: 12
Joined: Mon Jul 06, 2009 11:37 am

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

Post 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
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

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

Post by Christopher »

If there are two rows returned then there are two prices -- one in each row array.
(#10850)
peter162in
Forum Newbie
Posts: 12
Joined: Mon Jul 06, 2009 11:37 am

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

Post 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
peter162in
Forum Newbie
Posts: 12
Joined: Mon Jul 06, 2009 11:37 am

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

Post by peter162in »

THE OUTPUT

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