Jump to content


Photo

Select and display in table.


  • Please log in to reply
4 replies to this topic

#1 bach

bach
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 19 October 2006 - 07:35 PM

Hello,

I have the following MySQL statement
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;

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.
<!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>

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
if ($row = mysql_fetch_array($phonelist))


#2 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 19 October 2006 - 07:47 PM

can shorted things up a bit. try using this
<!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>


#3 bach

bach
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 19 October 2006 - 08:07 PM

Thanks for your help, I have enabled the code as above. It is now complaining of line 33
while ($row = mysql_fetch_array($phonelist)){
any idears.

#4 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 19 October 2006 - 08:10 PM

oops my bad. the line should read

while ($row = mysql_fetch_array($result)){

Ray

#5 bach

bach
  • Members
  • PipPip
  • Member
  • 16 posts

Posted 19 October 2006 - 08:27 PM

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.
$result = mysql_query($phonelist) or die("Error: ".mysql_error()."<br />");
$num_rows = mysql_num_rows($result);
$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 ??

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





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users