php, mysql, counting and arrays

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!

Moderator: General Moderators

Post Reply
c4l3b
Forum Newbie
Posts: 2
Joined: Tue Aug 08, 2006 3:55 pm

php, mysql, counting and arrays

Post by c4l3b »

feyd | Please use

Code: Select all

,

Code: Select all

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:

Code: Select all

<?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;">
		&nbsp;
	</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.


feyd | Please use

Code: Select all

,

Code: Select all

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]
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

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...

Code: Select all

<?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
?>
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Oh yeah, and
:arrow: to PHP - Code.
c4l3b
Forum Newbie
Posts: 2
Joined: Tue Aug 08, 2006 3:55 pm

Post by c4l3b »

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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

OK, I think I know what you are after. This is how I did for a project recently...

I made a function that returns a count based on a passed value. In your case, the function would return the count of contracts on a given date...

Code: Select all

<?php
function get_contract_count($date)
{
    // Do a SQL that gets the count where date = $date
    return $count;
}
?>
Then, during the while loop of date selection query, use that function to display counts on that date.

Code: Select all

<?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.
Post Reply