[SOLVED] Query help

[teng84]

$ping = mysql_query("SELECT AVG(size), yourIPhere FROM serverInfo GROUP BY timestamp");

 

AVG(size) what is this? is this a field in your db

 

ok the above sample will give you

 

ip the average

ip the average

 

something like that but i dont know the exact field so i guess try to analyze

[clanstyles]

I posted the table above.

its

`id`, `serverid`, `size`, `filename`, `timestamp`

 

I need the `id` to match the one in the loop and `timestamp` to match up.

 

$id = $res['id'];

mysql_query("SELECT AVG(size) as avsize FROM `serverInfo` `id`='$id' GROUP BY `timestamp`") or die(mysql_error());

 

gives me the error

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '='16' GROUP BY `timestamp`' at line 1

[lemmin]

You forgot WHERE.

[clanstyles]

lawls at me .

 

But it isnow coming out with "1" again.

[teng84]

yes and maybe you have to put a tick on the size i believe its the reserve words

[clanstyles]

kk now were geitng somewehre this works but it doesn't

 

							$ping = mysql_query("SELECT AVG(size) FROM `serverInfo` WHERE `id`='$id' AND `timestamp`='$date'") or die(mysql_error());
							//$ping = mysql_query("SELECT AVG(size) FROM `serverInfo` WHERE `id`='".$res['id']."' GROUP BY `timestamp`") or die(mysql_error());
							//$ping = mysql_query("SELECT AVG(size) FROM `serverInfo`");
							$ping1 = mysql_fetch_array($ping);
							echo "<p>" . print_r($ping1) . "</p>";

This is working its printing out

 

Array ( [0] => [AVG(size)] => )

1

 

test15

Array ( [0] => [AVG(size)] => )

1

 

pooo17

Array ( [0] => [AVG(size)] => )

1

 

 

And i know there is data in there that matches.. So it shouldn't have a problem giving an average but it does and that god dam # 1 is still in there.

[teng84]

try to put a tick

AVG(`size`) if size is a field as i said it may be reserve or a function of sql

[lemmin]

I am pretty sure that you need to set a variable when you are directly modifying data through a query. You need something like you had before:

AVG(size) as avsize

 

I would really suggest trying a small test query and build it up, to see where the error occurs.

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

If that small query doesn't work than we can narrow down the problem.

[clanstyles]

Sorry, I went on vacation. Was out of town for a week.

 

Back !

 

$ping = mysql_query("SELECT AVG(size) FROM `serverInfo` WHERE `id`='$id' GROUP BY `timestamp`") or die(mysql_error());

 

That is what I have YET, it produces 1

[lemmin]

How many different rows have an id of $id? Make sure there is more than one, otherwise the average of one number is just that number.

[clanstyles]

Hey,

 

Yeah I narrowed it down.

mysql_query("SELECT AVG(size) as avg FROM `serverInfo` WHERE `id`='$id' GROUP BY `timestamp`");

it has something to do with GROUP BY `timestamp` Without it it works fine. this returns a blank result. i even said `id`='1' witch should work.

 

[clanstyles]

$ping = mysql_query("SELECT AVG(size) as avg FROM `serverInfo` GROUP BY `timestamp` AND `id`='$id'") or die(mysql_error());

 

Now this is returning a number BUT it doesn't see the number right. Its like it never gets to the `id`='$id' part. it just averages the entire thing.

[lemmin]

It doesn't make sence to group by setting a value (or testing), stick with the WHERE clause for that.

 

As for your previous post:

That means that all the rows that it finds that have that id value, all have the same Timestamp value (That sentence had way to many "that's" in it). I would imagine you want the timestamps to be different so try making sure that is the case.

[clanstyles]

Well how this works is.

At midnight  a script runs.

 

The script runs a command in bash. Once it runs it it waits for the output, granted this will take some time since its big. But in any case. It is going to then SAVE the data For each folder( like 30 ) it is puting it into serverInfo the "ServerID" so it can support many servers, and the size it prints out and the Folder's name and time it ran.

 

So, every day I need it to display the average of the size for the day ( midnight run ) BY each server.

[lemmin]

What data type is the timestamp? Is it an actual date with time or just a date (or just a number indicating the day)?

 

Assuming it is just a date, "GROUP BY timestamp" will only return one result (which is what is happening). Change your query to (only if timestamp only contains data unique to a day, not time):

"SELECT AVG(size) as avg FROM serverInfo WHERE timestamp='$theday' AND id='$id'"

[clanstyles]

 

