CODEWITHSUNDEEP
phpmysql
Toube
Toube

Reputation: 67

Php for loop and mysql query

I'm running a second database search using mysql inside a for loop but I can't get it show the correct amount of rows:

Original search:

    $topicemailsql = "select se.id as id, se.users as users, se.topic as topic, se.body as body, se.postID as postID, DATE_FORMAT(se.sent, '%d.%m.%Y %H:%i:%s' ) as sent, (SELECT u.email from users u where u.users_id in (se.users)) as emails from sentEmail se LEFT OUTER JOIN topics t on (t.ID = se.theader_) where t.ID = '$topicID'";
$topicemailsqlquery = mysql_query($topicemailsql)or die(mysql_error());

$numrows = mysql_num_rows($topicemailsqlquery);

php for loop:

    for ($i=0; $i < $numrows ; $i++){

    $sqlarray = mysql_fetch_array($topicemailsqlquery);
    $users = $sqlarray['users'];
    $sqlemail = ('select email from users where users_id in ("'.$users.'")');
    //echo $sqlemail;
    $emailsqlquery = mysql_query($sqlemail)or die(mysql_error());
    $amountofusers = mysql_num_rows($emailsqlquery);
    $sqlarrayemail = mysql_fetch_array($emailsqlquery);
    echo $amountofusers;
//echo $sqlarrayemail['email'];

    for ($a=0; $a < $amountofusers ; $a++){
        if($a == 0){
         $email = $sqlarrayemail['email'];
    }
        else if($a < $amountofusers){
        $email = $sqlarrayemail['email'].','.$sqlarrayemail['email'];
        }
        }
}

So based on this the $amountofusers should return more than 1 row but now it always return only one row. When I echo the $sqlemail it should return 2 rows because it looks like this: select email from users where users_id in ("4,82") --> this should return 2 rows and a count of 2 but it only returns one row.

Where does it go wrong?

Br, Toby

Upvotes: 0

Views: 93

Answers (1)

VMai
VMai

Reputation: 10336

I think, it's a very bad idea to store multiple values in one field. Furthermore I wouldn't fire up queries in a loop, if I can avoid it. Better fetch the data in one go and let PHP do the rest. If you try your query i.e. with PHPAdmin you would use

SELECT email 
FROM users 
WHERE users_id IN (4, 82)

The IN operator needs a comma separated list of arguments. You give one single value

"4,82"

That's a huge difference. MySQL would accept ("4","82") too (other DBMS are not as tolerant) and handle the not needed conversion for you.

Upvotes: 1

Related Questions