php help

[DeathByTray]

I run into another problem.
Let's assume my table looks like this:

[table="head"]gfxid|price|date
1|300|02.04.2010
1|295|05.04.2010
1|305|07.04.2010
[/table]

As you can see, some dates are missing.
So, I'd like to fill these gaps and put the data into arrays.
Like this:

$date[1] = 02.04.2010;
$date[2] = 03.04.2010;
$date[3] = 04.04.2010;
$date[4] = 05.04.2010;
//etc.

and

$price[1] = 300;
$price[2] = 300;
$price[3] = 300;
$price[4] = 295;
//etc.

I assume you would need to convert the dates to unix timestamps in order to do some math with them. I haven't come up with anything useful yet though.
Anyone got an idea?

 

[FordGT90Concept]

I don't follow what you are trying to accomplish.

 

[Kreij]

Neither did I, but I thought maybe I was just having a bad day. lol

More info please, DBT.

 

[Disparia]

Will want to read up on date() and strtotime().

 

[DeathByTray]

Cool, that's way better than gmmktime.

Anyway, let me clarify what I'm trying to do.

[table="head"]gfxid|price|date
1|300|02.04.2010
1|295|05.04.2010
1|305|07.04.2010
[/table]

Reading from this table will give me this:

$date[1] = 02.04.2010
$date[2] = 05.04.2010
$date[3] = 07.04.2010

$price[1] = 300;
$price[2] = 295;
$price[3] = 305;

The database is missing the dates/prices (of the 3rd, 4th and 6th April) in between because price change is only being updated on actual change.
What I want to do is write a little piece of code that automatically calculates the missing dates/prices and puts them into the respective arrays.

 

[Ross211]

I'm assuming you are reading from a MySQL Database?

Here is one way of doing this by reading the values from a MySQL Database and putting them into a table.

dbinfo.php

<?php
$username="root";
$password="";
$database="";
?>

index.php -

<?php
      include("dbinfo.php");
      mysql_connect(localhost,$username,$password);
      @mysql_select_db($database) or die( "Unable to select database");
      $query="SELECT * FROM [name of your table here]";
      $result=mysql_query($query);
      $num=mysql_numrows($result);

 echo "<table border=3 width =50% align=left>";
        echo    "<tr>";
           echo "<th style=font-family:verdana;font-size:medium;font-style:oblique>
                <b>gfxid</b></th>";
           echo "<th style=font-family:verdana;font-size:medium;font-style:oblique>
                <b>price</b></th>";
           echo "<th style=font-family:verdana;font-size:medium;font-style:oblique>
                <b>date</b></th>";
           echo "</th>";
            echo "</tr>";

if (mysql_num_rows($result) > 0) {
 
    while($row = mysql_fetch_row($result)) {
        echo "<tr>";
        echo "<td>".$row[0]."</td>";
        echo "<td>".$row[1]."</td>";
        echo "<td>".$row[2]."</td>";
        echo "</tr>";
    }
    echo "</table>";
} 
mysql_free_result($result);

mysql_close();

        php?>

 

[FordGT90Concept]

The database is missing the dates/prices (of the 3rd, 4th and 6th April) in between because price change is only being updated on actual change.
What I want to do is write a little piece of code that automatically calculates the missing dates/prices and puts them into the respective arrays.

The easiest way to take care of that is to go into the database and add the missing rows. They will be out of order by ID but they will be in correct order by date. Something like:

INSERT INTO ##### (gfx_id, price, date) VALUES (1, ###, "03.04.2010");
INSERT INTO ##### (gfx_id, price, date) VALUES (1, ###, "04.04.2010");
INSERT INTO ##### (gfx_id, price, date) VALUES (1, ###, "06.04.2010");

 

[DeathByTray]

Ross211
Hmpf.. thanks for the effort but that's not what I was looking for.

I'll try again.
I have 3 dates with 3 prices.

02.04.2010 - $300
05.04.2010 - $295
07.04.2010 - $305

03.04.2010, 04.04.2010 and 06.04.2010 are missing because the price didn't change on these days.
So, in reality, the prices looked like this:

02.04.2010 - $300
03.04.2010 - $300
04.04.2010 - $300
05.04.2010 - $295
06.04.2010 - $295
07.04.2010 - $305

The red lines are the gaps I'm talking about but I don't know how to tell php that there are gaps that need to be filled with value xyz.


FordGT90Concept
Yay, finally someone understood it!
If I do that, the database will get huge. Remember when we talked about the layout of my database? I changed it in order to keep it small and avoid redundancy.

 

[Kreij]

You could also code it so that if a date was missing, the program assumes that there was no price change and get's the most current, previously entered price.
That would prevent you from having to add records to the table when no price change occurred.

So if you queried for a price for 04.04.2010, when the query returns no results, the program generates a new query for the date that is closest to (but less than) the entered date (in this case 02.04.2010) and uses the results from the new query as the valid price.

 

[Ross211]

Ross211
Hmpf.. thanks for the effort but that's not what I was looking for.

I tried

You can always use that method to display the values from your database now that you will have the data you want in your tables.

 

[W1zzard]

what are you trying to do with the data in your database?

are you plotting a graph? consider linear interpolation (a straight line between data you have) or use steps

do you just want the latest value? sort by date descending limit 1

 

[DeathByTray]

are you plotting a graph? consider linear interpolation (a straight line between data you have) or use steps

