question about select query

hi

i have an enquiry table which collects information about users making
an online travel enquiry

the fields in the table are = StoryTitle, EndCity, mode, PricedFrom,
numAdults, numChildren, numInfants

presently the select query is


$sql = "SELECT Count(*) as Counts, StoryTitle, EndCity, mode,
PricedFrom, numAdults, numChildren, numInfants, ReturnLocation FROM
`enquiry` WHERE date_format(en_date,'%Y-%m-%d') BETWEEN '" .
$startDate . "' AND '" . $endDate . "' " . $enquiryText . "
Group By StoryTitle, EndCity, mode, PricedFrom, numAdults,
numChildren, numInfants, ReturnLocation
Order By Counts Desc, StoryTitle Desc, mode, PricedFrom, numAdults,
numChildren, numInfants, ReturnLocation";

while ($row_rs_newEnquiries
= mysql_fetch_assoc($rs_newEnquiries))
{
$echoStr .= "
class='default'>".$row_rs_newEnquiries['Counts']."
class='default'>  ".$row_rs_newEnquiries['StoryTitle']."
class='default'>  ".$row_rs_newEnquiries['EndCity']."
class='default'>  ".$row_rs_newEnquiries['mode']."
class='default'>  ".$row_rs_newEnquiries['PricedFrom']."
class='default'>  ".$row_rs_newEnquiries['numAdults']."
class='default'>  ".$row_rs_newEnquiries['numChildren']."
class='default'>  ".$row_rs_newEnquiries['numInfants']."
  ".$row_rs_newEnquiries
['ReturnLocation']."
";
$total = $total +
$row_rs_newEnquiries['Counts'];
}

$totalamount=$row_rs_newEnquiries['totalvalue'];

echo "value of total enquiry is $ ". $totalamount;

?>

i have used $totalamount=$row_rs_newEnquiries['totalvalue']; both
inside the while loop and outside the while loop however this is not
working.

here Counts is the column which collects the number of times an
enquiry was made to a particular city example Sydney however Counts
DOES NOT exist as a field in the table and PricedFrom collects the
value of the $ amount to a city and this field exists in the table,
so multiplying Counts and PricedFrom should give the result

is the syntax of the select query correct as there is a comma before
Count(*) & am i reading the value of this multiplied value correctly

please advice.

thanks.

Re: question about select query

Sudhakar wrote:
> hi
>
> i have an enquiry table which collects information about users making
> an online travel enquiry
>
> the fields in the table are = StoryTitle, EndCity, mode, PricedFrom,
> numAdults, numChildren, numInfants
>
> presently the select query is
>
> >
> $sql = "SELECT Count(*) as Counts, StoryTitle, EndCity, mode,
> PricedFrom, numAdults, numChildren, numInfants, ReturnLocation FROM
> `enquiry` WHERE date_format(en_date,'%Y-%m-%d') BETWEEN '" .
> $startDate . "' AND '" . $endDate . "' " . $enquiryText . "
> Group By StoryTitle, EndCity, mode, PricedFrom, numAdults,
> numChildren, numInfants, ReturnLocation
> Order By Counts Desc, StoryTitle Desc, mode, PricedFrom, numAdults,
> numChildren, numInfants, ReturnLocation";
>

Surely there needs to be something in here to actually GET the data from
the database?

Something like

$rs_newEnquiries=mysql_query($sql);
$row_rs_newEnquiries=mysql_numrows($result);

