Tricky PHP/MYSQL select

[Crow77]

I need to select a value on a MYSQL table using PHP.

Basically I have PDATE column (which I need to make sure it's today's date) then STKNO column (if the value there is 11, then it's Price Index and if the value there is 10 then it's Weighted Index). Which means every Day has two values (PDATE) one has STKNO 10 and STKNO 11 columns. The value I want to pull is a cell in a column PLAST. How do I do that?

Example:

PDATE | STKNO | PLAST
2012-06-11 | 11 | 1000
2012-06-11 | 10 | 90

The cell that reads 1000 is today's Price Index while the cell that reads 90 is today's weighted index. The "indicator" is column STKNO, if its 11 it's price and if it's 10 it's weighted. How can I select/pull that?

Thanks :)

[pritaeas]

SELECT 'weighted index' AS pricetype, plast FROM table WHERE pdate = DATE(NOW()) AND STKNO = 10
UNION
SELECT 'price index' AS pricetype, plast FROM table WHERE pdate = DATE(NOW()) AND STKNO = 11

[Crow77]

Hello Pritaeas,

Below is the code I'm trying to alter

$sql=mysql_query('select PINDEX,PDATE from  hist_index order by PDATE DESC 
LIMIT 0 , 2');
$num=mysql_num_rows($sql);

 $arr1 =  mysql_fetch_row($sql);
 $arr1 =  mysql_fetch_row($sql);
           $PINDEX=$arr1[0];
           $yindex=$PINDEX;

the term WHERE pdate = DATE(NOW()) AND STKNO = 10 make sense, but I don't understand where to add 'weighted index' as pricetype in the code.

[rajengg]

Please check following Query..it's working

SELECT 
(SELECT  plast FROM table1 WHERE pdate = DATE(NOW()) AND STKNO = 10) AS `weighted index`,
(SELECT  plast FROM table1 WHERE pdate = DATE(NOW()) AND STKNO = 11) AS `price index`
FROM table1 
GROUP BY pdate

[rajengg]

Check this Query also...

SELECT 
SUM(IF(STKNO=10,plast,0)) AS `weighted index`,
SUM(IF(STKNO=11,plast,0)) AS `price index`
FROM table1 
GROUP BY pdate

[Crow77]

Hello Guys,

Thank you for your support, I really appreciate it.

Forgive my newbiness but the query on and off itself doesn't seem to work when out "echo" it on the HTML table.

If I can input $MYVALUE then I can do everything else because the new TABLE calculates Changes for me so all these changes calculations in the code are not necessary.

I know there's an array which I will remove but why is it $MYVALUE doesn't show what I "selected"?

I forgot to mention I made a mistake, it's PREF (not PLAST) that is the current value of PINDEX but let's focus on PVALUE if I can pull this then I can do the rest. The database structure is this:

PDATE | STKNO | PREF | PLAST | PVOL | PVALUE | CHANGE <-- that's excellent, no need to calculate change!
xxxx | 11 | Index | yesterday | Today Volume | Today Value | Calculated Change since yesterday

   <table width='80%' border='0' align='center' cellpadding='2' cellspacing='1' id='ctl00_MainContent_TGGrid2'>
                <tbody>
<?php
require_once('db.class1.php');

 $db =  new MySQLConnect();
 $db->MySQLSelect();
 $stu=0;
 $std=0;
 $ted=0;
 $sql=mysql_query("select PLAST,PREF,PTRADES from  hist_sec where DATE(PDATE) = DATE(NOW())");
$num=mysql_num_rows($sql);
                    for($i = 0;$i < $num;$i++)
                        { $arr1 =  mysql_fetch_row($sql);
                          $PLAST=$arr1[0];
                          $PREF=$arr1[1];
                                          $PTRADES=$arr1[2];
                                          $change=$PLAST-$PREF;
                                          if($change>0)
                                          {
                                            $stu=$stu+1;  
                                          }
                                          if($change<0)
                                          {
                                             $std=$std+1; 
                                          }
                                          if($PTRADES!="null")
                                          {
                                            $ted=$ted+1;  
                                          }
                                        }






$yindex=0;

/*PINDEX OLD
//$sql=mysql_query('select PLAST from  hist_index where PDATE = DATE(NOW()) AND STKNO = 11 order by PDATE DESC 
LIMIT 0 , 2');
$num=mysql_num_rows($sql);

 $arr1 =  mysql_fetch_row($sql);
 $arr1 =  mysql_fetch_row($sql);
           $PLAST=$arr1[0];
           $yindex=$PLAST;
  PINDEX OLD         */

$sql=mysql_query('select PREF from  hist_index where PDATE = DATE(NOW()) AND STKNO = 11');

    $PINDEX=$PREF;

/*$sql=mysql_query('select PLAST from  hist_index where PDATE = DATE(NOW()) AND STKNO = 10');

    $YINDEX=$PLAST;*/

/* WINDEX OLD
$sql=mysql_query('select PLAST from hist_index where PDATE = DATE(NOW()) AND STKNO = 10 order by PDATE DESC 
LIMIT 0 , 2');
$num=mysql_num_rows($sql);

 $arr1 =  mysql_fetch_row($sql);
 $arr1 =  mysql_fetch_row($sql);
           $WINDEX=$arr1[0];
           $ywindex=$WINDEX;
WINDEX OLD */

$sql=mysql_query('select PREF from hist_index where PDATE = DATE(NOW()) AND STKNO = 10'); 

    $WINDEX=$PREF;

$sql=mysql_query('select PVALUE from hist_index where PDATE = DATE(NOW()) AND STKNO = 11');

    $MYVALUE=$PVALUE;



$sql=mysql_query("select PDATE,PVALUE,PTRADES,PVOL from  hist_index where  DATE(PDATE) = DATE(NOW()) AND STKNO = 11");
/*
$num=mysql_num_rows($sql);
                    for($i = 0;$i < $num;$i++)
                        { $arr1 =  mysql_fetch_row($sql);
                          $MKT_SECT=$arr1[0];
                          $PDATE=$arr1[1];
                          $PVALUE=$arr1[2];
                                         $PVALUE=number_format($PVALUE, 2, '.', ',');
                          $PINDEX=$arr1[3];
                          $WINDEX=$arr1[4];
                          $PTRADES=$arr1[5];
                          $PVOL=$arr1[6];


                                           $PVOL=number_format( $PVOL, 2, '.', ',');
                                          $change=$PINDEX-$yindex;
                                          $change=number_format($change, 2, '.', ',');
                                          $change1=($PINDEX-$yindex)*100/$yindex;
                                           $change1=number_format($change1, 2, '.', ',');
                                          $wchange=$WINDEX-$ywindex;
                                           $wchange=number_format($wchange, 2, '.', ',');
                                          $wchange1=($WINDEX-$ywindex)*100/$ywindex;
                                         $wchange1=number_format($wchange1, 2, '.', ',');
                                         */
                          echo "<tr>

                    <td width='45%' bgcolor='#FFFFFF'><span >Index Value</span></td>
                    <td width='55%' bgcolor='#FFFFFF'>$PINDEX</td>
                  </tr>
                  <tr>
                    <td width='45%'><a href='http://www.kse.com.kw/PORTAL/Stock/Stock.aspx?STK=634'><span class='ui-icon-arrow-1-s'>Change</span></a><a href='http://www.kse.com.kw/PORTAL/Stock/Stock.aspx?STK=632'></a></td>
                       ";
                                          if($change==0)
                                          {
       echo"<td width='55%'> <span id='ctl00_CP_marketSummary_lblChangeVal'> +$change</span>  <span id='ctl00_CP_marketSummary_lblChangePercentVal' dir='ltr'>+$change1%</span></td>";

                                           };
                                          if($change>0)
                                          {
       echo"<td width='55%'><img src='images/index_up.gif' alt='' id='ctl00_CP_marketSummary_imgChange'> <span id='ctl00_CP_marketSummary_lblChangeVal'> +$change</span>  <span id='ctl00_CP_marketSummary_lblChangePercentVal' dir='ltr'>+$change1%</span></td>";

                                           };
                                            if($change<0)
                                          {
       echo"<td width='55%'><img src='images/index_down.gif' alt='' id='ctl00_CP_marketSummary_imgChange'> <span id='ctl00_CP_marketSummary_lblChangeVal'> $change</span>  <span id='ctl00_CP_marketSummary_lblChangePercentVal' dir='ltr'>$change1%</span></td>";

                                           };
                    echo"
                  </tr>
                  <tr>
                    <td width='45%' bgcolor='#FFFFFF'><a href='http://www.kse.com.kw/PORTAL/Stock/Stock.aspx?STK=2008'><span class='ui-icon-arrow-1-s'>Weight Index</span></a><a href='http://www.kse.com.kw/PORTAL/Stock/Stock.aspx?STK=252'></a></td>
                    <td width='55%' bgcolor='#FFFFFF'><span class='ui-icon-arrow-1-s'>$WINDEX</span></td>
                  </tr>
                  <tr>
                    <td width='45%'><a href='http://www.kse.com.kw/PORTAL/Stock/Stock.aspx?STK=810'><span class='ui-icon-arrow-1-s'>Weight Change</span></a><a href='http://www.kse.com.kw/PORTAL/Stock/Stock.aspx?STK=201'></a></td>";

                    if($wchange==0)
                                          {
                    echo "<td width='55%'> <span id='ctl00_CP_marketSummary_lblChangeWeightVal'>+$wchange</span>  <span id='ctl00_CP_marketSummary_lblChangeWeightPercentVal' dir='ltr'>+$wchange1%</span></td>";

                                          }
                     if($wchange>0)
                                          {
                    echo "<td width='55%'><img src='images/index_up.gif' alt='' id='ctl00_CP_marketSummary_imgChangeWeight'> <span id='ctl00_CP_marketSummary_lblChangeWeightVal'>+$wchange</span>  <span id='ctl00_CP_marketSummary_lblChangeWeightPercentVal' dir='ltr'>+$wchange1%</span></td>";

                                          }
                                          if($wchange<0)
                                          {
                    echo "<td width='55%'><img src='images/index_down.gif' alt='' id='ctl00_CP_marketSummary_imgChangeWeight'> <span id='ctl00_CP_marketSummary_lblChangeWeightVal'>$wchange</span>  <span id='ctl00_CP_marketSummary_lblChangeWeightPercentVal' dir='ltr'>$wchange1%</span></td>";

                                          }
                    echo "</tr>
                  <tr>
                    <td width='45%' bgcolor='#FFFFFF'><a href='http://www.kse.com.kw/PORTAL/Stock/Stock.aspx?STK=636'><span class='ui-icon-arrow-1-s'>Trades</span></a><a href='http://www.kse.com.kw/PORTAL/Stock/Stock.aspx?STK=604'></a></td>
                    <td width='55%' bgcolor='#FFFFFF'><span class='ui-icon-arrow-1-s'>$PTRADES</span></td>
                  </tr>
                  <tr>
                    <td ><span class='ui-icon-arrow-1-s'>Value</span></td>
                    <td ><span class='ui-icon-arrow-1-s'>$PMYALUE</span></td>
                  </tr>
                  <tr>
                    <td bgcolor='#FFFFFF'><span class='ui-icon-arrow-1-s'>Volume</span></td>
                    <td bgcolor='#FFFFFF'><span class='ui-icon-arrow-1-s'>$PVOL</span></td>
                  </tr>
                  <tr>
                    <td ><span class='ui-icon-arrow-1-s'>Stocks Up</span></td>
                    <td ><span class='ui-icon-arrow-1-s'>$stu</span></td>
                  </tr>
                  <tr>
                    <td bgcolor='#FFFFFF'><span class='ui-icon-arrow-1-s'>Stocks Down</span></td>
                    <td bgcolor='#FFFFFF'><span class='ui-icon-arrow-1-s'>$std</span></td>
                  </tr>
                  <tr>
                    <td ><span class='ui-icon-arrow-1-s'>Stocks Traded</span></td>
                    <td ><span class='ui-icon-arrow-1-s'>$ted</span></td>
                  </tr>";
//                        }
 ?>                

                </tbody>
              </table>

[Crow77]

When I go to my QUERY BROWSER for MYSQL and I type this for instance:

SELECT PVALUE FROM hist_index where DATE(PDATE)=DATE(NOW()) and STKNO=11;

I get the PVALUE correctly.

But I don't know how to take that and put it in HTML.

[Crow77]

Update;

This is getting interesting, I tried the code below.

$sql="select PREF from  hist_index where DATE(PDATE) = DATE(NOW()) AND STKNO = 11";

$PINDEX=mysql_query($sql);

and my output to HTML (just a snippet)

 echo "<tr>

                    <td width='45%' bgcolor='#FFFFFF'><span >Index Value</span></td>
                    <td width='55%' bgcolor='#FFFFFF'>$PINDEX</td>
                  </tr>
                  <tr>

On the website's table I get this result:
Resource id #4

[broj1]

This is because mysql_query() function returns an ID for a resource, not a result. To get the result use mysql_fetch_assoc() or mysql_fetch_row():

$res = mysql_query($sql);
$row = mysql_fetch_assoc($res);
$PINDEX=$row['PREF'];

[Crow77]

THANK YOU BROJ1 THAT DID THE TRICK!!!!

THANK YOU 99999^10003 times!!

[Crow77]

I would like to thank everyone who lend me their support.

Broj1, rajengg and pritaeas!

Now I'll go to step #2 hopefully I can do it myself :D