Retrieving some info from database once and the other info in a loop?

[the_loserkid]

I have a database called xyz. I’ve got tables in the name of all my family members. Call them mark, peter, jack and jill.

Now lets say I got 7 columns name, age, gender, hobbies, diary, date and mood. I want the last 4 to have multiple posts that I will be able to loop through while the name, age, gender remain on top of the page.

Like this;

Name:
Age:
Gender:

Posts to Diary:

Post 1
Post 2
Post 3 etc

Now when I try the usual code, there is only 1 post under the first 3 columns with the name, age and gender but 3 under diary. I get 3 loops even though I echo out the first 3 columns only with 1 post each.

How do I echo that out once and then loop the diary entries?

<?php

include 'inc/db.php';

try {
	$sql = 'select * from jack';
	$results = $pdo->query($sql);
	$data = $results ->fetchAll (PDO::FETCH_OBJ);
	}
	
catch(PDOException $e) {
	echo 'Sorry, could not fetch data at this point.' . $e->getMessage();
	}

?>

    <?php foreach ($data as $dataitem): ?>
    
    <p> Name of Customer: <?php echo $dataitem->Name; ?> </p>
    <p> Age: <?php echo $dataitem->age; ?> </p>
    <p> Gender: <?php echo $dataitem->gender; ?> </p>
    
    <?php endforeach; ?>

EDIT
This post has been reformatted by enclosing the code block in 3 backticks
```
on their own lines.

[droopsnoot]

Can you describe the database table layout a bit more please? In particular, is there a relation between the ‘name’ column in your ‘jack’ table and the fact that you have a table called ‘jack’? Will name, age and gender always be the same in the ‘jack’ table? Does your query bring out the data you are expecting if you add a var_dump() just before your loop?

I think I’d tend to do something like this pseudo-code:

$data = $results->fetchAll();
$lastname = "";
foreach ($data as $dataitem):
if ($dataitem->Name != $lastname) {
  $lastname = $dataitem->Name;
  echo "<p>" . $dataitem->Name . " - " . $dataitem->age . " - " . $dataitem->gender . "</p>";
  }
// echo diary postings here
endforeach;

So that will loop around all the data you retrieved from the query, but only display the name, age and gender when the name is different to the last record you displayed.

But I’m puzzled as to why you would have tables for each individual family member. Although I’m not 100% certain of what you’re doing, I’d have two tables:

Persons: person_id (auto-increment), person_name, person_age, person_gender
Diary: diary_id (auto-increment), personid (from Persons), Post text, Post Date

So all posts for all people would be in the diary table, linked to the person who made the post by the person id.

But ignore all that if that’s not what you were trying to do with the individual tables. And I’m not exactly sure what the problem you’re having is.

[the_loserkid]

Thanks for the response. I’m writing another script that I will develop into something more complex.

For now, I’m trying to make profiles for each member that will contain a ‘wall’ for each diary entry they make. There will be no log ins just yet. For now all I want is for me to be able to enter diary entries for each member and when I open the profile, the name, age and gender should show along with all of the diary entries. I’m not very good or familiar with linking tables. What approach do you reckon I take? I like the 2 table idea. How can I go ahead with that. I can then later work on the log ins for each member and the sign up etc.

[the_loserkid]

I created new tables for name and diary entries. I tried joining them and it is very confusing at the moment.

I have thought of a better test project. How would you go about this one?

Test app for: Vet Clinic

Requirements: Name of pet, breed, color, name of owner, date of vaccinations given and date of vaccinations due. There will only be multiple entries in the vaccinations column.

Function: To be able to retrieve vaccination info by searching up a pet owners name.

So it will look like this:

Name of Pet:
Breed:
Color:
Name of Owner:

/** vaccination info in loop because there will be more than one entry**/
Date Given:2007 for rabies
Due Date:2008 for rabies.

Date Given: 2007 for aids
Due Date: 2008 for aids
/** end loop **/

So do I go with joining tables with 2 tables for pet info and vaccination info? Or do I go with a single table for every pet with those columns. If joining tables is the better way, I will have to look up some tuts.

[droopsnoot]

You need to read up on a subject called “database normalisation” which covers how to decide what to put into each table to minimise data duplication. For your vet clinic application you should have multiple tables:

Owners table - owner-id, name, address, phone, email, etc.
Breeds table - breed-id, breed, species
Pets table - pet-id, breed-id, owner-id, dob, colour (id, probably)
Vaccinations table - pet id, vaccination type id (probably linked to the stock items table), due date

Then of top of that you’d have a history table to record all consultations for each pet, when each vaccination was given, weight and other observations, loads of other stuff.

Because the first four columns of the table you described will always be the same (except the owner name, if the pet is re-homed) you wouldn’t duplicate them for each row of your table, you’d put them in a separate table and reference that table in your queries.

[the_loserkid]

Thanks. Just what I needed to know. Will hopefully be back with the entire working code when I get it right.