Mysql do not fetch multiple rows with same field value

Question

I have two tables: categories and models. For now, categories table is important.

| id | nazwa | desc_s1 | photo | active | id_user |
|  1 | Audi  |   test  |  url  |   1    |    2    |
|  2 | BMW   |   test  |  url  |   1    |    2    |
|  3 | Ford  |   test  |  url  |   1    |    2    |
|  4 | BMW   |   test  |  url  |   1    |    2    |

And I have php:

$query_categories = "SELECT * FROM `categories` WHERE nazwa='$selected_category' AND active=1 AND id_user=".$_SESSION['id_user'].";";
$query_model = "SELECT * FROM models WHERE active=1";

$result_categories = mysqli_query($con, $query_categories);
$result_model = mysqli_query($con, $query_model);

if (mysqli_num_rows($result_categories) > 0 && mysqli_num_rows($result_model) > 0) {

    $row_categories = mysqli_fetch_assoc($result_categories);
    $selected_category =  $row_categories['nazwa'];
    $_SESSION['selected_category'] = $selected_category;
    $_SESSION['title_content'] = $title_content;
    $_SESSION['price'] = $price;
    $category =  $row_categories['nazwa'];
    $desc_s1 =  $row_categories['desc_s1'];
    $photo =  $row_categories['photo'];

    $row_model = mysqli_fetch_assoc($result_model);
    $selected_model =  $row_model['model'];
    $stan =  $row_model['stan'];
    $przeznaczenie =  $row_model['przeznaczenie'];
    $kolor =  $row_model['kolor'];
    $typ =  $row_model['typ'];

    $table = array();
    do  
    {
        array_push($table, '
            <tr>
            <td class="text-left">353</td>
            <td class="text-left">Stan|' . $row_model['stan'] . '<br />Przeznaczenie|' . $row_model['przeznaczenie'] . '<br />Kolor|' . $row_model['kolor'] . '<br />Typ|' . $row_model['typ'] . '<br /></td>
            <td class="text-left">' . str_replace(' ', '_', $row_model['model']) . '<br /></td>
            <td class="text-left">' . $title_content . ' ' . $selected_category . ' ' . $row_model['model'] . '</td>
            <td class="text-left">' . $price . '</td>
            <td class="text-left">' . nl2br($row_categories['photo']) . '</td>
            <td class="text-left">' . nl2br(htmlspecialchars($row_categories['desc_s1'])) . '</td>
            </tr>');
    } while ($row_model = mysqli_fetch_array($result_model));
} else {
    array_push($errors, '<span class="badge badge-warning">Error</span>');
}

Its working but, the output table gives me 3 results, missing is the last one. I know the reason is the same name name like row id=2 but how to get all of the rows in result? I have tried with while inside while but it doesn't work as I thought.

Your code is vulnerable to SQL injection. You should use prepared statements. — Dharman, Feb 19 '20 at 13:41

Jay Blanchard · Answer 1 · 2020-02-19T13:46:29.253

0

It is because you're fetching twice;

$row_model = mysqli_fetch_assoc($result_model);

and

while ($row_model = mysqli_fetch_array($result_model));

The first time gets one row, the second gets the rest. Refactor your code to use the fetch only one time. It is likely you can remove this entire block of code:

$row_model = mysqli_fetch_assoc($result_model);
$selected_model =  $row_model['model'];
$stan =  $row_model['stan'];
$przeznaczenie =  $row_model['przeznaczenie'];
$kolor =  $row_model['kolor'];
$typ =  $row_model['typ'];

Instead of do...while like you have here:

do  
    {
        array_push($table, '
            <tr>
            <td class="text-left">353</td>
            <td class="text-left">Stan|' . $row_model['stan'] . '<br />Przeznaczenie|' . $row_model['przeznaczenie'] . '<br />Kolor|' . $row_model['kolor'] . '<br />Typ|' . $row_model['typ'] . '<br /></td>
            <td class="text-left">' . str_replace(' ', '_', $row_model['model']) . '<br /></td>
            <td class="text-left">' . $title_content . ' ' . $selected_category . ' ' . $row_model['model'] . '</td>
            <td class="text-left">' . $price . '</td>
            <td class="text-left">' . nl2br($row_categories['photo']) . '</td>
            <td class="text-left">' . nl2br(htmlspecialchars($row_categories['desc_s1'])) . '</td>
            </tr>');
    } while ($row_model = mysqli_fetch_array($result_model));

Change it to a while loop:

while ($row_model = mysqli_fetch_array($result_model))
{
    array_push($table, '
        <tr>
        <td class="text-left">353</td>
        <td class="text-left">Stan|' . $row_model['stan'] . '<br />Przeznaczenie|' . $row_model['przeznaczenie'] . '<br />Kolor|' . $row_model['kolor'] . '<br />Typ|' . $row_model['typ'] . '<br /></td>
        <td class="text-left">' . str_replace(' ', '_', $row_model['model']) . '<br /></td>
        <td class="text-left">' . $title_content . ' ' . $selected_category . ' ' . $row_model['model'] . '</td>
        <td class="text-left">' . $price . '</td>
        <td class="text-left">' . nl2br($row_categories['photo']) . '</td>
        <td class="text-left">' . nl2br(htmlspecialchars($row_categories['desc_s1'])) . '</td>
        </tr>');
}

Warning!

Little Bobby says your script is at risk for SQL Injection Attacks. Learn about prepared statements for MySQLi. Even escaping the string is not safe!

edited Feb 19 '20 at 13:46

answered Feb 19 '20 at 13:14

Jay Blanchard

34,243
16
77
119

Yes I understand, but it doesn't takes any affect on the last row of categories table, and why it's not showing (the reason is the same name as row 2 but the names cannot be changed) – adi5497 Feb 19 '20 at 13:23
Can you clarify? What you're saying doesn't make sense. – Jay Blanchard Feb 19 '20 at 13:30
I'm still do not getting the last row – adi5497 Feb 19 '20 at 13:35
Did you comment out or remove that block of code? See my updated answer. – Jay Blanchard Feb 19 '20 at 13:42
I don't recommend using while loop either. A simple foreach should be enough. – Dharman Feb 19 '20 at 13:46
Why @Dharman? It is pretty typical to use a `while` loop to traverse query results. – Jay Blanchard Feb 19 '20 at 13:47
`foreach` is simpler and can be used multiple times. `while` is more complicated for new programmers. – Dharman Feb 19 '20 at 13:49
We'll have to agree to disagree @Dharman. In my experience, beginners have more issues understanding array traversal than anything else. YMMV. In order to use a `foreach` here the OP would have to fetch all (an array of arrays), then loop. – Jay Blanchard Feb 19 '20 at 13:50
No, mysqli_result is traversable. There's no need to use any fetch method. See [here](https://stackoverflow.com/a/60250222/1839439) or [here](https://stackoverflow.com/a/58000796/1839439) or [here](https://stackoverflow.com/a/59811163/1839439) and many more of my answers. Adding the extra call to some fetch function is usually confusing. It's simpler to write `foreach($result_model as $row_model)` than `while ($row_model = mysqli_fetch_array($result_model))` – Dharman Feb 19 '20 at 13:57
1

@Dharman, thank you, foreach inside foreach solved my problem :) – adi5497 Feb 19 '20 at 14:44

Mysql do not fetch multiple rows with same field value

1 Answers1