[RESOLVED] Any way to do NESTED QUERIES with mysqli

[bostonmacosx]

So I have two mysqli->query statements.
One loops fetch_object() and then inside that one based on the values in that loop a second query needs to fire off to pull some more information.

Is there a way to do this as mysqli_free_result releases the results then you can't get any more of the original look.

I know doing this gets and out of sync error however want to see if it is possible.

Rob

[bradgrafelman]

Can you show us the two queries and explain what it is you're trying to do?

A query inside of a loop of another query is a huge red flag for unoptimized SQL code that could (and probably should) be using JOINs instead.

[bostonmacosx]

Edit 1 0 2 1 0
Edit 1 0 2 1 0
Edit 1 0 2 1 0
Edit 1 0 2 1 0
Edit 1 0 2 2 0
Edit 1 0 2 2 0
Edit 1 0 2 2 0
Edit 1 0 2 2 0
Edit 1 0 2 3 0
Edit 1 0 2 3 0
Edit 1 0 2 3 0
Edit 1 0 2 4 0
Edit 1 0 2 4 0

So there is the data.
Disregard the EDIT column.
So what I have is heat_id swimmer_relay_order meet_id event_id swimmer_id in a table called HEATS.

I have another table of events...the common thread is event_id.

What I want to do is print out each event in lets say a DIV.
In each DIV which I'm looping through the events..
I need to show each HEAT with 3-6 swimmers in each.

SO

DIV EVENT

DIV HEAT
DIV SWIMMER1
DIV SWIMMER2
etc
/DIV HEAT
/DIV EVENT

So I'm not sure how to line everything up. I can join the tables however it gives just X number rows of data. and I'm not sure how to split it up.

[leatherback]

You could do:

select * from HEATS
left join EVENTS
on HEATS.event_id = EVENTS.event_id
order by event

Then in your processing loop you store the last event, like:

$lastevent = '';
echo "<div class=\"Event\'> ";

while($row = mysql_fetch_array($results))
  {
  if($row['event'] <> $lastevent && &;astevent <> '';)
    {
    echo "End & start DIV EVENT";
    $lastevent = $row['event'];
    }
  // create all swimmer divs
  }

[bostonmacosx]

So the query which works is ....

SELECT * from events LEFT JOIN heats on events.event_id=heats.event_id LEFT JOIN swimmers ON heats.swimmer_id=swimmers.usr_id where heats.meet_id='$_POST[whichmeet]'order by event_order ASC

So now I have 4 HEATS for each event.

What I want to do now is have it represent like this on the page.

DIV
EVENT ID EVENT NAME SWIMMER(4X) LANE(4X)
/DIV

So I'm not sure how to concatenate it so I can have it appear like one because it now has 4 rows returned per event due to the join with the HEATS table.

[johanafm]

Always specify exactly what you want. I.e. event.name, event.id etc - not *

SELECT e.id, e.name, COUNT(h.*) AS heat_count, COUNT(s.*) AS swimmer_count
FROM event e
LEFT JOIN heats h ...
LEFT JOIN swimmers s ...
...
GROUP BY e.id, e.name

[bostonmacosx]

Yeah I know I need to clean that up.

What I don't understand is the relevance of the count. I'm in the $mysqli->fetch_object loop and I get to an event lets say #1 which has 4 swimmers in it. How do I SUBLOOP through the swimmers. I'm just in the event loop with a count...

[bostonmacosx]

So here is the query that I have"

SELECT

e.event_id,
h.heat_id,
GROUP_CONCAT(s.bthdate order by h.swimmer_relay_order SEPARATOR ':') as test,
GROUP_CONCAT(h.swimmer_id order by h.swimmer_relay_order) as swimmers,
GROUP_CONCAT(h.swimmer_relay_order order by h.swimmer_relay_order) as relayorder,
GROUP_CONCAT(CONCAT_WS(',',s.lstnm,s.frstnm) order by h.swimmer_relay_order SEPARATOR ':') as names,
GROUP_CONCAT(s.bthdate order by h.swimmer_relay_order SEPARATOR ':') as swbthdate
from events e

LEFT JOIN heats h on e.event_id=h.event_id 
LEFT JOIN swimmers s ON h.swimmer_id=s.usr_id where h.meet_id='$_POST[whichmeet]'
group by e.event_id
order by e.event_order,h.heat_id,h.swimmer_relay_order  ASC [/QUOTE]

Here are the results:

stdClass Object ( [event_id] => 1 [heat_id] => 1 [test] => 1066795200:1066795200:1066795200 [swimmers] => 1,1,0,1 [relayorder] => 1,2,3,4 [names] => Dow,Kay😃ow,Kay:😃ow,Kay [swbthdate] => 1066795200:1066795200:1066795200 )
stdClass Object ( [event_id] => 2 [heat_id] => 1 [test] => [swimmers] => 0,0,0,0 [relayorder] => 0,0,0,0 [names] => ::: [swbthdate] => )

I'm pretty close however!!!!!!
I'm not sure why the UNIX timestamps are coming out as 3 and not as 4 values.
And I still need to play around with adding a additional HEAT to the mix

---

events
Field 	Type 	Null 	Default 	Comments
event_id 	int(11) 	No 		
max_age 	varchar(5) 	No 		
min_age 	varchar(5) 	No 		
event_order 	int(11) 	No 		
event_name 	varchar(255) 	No 		
event_distance 	varchar(255) 	No 		
event_gender 	enum('M', 'F', 'MF') 	No 		
event_slots 	varchar(5) 	No 	3 	

heats
Field 	Type 	Null 	Default 	Comments
heat_id 	varchar(10) 	No 		
swimmer_relay_order 	int(4) 	No 		
lane_id 	varchar(11) 	No 		
meet_id 	varchar(20) 	No 		
event_id 	varchar(20) 	No 		
swimmer_id 	varchar(20) 	No 		
heat_time 	varchar(20) 	No 		
heat_place 	varchar(20) 	No 		
heat_points 	varchar(20) 	No 		


swimmers
Field 	Type 	Null 	Default 	Comments
usr_id 	int(11) 	No 		
frstnm 	varchar(50) 	No 		
lstnm 	varchar(50) 	No 		
pfrstnm 	varchar(50) 	No 		
plstnm 	varchar(50) 	No 		
pemail 	varchar(50) 	No 		
bthdate 	varchar(50) 	No 		
gender 	enum('M', 'F') 	No 		
active 	enum('Y', 'N') 	No 	N 	

[bradgrafelman]

Why are you using all of those GROUP_CONCAT()'s? I'm guessing you're just splitting them back apart in the PHP code? Seems pretty silly to smash things together only to immediately separate them back out again.

Don't do any concatenation. Instead, ORDER the results by the event, heat, and then swimmer. In your PHP code, store the previous event and heat in a temporary variable. As you fetch each row from the result set, check to see if the heat and/or the event of the current row has changed from the previous row and, if so, output a new header/whatever.

[bostonmacosx]

as being new to mysqli I'm not sure about the storing then using methodology.

Rob

[bradgrafelman]

It has nothing to do with MySQLi or databases at all... you just store the values in temporary variables...

$event = $heat = null;

while(loop through result set)
{
	if($row['event'] != $event)
	{
		if($event != null)
		{
			// output footer for previous event here
		}

	// output header for new event here

	// and then store the current event in the temporary variable
	$event = $row['event'];
}

// same thing here for the heat

// output the current row here
}