FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Error trying to display SQL statement in html table





welshsteve
Hi everyone, I'm having trouble getting a HTML form to submit, run a SQL statement and display the data on a webpage. The strangest thing about this is that I have got this working on one area of my site, but now I want to use the same script for another area, it doesn't work.

The area that works fine is http://www.ldbsa.co.uk/s_stats.php. The area that doesn't work is http://www.ldbsa.co.uk/snookertables.php


Here's the code for snookertables_view.php which isn't working.
Code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >

<head>

   <title>Leamington and District Billiards and Snooker Association  - Player Statistics</title>
   <link rel="stylesheet" type="text/css" href="css/css_main.css" />
   <link rel="shortcut icon" href="images/favicon.ico" />

   <meta http-equiv="Content-Type" content="text/html; charset=shift_jis" />
   <meta name="Author" content="Steve Williams" />
   <meta name="Publisher" content="Steve Williams" />
   <meta name="Copyright" content="c Copyright 2007, Steve Williams" />
   <meta name="Revisit-After" content="5 days" />
   <meta http-equiv="Expires" content="none" />
   <meta name="Keywords" content="snooker, billiards, league, tables, fixtures, results, leamington, spa, warwick, district, association, sinj, computers, downloads, ldbsa, l.d.b.s.a, officers, rules" />
   <meta name="Description" content="All the latest news, fixtures, results, cup matches and league tables from the Leamington and District Billiards and Snooker Association." />
   <meta name="Pagetopic" content="Sports" />
   <meta name="Pagetype" content="Statistics" />
   <meta name="Audience" content=" All" />
   <meta name="Robots" content="INDEX,FOLLOW" />
   <meta name="Content-Language" content="English" />

<script type="text/javascript">
//ids of elements to show and hide
var ids=new Array('sel','prem','diva','divb');

function switchid(id){   
   hideallids();
   showdiv(id);
}

function hideallids(){
   //loop through the array and hide each element by id
   for (var i=0;i<ids.length;i++){
      hidediv(ids[i]);
   }       
}

function hidediv(id) {
   //function to hide an element with a specified id
   if (document.getElementById) { // DOM3 = IE5, NS6
      document.getElementById(id).style.display = 'none';
   }
   else {
      if (document.layers) { // Netscape 4
         document.id.display = 'none';
      }
      else { // IE 4
         document.all.id.style.display = 'none';
      }
   }
}

function showdiv(id) {
   //safe function to show an element with a specified id
       
   if (document.getElementById) { // DOM3 = IE5, NS6
      document.getElementById(id).style.display = 'block';
   }
   else {
      if (document.layers) { // Netscape 4
         document.id.display = 'block';
      }
      else { // IE 4
         document.all.id.style.display = 'block';
      }
   }
}
</script>

</head>

<body id="p1">

<!--HEADER -->
<div id="header">
   <?php include 'includes/header.php';?>
</div>
<!-- END HEADER -->

<!-- SPONSORS -->
<div id="sponsors">
   <?php include 'includes/sponsors.php';?>
</div>
<!-- END SPONSORS -->
<!-- CONTENT CONTAINER -->
<div id="wrapper">
   <!-- NAVIGATION -->
           <div class="leftMenu">
                    <?php include 'includes/nav.php';?>
            </div>
            <!-- END NAVIGATION -->
           
            <!-- CONTENT -->
   <div class="content">
      <h1>Snooker  -  Player Statistics</h1>
