Why returns the first element of an array only?

Question

$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);

Answer 1

dont u think $result4 too should also be in a while loop

Answer 2

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.

Answer 3

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.'";