CREATE TABLE IF NOT EXISTS `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  DEFAULT CHARSET=latin1 AUTO_INCREMENT=393 ;

 

--

-- Dumping data for table `serverInfo`

--

 

INSERT INTO `serverInfo` (`id`, `serverid`, `size`, `filename`, `timestamp`) VALUES

(285, 1, 4, 'index.php', '06/25/07'),

(284, 1, 4, 'header.php', '06/25/07'),

(283, 1, 4, 'graph.php', '06/25/07'),

(282, 1, 4, 'footer.php', '06/25/07'),

(281, 1, 4, 'data.php', '06/25/07'),

(280, 1, 4, 'config.php', '06/25/07'),

(279, 1, 36, 'TabathaHeavy Regular.ttf', '06/25/07'),

(278, 1, 60460, 'Data', '06/25/07'),

(277, 1, 4, 'testdata.php', '06/25/07'),

(276, 1, 4, 'ssh.php', '06/25/07'),

(275, 1, 4, 'main.php', '06/25/07'),

(274, 1, 4, 'login.php', '06/25/07'),

(273, 1, 4, 'index.php', '06/25/07'),

(272, 1, 4, 'header.php', '06/25/07'),

(271, 1, 4, 'graph.php', '06/25/07'),

(270, 1, 4, 'footer.php', '06/25/07'),

(269, 1, 4, 'data.php', '06/25/07'),

(268, 1, 4, 'config.php', '06/25/07'),

(267, 1, 36, 'TabathaHeavy Regular.ttf', '06/25/07'),

(266, 1, 60460, 'Data', '06/25/07'),

(265, 1, 4, 'testdata.php', '06/25/07'),

(264, 1, 4, 'ssh.php', '06/25/07'),

(263, 1, 4, 'main.php', '06/25/07'),

(262, 1, 4, 'login.php', '06/25/07'),

(261, 1, 4, 'index.php', '06/25/07'),

(260, 1, 4, 'header.php', '06/25/07'),

(259, 1, 4, 'graph.php', '06/25/07'),

(258, 1, 4, 'footer.php', '06/25/07'),

(257, 1, 4, 'data.php', '06/25/07'),

(256, 1, 4, 'config.php', '06/25/07'),

(255, 1, 60460, 'Data', '06/25/07'),

(254, 1, 4, 'testdata.php', '06/24/07'),

(253, 1, 4, 'ssh.php', '06/24/07'),

(252, 1, 4, 'main.php', '06/24/07'),

(251, 1, 4, 'login.php', '06/24/07'),

(250, 1, 4, 'index.php', '06/24/07'),

(249, 1, 4, 'header.php', '06/24/07'),

(248, 1, 4, 'graph.php', '06/24/07'),

(247, 1, 4, 'footer.php', '06/24/07'),

(246, 1, 4, 'data.php', '06/24/07'),

(245, 1, 4, 'config.php', '06/24/07'),

(244, 1, 60460, 'Data', '06/24/07'),

(243, 1, 4, 'testdata.php', '06/23/07'),

(242, 1, 4, 'ssh.php', '06/23/07'),

(241, 1, 4, 'main.php', '06/23/07'),

(240, 1, 4, 'login.php', '06/23/07'),

(239, 1, 4, 'index.php', '06/23/07'),

(238, 1, 4, 'header.php', '06/23/07'),

(237, 1, 4, 'graph.php', '06/23/07'),

(236, 1, 4, 'footer.php', '06/23/07'),

(235, 1, 4, 'data.php', '06/23/07'),

(234, 1, 4, 'config.php', '06/23/07'),

(233, 1, 60460, 'Data', '06/23/07'),

(232, 1, 4, 'testdata.php', '06/21/07'),

(231, 1, 4, 'ssh.php', '06/21/07'),

(230, 1, 4, 'main.php', '06/21/07'),

(229, 1, 4, 'login.php', '06/21/07'),

(228, 1, 4, 'index.php', '06/21/07'),

(227, 1, 4, 'header.php', '06/21/07'),

(226, 1, 4, 'graph.php', '06/21/07'),

(225, 1, 4, 'footer.php', '06/21/07'),

(224, 1, 4, 'data.php', '06/21/07'),

(223, 1, 4, 'config.php', '06/21/07'),

(222, 1, 60460, 'Data', '06/21/07'),

(221, 1, 4, 'work.txt', '06/21/07'),

(220, 1, 4, 'whatismyip', '06/21/07'),

(219, 1, 52, 'test', '06/21/07'),

(218, 1, 60500, 'technicolor', '06/21/07'),

(217, 1, 4, 'ssh.php', '06/21/07'),

(216, 1, 4, 'search.php', '06/21/07'),

(215, 1, 656, 'raiderboy', '06/21/07'),

(214, 1, 4, 'musicinfo.xml', '06/21/07'),

(213, 1, 49560, 'music', '06/21/07'),

(212, 1, 1300, 'layout.bmp', '06/21/07'),

(211, 1, 284, 'java', '06/21/07'),

(210, 1, 4, 'index.php', '06/21/07'),

(209, 1, 8, 'hl.class.php', '06/21/07'),

(208, 1, 4, 'header.php', '06/21/07'),

(207, 1, 116, 'gungame.sql', '06/21/07'),

(206, 1, 4, 'glider.php', '06/21/07'),

(205, 1, 4, 'footer.php', '06/21/07'),

(204, 1, 4, 'config.php', '06/21/07'),

(203, 1, 12, 'carmod.rar', '06/21/07'),

(202, 1, 12, 'blackmarket', '06/21/07'),

(201, 1, 4, 'ReadMe.txt', '06/21/07'),

(200, 1, 4, 'Jumba.rar', '06/21/07'),

(199, 1, 4, 'Gungame.rar', '06/21/07'),

(198, 1, 12, 'BrentAIM.txt', '06/21/07'),

(197, 1, 8, 'Brent.txt', '06/21/07'),

(196, 1, 112, 'BMMods.rar', '06/21/07'),

(195, 1, 4, 'testdata.php', '06/21/07'),

(194, 1, 4, 'ssh.php', '06/21/07'),

(193, 1, 4, 'main.php', '06/21/07'),

(192, 1, 4, 'login.php', '06/21/07'),

(191, 1, 4, 'index.php', '06/21/07'),

(190, 1, 4, 'header.php', '06/21/07'),

(189, 1, 4, 'graph.php', '06/21/07'),

(188, 1, 4, 'footer.php', '06/21/07'),

(187, 1, 4, 'data.php', '06/21/07'),

(186, 1, 4, 'config.php', '06/21/07'),

(185, 1, 60460, 'Data', '06/21/07'),

(286, 1, 4, 'login.php', '06/25/07'),

(287, 1, 4, 'main.php', '06/25/07'),

(288, 1, 4, 'ssh.php', '06/25/07'),

(289, 1, 4, 'testdata.php', '06/25/07'),

(290, 1, 0, '', '06/25/07'),

(291, 1, 0, '', '06/25/07'),

(292, 1, 0, '', '06/25/07'),

(293, 1, 0, '', '06/25/07'),

(294, 1, 0, '', '06/25/07'),

(295, 1, 1184, '193761048       American Dad\n', '06/25/07'),

(296, 1, 378134032, '4456    Andy Barker P_I_\n', '06/25/07'),

(297, 1, 1856561960, '702037384       Bag Boy\n', '06/25/07'),

(298, 1, 3856, '435888  Black Book\n', '06/25/07'),

(299, 1, 56, '3648    Bomb It\n', '06/25/07'),

(300, 1, 1776, '1136    Brenda Storm Project\n', '06/25/07'),

(301, 1, 1504, '1640    Case Closed\n', '06/25/07'),

(302, 1, 3544, '1985182640      Closer_The\n', '06/25/07'),

(303, 1, 1096, '310006936       Criminal Minds\n', '06/25/07'),

(304, 1, 73198720, '103555752       Desperate Housewives\n', '06/25/07'),

(305, 1, 401286392, '118720768       Drive\n', '06/25/07'),

(306, 1, 3848, '12163240        Family Guy\n', '06/25/07'),

(307, 1, 1640, '1560    Fetch\n', '06/25/07'),

(308, 1, 3368, '1624    Forever\n', '06/25/07'),

(309, 1, 3392, '1624    Imperfect Union\n', '06/25/07'),

(310, 1, 8160, '14930552        In Treatment\n', '06/25/07'),

(311, 1, 123616, '3512    Lifes\n', '06/25/07'),

(312, 1, 6560, '1632    Manchild\n', '06/25/07'),

(313, 1, 4712, '821619768       Monk\n', '06/25/07'),

(314, 1, 40551936, '50862152        Nina Quebrada\n', '06/25/07'),

(315, 1, 10048, '46875776        Parental Guidance Suggested\n', '06/25/07'),

(316, 1, 17924184, '26785736        Pope Dreams\n', '06/25/07'),

(317, 1, 59408328, '61743328        Sarah Silverman\n', '06/25/07'),

(318, 1, 1215882952, '1136    Shrek The Halls\n', '06/25/07'),

(319, 1, 4872, '29540280        South Pavilion_The\n', '06/25/07'),

(320, 1, 528833256, '25858096        Suspect\n', '06/25/07'),

(321, 1, 1504, '23140304        TSat\n', '06/25/07'),

(322, 1, 2110123232, '96761768        True Blood\n', '06/25/07'),

(323, 1, 89680832, '2744    Underfunded\n', '06/25/07'),

(324, 1, 5760, '1040    Untitled Cynthia Cider Project\n', '06/25/07'),

(325, 1, 1040, '7064    Vanished\n', '06/25/07'),

(326, 1, 1624, '', '06/25/07'),

(327, 1, 1184, '2 Dreadful Children\n 193761048', '06/25/07'),

(328, 1, 0, '   An Arctic Tale\n 4456', '06/25/07'),

(329, 1, 0, '  Army Wives\n 702037384', '06/25/07'),

(330, 1, 0, 'Bickford Schmeklers Cool Ideas\n 435888  Black Book\n 56', '06/25/07'),

(331, 1, 0, 'Bomb It\n 1776', '06/25/07'),

(332, 1, 0, 'Brenda Storm Project\n 1504', '06/25/07'),

(333, 1, 0, 'Case Closed\n 3544', '06/25/07'),

(334, 1, 0, '  Closer_The\n 1096', '06/25/07'),

(335, 1, 0, '   Criminal Minds\n 73198720', '06/25/07'),

(336, 1, 0, 'Curb Your Enthusiasm\n 103555752', '06/25/07'),

(337, 1, 0, '   Dexter\n 118720768', '06/25/07'),

(338, 1, 0, 'Extras\n 12163240', '06/25/07'),

(339, 1, 0, 'Family Guy\n 1640', '06/25/07'),

(340, 1, 0, 'Fetch\n 3368', '06/25/07'),

(341, 1, 0, 'Forever\n 3392', '06/25/07'),

(342, 1, 0, 'Imperfect Union\n 8160', '06/25/07'),

(343, 1, 0, '', '06/25/07'),

(344, 1, 0, 'Lifes\n 6560', '06/25/07'),

(345, 1, 0, 'Manchild\n 4712', '06/25/07'),

(346, 1, 0, '   Monk\n 40551936', '06/25/07'),

(347, 1, 0, 'Mr and Mrs Smith\n 50862152', '06/25/07'),

(348, 1, 0, 'Nina Quebrada\n 10048   Nip Tuck\n 46875776', '06/25/07'),

(349, 1, 0, 'Parental Guidance Suggested\n 17924184', '06/25/07'),

(350, 1, 0, 'Plumm Summer\n 26785736', '06/25/07'),

(351, 1, 0, 'Pope Dreams\n 59408328', '06/25/07'),

(352, 1, 0, 'Reaper\n 61743328', '06/25/07'),

(353, 1, 0, 'Sarah Silverman\n 1215882952', '06/25/07'),

(354, 1, 0, 'Shrek The Halls\n 4872', '06/25/07'),

(355, 1, 0, '', '06/25/07'),

(356, 1, 0, '   State of Mind\n 25858096', '06/25/07'),

(357, 1, 0, 'Suspect\n 1504', '06/25/07'),

(358, 1, 0, '', '06/25/07'),

(359, 1, 0, '  Tell Me You Love Me\n 96761768', '06/25/07'),

(360, 1, 0, 'True Blood\n 89680832', '06/25/07'),

(361, 1, 0, 'Ugly Betty\n 2744', '06/25/07'),

(362, 1, 0, 'Underground_The\n 1040', '06/25/07'),

(363, 1, 0, 'Untitled Tom Kapinos Project\n 7064', '06/25/07'),

(364, 1, 0, 'Virgin of Akron Ohio_The', '06/25/07'),

(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'),

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

(380, 16, 4, 'ajax.js', '06/25/07'),

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

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

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

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

(385, 16, 4, 'getSpace.php', '06/25/07'),

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

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

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

(389, 16, 128, 'keys', '06/25/07'),

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

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

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

 

thats the table. Those are some "Fake" values that were generated.

for my date I have $date=date("mm/dd/yy");

returns Resource id #7

1

as the answer for everyone granted the # changes.

 

 

[lemmin]

You can use a DATETIME data type to make it a bit easier, but it doesn't matter.

 

The code in my previous post should work. Just make $theday equal to a string in the same format, like "06/25/07".

[clanstyles]

It is .  I set it too today and added some stuff for todays date.

[clanstyles]

Thx I fixed it lol STupid me u helped soo much .