Page 1 of 1

SQL + php array result

Posted: Wed Jun 22, 2011 1:26 am
by mhonnphp
I encounter a big query with 2 sub queries in my maintaining project.
I want to remove this sub queries, is it possible to do something like this :

Code: Select all

function subquery(){
   $sql = 'SELECT * FROM Table2';
   $result = mysql_query($sql);
   while($row = db_fetch_array($result)){
       $rows[0] = $row[0];
   }
   return $rows;
}

$sql = 'SELECT Table1.*, sub1.* FROM Table1,( ' . subquery()  . ' )sub1 
           WHERE sub1.field1 = 1 
           AND Table1.field1 = sub1.field1' ;
$result = mysql_query($sql);
...
I tried it but getting error because mysql can't read php array.
If possible, code patern is really appreciated.

Thanks

Re: SQL + php array result

Posted: Wed Jun 22, 2011 3:30 am
by Apollo
I use something like this:

Code: Select all

function query_array($sql)
{
	$rc = mysql_query($sql);
	while ($row=mysql_fetch_assoc($rc)) $rows[]=$row;
	return $rows;
}
Gives the selected rows as associative arrays, with the field names as indices. For example a print_r dump would give:

Array (
  [0] => Array ( [firstname] => John [lastname] => Doe )
  [1] => Array ( [firstname] => Justin [lastname] => Bieber )
)