Extracting array data with loops

[meigwil]

Hi all,

I have a SQL query which returns values from two tables. It matches one record of table a with three records from table b.

When I use print_r($array) in a loop to view the contents, I get this:

Array
(
   [0]data x from table b
   [1]data y from table b
   [2]data z from table b
   [3]data from table a
   [4]data from table a
   [5]data from table a
   [6]data from table a
)

For each 3 arrays the data from table b (x, y, z) is different, but the data from table a is the same.

I want to extract the b data 3 times, and the a data once. Can I do this using a loop within a loop?

Thanks,

Mei

[laserlight]

What are you trying to do? It may be possible to write the SQL statement to get the result for you. Providing the related database schema and relationships between the tables would help.

[meigwil]

OK:

I'm building a system that provides results from competitions. People must request which competition they want, which is stored in the request table. Competition results are stored in the result table, with three records for each competition.

request table

Fields:

id_request
request_name
comp_number
reply_sent (true/false)

result table

Fields:

id_result
comp_number
name
area
position (first, second or third)

I'm using this SQL query:

$sql_rhag = "SELECT * FROM result AS c INNER JOIN request AS r ON r.comp_number = c.comp_number AND r.reply_sent = '0' ORDER BY id_request, position";

which returns:

Array
(
    [id_result] => 5
    [comp_number] => 482
    [name] => Hedd Wyn
    [area] => Felinheli
    [position] => 1

[id_request] => 1
[request_name] => Hedd's Dad
[comp_number] => 482
[reply_sent] => 0
)

Array
(
    [id_result] => 6
    [comp_number] => 482
    [name] => Dic Jones
    [area] => Caernarfon
    [position] => 2

[id_request] => 1
[request_name] => Hedd's Dad
[comp_number] => 482
[reply_sent] => 0
)

Array
(
    [id_result] => 7
    [comp_number] => 482
    [name] => Geraint Lloyd Owen
    [area] => llandwrog
    [position] => 3

[id_request] => 1
[request_name] => Hedd's Dad
[comp_number] => 482
[reply_sent] => 0
)

In the above case Hedd's Dad has requested results for competition number 482 (which Hedd has won!).

For each record in the request table (that has records of the same comp_number in the result table) it returns three results/arrays.

I need to return something similar to this:

to: Hedd's Dad : 1 Hedd Wyn 2 Dic Jones 3 Gerallt Lloyd Owen

Any ideas?

Thanks,

Mei

[meigwil]

Still haven't cracked this.

Would it be easier to extract the data from the result table to another table, but insert three records as one and then perform the loop on that table?

How about if I store the results from $row = mysql_fetch_array($result) as seperate arrays (array1, array2 etc)? In a loop, how would I change the name of a variable (array1 to array2 to array 3 etc).

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {

   $array1 = array(0 => $row["id_result"], 1 => $row["comp_name"] etc)

}

so I get a new set of arrays with all the uniwue data in each?

Thanks,

Mei

[meigwil]

Would using

$i = 1;

while ($row = mysql_fetch_array($result)) {

   $record[$i] = $row["id_result"];

}

mean that the $record variable will be $record1, $record2 etc?

Or am I completely off track?

Thanks,

Mei

[meigwil]

For anyone who's interested, here's what I did:

while ($row = mysql_fetch_array($result)) {

		if ($row["llwyfan"] == '1') {
			 $sql1 = "INSERT INTO new_result (comp_number, rhag_1) " .
			 "VALUES ('" . $row["rhif_testun"] . "', '" .
			 $row["enw_parti"] . "," . $row["cangen"] . "')";
		}elseif ($row["llwyfan"] == '2') {
			 $sql1 = "UPDATE new_result " .
			 "SET rhag_2 = '" . $row["enw_parti"] . "," . $row["cangen"] . 
                             "' WHERE comp_number = '" . $row["rhif_testun"]"'";
		}else {
		   $sql1 = "UPDATE new_result "
			 "SET rhag_2 = '" . $row["enw_parti"] . "," . $row["cangen"] . 
                             "' WHERE comp_number = '" . $row["rhif_testun"]"'"; 
		}

		$new_result = mysql_query($sql1) 
          or die ("Error - " . mysql_error() . " number " . mysql_errno());

}				

I then had a new table to use a

$sql_send_llwyfan = "SELECT comp_number, llwyfan_1, llwyfan_2, llwyfan_3 FROM new_result AS c " .
 "INNER JOIN new_request AS n ON r.comp_number = n.comp_number AND r.llwyfan_sent = '0'";

query.

Mei