qerry problem

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
dinno2
Forum Newbie
Posts: 9
Joined: Sun Aug 11, 2002 10:25 am

qerry problem

Post 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";

?>
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post 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'.
dinno2
Forum Newbie
Posts: 9
Joined: Sun Aug 11, 2002 10:25 am

Post 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
User avatar
Takuma
Forum Regular
Posts: 931
Joined: Sun Aug 04, 2002 10:24 am
Location: UK
Contact:

Post 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...
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Re: qerry problem

Post 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>";
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post 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'
Post Reply