<?php
   $division = $_REQUEST['division'] ;
   $username = "myusername";
   $password = "apassword";
   $hostname = "localhost";   
   $dbcon = mysql_connect($hostname, $username, $password)
      or die("Unable to connect to database");
   mysql_select_db("mydatabase");
   $sql = 'SELECT * FROM '.$division.' ORDER BY Pts DESC , Won DESC , Team';
   $result = mysql_query($sql);
   $nrows = mysql_num_rows($result);
   if($nrows != 0)
   {
      echo "<h2>".$division." Division League Table</h2>";
      echo "<br />";
      echo "<table style='padding:5px;border: 1px solid #000;border-collapse:collapse;'>";
      echo "<tr>";
      echo "<th style='background-color:#0a0;color:#fff;width:150px;border: 1px solid #006001;'>Team</th>";
      echo "<th style='background-color:#0a0;color:#fff;width:60px;border: 1px solid #006001;'>Pld</th>";
      echo "<th style='background-color:#0a0;color:#fff;width:60px;border: 1px solid #006001;'>Won</th>";
      echo "<th style='background-color:#0a0;color:#fff;width:60px;border: 1px solid #006001;'>Lost</th>";
      echo "<th style='background-color:#0a0;color:#fff;width:60px;border: 1px solid #006001;'>Pts</th>";
      echo "<th style='background-color:#0a0;color:#fff;width:100px;border: 1px solid #006001;'>Pts Deducted</th>\n";
      for($j=0;$j<$nrows;$j++)
      {
         $row = mysql_fetch_array($result);
         echo "<tr><td style='border: 1px solid #000;'>" . $row["Team"] . "</td>";
         echo "<td style='text-align:center;border: 1px solid #006001;'>" . $row["Pld"] . "</td>";
         echo "<td style='text-align:center;border: 1px solid #006001;'>" . $row["Won"] . "</td>";
         echo "<td style='text-align:center;border: 1px solid #006001;'>" . $row["Lost"] . "</td>";
         echo "<td style='text-align:center;border: 1px solid #006001;'>" . $row["Pts"] . "</td>";
         echo "<td style='text-align:center;border: 1px solid #006001;'>" . $row["Pts_Deducted"] . "%</td></tr>";
         echo "\n";
      }
      echo "</table>\n";
      echo "<p><form action='snookertables.php'><input type=submit value='Click here to lookup another team' /></form></p>\n";
   }
   else   echo "<p>No Entry for " . $division . " division.</p><p><a href='s_stats.php'>Click here to try again</a>.</p>";

   mysql_close($dbcon);
?>   </div>
   <!-- END CONTENT -->
   
   <!-- RIGHT -->
   <div class="rightMenu">
                    <?php include 'includes/nav_snooker.php';?>
            </div>
</div>
<!-- END CONTAINER -->

<!-- FOOTER -->
<div id="footer">
   <?php include 'includes/footer.php';?>
</div>
<!-- END FOOTER -->

</body>

</html>


..and here's the code for s_statsresults.php which is working. I can't see why I get the error on the first one, because it's virtually the same code on this one.
Code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >

<head>

   <title>Leamington and District Billiards and Snooker Association  - Player Statistics</title>
   <link rel="stylesheet" type="text/css" href="css/css_main.css" />
   <link rel="shortcut icon" href="images/favicon.ico" />

   <meta http-equiv="Content-Type" content="text/html; charset=shift_jis" />
   <meta name="Author" content="Steve Williams" />
   <meta name="Publisher" content="Steve Williams" />
   <meta name="Copyright" content="c Copyright 2007, Steve Williams" />
   <meta name="Revisit-After" content="5 days" />
   <meta http-equiv="Expires" content="none" />
   <meta name="Keywords" content="snooker, billiards, league, tables, fixtures, results, leamington, spa, warwick, district, association, sinj, computers, downloads, ldbsa, l.d.b.s.a, officers, rules" />
   <meta name="Description" content="All the latest news, fixtures, results, cup matches and league tables from the Leamington and District Billiards and Snooker Association." />
   <meta name="Pagetopic" content="Sports" />
   <meta name="Pagetype" content="Statistics" />
   <meta name="Audience" content=" All" />
   <meta name="Robots" content="INDEX,FOLLOW" />
   <meta name="Content-Language" content="English" />

<script type="text/javascript">
//ids of elements to show and hide
var ids=new Array('sel','prem','diva','divb');

function switchid(id){   
   hideallids();
   showdiv(id);
}

