Echo results from mysqli_Query

问题:

I'm making a personal script for my own use, and I need to know how to echo the results from a mysqli_query. My code is as follows:

$conn = mysqli_connect($servername, $username, $password, $dbname);

if(isset($_POST['commercial'])){
if (isset($_POST['0'])){
    $sql = "SELECT email FROM CommercialEmails WHERE articleid = '$_POST[article]' AND dripid = 1 AND sent='a'";
    $resultsd1 = mysqli_query($conn, $sql);
    echo $resultsd1;
}   
if (isset ($_POST['1'])){
    $sql = "SELECT email FROM CommercialEmails WHERE articleid = '$_POST[article]' AND dripid = 2 AND sent='a'";
    $resultsd2 = mysqli_query($conn, $sql);
    echo $resultsd2;
}   
if (isset($_POST['2'])){
    $sql = "SELECT email FROM CommercialEmails WHERE articleid = '$_POST[article]' AND dripid = 3 AND sent='a'";
    $resultsd3 = mysqli_query($conn, $sql);
    echo $resultsd3;
}
if (isset ($_POST['3'])){
    $sql = "SELECT email FROM CommercialEmails WHERE articleid = '$_POST[article]' AND dripid = 4 AND sent='a'";
    $resultsd4 = mysqli_query($conn, $sql);
    echo $resultsd4;
}
if (isset ($_POST['4'])){
    $sql = "SELECT email FROM CommercialEmails WHERE articleid = '$_POST[article]' AND dripid = 5 AND sent='a'";
    $resultsd5 = mysqli_query($conn, $sql);
    echo $resultsd5;
}

}
?>

回答1:

If u want to return multiple rows

if (isset($_POST['0'])) {
 $sql = "SELECT email FROM CommercialEmails WHERE articleid = '$_POST[article]' AND dripid = 1 AND sent='a'";
 $resultsd1 = mysqli_query($conn, $sql);

 while ($row = mysqli_fetch_assoc($resultsd1))
 {
    echo $row['email'];
 }
}   

If only 1 row

if (isset($_POST['0'])){
 $sql = "SELECT email FROM CommercialEmails WHERE articleid = '$_POST[article]' AND dripid = 1 AND sent='a' LIMIT 1";
 $resultsd1 = mysqli_query($conn, $sql);

 $row = mysqli_fetch_assoc($resultsd1);

 echo $row['email'];
}   

回答2:

First of all as @fred-ii said, escape your post, there is also an error in your $_POST access, you are missing quotes around article key, and lastly use mysqli_fetch_assoc to acces your results:

...
if (isset($_POST['0'])) {
    $article = mysqli_real_escape_string($conn, $_POST['article']);
    $sql = "SELECT email FROM CommercialEmails WHERE articleid = '$article' AND dripid = 1 AND sent='a'";
    if ($resultsd1 = mysqli_query($conn, $sql)) {
        if ($row = mysqli_fetch_assoc($resultsd1)) {
            echo $row['email'];
        }
    }
}
...   

回答3:

You can simply loop on the result object with foreach loop. If you want to fetch all the rows into a PHP variable you can use fetch_all().

$result = mysqli_query($conn, 'SELECT ...');
foreach($result as $row) {
    print_r($row);
    // do something with each row
}
// or
$result = $conn->('SELECT ...')->fetch_all(MYSQLI_ASSOC);
foreach($result as $row) {
    print_r($row);
    // do something with each row
}

However, in your case you should not be using mysqli_query() at all! This leaves you vulnerable to SQL injection. You must use parameter binding, which is available with prepared statements.

For example your fixed query would look like this:

$stmt = $con->prepare("SELECT email FROM CommercialEmails WHERE articleid = ? AND dripid = 1 AND sent = 'a' ");
$stmt->bind_param('s', $_POST['article']);
$stmt->execute();
$result = $stmt->get_result();
foreach ($result as $row) {
    print_r($row);
}

The difference is that my variable is not separate from the SQL, so there is no risk of injection. You should never allow any variable input directly in SQL query. Doing this properly is really not that difficult.

Also, you don't really need to repeat the code so much. You can parameterize dripid too and reduce the number of lines in your code.