Trouble combining two where statements with mysql & where

[Q695]

Where would I need to put a MySql statement in the following script (combine the two to get one output statement):

 

///////////////////////////////////////////////////////////////////////////////////

      $sql="select cid from provider where pid=\"$id\"";

      $result=@mysql_query($sql,$con) or die(death($sql));

      while ($row = @mysql_fetch_array($result)) {

          $cid = $row["cid"];

      echo("<option>" . $cid . "</option>");$cid . "<br>";

      }

 

/////////////////////////////////////////////////////////////////////////////////////

      $sql="select * from people where id=\"$cid\";";

      $result=@mysql_query($sql,$con) or die(death($sql));

            while ($row = @mysql_fetch_array($result)) {

          echo "$sql<P>";

              echo("<option>" . $row["first_name"] . " " . $row["last_name"] . "</option>");

      }

////////////////////////////////////////////////////////////////////////////////////////////

 

 

Whenever I change one little thing it seems to create a syntax error for some reason (provider is a xref table) ???  any advice on how to fix my issue, or code to do it .

[Q695]

The current output is:

      <select size="1" name="provider" onChange="this.form.provider.value=">

      <option selected>Select a provider</option><option>1</option><option>3</option></select> 

[Ken2k7]

Well $cid doesn't exist in the second query statement if you have it outside the first while loop.

[DarkWater]

Well $cid doesn't exist in the second query statement if you have it outside the first while loop.

 

Well, it DOES, it just contains the last value.  You should really try a JOIN.

[Ken2k7]

Well $cid doesn't exist in the second query statement if you have it outside the first while loop.

 

Well, it DOES, it just contains the last value.  You should really try a JOIN.

Right, sorry. Thanks for the correction.

[Q695]

That is what I was trying to do, but how would I write it?

[DarkWater]

#SQL
SELECT * FROM people AS p INNER JOIN provider AS pr ON (pr.cid = p.id) WHERE pr.pid = 1;

 

Change up the WHERE to use your variable. Just put it in where the 1 is.

[Q695]

I'm still confused .  How would I create the join with my mysql scripts not changing using PHP, since I will need to do something similar several times  ???

[Q695]

I figured it out with the help of PHPmyadmin 2.10.1 query editor  .

 

now I just need to fix it up a little but here is the basic script:

$sql = "SELECT `provider`.`cid`,

`people`.`id`,

`people`.`first_name`,

`people`.`last_name`

 

FROM provider, people

WHERE cid = id

ORDER BY `people`.`id` ASC;";

      $result=@mysql_query($sql,$con) or die(death($sql));

 

      while ($row = @mysql_fetch_array($result)) {

          $cid = $row["cid"];

          $first_name = $row["first_name"];

          $last_name = $row["last_name"];

          echo ("$first_name $last_name<br>");

 

[DarkWater]

Mine does the same thing, only it's shorter.

[magic2goodil]

You might try:

 

$provider=array();
$people=array();
$sql = "SELECT provider.cid, people.* FROM provider JOIN people ON provider.cid = people.cid WHERE provider.pid = {$id};";
$result=@mysql_query($sql,$con) or die(death($sql));
while($row = @mysql_fetch_assoc($result)) {
    $provider[] = "<option value=\"{$row['cid']}\">{$row['cid']}</option>";
    $people[] = "<option>{$row['first_name']} {$row['last_name']}</option>";
}

//Now you can reuse provider and people as you wish.
foreach($provider as $value) {
    echo $value;
}

foreach($people as $value) {
    echo $value;
}