function hideallids(){
   //loop through the array and hide each element by id
   for (var i=0;i<ids.length;i++){
      hidediv(ids[i]);
   }       
}

function hidediv(id) {
   //function to hide an element with a specified id
   if (document.getElementById) { // DOM3 = IE5, NS6
      document.getElementById(id).style.display = 'none';
   }
   else {
      if (document.layers) { // Netscape 4
         document.id.display = 'none';
      }
      else { // IE 4
         document.all.id.style.display = 'none';
      }
   }
}

function showdiv(id) {
   //safe function to show an element with a specified id
       
   if (document.getElementById) { // DOM3 = IE5, NS6
      document.getElementById(id).style.display = 'block';
   }
   else {
      if (document.layers) { // Netscape 4
         document.id.display = 'block';
      }
      else { // IE 4
         document.all.id.style.display = 'block';
      }
   }
}
</script>

</head>

<body id="p1">

<!--HEADER -->
<div id="header">
   <?php include 'includes/header.php';?>
</div>
<!-- END HEADER -->

<!-- SPONSORS -->
<div id="sponsors">
   <?php include 'includes/sponsors.php';?>
</div>
<!-- END SPONSORS -->
<!-- CONTENT CONTAINER -->
<div id="wrapper">
   <!-- NAVIGATION -->
           <div class="leftMenu">
                    <?php include 'includes/nav.php';?>
            </div>
            <!-- END NAVIGATION -->
           
            <!-- CONTENT -->
   <div class="content">
      <h1>Snooker  -  Player Statistics</h1>
<?php
   $team = $_REQUEST['team'] ;
   $username = "myusername";
   $password = "apassword";
   $hostname = "localhost";   
   $dbcon = mysql_connect($hostname, $username, $password)
      or die("Unable to connect to database");
   mysql_select_db("mydatabase");
   $sql = 'SELECT * FROM '.$team.' ORDER BY Percentage DESC , Won DESC , PlayerName';
   $result = mysql_query($sql);
   $nrows = mysql_num_rows($result);
   if($nrows != 0)
   {
      echo "<h2>Player Statistics for " . $team . "</h2>";
      echo "<br />";
      echo "<table style='padding:5px;border: 1px solid #000;border-collapse:collapse;'><tr><th style='background-color:#0a0;color:#fff;width:150px;border: 1px solid #006001;'>Player Name</th><th style='background-color:#0a0;color:#fff;width:60px;border: 1px solid #006001;'>Pld</th><th style='background-color:#0a0;color:#fff;width:60px;border: 1px solid #006001;'>Won</th><th style='background-color:#0a0;color:#fff;width:60px;border: 1px solid #006001;'>Lost</th><th style='background-color:#0a0;color:#fff;width:100px;border: 1px solid #006001;'>Percentage</th>\n";
      for($j=0;$j<$nrows;$j++)
      {
         $row = mysql_fetch_array($result);
         echo "<tr><td style='border: 1px solid #000;'>" . $row["PlayerName"] . "</td>";
         echo "<td style='text-align:center;border: 1px solid #006001;'>" . $row["Pld"] . "</td>";
         echo "<td style='text-align:center;border: 1px solid #006001;'>" . $row["Won"] . "</td>";
         echo "<td style='text-align:center;border: 1px solid #006001;'>" . $row["Lost"] . "</td>";
         echo "<td style='text-align:center;border: 1px solid #006001;'>" . $row["Percentage"] . "%</td></tr>";
         echo "\n";
      }
      echo "</table>\n";
      echo "<p><form action='s_stats.php'><input type=submit value='Click here to lookup another team' /></form></p>\n";
   }
   else   echo "<p>No Entry for " . $team . "<p><a href='s_stats.php'>Click here to lookup another team</a></p>";

   mysql_close($dbcon);
?>   </div>
   <!-- END CONTENT -->
   
   <!-- RIGHT -->
   <div class="rightMenu">
                    <?php include 'includes/nav_snooker.php';?>
            </div>
