Page 1 of 1

qerry problem

Posted: Tue Aug 20, 2002 10:48 pm
by dinno2
what i am trying to acomplish is, to get a pilots total number of hours from mysql . The pilot simply enters each flight using a form that sends the information to mysql, the hours are listed under hours, simple enough. The querry i used in here works with mysql admin, but im doing something wrong here with the php end, i have been reading both php and mysql manuals, and have tried several differnt ways to go about this, with the same results, failure. ideas, or documentation sure would be appreciated.

heres the code i have, im new so dont laugh <G>

Code: Select all

<?php

mysql_connect (xxxx, xxxx, xxxx);

mysql_select_db (xxxx);
if ($pilotid == "")
&#123;$pilotid = '%';&#125;

$result = mysql_query ("SELECT * SUM(hours) AS Total FROM pirep where pilotid LIKE '$pilotid%'");
if ($row = mysql_fetch_array($result)) &#123;
do &#123;
print "<td>&#123;$row&#1111;'hours']&#125;</TD>\n";

?>

Posted: Tue Aug 20, 2002 10:58 pm
by nielsene
I have no experience with MySQL, having used other DBs, so I could be wide of the mark, but one thing that looks wrong to me is that in your query you've told it to rename SUM(hours) AS Total, so in your print I would think you would index $row['Total'] not 'hours'.

Posted: Tue Aug 20, 2002 11:34 pm
by dinno2
well that produces this error

Warning: Wrong parameter count for mysql_db_query() in /home/kunzair/public_html/total.php on line 8
Error in query: SELECT SUM(hours) AS Total FROM pirep where pilotid LIKE 'pilotid'.
even though that querry works fine in mysql admin.
its tough finding the right documentation on this. where did everyone learn this? trial and error?

Michael

Posted: Wed Aug 21, 2002 2:02 am
by Takuma
Fitrst of all you shouldn't use mysql_db_query and also I think you put the result as Total, so you should print out the result like "$row["Total"]" as nielsene said.

Code: Select all

<?php 

mysql_connect (xxxx, xxxx, xxxx); 

mysql_select_db (xxxx); 
if ($pilotid == "") &#123;
  $pilotid = '%';
&#125; 

$result = mysql_query ("SELECT * SUM(hours) AS Total FROM pirep where pilotid LIKE '$pilotid%'");
if ($row = mysql_fetch_array($result)) &#123; 
  do &#123; 
    print "<td>&#123;$row&#1111;"Total"]&#125;</TD>\n"; 
  &#125;
&#125;

?>
That's all I can say...

Re: qerry problem

Posted: Wed Aug 21, 2002 4:55 am
by mikeq
dinno2 wrote:what i am trying to acomplish is, to get a pilots total number of hours from mysql . The pilot simply enters each flight using a form that sends the information to mysql, the hours are listed under hours, simple enough. The querry i used in here works with mysql admin, but im doing something wrong here with the php end, i have been reading both php and mysql manuals, and have tried several differnt ways to go about this, with the same results, failure. ideas, or documentation sure would be appreciated.

heres the code i have, im new so dont laugh <G>

Code: Select all

<?php

mysql_connect (xxxx, xxxx, xxxx);

mysql_select_db (xxxx);
if ($pilotid == "")
&#123;$pilotid = '%';&#125;

$result = mysql_query ("SELECT * SUM(hours) AS Total FROM pirep where pilotid LIKE '$pilotid%'");
if ($row = mysql_fetch_array($result)) &#123;
do &#123;
print "<td>&#123;$row&#1111;'hours']&#125;</TD>\n";

?>
nielsene is correct it should be $row['Total'].

but in your select you can't have SELECT * SUM(hours)...

it has to be SELECT SUM(hours) as Total from...

If you wanted to use * then you would need to GROUP BY on all of the columns, but this rather defeats the purpose of SUM as each record would be unique giving you back each individual record rather than a SUM of group records.

You say that one of the errors is 'Wrong parameter count for mysql_db_query...', but in the code you posted nowhere was a call to mysql_db_query. But as Takuma said you should use mysql_query().

And it is okay to use single quotes (') around the field name, makes it easier if your string is enclosed in double quotes (")

print "<td>$row['Total']</td>";

Posted: Wed Aug 21, 2002 5:16 am
by mikeq
something else I forgot to mention if the $pilotid is the exact value don't use like and '%' as this can be a lot slower than =

...where pilotid = '$pilotid'