-1

I'm using the code below to query the database. 

mysql_query ("SELECT * FROM _$symbol ORDER BY date DESC;");     

$_10day = mysql_query ("SELECT AVG(close) FROM _$symbol limit 10;");
$_21day = mysql_query ("SELECT AVG(close) FROM _$symbol limit 21;");
$_50day = mysql_query ("SELECT AVG(close) FROM _$symbol limit 50;");

echo "$_10day\n";
echo "$_21day\n";
echo "$_50day\n";


mysql_query ("INSERT INTO _$symbol(_10day) VALUE ('$_10day');");

echo mysql_errno($sql) . ": " . mysql_error($sql). "\n";

I need to get the average of close from the database, and I'm using the AVG()function, then insert the return value into the database. The queries work in mysql however they do not work through the script. It does enter a value into the database, but it always enters 0. What am I doing wrong?

EDIT--solution found

$get10 = mysql_query ("SELECT AVG( close ) AS CloseAverage from ( SELECT close FROM _$symbol ORDER BY date DESC limit 10 ) sub1 ;");

$row = mysql_fetch_assoc($get10); $_10day = $row['CloseAverage'];

if (!mysql_query ("UPDATE _$symbol SET _10day = $_10day, _21day = $_21day, _50day = $_50day, _100day = $_100day, _120day = $_120day, _150day = $_150day, _200day = $_200day, _240day = $_240day, _20dayVol = $_20dayVol, _50dayVol = $_50dayVol where date = '$date';")) { echo "Update query failed"; }

I was querying it incorrectly apparently it needed to be selected as a sub query, solution is above, it is working now.

illcrx
  • 774
  • 1
  • 12
  • 32
  • 1
    where do you execute the query? you just wrote the query – xkeshav Sep 30 '14 at 13:51
  • PHP and yes just like that – illcrx Sep 30 '14 at 13:52
  • @illcrx: He asked you where the part of execution is. You just set up a query which never gets executed in the current snipped. – C4d Sep 30 '14 at 13:54
  • Ok well I guess that is where my problem is then. how do I execute it? I have just been making sure the query works in mysql then putting it in as you see above – illcrx Sep 30 '14 at 13:55
  • Hah lol. Yeah I thought you did that... ill post an example. – C4d Sep 30 '14 at 13:55
  • Use **proper** variable concatenation: `FROM _' . $symbol . ' limit` my eyes hurt from this kind of worst practice. – Daniel W. Sep 30 '14 at 14:01
  • @DanFromGermany There is absolutely nothing wrong with writing variables directly into double quoted strings. Where did you get the idea it was? – worldofjr Sep 30 '14 at 14:07
  • what is the benefit/drawback of doing it the two ways? (I am still learning here) Thanks @DanFromGermany – illcrx Sep 30 '14 at 14:07
  • @DanFromGermany, can you explain please, I would like to know why – illcrx Sep 30 '14 at 14:11
  • @DanFromGermany In a double quoted string, no. `$string = 'string'; echo "This is a $string";` is fine. `$string = 'string'; echo 'This is a $string';` is not. – worldofjr Sep 30 '14 at 14:11
  • Think of more complex situations. It doesn't work using arrays, anon functions, properties,... It's not as readable, it's not clean, it's not consistent,.. Examples: http://codepad.viper-7.com/qemnfr – Daniel W. Sep 30 '14 at 14:15
  • @DanFromGermany You put curly brackets around array and object properties and functions. eg `{$array[0]}` and `{$obj->func()}`. – worldofjr Sep 30 '14 at 14:39
  • @DanFromGermany Read this http://stackoverflow.com/questions/5605965/php-concatenate-or-directly-insert-variables-in-string – worldofjr Sep 30 '14 at 14:42

2 Answers2

0

$_10day is a mysql result, not a value. You can't just put a result into a query string and expect it to work.

You need to get the result data first, eg;

while($row = mysql_fetch_assoc($_10day)) {
     $10day = $row['AVG(close)'];
}

Then you can execute your insert query;

mysql_query ("INSERT INTO _$symbol (_10day) VALUES ('$10day')");

Hope this helps.

worldofjr
  • 3,868
  • 8
  • 37
  • 49
  • I was not able to get this to work, since I should be returning an AVG from the query, would the mysql_fetch_assoc be the right way to go, from what I am seeing that is used for arrays? $get10 = mysql_query ("SELECT AVG(close) FROM _$symbol limit 10;"); while($row = mysql_fetch_assoc($_10dayget) { $_10day = $row['AVG(close)'];'} mysql_query ("INSERT INTO _$symbol(_10day) VALUE ('$_10day');"); this is what I have now. thanks @worldofju – illcrx Sep 30 '14 at 15:29
  • As you only have one value returned, give `mysql_fetch_array` a go. The data would be saved in `$row[0]`. – worldofjr Sep 30 '14 at 15:37
  • You might also need to make your AVG select more explicit. Try `SELECT AVG(close) AS CloseAverage FROM _$symbol limit 10`. – worldofjr Sep 30 '14 at 15:41
  • Ok please forgive me but how do I implement that? '$10day = $row[0];' – illcrx Sep 30 '14 at 15:42
  • Yes `$10day = $row[0];` – worldofjr Sep 30 '14 at 15:43
  • I cant seem to get anything to work! I have been working on this issue for too too long, I know its simple but I dont know what Im doing wrong, Im sure your answer is correct I must have something else wrong and/or implementing it wrong. Also I have 2 kids Im watching at home so past several hours havent been spent doing anything on the project, I will definitely let you know when I get it working – illcrx Sep 30 '14 at 22:10
  • No worries. Take note of the `SELECT` statement a few comments above. This might solve your problems. – worldofjr Sep 30 '14 at 22:12
  • So I tried the AS CloseAverage statement is that going to save that as a variable in mysql for me without having to go through all that PHP? – illcrx Sep 30 '14 at 22:20
  • No, it just returns the average with the name `CloseAverage` in the result set. So you could access it with `$row['CloseAverage']` when using `mysql_fetch_assoc` but still `$row[0]` when using `mysql_fetch_array` (assuming it's the first column asked for). – worldofjr Sep 30 '14 at 22:24
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/62213/discussion-between-worldofjr-and-illcrx). – worldofjr Sep 30 '14 at 22:24
0

Here's an example on how to manage multiple result-rows:

function connect()
{
    $db = mysql_connect("localhost","username","password") or die("your error msg");
    mysql_select_db("database_name");
    return $db;
}

$db = connect();

$query = "SELECT value1 FROM my_table";
$result = mysql_query($query, $db);

while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
    // This one will loop through the data in your output//
    $outputValue = $row['value1'];

}

For inserting data:

// Continued from the state above //
$query = "INSERT INTO table_name (column1) VALUES (value1)";
$result = mysql_query($query, $db) or die(mysql_error());
C4d
  • 3,183
  • 4
  • 29
  • 50