running a query against each value in an array 1

[EvilAsh]

Can anybody help me??

I have a page that runs a sql query that returns an array of values.

I then want to run a further query incorporating each of the values from the array.

So, the first query (I have changed the names for simplicity's sake):

select c1 from table1

returns an array of 1 2 3 etc with the variable name of $c1.

The next query:

select value from table2 where value=$c1

Should return value1 value2 value3 etc.

However, it only appears to return the first item from the array ie value1, and then it stops. I don't get an error message.

Am I trying to oversimplify or is this a schoolboy error?!?

Thanks.

 

[sleipnir214]

Insufficient data for a meaningful answer.

What value is in $c1? What type of variable is $c1: a singleton, an array? How are you referencing any value(s) in $c1?

But it would be incorrect to say that your first query returns an array. It would be more correct to say that your first query returns a single-column resultset, which your script may fetch, one row at a time, and store in a PHP array by looping across the returned value(s).





Want the best answers? Ask the best questions! TANSTAAFL!

 

[EvilAsh]

Ok, here is the code from the query that extracts the first set of results:

<?php
require "db.inc.php";
mysql_connect ($host, $user, $pwd)mysql_select_db($server);
$query = "SELECT DISTINCT team FROM games ORDER BY team ASC   ";; 
$result = mysql_query($query)  
while($i = mysql_fetch_array($result)) 
{ $team= $i["team"];include "reshead.php";}
?>

The value $team would contain the values first, second, third as "FirstSecondThird".

The include file would then perform a query that (in theory) returns a table for each of the records where team=$team.

This is coded as follows:

<?php
//get db file
require "db.inc.php";
mysql_connect ($host, $user, $pwd) or die("Cannot connect to mysql server");
mysql_select_db($server)
$query = "SELECT DISTINCT games.date,  games.venue, games.opponent, games.result, games.id, games.team, games.fixtype
FROM games WHERE games.played = 'yes' and games.team= '$team'ORDER BY games.date aSC
  ";; 
$result = mysql_query($query)    or die ("Cannot do de query");
$numrows=mysql_num_rows($result);

echo "<table  width=100% align='left' cellspacing ='2'>";
while($i = mysql_fetch_array($result)) { 
$date = date( "d/m/Y", strtotime($i[0]));
$venue=$i[venue];
$opponent=$i[opponent];
$res=$i[result];
$id=$i[id];
$fixtype=$i[fixtype];
$team=$i[team];
echo"
<tr><td><strong>$custom $res $opponent</strong></td></tr>
<tr><td><p>$team Team, $date</p></td>\n
<tr><td ><p>$fixtype Fixture</p></td><tr>
<tr><td><a href='report.php?id=$id'><p>View Match Report</p></a></td><tr>";
}
echo "</table>";

?>

i hope this is clearer. Thanks for reading.

 

[sleipnir214]

The value $team would contain the values first, second, third as "FirstSecondThird".

I don't know whether your script is expecting data it will not get or whether you're describing the situation inexactly. But it would be more correct to say "The value $team will contain, in succession, each of the values from the first query"


But what happens? The script produces a table for only one team?



Want the best answers? Ask the best questions! TANSTAAFL!

 

[EvilAsh]

But it would be more correct to say "The value $team will contain, in succession, each of the values from the first query"

Agreed

But what happens? The script produces a table for only one team?

Yes. It produces a table for where team=First but does not go on to produce a table for where team=Second, team=Third etc.

 

[dkdude]

Don't know if it makes a difference, but try "quoting" your array associations....

Instead of

$venue=$i[venue];

try

$venue=$i["venue"];

Regards

 

[jpadie]

the problem is that the file you are including is all in the global scope. therefore in the second while loop across the internal recordset (the games recordset), you are expressly resetting $i (which is also used for the first recordset.

i would counsel not storing the interior script in an include file - why bother re-parsing the data each loop. instead put it in a function so your code looks like:

<?php
require "db.inc.php";
@mysql_connect ($host, $user, $pwd) or die("cannot connect to dbserver");
@mysql_select_db($server) or die ("cannot select database") ;
$query = "SELECT DISTINCT team FROM games ORDER BY team ASC   ";
$result = mysql_query($query) or die(mysql_error());
while($i = mysql_fetch_array($result))
{ getGames($i['team']);}

function getGames($teamID) {
$query = "SELECT DISTINCT games.date,  games.venue, games.opponent, games.result, games.id, games.team, games.fixtype
FROM games WHERE games.played = 'yes' and games.team= '".mysql_escape_string($teamID)."' ORDER BY games.date ASC  ";
$result = mysql_query($query)    or die ("Cannot do de query");
$numrows=mysql_num_rows($result);

echo "<table  width=100% align='left' cellspacing ='2'>";
while($i = mysql_fetch_array($result)) {
$date = date( "d/m/Y", strtotime($i[0]));
$venue=$i["venue"];
$opponent=$i["opponent"];
$res=$i["result"];
$id=$i["id"];
$fixtype=$i["fixtype"];
$team=$i["team"];
echo"
<tr><td><strong>$custom $res $opponent</strong></td></tr>
<tr><td><p>$team Team, $date</p></td>\n
<tr><td ><p>$fixtype Fixture</p></td><tr>
<tr><td><a href=\"report.php?id=$id\"><p>View Match Report</p></a></td><tr>";
}
echo "</table>";
}
?>