[RESOLVED] Creating an ARRAY and extracting the month of the date

[dcjones]

Hi all

After a long look at over 200 posts I cannot find what I am looking for:

I have a table (baggage) which has a number od fields two of which are f_baggae and f_date. The f_baggage f_baggage will contain "YES" or it will be emptyand the f_date hold dates in the format of "0000-00-00".

What I am trying to do is to extract the data from the table in a format where I can use it in jpGraph.

So far I have only managed to extract the number of rows that contain data which is fine as far as it goes. My query is as follows:

$query_found = sprintf("SELECT f_baggage, f_date FROM baggage WHERE f_baggage = 'yes' ORDER BY f_date");

$found = mysql_query($query_found, $baggage) or die(mysql_error());
$row_found = mysql_fetch_assoc($found);
$totalRows_found = mysql_num_rows($found);

Now that I have the data in an array how do I say "The baggage sold this month = x"

Many thanks

[Flyier]

I use -> $array=mysql_fetch_array -> extract($array) -> $date=substr($date, 5, 2)

[dcjones]

Hi all.

I have been trying to get this working all day and do I need some help, please.

All I am trying to do is to get the my SQL query to pass the required data to an array which I can then use in jpGraph.

My data table has 2 fields, "f_date" which has a field type of DATE "0000-00-00".
"f_bag" has a field type VARCHAR .

Can anyone see what I am doing wrong.

My code so far is :

session_start();
$company = $_SESSION['_user']['company_name'] ;
require_once('../../Connections/c.php'); 

include ("../jpGraph/jpgraph.php");
include ("../jpGraph/jpgraph_line.php");
include ("../jpGraph/jpgraph_bar.php");

$SQL = mysql_query("SELECT f_date, f_bag FROM baggage WHERE `f_bag`= 'yes' GROUP BY YEAR( f_date ), MONTH( f_date )" );
$result = mysql_query($SQL); 

   if ($myrow=mysql_fetch_array($result)) 
   { do 
   { 
   $datay[] = $myrow["f_date"]; 
   $datax[] = $myrow["f_bag"];
   }while 
   ($myrow=mysql_fetch_array($result)); }




$datay=array($datay);
$datax=array($datax);
$months=$gDateLocale->GetShortMonth();

// Create the graph. These two calls are always required
$graph = new Graph(600,200,"auto");	
$graph->SetScale("textlin");
$graph->yaxis-> scale-> SetGrace(20);

// Add a drop shadow
$graph->SetShadow();

// Specify X-labels
$graph->xaxis->SetTickLabels($months);

// Adjust the margin a bit to make more room for titles
$graph->img->SetMargin(40,40,40,40);

// Create a bar pot
$bplot = new BarPlot($datay);
$bplot = new BarPlot($datax);

// Adjust fill color
$bplot->SetFillColor('red');
$bplot->value->Show();
$graph->Add($bplot);

// Setup the titles
$graph->title->Set($company);
$graph->yaxis->title->Set("Bag");

$graph->title->SetFont(FF_FONT1,FS_BOLD);
$graph->yaxis->title->SetFont(FF_FONT1,FS_BOLD);


// Display the graph
$graph->Stroke();

Many thanks again.

[dcjones]

Hi all,

Can anyone please help me solve this problem

Many thanks

[j_70]

I may be misreading what you want but if you just want the month from f_date, select month(f_date) (or monthname(f_date)) as part of your query.

[j_70]

I may be misreading what you want but if you just want the month from f_date, select month(f_date) (or monthname(f_date)) as part of your query.

[dcjones]

Hi and thanks for the reply.

I now have my query like this:

mysql_select_db($database_bag, $bag);
$query_found = "SELECT f_bag, month(f_date)  FROM amember_bag GROUP BY MONTH (f_date)"; 
$found = mysql_query($query_found, $bag) or die(mysql_error());

while ($row = mysql_fetch_assoc($found)) { 
    $f_month = $row['month(f_date)']; 

$f_items = $row["f_baggage"]; 
}

I know what data is in the table which is:

f_bag------f_date
1-----------2007-06-01
1-----------2007-04-01
1-----------2007-08-01
1-----------2007-04-19
1-----------2007-04-24
1-----------2007-04-20
1-----------2007-04-16
1-----------2007-04-28
1-----------2007-04-21

This give me 7 records with a date month of april, 1 record with June and 1 record with August.

When I run the query the results are:
$bag------month(f_date)
1----------- 4 // April
1 ---------- 6 // June
1 ---------- 7 // I don't know where thsi is coming from
1 ---------- 8 // August

Then I want to be able to create 2 arrays:

one for $bag which hold "0" or "1" and an array for $month(f_date) which will hold the month part od the date.

I then need to use the content of both arrays to display the data in my graph as:

on the x axis display the month and on the y axis display the total $bag for that month.

I hope that this makes sense.

Many thanks again

[Installer]

I know what data is in the table...

I don't think so. With the same data and the same query I get:

1-----------4 // April
1-----------6 // June
1-----------8 // August

[dcjones]

Hi Installer

Your right, I missed one. The data in the table is:

f_bag------f_date
1-----------2007-07-19
1-----------2007-06-01
1-----------2007-04-01
1-----------2007-08-01
1-----------2007-04-19
1-----------2007-04-24
1-----------2007-04-20
1-----------2007-04-16
1-----------2007-04-28
1-----------2007-04-21

Sorry about that. How then do I get the data into my arrays so I can produce array1 with the months and array2 with the totals for the months.

many thanks for looking at this.

[Installer]

I think this will do it, giving you two arrays, $f_month and $f_count:

$query = "SELECT month(f_date), COUNT(f_bag) as ct FROM amember_bag GROUP BY MONTH(f_date)"; 
$found = mysql_query($query) or die(mysql_error()); 

while ($row = mysql_fetch_assoc($found)) { 
    $f_month[] = $row['month(f_date)']; 
    $f_count[] = $row['ct'];
}

[dcjones]

Hi Installer,

well what can I say, but many thanks for your help. it works.

If your ever in warwickshire, england look me up and I will buy you a pint.

[Installer]

Sounds good, Dereck, but be warned: Give this man a pint and he'll take a barrel.