Page 1 of 1

SQL Table Display

Posted: Thu Feb 03, 2011 4:29 pm
by Pegasus3245
Hi Everyone,
I'm new to the forums, and new to php and sql. Ive been doing a website with a friend for the past few weeks, and its pretty much completed. Unfortunately my friend has gone away with work for a few months and wont be able to help me complete the site. He was doing pretty much all the PHP and SQL work, and myself the HTML, Flash and Graphics - thus the problem I have. I only have one aspect of the website to do which requires php and sql.

All I want to do is display the database table 'customers' in its entirity on a HTML page, doesnt matter about the format, font anything, as long as it is readable by anyone and would print out as a table, ive been searching the net for ways to do this - and every way I have found are all very complicated and not for the novice user. Im quite familliar with VB, and there are loads of easy ways to do this - but i cant seem to find a way thats simple in php in the same way... I have attached the code I have so far which I tried to construct using an online tutorial, and ive also attached a copy of the SQL database off our server. Any help would be greatly apreciated, thankyou in advance.

PHP CODE

Code: Select all

<html>
<head>
<titleTable Contents</title>
</head>
<body>
<?php
//Get Tablename and Filter/Sort info from referring page. 
$tablename = $_POST['customers']; 
$sql = "SELECT $tablename"; 
//Connecting, selecting database
$link = mysql_connect("localhost", "web155-roberts", "ib1313495611") or die("Could not connect : " . mysql_error()); 
echo "Connected successfully to $tablename"; 
mysql_select_db("web155-roberts") or die("Could not select database"); 
/* Performing SQL query */ 
$result = mysql_query($sql) or die("Query failed : " . mysql_error()); 
/* Printing results in HTML */ 
$row = 1; 
echo "<table>\n"; 
//the MYSQL_ASSOC gets field names instead of numbers
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) { 
if ($row == 1) { 
echo "\t<tr>\n"; 
foreach ($line as $col_key => $col_value) { 
echo "\t\t<th>$col_key</th>\n"; 
} 
echo "\t</tr>\n"; 
} 
echo "\t<tr>\n"; 
foreach ($line as $col_value) { 
echo "\t\t<td>$col_value</td>\n"; 
} 
echo "\t</tr>\n"; 
$row++; 
} 
echo "</table>\n"; 
/* Free resultset */ 
mysql_free_result($result); 
/* Closing connection */ 
mysql_close($link); 
?> 
</body>
</html> 
SQL DATABASE
-- phpMyAdmin SQL Dump
-- version 3.3.9
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Feb 02, 2011 at 03:14 PM
-- Server version: 5.0.91
-- PHP Version: 5.2.9
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Database: `web155-roberts`
--
-- --------------------------------------------------------
--
-- Table structure for table `customers`
--
CREATE TABLE IF NOT EXISTS `customers` (
`key` int(11) NOT NULL auto_increment,
`first_name` varchar(100) NOT NULL,
`last_name` varchar(100) NOT NULL,
`email` varchar(350) NOT NULL,
`password` char(32) NOT NULL,
`address_line_1` varchar(100) NOT NULL,
`address_line_2` varchar(100) default NULL,
`town_city` varchar(100) NOT NULL,
`postcode` char(8) NOT NULL,
`registration_date` int(15) NOT NULL,
UNIQUE KEY `key` (`key`)
) TYPE=MyISAM AUTO_INCREMENT=9 ;
--
-- Dumping data for table `customers`
--
INSERT INTO `customers` (`key`, `first_name`, `last_name`, `email`, `password`, `address_line_1`, `address_line_2`, `town_city`, `postcode`, `registration_date`) VALUES
(4, 'anthony', 'alston', 'anthony-uk@live.co.uk', 'f10078c979e96a2a4b8577b1a4827fc0', 'somestreet', '', 'sometown', 'somecode', 1296077692),
(3, 'Scott', 'Robert', 'scott@btinternet.com', '24569d62fcba4af341ce476ceba83da4', '100 Millbank Road', 'Highfield', 'Birmingham', 'S70OJR', 1296077681),
(5, 'richard', 'cross', 'cross@international.com', '92be8a3b8305e83b812942d2d898c2dc', 'x', 'x', 'barnsley', 'x', 1296115960);
--
Thanks everyone. :D

Re: SQL Table Display

Posted: Fri Feb 04, 2011 10:39 pm
by TorMike
Here is an example from a simple library database app I wrote a while ago:

