Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Posts
    18

    Unanswered: a more effecient query?

    hi,

    im currently developing a web database for a conservation genetics company. on a few of the forms i have long textareas in the shape of collums that allow the input of multiple rows once the text input has been parsed for carriage returns (i know this is hazardous but theyre adamant). what im trying to write at the moment is a script to query the database and build a string to display if the user wishes to edit the data. the affected tables are:

    PCRPosition(idPCRPosition(PK), PCRRun_idPCRRun(FK), Sample_idSample(FK), PCRPositionNum, PCRPositionAttributes)

    SequencerLane(idSequencerLane(PK), PCRPosition_idPCRPosition(FK), SequencingRun_idSequencingRun, SequencingLaneNum, Sequence)

    SequencingRun(idSequencingRun, various attribs......)

    and this is my script:

    $queryLane = "SELECT * FROM SequencerLane WHERE SequencingRun_idSequencingRun = "
    . $idSequencingRun;

    if(!($resultLane = @ mysql_query($queryLane, $connection)))
    showerror();

    $pcr = "";
    $place = "";
    $sample = "";

    while($rowLane = mysql_fetch_array($resultLane))
    {

    $queryPCR = "SELECT * PCRPosition WHERE idPCRPosition = " .
    $rowLane["PCRPosition_idPCRPosition"];

    if(!($resultPCR = @ mysql_query($queryPCR,$connection)))
    showerror();

    $rowPCR = mysql_fetch_array($resultPCR);

    $sample .= $rowPCR["Sample_idSample"];
    $sample .= "\r";
    $place .= $rowPCR["PCRPositionNum"];
    $place .= "\r";
    $pcr .= $rowPCR["PCRRun_idPCRRun"];
    $pcr .= "\r";
    }

    now were still waiting on our server to arrive, so im not even sure this script is correct - but im sure there must be a more effecient means of extracting the data with a single sql query. im just not sure how - or i cant quite picture how the data will be structured.

    if anyone has the inclination to discuss this with me i would be very grateful indeed.

    cheers

    j

  2. #2
    Join Date
    Feb 2004
    Location
    Ninth Hell
    Posts
    19
    A simple way to do what you want is:

    Code:
    $query = 'SELECT PCRPosition_idPCRPosition FROM SequencerLane WHERE SequencingRun_idSequencingRun = '
    $query .= $idSequencingRun
    $res1 = mysql_query($query) or die(mysql_error());
    while(list($row) = mysql_fetch_row($res1))
    {
      $query2 = 'SELECT Sample_idSample, PCRPositionNum, PCRRun_idPCRRun FROM PCRPosition WHERE idPCRPosition = '
      $query2 .= $row
      $res2 = mysql_query($query2) or die(mysql_error());
      while(list($row1, $row2, $row3) = mysql_fetch_row($res2))
      {
        $sample .= $row1;
        $sample .= "\r";
        $place  .= $row2;
        $place  .= "\r";
        $pcr    .= $row3;
        $pcr    .= "\r";
      }
    }
    Last edited by death2all; 02-20-04 at 03:55.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •