My SQL Query

[Thomas001]

$row = mysql_query("SELECT dept FROM $db_table WHERE superemail = '$sent_superemail' ");
$set_dept = $row[?];

$sql = "SELECT * FROM $dbtable WHERE dept = '$set_dept' "

Basicly I'm trying to get the dept based on the superemail, but I can't figure out how I would do it. I'm farely new to SQL.


Thanks.

 

[Bastien]

$row is not the resultset, its a pointer to the resultset

$row = mysql_query("SELECT dept FROM $db_table WHERE superemail = '$sent_superemail' ");

if($row){
  $my_row = mysql_fetch_array($row);

$set_dept = $my_row['dept'];

$sql = "SELECT * FROM $dbtable WHERE dept = '$set_dept' "

Bastien

Cat, the other other white meat

 

[Thomas001]

What if the superemail has multiple depts and I need to retreive all of them and use them in the next query?

 

[cLFlaVA]

You'll have to loop through the result set and build the WHERE clause.

$count = 0;
$result = mysql_query("SELECT dept FROM $db_table WHERE superemail = '$sent_superemail' ");

$query = "SELECT * FROM $dbtable WHERE dept IN ("
if($result){
    while ($row = mysql_fetch_array($result)) {
        if ($count > 0)
            $query .= ",'" . $row['dept'] . "'";
        else
            $query .= "'" . $row['dept'] . "'";
        $count = $count + 1;
    }
    $query .= ")";
} else {
    echo 'error executing query.";
}

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[Thomas001]

this is still only giving me back one entry.

$count = 0;
$result = mysql_query("SELECT dept FROM $dbtable WHERE superemail = '$sent_superemail' ");
$query = "SELECT * FROM $dbtable WHERE dept IN (";
if ($result)
 {
    while ($row = mysql_fetch_array($result))
    {
        if ($count > 0)
             $query .= ",'" . $row['dept'] . "'";
            else
             $query .= "'" . $row['dept'] . "'";
             
        $count = $count + 1;
    }
    $query .= ")";
 } 
 else

 

[cLFlaVA]

echo the $query after this processing, to see what the SQL looks like.

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[Thomas001]

I did, it returns:

Print("[$sent_superemail] controls [$query] with a total of [$count]");

[JMONTGOM] controls [SELECT * FROM employees WHERE dept IN ('E1433')] with a total of [1]

 

[cLFlaVA]

So, it looks to me as if you're only returning one department in the original query.

How many are you expecting?

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[Thomas001]

Give or take 15 different dept numbers.

Perhaps it's the SQL statement? The loop looks flawless. Or maybe there should be a SQL statement within the loop.

 

[cLFlaVA]

For testing-

Print out the previous SQL statement, as well as mysql_num_rows($result) right after the $result = ... above the while loop.

What do you get?

*cLFlaVA
----------------------------
A polar bear walks into a bar and says, "Can I have a ... beer?"
The bartender asks, "What's with the big pause?

 

[Thomas001]

nevermind, apparrently there is only one entry.

Though I may have to create another thread to house my next problem..

thanks cLFlaVA.

 

[KarveR]

If its mysql related, probably a better home for it will be in the MySQL Forum

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.