Linking two recordsets together

How can you link two recordsets together so that the primary key used
in one is from the other recordset. I need to have two repeat regions
(one within another). Both of the repeat regions are from recordsets
from the same table, just different queries in mysql.

My code looks like this now:

mysql_select_db($database_connBeetles, $connBeetles);
$query_rsBeetles = sprintf("SELECT id, date, habitat, replica,
trap_number, species, number_of_individuals,
COUNT(Number_of_individuals), SUM(Number_of_individuals),
COUNT(DISTINCT Species), date_format(date, '%%D %%M %%Y') as date2,
ref, ref2, week FROM data WHERE Habitat = %s GROUP BY ref ORDER BY ref
ASC", GetSQLValueString($colname_rsBeetles, "text"));
$rsBeetles = mysql_query($query_rsBeetles, $connBeetles) or
die(mysql_error());
$row_rsBeetles = mysql_fetch_assoc($rsBeetles);
$totalRows_rsBeetles = mysql_num_rows($rsBeetles);

mysql_select_db($database_connBeetles, $connBeetles);
$query_Recordset2 = "SELECT SUM(Number_of_individuals), Species, ref
FROM data WHERE ref = '".$row_rsBeetles['ref']."' GROUP BY species
ORDER BY species ASC";
$Recordset2 = mysql_query($query_Recordset2, $connBeetles) or
die(mysql_error());
$row_Recordset2 = mysql_fetch_assoc($Recordset2);
$totalRows_Recordset2 = mysql_num_rows($Recordset2);

The ref field is a combination of the habitat, date and replica
fields. Which separates the entries into the different datasets that
need to be queried. The second recordset (Recordset2) will need to be
repeated for each repetition of the higher recordset (rsBeetles), and
be linked so that the data for each of the Recordset2 repetitions is
the same data that is used for the rsBeetles recordset.

Is there any way to do this?

On 31 Jan, 16:35, "chris_huh" <chris....@gmail.com> wrote:
> How can you link two recordsets together so that the primary key used
> in one is from the other recordset. I need to have two repeat regions
> (one within another). Both of the repeat regions are from recordsets
> from the same table, just different queries in mysql.
>
> My code looks like this now:
>
> mysql_select_db($database_connBeetles, $connBeetles);
> $query_rsBeetles = sprintf("SELECT id, date, habitat, replica,
> trap_number, species, number_of_individuals,
> COUNT(Number_of_individuals), SUM(Number_of_individuals),
> COUNT(DISTINCT Species), date_format(date, '%%D %%M %%Y') as date2,
> ref, ref2, week FROM data WHERE Habitat = %s GROUP BY ref ORDER BY ref
> ASC", GetSQLValueString($colname_rsBeetles, "text"));
> $rsBeetles = mysql_query($query_rsBeetles, $connBeetles) or
> die(mysql_error());
> $row_rsBeetles = mysql_fetch_assoc($rsBeetles);
> $totalRows_rsBeetles = mysql_num_rows($rsBeetles);
>
> mysql_select_db($database_connBeetles, $connBeetles);
> $query_Recordset2 = "SELECT SUM(Number_of_individuals), Species, ref
> FROM data WHERE ref = '".$row_rsBeetles['ref']."' GROUP BY species
> ORDER BY species ASC";
> $Recordset2 = mysql_query($query_Recordset2, $connBeetles) or
> die(mysql_error());
> $row_Recordset2 = mysql_fetch_assoc($Recordset2);
> $totalRows_Recordset2 = mysql_num_rows($Recordset2);
>
> The ref field is a combination of the habitat, date and replica
> fields. Which separates the entries into the different datasets that
> need to be queried. The second recordset (Recordset2) will need to be
> repeated for each repetition of the higher recordset (rsBeetles), and
> be linked so that the data for each of the Recordset2 repetitions is
> the same data that is used for the rsBeetles recordset.
>
> Is there any way to do this?

You need to look at JOIN, but more than that you SERIOUSLY need to
look at formatting your code!

On 31 Jan, 17:06, "Captain Paralytic" <paul_laut...@yahoo.com> wrote:
> On 31 Jan, 16:35, "chris_huh" <chris....@gmail.com> wrote:
>
>
>
> > How can you link two recordsets together so that the primary key used
> > in one is from the other recordset. I need to have two repeat regions
> > (one within another). Both of the repeat regions are from recordsets
> > from the same table, just different queries in mysql.
>
> > My code looks like this now:
>
> > mysql_select_db($database_connBeetles, $connBeetles);
> > $query_rsBeetles = sprintf("SELECT id, date, habitat, replica,
> > trap_number, species, number_of_individuals,
> > COUNT(Number_of_individuals), SUM(Number_of_individuals),
> > COUNT(DISTINCT Species), date_format(date, '%%D %%M %%Y') as date2,
> > ref, ref2, week FROM data WHERE Habitat = %s GROUP BY ref ORDER BY ref
> > ASC", GetSQLValueString($colname_rsBeetles, "text"));
> > $rsBeetles = mysql_query($query_rsBeetles, $connBeetles) or
> > die(mysql_error());
> > $row_rsBeetles = mysql_fetch_assoc($rsBeetles);
> > $totalRows_rsBeetles = mysql_num_rows($rsBeetles);
>
> > mysql_select_db($database_connBeetles, $connBeetles);
> > $query_Recordset2 = "SELECT SUM(Number_of_individuals), Species, ref
> > FROM data WHERE ref = '".$row_rsBeetles['ref']."' GROUP BY species
> > ORDER BY species ASC";
> > $Recordset2 = mysql_query($query_Recordset2, $connBeetles) or
> > die(mysql_error());
> > $row_Recordset2 = mysql_fetch_assoc($Recordset2);
> > $totalRows_Recordset2 = mysql_num_rows($Recordset2);
>
> > The ref field is a combination of the habitat, date and replica
> > fields. Which separates the entries into the different datasets that
> > need to be queried. The second recordset (Recordset2) will need to be
> > repeated for each repetition of the higher recordset (rsBeetles), and
> > be linked so that the data for each of the Recordset2 repetitions is
> > the same data that is used for the rsBeetles recordset.
>
> > Is there any way to do this?
>
> You need to look at JOIN, but more than that you SERIOUSLY need to
> look at formatting your code!

I thought JOIN was just for joining two tables together, can it be
used for two recordsets using the same table then?

On 31 Jan, 17:09, "chris_huh" <chris....@gmail.com> wrote:
> On 31 Jan, 17:06, "Captain Paralytic" <paul_laut...@yahoo.com> wrote:
>
>
>
>
>
> > On 31 Jan, 16:35, "chris_huh" <chris....@gmail.com> wrote:
>
> > > How can you link two recordsets together so that the primary key used
> > > in one is from the other recordset. I need to have two repeat regions
> > > (one within another). Both of the repeat regions are from recordsets
> > > from the same table, just different queries in mysql.
>
> > > My code looks like this now:
>
> > > mysql_select_db($database_connBeetles, $connBeetles);
> > > $query_rsBeetles = sprintf("SELECT id, date, habitat, replica,
> > > trap_number, species, number_of_individuals,
> > > COUNT(Number_of_individuals), SUM(Number_of_individuals),
> > > COUNT(DISTINCT Species), date_format(date, '%%D %%M %%Y') as date2,
> > > ref, ref2, week FROM data WHERE Habitat = %s GROUP BY ref ORDER BY ref
> > > ASC", GetSQLValueString($colname_rsBeetles, "text"));
> > > $rsBeetles = mysql_query($query_rsBeetles, $connBeetles) or
> > > die(mysql_error());
> > > $row_rsBeetles = mysql_fetch_assoc($rsBeetles);
> > > $totalRows_rsBeetles = mysql_num_rows($rsBeetles);
>
> > > mysql_select_db($database_connBeetles, $connBeetles);
> > > $query_Recordset2 = "SELECT SUM(Number_of_individuals), Species, ref
> > > FROM data WHERE ref = '".$row_rsBeetles['ref']."' GROUP BY species
> > > ORDER BY species ASC";
> > > $Recordset2 = mysql_query($query_Recordset2, $connBeetles) or
> > > die(mysql_error());
> > > $row_Recordset2 = mysql_fetch_assoc($Recordset2);
> > > $totalRows_Recordset2 = mysql_num_rows($Recordset2);
>
> > > The ref field is a combination of the habitat, date and replica
> > > fields. Which separates the entries into the different datasets that
> > > need to be queried. The second recordset (Recordset2) will need to be
> > > repeated for each repetition of the higher recordset (rsBeetles), and
> > > be linked so that the data for each of the Recordset2 repetitions is
> > > the same data that is used for the rsBeetles recordset.
>
> > > Is there any way to do this?
>
> > You need to look at JOIN, but more than that you SERIOUSLY need to
> > look at formatting your code!
>
> I thought JOIN was just for joining two tables together, can it be
> used for two recordsets using the same table then?- Hide quoted text -
>
> - Show quoted text -

Read up on table aliases.
FROM data a
JOIN data b

a and b are treated as 2 completely separate tables that just happen
to contain exactly the same records.

SELECT b.id,b.title,a.id,a.title
FROM article a JOIN article b ON a.id=b.id
LIMIT 10