Exactly, but it isn't accurate enough right now. This is a good example; it serves its purpose but I'd like it to be more accurate.

Kreij
Good idea, I'll see if I can come up with something.

 

[W1zzard]

what causes a day not to have any pricing data ?

 

[Disparia]

May be some help:

$date = array();
$price = array();

# Lets get the last week;
for ($i = 7; $i >= 1; --$i) {

	# Enter in date $i days back.
	$date[$i] = date('Y-m-d', strtotime('-'.$i.' days'));

	# Use date to limit results / grab last result
	$sql = 'SELECT price FROM price_table WHERE date <= '.$date[$i].' ORDER BY date DESC LIMIT 1';

	/* SQL stuff happens here */

	# $date and $price keys stay synced
	$price[$i] = 'The price returned';
}

Was thinking that if you were going with a date range (eventually you'll have so many entries your chart wouldn't work) the first day may not have any data in it. Whats needed is the latest value up until and including that date.

Don't have an actual table with data to test against, but I hope would happen is ending up with two arrays like:

Array
(
    [7] => 2010-03-31
    [6] => 2010-04-01
    [5] => 2010-04-02
    [4] => 2010-04-03
    [3] => 2010-04-04
    [2] => 2010-04-05
    [1] => 2010-04-06
)
Array
(
    [7] => 353.38
    [6] => 838.76
    [5] => 679.10
    [4] => 814.21
    [3] => 255.95
    [2] => 108.44
    [1] => 739.34
)

There are better ways about it, but that gets what you want accomplished.

 

[DeathByTray]

what causes a day not to have any pricing data ?

No price change, no entry.

Was thinking that if you were going with a date range (eventually you'll have so many entries your chart wouldn't work) the first day may not have any data in it. Whats needed is the latest value up until and including that date.

Yes, that will be a problem, your code above may come in handy!



Anyway, here's what have done so far. It calculates the missing dates and puts them into arrays. Thanks again Kreij for the great idea!

$x = 1; //future
$y = 0; //past
$i = 1; //lvl up day
$a = 5; //gfxid 

//Get 1st day the database
$result = mysql_query("SELECT * FROM price WHERE gfxid = '".$a."' ORDER BY ID ASC") or die(mysql_error()); 
$row = mysql_fetch_array($result);
$price[$y] = $row['price'];
$date[$y] = $row['dateadd'];
//---//

//Days between today and 1st day
$z = (strtotime(date("Y-m-d")) - strtotime($date[$y])) / (60 * 60 * 24);
//---/

while ($z >= $i)
	{
	//Get the next day
	$days = explode('-',$date[$y]);
	$nextday = date('Y-m-d',mktime(0,0,0,$days[1],($days[2]+1),$days[0]));
	//---//
	
	$date_check = mysql_query("SELECT * FROM price WHERE gfxid = '".$a."' and dateadd = '".$nextday."'");
	if (mysql_num_rows($date_check) > 0)
		{		
		$row = mysql_fetch_array($result);
		$date[$x] = $row['dateadd'];
		$price[$x] = $row['price'];
		}
	else
		{
		$date[$x] = $nextday;
		$price[$x] = $price[$y];
		}
	$x++;
	$y++;
	$i++;
	}

 

[W1zzard]

sort your pricing array by date, foreach over all entries, when price is 0 look in adjacent entries until you find a price

 

[DeathByTray]

If I understood you correctly that's what I did above. Well, more or less.

I run into yet another issue though

  $DataSet = new pData;   
	$DataSet->AddPoint(array($price),"Serie1");  
	$DataSet->AddPoint(array($date),"Serie2");

This is some code of pChart and all I'm doing is adding my values; $price & $date
$date contains 1270332000,1270418400,1270504800 etc.
$price contains 134,134,134 etc.

Everything works perfectly if I manually enter the values, however, it won't with variables.
I've echoed both variables and they are fine, so that's not the issue?!

 

[Disparia]

If you send an array($price), then you are sending $price which is already an array in an array.

Just do: $DataSet->AddPoint($price, 'Serie1'); if that's what the method AddPoint accepts.

 

[DeathByTray]

They're not arrays though. $price and $date are both variables.
Btw. what does the '->' do, where can I read up on that? My google skills failed.

 

[Disparia]

They're not the same $price and $data from your earlier script snippet?

It's how you access a variable or method (or const, static, magic method) from an object.

$phpObject = new ClassName;
$phpObject->FormatDrive('C:');

 

[DeathByTray]

Nope, I went further ahead, put them into variables and added the commas.

Hmkay, sounds very Greek to me

 

[DeathByTray]

Oh well, this works:

	foreach ($price as $k => $v) 
	{
	$DataSet->AddPoint($v,"Serie1");
	}
	
	foreach ($date as $k => $v) 
	{
	$v = mktime(0,0,0,$days[1],($days[2]),$days[0]);
	$DataSet->AddPoint($v,"Serie2"); 
	}

Don't know why the other didn't though, whatever.

 

[Disparia]

foreach ($date as $k => $v) {
	$DataSet->AddPoint($price[$k], "Serie1");
	$DataSet->AddPoint($v, "Serie2");
}

Should be able to combine the work.

As foreach() works through the $date array, can use the key ($k) to reference the corresponding entry in the $price array.

 

[DeathByTray]

Good to know.
Replaced, tested and approved. Thanks