php queries for lots of data / manipulation (mysql backend)

[egiblock]

not sure if i should file this under php help or mysql help.

 

 

i'm building a site with a mysql backend (phpnuke based) and i'm creating a module with a lot of data. / reporting.

 

 

in the old days, i would have done this in access, but i don't think that down the road the number of connections into the database will be able to support it.

 

 

what i would have done is a bunh of custom queries in access to manipulate the data, and then just use cases in the code for what i needed to display.  this ovbiously is different with mysql.

 

the php side of things is all new to me as well.

 

 

what's the easiest way to do the queries with php?  so far i have come to the conclusion that for every query i do with php, i'll need 5-10 lines of code for each connection and the query structure.

 

is there a way to save the queries in mysql ?

 

any help / tutorials on this would be greatly appreciated.

 

thanks !!

 

???

 

 

[.josh]

You only need to open a connection one time.  You can then use that connection for all of your queries.

[egiblock]

You only need to open a connection one time.  You can then use that connection for all of your queries.

 

i figured that out so far, which was good for me to figure out early..  i'm more concerned with all of the code per query.

 

just for displaying two result tables with calculations, i have the following:

 

** please note it's for a golf scoring system...

 

<?php require_once('golfscoringdbcon.php'); ?>

<?php

mysql_select_db($database_golfscoring, $golfscoring);
$query_CourseDetail = "select * from tbl_courses WHERE $id = tbl_courses.courseID";
$CourseDetail = mysql_query($query_CourseDetail, $golfscoring) or die(mysql_error());
$row_CourseDetail = mysql_fetch_assoc($CourseDetail);
$totalRows_CourseDetail = mysql_num_rows($CourseDetail);

mysql_select_db($database_golfscoring, $golfscoring);
$query_EventListingByCourse = "SELECT tbl_events.eventID, tbl_events.eventName, tbl_events.eventDateTime, tbl_events.eventCourseID FROM tbl_events WHERE $id = tbl_events.eventCourseID ORDER BY eventDateTime DESC";
$EventListingByCourse = mysql_query($query_EventListingByCourse, $golfscoring) or die(mysql_error());
$row_EventListingByCourse = mysql_fetch_assoc($EventListingByCourse);
$totalRows_EventListingByCourse = mysql_num_rows($EventListingByCourse);

$YI = "SELECT (y1 + y2 + y3 + y4 + y5 + y6 + y7 + y8 + y9) AS yardsIn FROM tbl_courses WHERE courseID = $id";
$YO  = "SELECT (y10 + y12 + y13 + y14 + y15 + y16 + y17 + y18) AS yardsOut FROM tbl_courses WHERE courseID = $id";

$yardsIn =  mysql_query($YI, $golfscoring);
$yardsOut =  mysql_query($YO, $golfscoring);

$Yin = mysql_fetch_assoc($yardsIn);
$Yout = mysql_fetch_assoc($yardsOut);

$yardsTotal = $Yin['yardsIn'] + $Yout['yardsOut'];

?>

 

html code here..  blah blah blah..

 

<?php
mysql_free_result($CourseDetail);

mysql_free_result($EventListingByCourse);
mysql_free_result($totalyards);
?>

 

 

Can Queries be "pre coded" in mysql like you can in access so the queries don't have to be spelled out in the php file ?