using an array and foreach to search mysql and echo results

Question

Our registrars receive a list of student attendees which they either insert in the database as new, or update as existing students. Currently, they must do this with each individual email address. It's slow.

We'd like to let them copy and paste a comma separated list into a textarea, and click a button to search the database which will echo out the list of those previously registered.

I'm exploding the Post textarea into array $students, trimming it, etc. and the results print_r just fine. But extracting from the db and echoing the list of registered users is breaking down. This is the latest errant permutation, but I've been through 31 flavors 'cause I just don't know how to write this.

In the head code:

$students = rtrim($_POST['CSValues'],','); for readability, I removed the sanitation string
$students = explode(',', $students);
foreach($students as $key=>$value) {

mysql_select_db($database_xxxxxxx, $xxxxxxx);
$query_rsFindStudents = "SELECT Students.Stud_email_addr FROM Students 
WHERE Students.Stud_email_addr = '".$value."'";

$rsFindStudents = mysql_query($query_rsFindStudents, $xxxxxxx) or die(mysql_error());
$row_rsFindStudents = mysql_fetch_assoc($rsFindStudents);
$totalRows_rsFindStudents = mysql_num_rows($rsFindStudents);

in the body

do { 
echo $row_rsFindStudents['Stud_email_addr'], '<br />';
} while ($row_rsFindStudents = mysql_fetch_assoc($rsFindStudents));

But it either isn't finding, or echoing, any of the registered email addresses. TIA. Any pointers you can give me are deeply appreciated.

did you check if mysql_num_rows() is returning non-zero? Did you do a var_dump($row_rsFindStudents) to see what you're REALLY getting back from the database? — Marc B
– Marc B, Commented Jul 30, 2013 at 17:51
I would like to save you some headaches in the future. Imagine what would happen if a student's name had a comma in it due to a clerical error or something crazy (never trust user input). Use str_getcsv() php.net/manual/en/function.str-getcsv.php — MonkeyZeus
– MonkeyZeus, Commented Jul 30, 2013 at 17:52
mysql extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQL extension should be used. — bansi
– bansi, Commented Jul 30, 2013 at 17:52

bansi · Accepted Answer · 2013-07-30 18:01:04Z

1

if you really trust the comas will be in the correct place then try the following

$students = rtrim($_POST['CSValues'],','); for readability, I removed the sanitation string
$students = implode("','",explode(',', $students));
mysql_select_db($database_xxxxxxx, $xxxxxxx);

$query_rsFindStudents = "SELECT Students.Stud_email_addr FROM Students 
WHERE Students.Stud_email_addr IN ('$students')";

$rsFindStudents = mysql_query($query_rsFindStudents, $xxxxxxx) or die(mysql_error());
$totalRows_rsFindStudents = mysql_num_rows($rsFindStudents);



while ($row_rsFindStudents = mysql_fetch_assoc($rsFindStudents)){
    echo $row_rsFindStudents['Stud_email_addr'], '<br />';
}

The above code is not tested, just typed in here. please let me know if you find any errors, coding in a mobile is not easy.

answered Jul 30, 2013 at 18:01

bansi

57.3k6 gold badges44 silver badges52 bronze badges

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

5 Comments

AllThisOnAnACER Over a year ago

Bansi, give me a minute... I've also been trying your suggestion of 'IN' yesterday and no results there either. But at least my code hasn't triggered any outright denunciations. So I'm on the right track, right?

AllThisOnAnACER Over a year ago

Wait...we have a hiccup...maybe...hold on

AllThisOnAnACER Over a year ago

Hmm. I tested with totalRows_rsFindStudents and it says positive, but the email addresses aren't actually showing up. hmm...

bansi Over a year ago

is your column name Stud_email_addr (case sensitive). also what is the value of $totalRows_rsFindStudents?

AllThisOnAnACER Over a year ago

Thanks bansi. Working while mobile worked out pretty well. I've taken what you said and re-tricked a few things so it seems to be echoing one of the email addresses at the moment. I'll work with this more. Thanks to all of you.

Collectives™ on Stack Overflow

using an array and foreach to search mysql and echo results

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related