Php for loop and mysql query

Question

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

Doesn't answer your question, but you should be using mysqli or PDO as the mysql_ extensions have been deprecated. — muttley91
– muttley91, Commented Apr 8, 2014 at 16:24
Hi, yes Im avare of this. Just haven't got the time to modify all the files. Someday I will do it :) — Toube
– Toube, Commented Apr 8, 2014 at 17:20

VMai · Accepted Answer · 2014-04-08 16:45:19Z

1

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.

answered Apr 8, 2014 at 16:45

VMai

10.4k9 gold badges28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Toube Over a year ago

Hi VMai, you are correct, now it returns 2 rows: select email from users where users_id in (4,82). Though it now returns same email twice. How do I get the result to return email for id 4 and 82 and not repeating the 4 id twice?

Toube Over a year ago

This is what it prints: select email from users where users_id in (4,82)[email protected],[email protected]

VMai Over a year ago

You fetch only once from the resultset. Because of that you will only display the first record over and over again. You've got to fetch the following records in your loop. In your outer for-loop you've got the fetch as first statement of your loop. You could do the same with your inner for-loop. But do not fetch a record before your loop as you do now.

Toube Over a year ago

ok, I'm not sure if I follow you.. can you give an small example?

VMai Over a year ago

Well I don't know exactly what you want to do in your second for-loop. Can it be that you want a comma-separated list of email-adresses, if you've got more than one result?

|

Collectives™ on Stack Overflow

Php for loop and mysql query

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related