Query in Query problems

------=_Part_9448_12316866.1208392969666
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hello All,

We many times encounter the situations of having Queries inside loop of
another query. Many times we can solve the issue by query joins but there
will be some situations where we cannot do it.

For Example:

function Change($id){

$qry_reg = "SELECT registrationID FROM registration WHERE event_id
= '$id'";
$res_reg =& $this->mdb2->query($qry_reg);

while ($row_reg = $res_reg->fetchRow()) {
$userid = $row_reg['registrationid'];
$sql_insert = "INSERT
INTO
run
(command, event_id, created, user_id, runtime)
VALUES
('topic_change', '".$id."', NOW(), '".$userid."', NOW())";
$res_insert =& $this->mdb2->query($sql_insert);

}
}

How can we deal with this kind of stuff? My concern is to just use the query
once instead on burdening the Mysql Server with so many queries.... This is
just one case. How do we actually deal with these kind of situations?

Thanks

------=_Part_9448_12316866.1208392969666--

Re: Query in Query problems

VamVan wrote:
> Hello All,
>
> We many times encounter the situations of having Queries inside loop of
> another query. Many times we can solve the issue by query joins but there
> will be some situations where we cannot do it.
>
> For Example:
>
> function Change($id){
>
> $qry_reg = "SELECT registrationID FROM registration WHERE event_id
> = '$id'";
> $res_reg =& $this->mdb2->query($qry_reg);
>
> while ($row_reg = $res_reg->fetchRow()) {
> $userid = $row_reg['registrationid'];
> $sql_insert = "INSERT
> INTO
> run
> (command, event_id, created, user_id, runtime)
> VALUES
> ('topic_change', '".$id."', NOW(), '".$userid."', NOW())";
> $res_insert =& $this->mdb2->query($sql_insert);
>
> }
> }

In this example you can just use a single query:

$query = "insert into run(command, event_id, created, user_id, runtime)
select 'topic_change', registrationID, NOW(), ".(int)."$userid, NOW()
FROM registration WHERE event_id='".(int)$id."'";


--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php