Asked  7 Months ago    Answers:  5   Viewed   34 times
  $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>";
    }
?>

 Answers

66

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.

Wednesday, March 31, 2021
 
qitch
answered 7 Months ago
40

Try to create your checkbox like below:

Solar_Time_Decimal<checkbox name='columns[]' value='1'>
GHI<checkbox name='columns[]' value='2'>
DiffuseHI<checkbox name='columns[]' value='3'>
Zenith_Angle<checkbox name='columns[]' value='4'>
DNI<checkbox name='columns[]' value='5'> 

And try to hange your PHP code to this:

<?php
//HTML forms -> variables
$fromdate = isset($_POST['fyear']) ? $_POST['fyear'] : data("d/m/Y");
$todate = isset($_POST['toyear']) ? $_POST['toyear'] : data("d/m/Y");
$all = false;
$column_names = array('1' => 'Solar_Time_Decimal', '2'=>'GHI', '3'=>'DiffuseHI', '4'=>'Zenith_Angle','5'=>'DNI');
$column_entries = isset($_POST['columns']) ? $_POST['columns'] : array();
$sql_columns = array();
foreach($column_entries as $i) {
   if(array_key_exists($i, $column_names)) {
    $sql_columns[] = $column_names[$i];
   }
}
if (empty($sql_columns)) {
 $all = true;
 $sql_columns[] = "*";
} else {
 $sql_columns[] = "DATE,Local_Time_Decimal";
}

//DNI CHECKBOX + ALL
$tmp ="SELECT ".implode(",", $sql_columns)." FROM $database_Database_Test.$table_name where DATE>="$fromdate" AND DATE<="$todate""; 

$result = mysql_query($tmp);
echo "<table border='1' style='width:300px'>
<tr>
<th>DATE</th>
<th>Local_Time_Decimal</th>";
foreach($column_names as $k => $v) { 
  if($all || (is_array($column_entries) && in_array($k, $column_entries)))
     echo "<th>$v</th>";
}
echo "</tr>";
while( $row = mysql_fetch_assoc($result))
{
    echo "<tr>";  
    echo "<td>" . $row['DATE'] . "</td>";   
    echo "<td>" . $row['Local_Time_Decimal'] . "</td>";  
    foreach($column_names as $k => $v) { 
      if($all || (is_array($column_entries) && in_array($k, $column_entries))) {
         echo "<th>".$row[$v]."</th>";
       }
    }
    echo "</tr>";
}
echo '</table>';

if($result){
        echo "Successful";
    }
    else{
    echo "Enter correct dates";
    }
?>
<?php
mysql_close();?>

This solution consider your particular table columns but if your wish a generic solution you can try to use this SQL too:

$sql_names = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '$database_Database_Test' AND TABLE_NAME = '$table_name'";

and use the result to construct the $column_names array.

Wednesday, March 31, 2021
 
Easen
answered 7 Months ago
31

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.

Wednesday, March 31, 2021
 
erotsppa
answered 7 Months ago
96

I have found out the way to get around this problem and I can imagine it could be an issue for other VistaPanel/cPanel users of phpMyAdmin.

I had to log into the cPanel, change my password, logout, clear all my cookies and then log back in. When I logged back in and tried to connect to phpMyAdmin I could get in fine!

Wednesday, March 31, 2021
 
commonpike
answered 7 Months ago
81

Had the same issue with PHPStorm on MAMP. Checked the port in localhost where MySQL was installed. It was 8889 and not the default 3306. Updating the port fixed the issue for me.

Tuesday, August 24, 2021
 
Sergey Snegirev
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :