retrieve data from db and display it in table in php .. see this code whats wrong with it?

Question 1

  $db = mysql_connect("localhost", "root", "");
  $er = mysql_select_db("ram");
  $query = "insert into names values('$name','$add1','$add2','$mail')";
  $result = mysql_query($query);
  print "<p> Person's Information Inserted </p>";
  $result = mysql_query("SELECT * FROM names");
?>

<table border="2">
   <tr>
      <th>Name</th>
      <th>Address Line 1</th>
      <th>Address Line 2 </th>
      <th>E-mail Id </th>
    </tr>
    <? 
    while ($array = mysql_fetch_row($result));
    {
        print "<tr> <td>";
        echo $array[0]; 
        print "</td> <td>";
        echo $array[1]; 
        print "</td> <td>";
        echo $array[2]; 
        print "</td> <td>";
        echo $array[3]; 
        print "</td> </tr>";
    }
?>

Question 2

Try this:

<?php

 # Init the MySQL Connection
  if( !( $db = mysql_connect( 'localhost' , 'root' , '' ) ) )
    die( 'Failed to connect to MySQL Database Server - #'.mysql_errno().': '.mysql_error();
  if( !mysql_select_db( 'ram' ) )
    die( 'Connected to Server, but Failed to Connect to Database - #'.mysql_errno().': '.mysql_error();

 # Prepare the INSERT Query
  $insertTPL = 'INSERT INTO `name` VALUES( "%s" , "%s" , "%s" , "%s" )';
  $insertSQL = sprintf( $insertTPL ,
                 mysql_real_escape_string( $name ) ,
                 mysql_real_escape_string( $add1 ) ,
                 mysql_real_escape_string( $add2 ) ,
                 mysql_real_escape_string( $mail ) );
 # Execute the INSERT Query
  if( !( $insertRes = mysql_query( $insertSQL ) ) ){
    echo '<p>Insert of Row into Database Failed - #'.mysql_errno().': '.mysql_error().'</p>';
  }else{
    echo '<p>Person's Information Inserted</p>'
  }

 # Prepare the SELECT Query
  $selectSQL = 'SELECT * FROM `names`';
 # Execute the SELECT Query
  if( !( $selectRes = mysql_query( $selectSQL ) ) ){
    echo 'Retrieval of data from Database Failed - #'.mysql_errno().': '.mysql_error();
  }else{
    ?>
<table border="2">
  <thead>
    <tr>
      <th>Name</th>
      <th>Address Line 1</th>
      <th>Address Line 2</th>
      <th>Email Id</th>
    </tr>
  </thead>
  <tbody>
    <?php
      if( mysql_num_rows( $selectRes )==0 ){
        echo '<tr><td colspan="4">No Rows Returned</td></tr>';
      }else{
        while( $row = mysql_fetch_assoc( $selectRes ) ){
          echo "<tr><td>{$row['name']}</td><td>{$row['addr1']}</td><td>{$row['addr2']}</td><td>{$row['mail']}</td></tr>n";
        }
      }
    ?>
  </tbody>
</table>
    <?php
  }

?>

Notes, Cautions and Caveats

Your initial solution did not show any obvious santisation of the values before passing them into the Database. This is how SQL Injection attacks (or even un-intentional errors being passed through SQL) occur. Don't do it!

Your database does not seem to have a Primary Key. Whilst these are not, technically, necessary in all usage, they are a good practice, and make for a much more reliable way of referring to a specific row in a table, whether for adding related tables, or for making changes within that table.

You need to check every action, at every stage, for errors. Most PHP functions are nice enough to have a response they will return under an error condition. It is your job to check for those conditions as you go - never assume that PHP will do what you expect, how you expect, and in the order you expect. This is how accident happen...

My provided code above contains alot of points where, if an error has occured, a message will be returned. Try it, see if any error messages are reported, look at the Error Message, and, if applicable, the Error Code returned and do some research.

Good luck.

Question 3

If you make your db connection a Singleton class and you have an __autoload() function, then you can just have a dbconnection property in each of your classes which is set during construction.

e.g.

class DBConnection{

  private static $instance;

  private function __construct(){
    //make your db connnection
  }

  public static function get_connection(){
    if(empty self::$instance) self::$instance = new DBConnection;
    return self::$instance;
  }
}

class Foo {

  private $db;

  function __construct(){
    $this->db=DBConnection::get_connection();
  }
}

Question 4

Approach

This is possible. The main difficulty is the variable nature of the string lengths, which will typically cause the output data to be difficult to read.

To address this, I suggest that you:

Find the maximum string length in each column
Output Headers with top and lower borders, with header names padded to be same width as maximum cell width
Output each row with the cells padded to the target column width.

Code Sample

I'm not able to unit-test the code shown below at present, but it should be ok; post a comment if you have any trouble.

<?php

// Open connection
$mysqli = new mysqli("db_host", "db_user", "db_password", "db_schema");

// Check that connection succeeded
if ($mysqli->connect_errno) {
    printf("Connect failed: %sn", $mysqli->connect_error);
    exit();
}

// Prepare query
$query = "SELECT * FROM `myTable`";

// Fetch results as associative array
$resultSet = [];
if ($result = $mysqli->query($query)) {

    while ($row = $result->fetch_assoc()) {
        $resultSet[] = $row;
    }

    // Free result set
    $result->free();

    // Close Connection to DB
    $mysqli->close();
} else {

  // Close Connection to DB and print error message
  $mysqli->close();
  die('An error occurred when executing SQL, please check your SQL query.');
}



// If results empty, output error
if (count($resultSet)<1) {
  die('<pre>----NO DATA----'+"nn"+'Please revise your query</pre>');
}

// Get column names
$keys = array_keys($resultSet[0]);

// Iterate over column names, get starting widths
$maxLength = [];
foreach ($keys as $column) {
  $maxLength[$column] = mb_strlen($column);
}

// Iterate over result-set, get maximum string length for each column
foreach ($resultSet as $row) {
  foreach ($keys as $column) {

    // If current cell length is greater than column width, increase column width
    if (mb_strlen($row[$column]) > $maxLength[$column]) {
      $maxLength[$column] = mb_strlen($row[$column]);
    }

  }
}

echo '<pre>';

// Output top border
echo '+';
foreach ($keys as $column) {
  echo str_repeat('-', $maxLength[$column]+2);
  echo '+';
}
echo "n";

// Output header
echo '| ';
foreach ($keys as $column) {
  echo $column.str_repeat(' ', $maxLength[$column] - mb_strlen($column));
  echo ' | ';
}
echo "n";

// Output bottom border
echo '+';
foreach ($keys as $column) {
  echo str_repeat('-', $maxLength[$column]+2);
  echo '+';
}
echo "n";

// Output all rows
foreach ($resultSet as $row) {
  echo '| ';
  foreach ($keys as $column) {
    echo $row[$column].str_repeat(' ', $maxLength[$column] - mb_strlen($row[$column]));
    echo ' | ';
  }
echo "n";
}

echo '</pre>';


?>

NOTE The above code is multi-byte safe but it disregards character width; if the output contains characters of variable width then there might be slight 'kinks' in the column separators. This may be worth a revision if you encounter such problems in the display.

Question 5

Feel free to let me know how bad it is.

It's bad!

...

What?

You asked!

Okay, in all seriousness, it's not so much bad as it is silly. You're wrapping PDO in another class. If you want to add more functionality to PDO, you should be extending it instead.

My question is: how can I get my query class to be only invocable from the database class?

PDO already does this during day to day operations. When you prepare a query, it returns a PDOStatement object. You can configure it to return another object (via PDO::ATTR_STATEMENT_CLASS) that extends PDOStatement instead.

If you want to pre-process the query using your parser, you'll need to override the exec, query and prepare methods in your class that extends PDO. Once you've processed the query, you can call the parent method and return your extended statement class.

If you're worried about people invoking the statement class without going through exec/query/prepare, just keep in mind that no queries can be executed unless the statement knows how to access the database, and it won't be able to do that without the parent PDO object.

Also,

$q = $db->query(sprintf("
    SELECT id,name,modified
    FROM users
    WHERE id_account = %u",
    $id
));

This is downright absurd given the circumstances. You have a PDO object here, there's no reason not to use prepared statements and placeholders here. If you don't want to bind one variable at a time (and I don't blame you), that's what execute's optional array argument is for.

Question 6

<?php
//Connect to DB
$db = mysql_connect("localhst","user","pass") or die("Database Error");
mysql_select_db("db_name",$db);

//Get ID from request
$id = isset($_GET['id']) ? (int)$_GET['id'] : 0;

//Check id is valid
if($id > 0)
{
    //Query the DB
    $resource = mysql_query("SELECT * FROM table WHERE id = " . $id);
    if($resource === false)
    {
        die("Database Error");
    }

    if(mysql_num_rows($resource) == 0)
    {
        die("No User Exists");
    }

    $user = mysql_fetch_assoc($resource);

    echo "Hello User, your number is" . $user['number'];
}

This is very basic but should see you through.

retrieve data from db and display it in table in php .. see this code whats wrong with it?

Answers

Notes, Cautions and Caveats

Approach

Code Sample

php,database