Search database using array and then echo/print result in foreach loop using PHP

Question

I need to get variable code from URL so I $codes = $_GET['code']; (url example website.com/update?code[]=7291&code[]=9274&code[]=8264&) then I SELECT firstname FROM guests WHERE invitecode = $codes" then I output data and set as $relatives = $row["firstname"] and then later on in the file I need to echo/print print $relative.

Why is this not working for me?

... connection made ...
$codes = $_GET['code'];
$sql = "SELECT firstname FROM guests WHERE invitecode = $codes";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
// output data of each row
while($row = mysqli_fetch_assoc($result)) {
    $relatives[] = $row["firstname"];
}
}

foreach ($relatives as $relative) {
print $relative;
}

Update:

So now using:

<?php

$codes = $_GET['code'];
$thecodes = "";
foreach($codes as $vals)
    $thecodes .= (int)$vals . ",";
if($thecodes != "")
{
    $thecodes = trim($thecodes, ",");
    $sql = "SELECT firstname FROM guests WHERE invitecode IN ($thecodes)";
    $result = mysqli_query($conn, $sql);
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
while($row = mysqli_fetch_assoc($result)) {
    $relatives[] = $row["firstname"];
}
}
foreach ($relatives as $relative) {
print $relative;
}
}
else
{
}

?>

It works but I would like to enter the foreach ($relatives as $relative) { echo $relative; }; into a value like this $message = $firstname . " " . $lastname . " will be coming to your event. " . ;.

In the end it would turn out something like this: $message = $firstname . " " . $lastname . " will be coming to your event. " . foreach ($relatives as $relative) { echo $relative . " "; };.

For some reason it won't work when I combine them.

Please use proper handling of user controlled variables when inserting them into an SQL statement: [How can I prevent SQL-injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — MatsLindh, Jan 16 '16 at 23:43
I'd *HIGHLY* advise against just doing a separate query for each code within a loop. That's ridiculously inefficient. It's quite easy to just build a `WHERE` clause and then use one query, then use PHP to structure it out how you want. In any case, a query within a `foreach` is an insanely terrible idea. There is no case I'd ever say that is acceptable unless it's a personal project where no one else will ever have access to it. — Nate I, Apr 11 '16 at 18:59

Matt · Accepted Answer · 2016-01-17T20:23:10.930

2

Use the IN operator for this.

<?php

$codes = $_GET['code'];
$thecodes = "";
foreach($codes as $vals)
    $thecodes .= (int)$vals . ","; //Loop through making sure each is an int for security reasons (No sqli)
if($thecodes != "") //There is at least one code
{
    $thecodes = trim($thecodes, ","); //Remove any additional commas
    $sql = "SELECT firstname, lastname FROM guests WHERE invitecode IN ($thecodes)"; //Use the IN operator
    $result = mysqli_query($conn, $sql);
    if (mysqli_num_rows($result) > 0) {
        while($row = mysqli_fetch_assoc($result)) {
            echo $row["firstname"] . " " . $row["lastname"] . "is coming to your event";
        }
    }

}
else //No codes to be queried
{

}

?>

edited Jan 17 '16 at 20:23

answered Jan 16 '16 at 23:39

Matt

2,851
1
13
27

Your example works, but if you check my update on the post above. I can't seem to print the `$relatives` anywhere I want. I need it inside this stament: `$message = $firstname . " " . $lastname . " will be coming to your event. " . ;` please see my example above. Thanks. – Sammy7 Jan 17 '16 at 19:46
Updated it, would you like something like that? – Matt Jan 17 '16 at 20:23

Adriano · Answer 2 · 2016-01-17T13:32:25.193

1

Can this be a solution for you?

$relatives = array(); // declare array
$codes = $_GET['code'];
$sql = "SELECT firstname FROM guests WHERE ";
foreach ($codes as $code) $sql .= "invitecode = " . intval($code) . " OR ";
$sql .= "1=2"; // simple way to remove last OR or to make sql valid if there are no codes
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
  // output data of each row
  while($row = mysqli_fetch_assoc($result)) {
      array_push($relatives, $row["firstname"]);
  }
}

foreach ($relatives as $relative) {
print $relative;
}

edited Jan 17 '16 at 13:32

answered Jan 16 '16 at 23:40

Adriano

1,743
15
28

This is vulnerable to SQL injection. Please escape your input. – Matt Jan 16 '16 at 23:43
@Adriano your example works but I will use Matt's because he shows it with `IN` Please see my update – Sammy7 Jan 17 '16 at 02:10
@Matt You're right. I changed the code so that the codes are converted to integers. Your solution is also much more elegant than my ;) – Adriano Jan 17 '16 at 13:34
@user3263981 I didn't solved only the codes problem, I solved also your question about the firstname array! You have to declare $relatives as array and then you can add more usernames with array_push. – Adriano Jan 17 '16 at 13:38
@Adriano yes that's what Matt showed. And it works, but if you check my update on the post above. I can't seem to print the `$relatives` anywhere I want. I need it inside this stament: `$message = $firstname . " " . $lastname . " will be coming to your event. " . ;` please see my example above. – Sammy7 Jan 17 '16 at 19:45

score -1 · Answer 3 · answered Jan 17 '16 at 01:39

I think this will work...

... connection made ...
$codes = $_GET['code'];
$sql = "SELECT firstname FROM guests WHERE invitecode = '$codes'";
$result = mysqli_query($conn, $sql) or die('-1' . mysqli_error());

if (mysqli_num_rows($result) > 0) {
// output data of each row
while($row = mysqli_fetch_assoc($result)) {
    echo ($row['firstname']);
}
}

Search database using array and then echo/print result in foreach loop using PHP

3 Answers3