PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Hello all,
I have a piece of code that I've been trying to wrap my head around for weeks now.
Here's what I have:
1.a database with beginning date, end date, and unix timestamps that appply to both.
2.jpgraph or any other graphing software
here's what I need.
1. to do a count of all rows that have a beginning date in a certain range. then spit out how many were created on each date.
2. put this into an array to be used in a graph.
I can currently able to spit out the information to the screen in plain text. I'm having some problems getting it into an array though.
here's an example of the whole thing: be sure to use yyyy-mm-dd (you'll more than likely get "0 contracts created on xxxxxxx day"
http://www.cosmeticautocare.com/graph.php
and here is the code that produces that:
<?php
$link = @mysql_connect("fxxxxx", "xxxxx", "xxxxx")
or die("Could not connect");
$db = @mysql_select_db("cosmetic",$link)
or die("Could not select database");
?>
<?php
if (isset($_POST['submit'])){
$Begin = $_POST['Begin'];
$End = $_POST['End'];
$b_string = strtotime($Begin);
$e_string = strtotime($End);
$result1 = mysql_query("SELECT * FROM Contracts ", $link);
$num_rows1 = mysql_fetch_array($result1);
//$day=strftime(%D(my_row['Contract_b_str']))
//$b_string= '1154494800';
//$e_string= '1154840400';
while ($b_string<=$e_string){
$ID = $_SESSION['id']; //security stuff for the site, don't stress.
if ($ID == 1) { //security stuff for the site, don't stress. this is what admin sees.
$result = mysql_query("SELECT * FROM Contracts WHERE `Contract_B_str` = '$b_string' ", $link);
} else {//security stuff for the site, don't stress.
$result = mysql_query("SELECT * FROM Contracts WHERE `Contract_B_str` = '$b_string' AND `Affiliate_ID` = '$ID' ", $link);
}
$date= mysql_fetch_array($result);
$rows = mysql_num_rows($result);// this is the money maker... how do I get this into an array?
$str=$date['Contract_B_str'];
$day=strftime("%A" ,$b_string);
$date_p=strftime("%Y-%m-%d" ,$b_string);
echo "$rows Contracts sold on " ;
echo "$day ";
echo $date_p;
echo "<br>";
$b_string= $b_string+'86400';
}
}else{
?>
<h2>Reports</h2>
<form method="post" action="#">
<div style="float: left; width: 300px;">
<div style="float: left; width: 100px; margin-bottom: 3px; color: #fff;">
Begin Date:
</div>
<div style="float: left; width: 200px; margin-bottom: 3px; color: #fff;">
<input type="text" name="Begin" />
</div>
<div style="float: left; width: 100px; margin-bottom: 3px; color: #fff;">
End Date:
</div>
<div style="float: left; width: 200px; margin-bottom: 3px; color: #fff;">
<input type="text" name="End" />
</div>
<div style="float: left; width: 100px; margin-bottom: 3px; color: #fff;">
</div>
<div style="float: left; width: 200px; margin-bottom: 3px; color: #fff;">
<input type="submit" value="Run Report" name="submit" />
</div>
</div>
</form>
<div style="float: left; width: 300px;">
<div style="float: left; width: 300px; margin-bottom: 3px; color: #fff;">
<?php
$sql = "SELECT * FROM `Page` WHERE `Link` = 'report'";
$result = mysql_query($sql);
while ($row = mysql_fetch_array($result)) {
?>
<?php
$ID = $_SESSION['id'];
if ($ID == 1) { ?>
<div style="float: right; color: #fff;"><A HREF="javascript:popUp('edit.php?link=report')">Edit</A></div>
<?php } ?>
<?php echo $row['Content']; ?>
<?php } ?>
</div>
</div>
<?php } ?>
What I really need is to get jpgraph in there and specify the y coordinates by pulling $row. Just getting all the numbers from $row into an array would do it I think.
Any help would be much appreciated.
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Here is a small sample of your code with comments. Read through them and see if anything is useful. Oh yeah, I cleaned up what I touched a little bit for you...
<?php
if ( !$link = mysql_connect("fxxxxx", "xxxxx", "xxxxx") )
{
die('Could not connect: ' . mysql_error());
}
if ( !$db = mysql_select_db('cosmetic', $link) )
{
die('Could not select database: ' . mysql_error());
}
if ( isset($_POST['submit']) ) {
$Begin = $_POST['Begin']; // This is expecting a date (aren't we all
$End = $_POST['End']; // This is expecting a date (aren't we all
$b_string = strtotime($Begin); // Make the date into a unixtimestamp
$e_string = strtotime($End); // Make the date into a unixtimestamp
if ( !$result1 = mysql_query('SELECT * FROM Contracts', $link) )
{
die('Could not execute the first query: ' . mysql_error());
}
$num_rows1 = array();
while ($row = mysql_fetch_array($result1))
{
$num_rows1[] = $row;
}
if ( $b_string <= $e_string )
{
$ID = $_SESSION['id']; //security stuff for the site, don't stress.
if ( $ID == 1 )
{
//security stuff for the site, don't stress. this is what admin sees.
$result = mysql_query("SELECT * FROM Contracts WHERE `Contract_B_str` = '$b_string' ", $link);
}
else
{
//security stuff for the site, don't stress.
$result = mysql_query("SELECT * FROM Contracts WHERE `Contract_B_str` = '$b_string' AND `Affiliate_ID` = '$ID' ", $link);
}
if ( !$result )
{
die('Queries within the comparison failed: ' . mysql_error());
}
/**
* This functions always returns an integer...
* That means you will never get an array out of this
**/
$rows = mysql_num_rows($result);// this is the money maker... how do I get this into an array?
/**
* Sorry to tell you
* But I hope it helps you in some capactity
**/
$date = array();
while ( $row = mysql_fetch_array($result) )
{
$date[] = $row;
}
/**
* At this point you have two arrays built
* The first, $num_rows1, contains all data
* from the `Contracts` table
*
* The second, $date, contains all data from
* the `Contracts` table that have a `Contract_B_str`
* field value of $b_string...
* But only if $b_string <= $e_string
*
* Kinda see how that is working?
**/
} //Close the B and E string comparison
} // Close the isset post comparison
?>
Thanks for your responce. I think you may have almost got what I need.
The part that is missing is this:
I need to take 2 dates. Start at the first date and count how many contracts were created on that day, then move to the next day and so on untill we get to the end date. then... populate an array from that information.
It seems that you were able to create arrays from the data in the db, but not count how many for each date.
Thanks for your help so far, its much appreciated.
<?php
while ($row = mysql_fetch_array($result))
{
echo 'The date is ' . $row['date'] . ' and there were ' . get_contract_count($row['date']) . ' contracts on this date...';
}
?>
This is the logic behind it. Of course, you will have to make this yours so it works with your database, but the concept should be pretty solid.