> while ($row_rs_newEnquiries
> = mysql_fetch_assoc($rs_newEnquiries))
> {
> $echoStr .= "
> > class='default'>".$row_rs_newEnquiries['Counts']."
> > class='default'>  ".$row_rs_newEnquiries['StoryTitle']."
> > class='default'>  ".$row_rs_newEnquiries['EndCity']."
> > class='default'>  ".$row_rs_newEnquiries['mode']."
> > class='default'>  ".$row_rs_newEnquiries['PricedFrom']."
> > class='default'>  ".$row_rs_newEnquiries['numAdults']."
> > class='default'>  ".$row_rs_newEnquiries['numChildren']."
> > class='default'>  ".$row_rs_newEnquiries['numInfants']."
>   ".$row_rs_newEnquiries
> ['ReturnLocation']."
> ";
> $total = $total +
> $row_rs_newEnquiries['Counts'];
> }
>
> $totalamount=$row_rs_newEnquiries['totalvalue'];
>
> echo "value of total enquiry is $ ". $totalamount;
>
> ?>
>
> i have used $totalamount=$row_rs_newEnquiries['totalvalue']; both
> inside the while loop and outside the while loop however this is not
> working.
>
> here Counts is the column which collects the number of times an
> enquiry was made to a particular city example Sydney however Counts
> DOES NOT exist as a field in the table and PricedFrom collects the
> value of the $ amount to a city and this field exists in the table,
> so multiplying Counts and PricedFrom should give the result
>
> is the syntax of the select query correct as there is a comma before
> Count(*) & am i reading the value of this multiplied value correctly
>
> please advice.
>
> thanks.

Re: question about select query

Sudhakar wrote:
> hi
>
> i have an enquiry table which collects information about users making
> an online travel enquiry
>
> the fields in the table are = StoryTitle, EndCity, mode, PricedFrom,
> numAdults, numChildren, numInfants
>
> presently the select query is
>
> >
> $sql = "SELECT Count(*) as Counts, StoryTitle, EndCity, mode,
> PricedFrom, numAdults, numChildren, numInfants, ReturnLocation FROM
> `enquiry` WHERE date_format(en_date,'%Y-%m-%d') BETWEEN '" .
> $startDate . "' AND '" . $endDate . "' " . $enquiryText . "
> Group By StoryTitle, EndCity, mode, PricedFrom, numAdults,
> numChildren, numInfants, ReturnLocation
> Order By Counts Desc, StoryTitle Desc, mode, PricedFrom, numAdults,
> numChildren, numInfants, ReturnLocation";
>
> while ($row_rs_newEnquiries
> = mysql_fetch_assoc($rs_newEnquiries))
> {
> $echoStr .= "
> > class='default'>".$row_rs_newEnquiries['Counts']."
> > class='default'>  ".$row_rs_newEnquiries['StoryTitle']."
> > class='default'>  ".$row_rs_newEnquiries['EndCity']."
> > class='default'>  ".$row_rs_newEnquiries['mode']."
> > class='default'>  ".$row_rs_newEnquiries['PricedFrom']."
> > class='default'>  ".$row_rs_newEnquiries['numAdults']."
> > class='default'>  ".$row_rs_newEnquiries['numChildren']."
> > class='default'>  ".$row_rs_newEnquiries['numInfants']."
>   ".$row_rs_newEnquiries
> ['ReturnLocation']."
> ";
> $total = $total +
> $row_rs_newEnquiries['Counts'];
> }
>
> $totalamount=$row_rs_newEnquiries['totalvalue'];
>
> echo "value of total enquiry is $ ". $totalamount;
>
> ?>
>
> i have used $totalamount=$row_rs_newEnquiries['totalvalue']; both
> inside the while loop and outside the while loop however this is not
> working.
>
> here Counts is the column which collects the number of times an
> enquiry was made to a particular city example Sydney however Counts
> DOES NOT exist as a field in the table and PricedFrom collects the
> value of the $ amount to a city and this field exists in the table,
> so multiplying Counts and PricedFrom should give the result
>
> is the syntax of the select query correct as there is a comma before
> Count(*) & am i reading the value of this multiplied value correctly
>
> please advice.
>
> thanks.
>

You have no column named "total_value" in your list, so you can't
retrieve it.

For SQL syntax, try a database newsgroup - such as comp.databases.mysql.
That's where the MySQL experts hang out.

Once the SQL is correct, this is a good group for fetching the values -
like you did here.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================