jim7567g
Posts: 38
|
| Posted: 12/26/2004, 2:19 PM |
|
Hello I am having trouble with the following query in my php page and would like to know if anyone can help?
$SQLstatement = "Select Month(TimeStamp) - 1, Sum(junkout_parts_total_saved) " .
"From production_tracking Where Year(TimeStamp)=" . $SelectedYear .
"Group By Month(TimeStamp) - 1";
This query works in phpMyAdmin but does not work in the php script.
I get the following error:
Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /home/test.php on line 28
Line 28 reads:
while ($row = mysql_fetch_row($result)) {
$software[$row[0]] = $row[1];
}
Best Regards,
Jim
_________________
Jim |
 |
 |
peterr
Posts: 5971
|
| Posted: 12/26/2004, 2:31 PM |
|
The query may not be a problem, but you may be using wrong database connection, don't have correct DB permissions, never executed $SQLstatement, etc.
Try debugging this and for more ideas see:
http://groups-beta.google.com/groups?q=mysql_fetch_row%...lr=&sa=N&tab=wg
http://www.google.com/search?hl=en&lr=&q=mysql_fetch_ro...ult+resource%22
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
jim7567g
Posts: 38
|
| Posted: 12/26/2004, 2:42 PM |
|
I added and "or Die" statement and the result returned was:
Cannot get query
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 'By Month(TimeStamp) - 1' at line 1
If I remove the SUM action and the GROUP BY action then the script works.
Like this:
$SQLstatement = "Select Month(TimeStamp) - 1, junkout_parts_total_saved " .
"From production_tracking Where Year(TimeStamp)=" . $SelectedYear ;
The interesting this is that it works in phpMyAdmin but somehow the syntax is not correct in the php page.
Does this offer any more clues?
Thanks
_________________
Jim |
|
|
|
jim7567g
Posts: 38
|
| Posted: 12/26/2004, 2:51 PM |
|
The problem is with this clause:
"Group By Month(TimeStamp) - 1";
Somehow this statement must need to be formated differently in the php page than in phpMyAdmin.
Any clues?
_________________
Jim |
|
|
|
peterr
Posts: 5971
|
| Posted: 12/26/2004, 3:01 PM |
|
Sorry but I also don't understand why this would work from one PHP program but not another. Possibly the database extension, connection method or record fetching method is different. Maybe try creating a separate PHP program that connects to the database and executes only the SQL.
Other questions that may provide some clues:
Are you connecting to the same database from CCS as from phpMyAdmin?
And are you selecting the same year in CCS and phpMyAdmin, because $SelectedYear would be invalid in phpMyAdmin. I'm assuming that you used syntax like "Select Month(TimeStamp) - 1, Sum(junkout_parts_total_saved) From production_tracking Where Year(TimeStamp)=2004 Group By Month(TimeStamp) - 1";
Also, how many rows are returned by phpMyAdmin for the same year?
And which version of MySQL are you using?
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
jim7567g
Posts: 38
|
| Posted: 12/26/2004, 3:12 PM |
|
Are you connecting to the same database from CCS as from phpMyAdmin?
Yes, I am using the same database and table
And are you selecting the same year in CCS and phpMyAdmin
Yes, in phpMyAdmin I am typing in the year 2004 which is the same as $SelectedYear. I verified that this variable was returned correctly.
Also, how many rows are returned by phpMyAdmin for the same year?
In phpMyAdmin 12 rows are returned, one for each month. In the 0 month or January I have two rows that are combined by the SUM function. The database has a total of 13 records.
And which version of MySQL are you using?
4.0.22-standard
_________________
Jim |
|
|
|
jim7567g
Posts: 38
|
| Posted: 12/26/2004, 3:16 PM |
|
This is the page from the MySQL manual that I am referencing to write the query:
http://dev.mysql.com/doc/mysql/en/GROUP-BY-Modifiers.html
_________________
Jim |
|
|
|
jim7567g
Posts: 38
|
| Posted: 12/26/2004, 3:25 PM |
|
I got passed the error message by making this change:
was: "Group By TimeStamp"
changed to " Group By TimeStamp"
An extra space at the beginning of the clause made it work.
But now I get no data output???? hahahahhahah
I hate being a beginner.
_________________
Jim |
|
|
|
peterr
Posts: 5971
|
| Posted: 12/26/2004, 3:27 PM |
|
Looks you did everything that you could and I am probably even less experienced with PHP & MySQL. You may want to try contacting CCS product support if no one else responds here.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
peterr
Posts: 5971
|
| Posted: 12/26/2004, 3:29 PM |
|
Oh, that's better - I can't believe you're a beginner 
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
jim7567g
Posts: 38
|
| Posted: 12/26/2004, 3:30 PM |
|
The problem is not with CCS, I tried rewriting the script in my editor to test it.
_________________
Jim |
|
|
|
jim7567g
Posts: 38
|
| Posted: 12/26/2004, 3:33 PM |
|
By the way.. Thanks for the help. I appreciate it.
_________________
Jim |
|
|
|
