Skip to content
Advertisement

How to print multiple value column in PHP

I have a SQL Server database about Customer information and CustomerPhone has a multiple values column which is C_Phone.

This is my code:

if (!empty($_GET['customer_id'])){
    // 'id' input from the user
    $id = $_GET['customer_id'];
    // Customer Information
    $sql = "SELECT Customer.C_Code, C_FirstName, C_LastName, C_Email, C_HomeAddress, C_OfficeAddress, C_Phone
            FROM Customer, CustomerPhone
            WHERE Customer.C_code = CustomerPhone.C_code and Customer.C_code = $id;";
    $query = sqlsrv_query($conn, $sql);
    $row = sqlsrv_fetch_array($query);
    if (!empty($row)) {
        echo "
        <div class="card" style="width: 25rem; margin-left: auto; margin-right: auto;">
            <div class="card-body">
                <h5 class="card-title">Customer Information</h5>";
        echo "<p class="card-text">C_code: " . $row['C_Code'] . "</p>";
        echo "<p class="card-text">C_Name: " . $row['C_FirstName'] . " " . $row['C_LastName'] . "</p>";
        echo "<p class="card-text">C_Email: " . $row['C_Email'] . "</p>";
        echo "<p class="card-text">C_HomeAddress: " . $row['C_HomeAddress'] . "</p>";
        echo "<p class="card-text">C_OfficeAddress: " . $row['C_OfficeAddress'] . "</p>";
        echo "<p class="card-text">C_Phone: ";
        while($row = sqlsrv_fetch_array($query))
        {   
            echo $row['C_Phone'] . "<br>";
        }
        echo "</p>";
        echo "
            </div>
        </div>";
    }

This is the result I got. How can I print the second phone number under the first one? enter image description here

If I run the SQL query above on the SSMS, I got this result with Customer.C_Code = 7:

enter image description here

Advertisement

Answer

The reason for this unexpected behaviour is that you need to echo the customer phone from the first fetched row (echo "<p class="card-text">C_Phone: ". $row['C_Phone'] . "<br>"; instead of echo "<p class="card-text">C_Phone: ";).

You may also consider the following:

  • Use explicit JOIN syntax.
  • Use parameterized queries. As is mentioned in the documentation, the sqlsrv_query function is well-suited for one-time queries and should be the default choice to execute queries unless special circumstances apply and sqlsrv_query function does both statement preparation and statement execution, and can be used to execute parameterized queries.

The following example, based on your code, is a possible solution to your problem:

if (!empty($_GET['customer_id'])) { 
    // Customer Information
    $sql = "
        SELECT c.C_Code, c.C_FirstName, c.C_LastName, c.C_Email, c.C_HomeAddress, c.C_OfficeAddress, p.C_Phone
        FROM Customer c
        JOIN CustomerPhone p ON c.C_code = p.C_code
        WHERE c.C_code = ?;
    ";
    $params = array($_GET['customer_id']);
    $query = sqlsrv_query($conn, $sql, $params);
    if ($query === false) {
        echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
        exit;
    }   
    // fetch data
    $row = sqlsrv_fetch_array($query);
    if (!empty($row)) {
        echo "
        <div class="card" style="width: 25rem; margin-left: auto; margin-right: auto;">
            <div class="card-body">
                <h5 class="card-title">Customer Information</h5>";
        echo "<p class="card-text">C_code: " . $row['C_Code'] . "</p>";
        echo "<p class="card-text">C_Name: " . $row['C_FirstName'] . " " . $row['C_LastName'] . "</p>";
        echo "<p class="card-text">C_Email: " . $row['C_Email'] . "</p>";
        echo "<p class="card-text">C_HomeAddress: " . $row['C_HomeAddress'] . "</p>";
        echo "<p class="card-text">C_OfficeAddress: " . $row['C_OfficeAddress'] . "</p>";
        echo "<p class="card-text">C_Phone: ". $row['C_Phone'] . "<br>";
        while ($row = sqlsrv_fetch_array($query))
        {   
            echo $row['C_Phone'] . "<br>";
        }
        echo "</p>";
        echo "
            </div>
        </div>";
    }
}
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement