[SOLVED] Query help

[clanstyles]

			<?php
				$result = mysql_query("SELECT * FROM `servers`");
				while($res = mysql_fetch_array($result))
				{
					echo "<li>";
					echo "<h3>" . $res['name'] . "</h3>";
					$ping = mysql_query("SELECT AVG(size) as avsize FROM `serverInfo` GROUP BY timestamp WHERE `id`=".$res['id']."'");
					echo "<p>" . $ping['avsize'] . "</p>";
					echo "</li>";
				}
			?>

 

That isna't returning the avsize. I don't kno whwy

 

[frost]

Just to be sure, is the column in your table called size or avsize?

 

 

[clanstyles]

Okay hers how it is. There is a colum SIZE in Bytes.

 

I need the average of it depening on the timestamp.

 

 

[clanstyles]

bump

[lemmin]

What does it return? Does it return a number that isn't the average or does it not return anything?

 

If it is returning the wrong average: AVG is only going to return one value, so using GROUP will return multiple averages from each unique timestamp (your timestamp field). And with the WHERE clause, you are probably covering up some values that would normally complete the average.

 

If that is how you meant for it to query, then more info on what it is outputting would be helpful.

[clanstyles]

Yeah i caught that my updcae is this and it still isan't working

 

<?php
$date = date("M/D/Y");
$result = mysql_query("SELECT * FROM `servers`");
				while($res = mysql_fetch_array($result))
				{
					echo "<li>";
					echo "<h3>" . $res['name'] . "</h3>";
					$ping = mysql_query("SELECT AVG(size) as avsize FROM `serverInfo` GROUP BY timestamp WHERE `id`=".$res['id']."' && `timestamp`='".$date."' LIMIT 1");
					echo "<p>" . $ping['avsize'] . "</p>";
					echo "</li>";
				}

?>

[clanstyles]

When ever I do var_dump($ping) it comes out with test

test

bool(false)

 

test

bool(false)

 

bool(false)

 

(365, 16, 152, 'images', '06/25/07'),

(366, 16, 124, 'keys', '06/25/07'),

(367, 16, 36, 'TabathaHeavy Regular.ttf', '06/25/07'),

(368, 16, 4, 'config.php', '06/25/07'),

(369, 16, 4, 'data.php', '06/25/07'),

(370, 16, 4, 'default.css', '06/25/07'),

(371, 16, 4, 'footer.php', '06/25/07'),

(372, 16, 4, 'graph.php', '06/25/07'),

(373, 16, 152, 'images', '06/25/07'),

(374, 16, 8, 'index.php', '06/25/07'),

(375, 16, 124, 'keys', '06/25/07'),

(376, 16, 4, 'login.php', '06/25/07'),

(377, 16, 4, 'ssh.php', '06/25/07'),

(378, 16, 4, 'testdata.php', '06/25/07');