</div>
<!-- END CONTAINER -->

<!-- FOOTER -->
<div id="footer">
   <?php include 'includes/footer.php';?>
</div>
<!-- END FOOTER -->

</body>

</html>
ncwdavid
Hey, I don' have enough time to look at all the code properly but I will make an example of what I do to display information from the database on a webpage when you want it to display more then one row in the database:

Code:

<table border="1" width="500" align="center">
<?php
$sql = mysql_query("SELECT * FROM users");
while( $row = mysql_fetch_array($sql) ){
echo "<tr><td>$row[username]</td></tr>";
}
?>
</table>


That is just an example I made where it gets all the information of every user in the table "users" and then diplays every user's username in a seperate row. Does it help? I can help more later if it hasnt already been solved.
welshsteve
Thanks. I'll take a look at that and try it. Ideally I'd liek to do it like below though. I've slightly re-jigged it now
Code:


<?php
$division = $_REQUEST['division'] ;
$con = mysql_connect("localhost","USERNAME","PASSWORD");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("DBNAME", $con);

$result = mysql_query("SELECT * FROM '.$division.' ORDER BY Pts DESC , Won DESC , Team");

echo "<h2>".$division." Division League Table</h2>
<br />
<table style='padding:5px;border: 1px solid #000;border-collapse:collapse;'>
<tr>
<th style='background-color:#0a0;color:#fff;width:150px;border: 1px solid #006001;'>Team</th>
<th style='background-color:#0a0;color:#fff;width:60px;border: 1px solid #006001;'>Pld</th>
<th style='background-color:#0a0;color:#fff;width:60px;border: 1px solid #006001;'>Won</th>
<th style='background-color:#0a0;color:#fff;width:60px;border: 1px solid #006001;'>Lost</th>
<th style='background-color:#0a0;color:#fff;width:60px;border: 1px solid #006001;'>Pts</th>
<th style='background-color:#0a0;color:#fff;width:100px;border: 1px solid #006001;'>Pts Deducted</th>
</tr>";
      
while($row = mysql_fetch_array($result))
  {
   echo "<tr>";
   echo "<td style='text-align:center;border: 1px solid #006001;'>" . $row['Team'] . "</td>";
   echo "<td style='text-align:center;border: 1px solid #006001;'>" . $row['Pld'] . "</td>";
   echo "<td style='text-align:center;border: 1px solid #006001;'>" . $row['Won'] . "</td>";
   echo "<td style='text-align:center;border: 1px solid #006001;'>" . $row['Lost'] . "</td>";
   echo "<td style='text-align:center;border: 1px solid #006001;'>" . $row['Pts'] . "</td>";
   echo "<td style='text-align:center;border: 1px solid #006001;'>" . $row['Pts_Deducted'] . "%</td>";
  echo "</tr>";
  }
echo "</table>";
echo "<p><form action='snookertables.php'><input type=submit value='Click here to lookup another team' /></form></p>\n";

mysql_close($con);
?>

</body>

</html>


If you can help, I'd appreciate it. Would be willing to part with 100 Frih$ if someone can get this working for me.
blueray
Code:
$result = mysql_query("SELECT * FROM '.$division.' ORDER BY Pts DESC , Won DESC , Team");


try changed to

Code:
$result = mysql_query("SELECT * FROM $division ORDER BY Pts DESC , Won DESC , Team");


Wish this help !!
Related topics
ALTER TABLE mySQL problem.
Java tutorials
Sql/Phpbb error...
Error on running a Big SQL Statement on MySql
mySQL create table problem (parse error)..
PM Logout
O'calhoun's reusable stupid php question thread [NEW]
Web form to save HTML table as spreadsheet
SQL error
Error while creating the SQL table pls help
SQL help.
oracle sql Alter table from a triggered package procedure
Getting Fatal Error in php mysqli
MySQL Error in code but works in phpMyAdmin
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.