strange issue regarding nested mysql_fetch_array calls

[ItecKid]

Hello,

I have two different tables I need to extract data from. They are linked by a number, that is to say the value of the field 'number' in table 1 is the same value as the value of the field 'num' in table 2.

I can select data from the first table fine, however I get issues when trying to get data from the second table:

while ($row = mysql_fetch_array ($results)) { #First table results, this loop works fine
$data = sprintf (SELECT * FROM table_2 WHERE num = '%s'", $row['number']);
$foo = mysql_query ($data);
$bar = mysql_fetch_array ($foo);
echo $bar['field_name'];
}

This only works on the FIRST iteration of the loops. Subsequent iterations, while they do execute (I can access the changing values of $row fine) do seem to make the call to mysql_fetch_array or the echo statement. It doesn't even echo the same thing over and over again; it acts as if it doesn't exist.

I can verify that resource $foo is also changing each iteration through the loop.

Thanks for any help you can provide.

[saiprem]

Copy the following code and try, I hope everything will works fine

while ($row = mysql_fetch_assoc ($results)) { #First table results, this loop works fine
$data = sprintf ("SELECT * FROM table_2 WHERE num = '%s'", $row['number']);
$foo = mysql_query ($data);
$bar = mysql_fetch_assoc ($foo);
echo $bar['field_name'];
}

Hello,

I have two different tables I need to extract data from. They are linked by a number, that is to say the value of the field 'number' in table 1 is the same value as the value of the field 'num' in table 2.

I can select data from the first table fine, however I get issues when trying to get data from the second table:

while ($row = mysql_fetch_array ($results)) { #First table results, this loop works fine
$data = sprintf (SELECT * FROM table_2 WHERE num = '%s'", $row['number']);
$foo = mysql_query ($data);
$bar = mysql_fetch_array ($foo);
echo $bar['field_name'];
}

This only works on the FIRST iteration of the loops. Subsequent iterations, while they do execute (I can access the changing values of $row fine) do seem to make the call to mysql_fetch_array or the echo statement. It doesn't even echo the same thing over and over again; it acts as if it doesn't exist.

I can verify that resource $foo is also changing each iteration through the loop.

Thanks for any help you can provide.

[ItecKid]

Hello,
Thank you for your reply. It seems to be a specific defect when attempting to associate $row with the num field, as I have the exact same query structure in other parts of the program, (where num comes from a different source than $row) Is it possible to have something to do with number being the primary, auto-incrementing key in table 1?

[urtrivedi]

when you use mysql_fetch_array will have number as array key that is 0,1,2,3,....., if you want to access it thourgh field name then you must user mysql_fetch_assoc();
Also I think you need to set an inner loop because second query may give you more than one record. Right now you will be able to print ouput only once for each second query.

$bar = mysql_fetch_array ($foo);
echo $bar['field_name'];

[ItecKid]

when you use mysql_fetch_array will have number as array key that is 0,1,2,3,....., if you want to access it thourgh field name then you must user mysql_fetch_assoc();
Also I think you need to set an inner loop because second query may give you more than one record. Right now you will be able to print ouput only once for each second query.

$bar = mysql_fetch_array ($foo);
echo $bar['field_name'];

mysql_fetch_array() actually returns both the numeric array and associative array...alas, I have tried both ways with same result.

Also, the num field on table 2 is unique, so that query will only ever return one result.

[urtrivedi]

check do you have value in 'field_name' column in table 2 for all rows?

Also my another observation is as follows:
If table 2 contains only one row then you need not to execute subquery in side the loop you can simply join two tables and execute query only once.