mysql_fetch cannot collect all the data in database

Question

i have here a code for the selection of all the data from the database, here's my code:

<?php

$name = $_POST['cname'];

if ($_POST["Search"] == "Search") {
    $query = mysql_query("SELECT * FROM parts WHERE cname LIKE '%$name%'");
    if (!empty($query)) {
        $vsi = 'No Data';
        $date = 'No Data';
        $cname = 'No Data';
    }

    while ($row = @mysql_fetch_array($query)) {
        $vsi = $row["vsi"];
        $date = $row["date"];
        $cname = $row["cname"];
    }

and here is how i echo all the variables,

<tr class="gradeC">
<?php echo "<td width='10%'><font size='-2'> $vsi </font></td>" ?>
<?php echo "<td width='20%'><font size='-2'>$date</font></td>" ?>
<?php echo "<td width='20%'><font size='-2'> $cname</font></td>" ?>
</tr>

My problem is that,

i only get one result from each row but my data in the database are 5.

here is my result

vsi      date             name
123      12/12/2012       test1

but the correct result should be:

vsi      date             name
123      12/12/2012       test1
123      12/12/2012       test1
123      12/12/2012       test1
123      12/12/2012       test1
123      12/12/2012       test1

note,

i make all the data in the database all the same

[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ). — Joseph Silber, Jan 18 '13 at 05:32
There's only one `$vsi` (and `$date` and `$cname`), so there's only one line. — Passerby, Jan 18 '13 at 05:35
once you fetch result form fetch array the pointer set to the end — NullPoiиteя, Jan 18 '13 at 05:37
even if i change the data in the database the result is the same, one data only. — Jerome, Jan 18 '13 at 05:47

score 8 · Accepted Answer · edited May 23 '17 at 12:03

You have to use TR in while loop.

<?php

$name = $_POST['cname'];

if ($_POST["Search"] == "Search") {
    $query = mysql_query("SELECT * FROM parts WHERE cname LIKE '%$name%'");
    if (!empty($query)) {
        $vsi = 'No Data';
        $date = 'No Data';
        $cname = 'No Data';
    }

    while ($row = @mysql_fetch_array($query)) {
        $vsi = $row["vsi"];
        $date = $row["date"];
        $cname = $row["cname"];

?>
<tr class="gradeC">
<?php echo "<td width='10%'><font size='-2'> $vsi </font></td>" ?>
<?php echo "<td width='20%'><font size='-2'>$date</font></td>" ?>
<?php echo "<td width='20%'><font size='-2'> $cname</font></td>" ?>
</tr>
<?
    }

?>

Warning

your code is vulnerable to sql injection you need to escape all get and post and the better approach will be using Prepared statement

Good Read

Note

The entire ext/mysql PHP extension, which provides all functions named with the prefix mysql_, is officially deprecated as of PHP v5.5.0 and will be removed in the future. So use either PDO or MySQLi

Good read

I Hope this helps.

yours welcome .. just tried to not give duplicate answer ... so improved existing answer .. — NullPoiиteя, Jan 18 '13 at 05:43

score 0 · Answer 2 · answered Jan 18 '13 at 05:56

The result of fetching will be an array. It will contain all of your matching database entries. But your echo is just outside of the while loop.Thats why it return only one result.Close the 2nd while loop after your echo.

while ($row = @mysql_fetch_array($query)) {
$vsi = $row["vsi"];
$date = $row["date"];
$cname = $row["cname"];

?>
<tr class="gradeC">
<?php echo "<td width='10%'><font size='-2'> $vsi </font></td>" ?>
<?php echo "<td width='20%'><font size='-2'>$date</font></td>" ?>
<?php echo "<td width='20%'><font size='-2'> $cname</font></td>" ?>
</tr>
<?
}

mysql_fetch cannot collect all the data in database

2 Answers2

Warning

Note