Hi,
I don't have any documentation you can use I think, but I have some comments on what you say...
By part 1 you say:
how do I handle the workflow when the data in related tables doesn't yet exist? Do I force a user to enter data into 5 different forms first? Do I have a place to enter the new data on the main form? Do I create temporary records as part of a step-by-step process?
Step by step answer:
* In MySQL I would say... If you have data which has to be inserted
in serveral tables, you must first check if all conditions are ok.
So, do some selects to check if everythin in your database is ok,
and after that, execute the query. But, when you really want to
make complex database driven applications, choose PostgreSQL! This
database is better build for complexity (and not only that ;) ),
and supports transactions and references, and more stuff like that
which can be really helpful to you... And when you compare MySQL
against PostgreSQL... PostgreSQL is a heavy system compared with
MySQL, also free, but when you look at the functionality: SUPERIOR
* Never force a user to enter data into 5 forms if you can do it
with 1!!! That won't give you a usable application in which users
think: "Yeah, this is really great to use"
* I don't see what you mean with entering data on the main form,
but, read my previous tip again :)
* Use variables to store all data, you can do this with sessions,
cookies, $_POST, whatever... But only execute your queries when
you have all data you want... That will really keep your script
clear, and you will earn more style points :-)By point 2 you allmost ask me to refer to PostgreSQL... And, in PHP you don't have to place the whole query on 1 rule... use that... This is what I mean:
$query = " SELECT * FROM table1, table2 WHERE table1.id = table2.id AND table1.value = 'value' OR table2.value = '' ORDER BY table1.name ";
is less readable than:
$query = " SELECT * FROM table1, table2
WHERE table1.id = table2.id
AND table1.value = 'value'
OR table.value = ''
ORDER BY table1.name ";I hope this helps you :)
Greetz
Chris Lott wrote:
Where can I learn topics such as:
1) Examples of complex PHP applications entering data into complex related table structures. For instance, if I am writing an application to create a class catalog that has 5 related tables, how do I handle the workflow when the data in related tables doesn't yet exist? Do I force a user to enter data into 5 different forms first? Do I have a place to enter the new data on the main form? Do I create temporary records as part of a step-by-step process?
2) Complex display with database results-- particularly when working with many joined tables, displaying results effectively for reporting (show me all departments, within that all classes, within that who is teaching, sorted by department). The queries aren't hard, but if optimized into one big query the constructs for displaying seem to get ugly. For instance, given a query that gives me these results as $result (joined together from three tables):
+---------+----------+----------+-----------+-------+ | first | last | relation | city | state | +---------+----------+----------+-----------+-------+ | Chris | Beks | business | Fairbanks | AK | | Robert | Hannon | friend | Fairbanks | AK | | Cindy | Lott | family | Fresno | CA | | Derryl | Hartz | business | Seattle | WA | | Kirsten | O'Malley | friend | Seattle | WA | +---------+----------+----------+-----------+-------+
It seems like there must be a more efficient way to get a listing of each city and who lives there than:
$currentcity = ''; $counter = 1;
while ($thisrow = mysql_fetch_array($result)) { if ($currentcity <> $thisrow['city']) { if ($counter > 1) { echo '</blockquote>'; } echo '<h1>' . $thisrow['city'] . '</h1>'; echo '<blockquote>'; $currentcity = $thisrow['city']; } echo $thisrow['first'] . ' ' . $thisrow['last'] . '<br />'; $counter++; }
Although this is preferable to running separate queries:
$query = 'select lid, city, state from locations order by city, state';
$result = mysql_query($query) or die('Couldn\'t perform the query: ' . $query);
while ($thisrow = mysql_fetch_array($result)) { echo '<h1>' . $thisrow['city'] . ', ' . $thisrow['state'] . '</h1>'; echo '<blockquote>'; $pquery = "select first, last from people where lid = {$thisrow['lid']} order by last, first"; $presult = mysql_query($pquery) or die('Couldn\'t perform the query: ' . $query); while ($prow = mysql_fetch_array($presult)) { echo $prow['first'] . ' ' . $prow['last'] . '<br />'; } echo '</blockquote>'; }
c
