Is this the best efficient way?

[jmcall10]

Hi,

 

I have 3 tables:

 

tbl_people (pid, name);

pidname

1Tom

2Dick

3Harry

4Roger

5Victor

6Daffy

7Mickey

8Morse

9Pete

10Dave

 

tbl_events (eid, date);

eiddate

12008-05-15

22008-05-16

32008-05-17

42008-05-18

52008-05-19

62008-05-20

72008-05-21

82008-05-22

92008-05-23

102008-05-24

 

tbl_people_events (peid, pid, eid);

 

peidpideid

111

121

131

142

152

162

173

183

194

1105

196

187

178

 

a also have the following code:

 

<table border="1" align="center">

<tr>
<td></td>
<?PHP

$events = mysql_db_query($database, "SELECT * FROM tbl_events ORDER BY date asc") or die ("$DatabaseError");
$i=1;
while (
$qry1 = mysql_fetch_array($events)) 
{
echo "<td>$qry1[date]</td>";
?>
</tr>


<tr>
<?PHP
$players = mysql_db_query($database, "SELECT * FROM tbl_people") or die ("$DatabaseError");
while (
$qry2 = mysql_fetch_array($players)) 
{
echo "<td>$qry2[name]</td>";

$events2 = mysql_db_query($database, "SELECT * FROM tbl_events ORDER BY date asc") or die ("$DatabaseError");
while (
$qry3 = mysql_fetch_array($events2)) 
{
		// find if player has played for a match
			$playerPlayed = "False";

			$people_events = mysql_db_query($database, "SELECT * FROM tbl_people_events where eid=$qry3[eid]") or die ("$DatabaseError");
			while (
				$qry4 = mysql_fetch_array($people_events)) 
				{
				if($qry4[pid] == $qry2[pid])
				{
				$playerPlayed = "True";
				break;
				} else 
				{
				$playerPlayed = "False";
				}
				}

			if($playerPlayed == "True"){
			echo "<td align=\"center\">Y</td>";
			}else{
			echo "<td align=\"center\">N</td>";
			}
}
echo "</tr>"; 
}
?>

</table>

 

This works. However I want to know if this is the best way to code this as I am rather rotten at php and mysql

 

Please can someone test and advise.

 

Thanks in advance

 

jmcall10

[JasonLewis]

Well I would do it slightly different. Personal preference I guess

In tbl_people I would have a column called 'events' and I would add the appropriate event id's separated by commas. So I could use it as an array. There is no right or wrong way really, but it depends on if you want to cut back the amount of tables you have as well as the amount of rows.

[jmcall10]

Ok, say I stick with my table design.

 

Is the way I have nested 3 loops the most efficient way?

 

Or am I using to much php resources or something?

 

at the moment I am looping through all the people

then for ech person looping through the events

  then for each event checking to see if that person attended or not.

 

It is my looping code I want to know if it is efficient or not

[Mchl]

Performing a query within a loop (and within a loop) is seldom the best solution. What is the inforamtion you want to get here? A list of people with their attendance on each event?

 

#     Person    Evnt1  Evnt2
1	Tom        Y         Y
2	Dick        Y        Y
3	Harry       Y        N
4	Roger      N         Y

 

Like that?

[jmcall10]

Yes that is the output I would like

 

I dont want to change my tables tho

 

Can you suggest anything?

[Mchl]

Maybe like this.

 

1. Select a list of all users and event_ids where they were present

SELECT pid,name,GROUP_CONCAT(eid) AS events FROM tbl_people CROSS JOIN tbl_people_events USING (pid) GROUP BY name;

 

This should give results like this

pid, name, events
9,Pete, '4,6'
...

 

Store this data into array, exploding 'events' into a nested array

$people = Array( 
9 => Array(
  "name" => "Pete", 
  "events" => Array(4,6),
)
);

 

2.Then get list of all events

SELECT * FROM tbl_events

 

And store this data into array as well.

 

3. Having these two arrays it should be possible to create a HTML table with data you need.

 

[jmcall10]

Ok I have ran the first query in phpmyadmin and I get back a table almost like what you have mentioned.

 

It has pid, name, events

 

However under the events heading it says [bLOB - 3 B] or [bLOB - 1 B] etc

 

is this correct?

[Mchl]

Yes. [bLOB - 3 B] means there are 3 bytes of data. In your case these will be two event ids and a separating comma.

When you assign this to a variable in PHP script, you should get a nice string.

[jmcall10]

Ok I understand that first part

 

onto the second part.

 

I understand arrays but could you explain what is happening in the code you posted as I am unfamiliar with nested arrays sorry

 

I appreciate all this help by the way   Thank you

[Mchl]

It's not a code as such. It's just how I imagine aray with stored data about players would look like if you created it by hand

 

Nested arrays are easy.

 

Consider this

$arr1 = Array(0 => "a",1 => "b");                   // that's just an array with two values, easy as a pie
$arr1["nestedArr"] = Array(0 => "c", 1 => "d");   //it's again an array with two values, but this time it's nested into $arr1 array

var_dump($arr1);   // do this to see structure of $arr
echo $arr1[0];   //  will echo "a"
echo $arr1["nestedArr"]  // will echo "Array()"
echo $arr1["nestedArr"][0]   //will echo "c"

 

 

[jmcall10]

ok I will have to ponder this as I can see what is happening however it means drastically re-writing my code

 

I am not the best coder as it is lol

 

I have one question however. With my original design. What is wrong with nesting a query within a loop?

 

Im assuming it has something to do with performance?

[Mchl]

Querying a database is always resource expensive operation.

Perhaps there won't be much difference in your case, when there are only a few iterations of a loop, the database is relatively small and not many people will use it at one time.

 

It's always better however, to use less queries if possible.

[jmcall10]

Thank you so much for your time and effort

 

This is a small project I am working on and will only be viewed by a small number of people.

 

If I had the experience then Id love to re-code it more efficiently.

 

It seems too large for me to do as it messes with my head haha

 

Thanks anyway for all the help

[JonnoTheDev]

I would not use this approach for a few of reasons:

n tbl_people I would have a column called 'events' and I would add the appropriate event id's separated by commas

 

1. It is not normalised

2. If a person is removed from an event it requires more application logic to update the table

3. If you wanted to sort records by event ID more application logic is required

 

Your current design is optimal.

 

[jmcall10]

Neil, Thanks for your response.

 

I agree that my tables are set up optimally however do you have any code that would get me my desired result?

 

Thanks

 

jmcall10

[JonnoTheDev]

Follow Mchl's guidelines, however if you are not planning on migrating this to work from a SQL database rather than a MYSQL database I would prefer to use a INNER JOIN instead of a CROSS JOIN.