$count =0;
$result1 = mysql_query("SELECT fwid FROM sbsw WHERE fword = '".$searchText."'");
while ($result2= mysql_fetch_array($result1))
{
$result3 = mysql_query("SELECT fsyn FROM wrsyn WHERE fwid = '".$result2[$count]."'");
$result4= mysql_fetch_array($result3);
print $result4[$count].'<br>';
$count++;
}
mysql_free_result($result1);
mysql_free_result($result3);
2 Answers
Let's have a look at how mysql_fetch_array works - for example if you have table structure like
id | name | surname | role
1 John Smith user
2 Peter Qeep user
3 Mark Ziii admin
When you execute a query SELECT * FROM table and then loop $result = mysql_fetch_array($query), $result will always be an array(4) containing
[0] => id,
[1] => name,
[2] => surname,
[3] => role
Therefore, when you execute the query $result3 = mysql_query("SELECT fsyn FROM wrsyn WHERE fwid = '".$result2[$count]."'");, in the first iteration, $count will be 0 which is the key for the result returned by the previous query, however in any further iteration it will increase and that will lead to an undefined key. This means that you have to stop using the variable $count and just use $result2[0] instead.
Also, way better approach to this would be using MySQL JOIN, in your example it would be SELECT w.fsyn FROM sbsw s JOIN wrsyn w ON s.fwid = w.fwid WHERE s.fword = "'.$searchText.'";
Comments
Please, use reasonable variable names and indent properly. You're getting one column from each row as you're only printing out once for each iteration over your rows.
Basically: For each row, print the value of a column.
The $count-variable decided which column, but it obviously didn't make sense at it counts the row you're at.
Something like this should do it: (not tested)
$result1 = mysql_query("SELECT fwid FROM sbsw WHERE fword = '".$searchText."'");
while ($result2= mysql_fetch_array($result1))
{
$result3 = mysql_query("SELECT fsyn FROM wrsyn WHERE fwid = '".$result2['fwid']."'");
$result4= mysql_fetch_row($result3);
for($x = 0; $x < count($result4); $x++){
print $result4[$x].'<br>';
}
}
mysql_free_result($result1);
mysql_free_result($result3);
Oh and I changed fetch_array to fetch_row in the inner loop to ease iteration.
5 Comments
cypher
This doesn't solve the main problem -
$result2[$count], therefore it will not work.
Alexander Sagen
Oh my, thanks :) Should work now though, as the closest solution to his approach, even though a join obviously is better
cypher
After the edit it won't work either, you are using mysql_fetch_array which is BY DEFAULT returning a numeric-indexed array, you'd need to use the option
MYSQL_ASSOC (mysql_fetch_array($query, MYSQL_ASSOC);) to get associative array keys :-)Alexander Sagen
Don't think so.. From manual: array mysql_fetch_array ( resource $result [, int $result_type = MYSQL_BOTH ] )
cypher
Oh, my bad, I always thought the default value was
MYSQL_NUM.
JOINs