Jump to content

Archived

This topic is now archived and is closed to further replies.

bach

Select and display in table.

Recommended Posts

Hello,

I have the following MySQL statement
[code]SELECT s_name, s_firstname, CONCAT_WS('-', LEFT(s_phone, 4), MID(s_phone, 5, 3), RIGHT(s_phone, 4))
FROM `bridgend`.`sam`
ORDER BY s_name asc;[/code]

I want to display the details of this in a table and loop round until the all records are displayed.

This is what I have so far but there problems would be greatful for some help.
[code]<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en-gb">
<head>
  <meta http-equiv="content-type" content="text/html; charset=UTF-8" />
</head>
<body>
<?php
include("condb.php");

$phonelist =
"SELECT s_name, s_firstname, CONCAT_WS('-', LEFT(s_phone, 4), MID(s_phone, 5, 3), RIGHT(s_phone, 4))
FROM `bridgend`.`sam`
ORDER BY s_name asc;";

$result = mysql_query($phonelist);

if (!$result)

{

Print("Error: ".mysql_error()."<br />");

die();

}

?>
<h1>Title</h1>
<h2>Phone</h2>
<table style="text-align: left; width: 100%;" border="0" cellpadding="2" cellspacing="2">
  <tbody>
    <tr>
      <td>Nick</td>
      <td>Ask for</td>
      <td>Contact Number</td>
    </tr>
<?php   
if ($row = mysql_fetch_array($phonelist))

{

while ($row = mysql_fetch_array($phonelist))

{
?>
    <tr>
      <td><?php print $row["s_name"]; ?></td>
      <td><?php print $row["s_firstname"]; ?></td>
      <td><?php print $row["s_phone"]; ?></td>
    </tr>
<?php
    } //End While
} //End if
?>
  </tbody>
</table>
</body>
</html>[/code]

I am not sure if this will fully work with printing s_phone when I am using concat in the query but the problem at present seems to be with [code]if ($row = mysql_fetch_array($phonelist))[/code]

Share this post


Link to post
Share on other sites
can shorted things up a bit. try using this
[code]<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en-gb">
<head>
  <meta http-equiv="content-type" content="text/html; charset=UTF-8" />
</head>
<body>
<?php
include("condb.php");

$phonelist =
"SELECT s_name, s_firstname, CONCAT_WS('-', LEFT(s_phone, 4), MID(s_phone, 5, 3), RIGHT(s_phone, 4))
FROM `bridgend`.`sam`
ORDER BY s_name asc;";

$result = mysql_query($phonelist) or die("Error: ".mysql_error()."<br />");
$num_rows = mysql_num_rows($result);
?>
<h1>Title</h1>
<h2>Phone</h2>
<table style="text-align: left; width: 100%;" border="0" cellpadding="2" cellspacing="2">
  <tbody>
    <tr>
      <td>Nick</td>
      <td>Ask for</td>
      <td>Contact Number</td>
    </tr>
<?php
if($num_rows > 0){
while ($row = mysql_fetch_array($phonelist)){
?>
    <tr>
      <td><?php print $row["s_name"]; ?></td>
      <td><?php print $row["s_firstname"]; ?></td>
      <td><?php print $row["s_phone"]; ?></td>
    </tr>
<?php
    } //End While
} else {
?>
    <tr>
      <td colspan=3 align=center>No data to display</td>
    </tr>
<?php
}//End if
?>
  </tbody>
</table>
</body>
</html>[/code]

Share this post


Link to post
Share on other sites
Thanks for your help, I have enabled the code as above. It is now complaining of line 33
[code] while ($row = mysql_fetch_array($phonelist)){[/code]
any idears.

Share this post


Link to post
Share on other sites
oops my bad. the line should read

[code]while ($row = mysql_fetch_array($result)){[/code]

Ray

Share this post


Link to post
Share on other sites
Craygo,

Thanks for your help.

The only thing that did not work is the s_phone, I would like to display the newly formated data I am missing something here.

Just to make sure I understand the code.
[code]$result = mysql_query($phonelist) or die("Error: ".mysql_error()."<br />");
$num_rows = mysql_num_rows($result);[/code]
$result is the query on the database using the select statement and returns an error if there is a problem
$num_rows just counts number of rows returned by query ??

[code]while ($row = mysql_fetch_array($result)){[/code]
as long as $row has data in it will stay in the while loop ?

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.