CREATE TABLE `serverInfo` (

  `id` int(11) NOT NULL auto_increment,

  `serverid` int(11) NOT NULL,

  `size` int(11) NOT NULL,

  `filename` text NOT NULL,

  `timestamp` text NOT NULL,

  PRIMARY KEY  (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=379 DEFAULT CHARSET=latin1 AUTO_INCREMENT=379 ;

 

 

[lemmin]

I am pretty sure that the query is not returning any results, not because something is wrong, but because there aren't any results that match your criteria. I really don't understand what you are doing with limit one and group

 

If you table looked something like this:

 

size | timestamp

1 | 1

2 | 2

4 | 2

4 | 1

5 | 1

 

grouping it by timestamp would retrieve two results, 5 and 3 (avg(1,4,5) and avg(2,4))

However, since you are requireing a timestamp WHERE clause, you are only grabbing one timestamp anyway, therefor nullifying the group clause. Take the GROUP and LIMIT clauses out.

 

However, though those clauses have no use in the line, I don't think they are hindering your results. I would assume the problem is that there is no row that has an 'id' field and 'timestamp' field that match your criteria, if there is, the size field of it is null or false.

 

Try echoing out the $res['id'] and $date variables before each query and check the tables to see if any actually match.

[clanstyles]

Well what im trying to do is this.

 

<?php
				$result = mysql_query("SELECT * FROM `servers`");
				while($res = mysql_fetch_array($result))
				{
					echo "<li>";
					echo "<h3>" . $res['name'] . "</h3>";
					$date = date("M/D/Y");
					$result = mysql_query("SELECT * FROM `servers`");
						while($res = mysql_fetch_array($result))
						{
							echo "<li>";
							echo "<h3>" . $res['name'] . "</h3>";
							$ping = mysql_query("SELECT AVG(size) as avsize FROM `serverInfo` GROUP BY timestamp WHERE `id`=".$res['id']."' && `timestamp`='".$date."' LIMIT 1");
							echo "<p>" . var_dump($ping) . "</p>";
							echo "</li>";
						}
						echo "<div \"main\"></div>";
						echo "</li>";
				}
			?>

 

I'm t rying to return each server's name then show the average size from the data grabed that night at midnight. So servername id=1 ( its 16 the one im testing w/ ).

 

U get it?

[lemmin]

I think what you need is a BETWEEN clause to specify the interval timeframe. Unless, of course, your timestamps are numbers representing the timeframe you are trying to retrieve from.

 

Is the timestamp strictly a date, no time? If so, then, like I said before, make sure that the variables you are checking against are, in fact, the correct ones that match the ones in the table. Another way to test if the query is finding anything is to use @ in front of mysql_query and an or die("error") after it. This way you can tell if it is actually finding a value.

 

No matter what you are doing, I would still take the GROUP and LIMIT clause because I don't think they are doing anything.

[clanstyles]

A time stamp is in the format of '06/25/07'

[lemmin]

date("M/D/Y") returns a textual date.

For example:

Aug/Wed/2007

 

Since this is not the format that your timestamp is in, it is not finding any results from this query. Use lowercase letters to get a numeric date. Lower case y outputs only the last two digits of the year.

 

date("m/d/y"); //output: 08/08/07

[clanstyles]

okaky that still didn't fix it th ough.

[lemmin]

Well, how about the other WHERE criteria?

 

Just to troubleshoot, put the line:

echo $res['id'] . "<br>";

right before your $ping = mysql_query line. See what it outputs and check to make sure that is exactly what you are looking for in the table. Make sure that, if it IS in the table, that it has a timeframe of whatever $date is.

[clanstyles]

Yeaht hose are shoing fine. I have a line saying:

echo "<p>" . is_array($ping) ? print_r($ping) : "np" . "</p>";

it prints out 1 under them all.

[lemmin]

$ping gets its value after the query. I am asking about values that are used in the query, like $res['id']. Though, if $ping doesn't have any false values in it, then that would mean that the query is completing successfully, so what is not working? Are you saying that it prints out an Average value of 1?

[clanstyles]

All i know is from this statement

 

$ping = mysql_query("SELECT AVG(size) as avsize FROM `serverInfo` GROUP BY timestamp WHERE `id`=".$res['id']."' && `timestamp`='".$date."' LIMIT 1");

echo "<p>" . is_array($ping) ? print_r($ping) : "np" . "</p>";

 

it prints out 1

[lemmin]

Does it print "Array ( ) 1"? If so, you have an empty array. Wich still means that something about your query is wrong. My suggestion remains to try to echo the $res['id'] and $date before the query and check your table manually to see if those values align.

[clanstyles]

ill even remove the time stamp checking

 

$ping = mysql_query("SELECT AVG(size) FROM `serverInfo` GROUP BY `timestamp` WHERE `id`='".$res['id']."'");

 

still doesn't work. No matter what it return "1" for $ping

[lemmin]

Just use:

$ping = mysql_query("SELECT AVG(size) FROM `serverInfo`);

And see what the print_r($ping) gives you. It should be something like:

Array ( [avsize] => 200 ) 1

 

If it doesn't, I would be surprised.

[clanstyles]

nope lol

 

test16

Resource id #5

1

 

test15

Resource id #6

1

 

pooo17

Resource id #7

1

 

[teng84]

yes because you have to use the fetch array or assoc to have the value

so lemmin be surpise now LOL

[teng84]

ill even remove the time stamp checking

 

$ping = mysql_query("SELECT AVG(size) FROM `serverInfo` GROUP BY `timestamp` WHERE `id`='".$res['id']."'");

 

still doesn't work. No matter what it return "1" for $ping

 

BUT is id here a primary key????? if so you dont need a group by

[clanstyles]

lol thanks forgot that yeah.

 

But as for that how it works is.

 

It is grouped by the day ( timestamp ).

For every day i need the average AND by the server's ID ( P rimary )

[lemmin]

Just for fun, try the same thing without the apostrophes. And give the AVG() function a variable to return to, like so:

$ping = mysql_query("SELECT AVG(size) as avsize FROM serverInfo");

 

I'm pretty sure removing the apostrophes wont do anything, but if it doesn't work in this format I would really be confused.