Code: Select all

// Make the query
$query = "SELECT isbn,
                 title,
                 lname,
                 fname,
                 cost as price,
                 year_pub,
                 type_of_book,
                 cover,
                 owner,
                 read_status
            FROM books
        ORDER BY $order_by, year_pub
           LIMIT $start, $display;";

$result = $dbc->query($query); // run the query.


include ('./includes/new_header.html');

echo '
       <div class="mainContent">
        <table align="center" border="1" cellspacing="0" cellpadding="5">
          <tr>
            <td align="center"><b><a href="' . $link1 . '">Isbn</a></b></td>
            <td align="center"><b><a href="' . $link2 . '">Title</a></b></td>
            <td align="center"><b><a href="' . $link3 . '">Last Name</a></b></td>
            <td align="center"><b>First Name</b></td>
            <td align="center"><b>Price</b></td>
            <td align="center"><b>Year Published</b></td>
            <td align="center"><b>Gender</b></td>
            <td align="center"><b>Cover</b></td>
            <td align="center"><b>Owner</b></td>
            <td align="center"><b>Status</b></td>
          </tr>';


// Fetch and print all the records.
$bg = '#eeeeee';

while ($row = $result->fetch_object())
{
  $bg = ($bg=='#eeeeee' ? '#ffffff' : '#eeeeee');
    echo '<tr bgcolor="' . $bg . '">
            <td align="left" width="150px">' . $row->isbn         . '</td>
            <td align="left" width="550px">' . $row->title        . '</td>
            <td align="left" width="150px">' . $row->lname        . '</td>
            <td align="left" width="200px">' . $row->fname        . '</td>
            <td align="left" width="50px">' . $row->price        . '</td>
            <td align="left" width="50px">' . $row->year_pub     . '</td>
            <td align="left" width="50px">' . $row->type_of_book . '</td>
            <td align="left" width="50px">' . $row->cover        . '</td>
            <td align="left" width="50px">' . $row->owner        . '</td>
            <td align="left" width="100px">' . $row->read_status  . '</td>
          </tr>';
}

echo '  </table>';
You'll notice that I use a 'while' loop to populate the HTML.

Initially I define a 'select' statement, display the table column headers and after that enter the while loop to display the values from extracted from the MySQL database.

I hope this helps.

Re: SQL Table Display

Posted: Sat Feb 05, 2011 1:42 pm
by Pegasus3245
Thankyou for that, Ive tried to impliment it - but as I said im new to PHP/SQL and having trouble getting it to work. Im sure its something very stupid im doing wrong, but heres what I have so far.

Code: Select all

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>

<body>
<?php
       <div class="mainContent">
        <table align="center" border="1" cellspacing="0" cellpadding="5">
          <tr>
            <td align="center"><b><a href="' . $link1 . '">Key</a></b></td>
            <td align="center"><b><a href="' . $link2 . '">First Name</a></b></td>
            <td align="center"><b><a href="' . $link3 . '">Last Name</a></b></td>
            <td align="center"><b>Email Address</b></td>
            <td align="center"><b>Address 1</b></td>
            <td align="center"><b>Address 2</b></td>
            <td align="center"><b>Town/City</b></td>
            <td align="center"><b>Postcode</b></td>
            <td align="center"><b>Date Of Registration</b></td>
           </tr>';


// Fetch and print all the records.
$bg = '#eeeeee';

while ($row = $result->fetch_object())
{
  $bg = ($bg=='#eeeeee' ? '#ffffff' : '#eeeeee');
    echo '<tr bgcolor="' . $bg . '">
            <td align="left" width="150px">' . $row->key         . '</td>
            <td align="left" width="550px">' . $row->first_name        . '</td>
            <td align="left" width="150px">' . $row->last_name        . '</td>
            <td align="left" width="200px">' . $row->email        . '</td>
            <td align="left" width="50px">' . $row->address_line_1        . '</td>
            <td align="left" width="50px">' . $row->address_line_2     . '</td>
            <td align="left" width="50px">' . $row->town_city . '</td>
            <td align="left" width="50px">' . $row->postcode        . '</td>
            <td align="left" width="50px">' . $row->registration_date        . '</td>
          </tr>';
}

echo '  </table>';
?>
</body>
</html>

Re: SQL Table Display

Posted: Thu Mar 24, 2011 8:53 am
by TorMike
You need an echo statement prior to the first <div> after the <?php.

Your mixing HTML and PHP statements