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?
If I run the SQL query above on the SSMS, I got this result with Customer.C_Code = 7
:
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>"; } }