Dropdown With Mysql Tables' Names Of A Database As Options And Php-Mysql Errors/suggestions

maicol07 · July 21, 2017

Ok, I applied it but it gives me some errors...

Notice: Undefined index: start in C:\xampp\htdocs\sitesld\view-paginated.php on line 83

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\sitesld\view-paginated.php on line 88Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\sitesld\view-paginated.php on line 111

[AFTER THE BLANK TABLE (See question below)]

Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in C:\xampp\htdocs\sitesld\view-paginated.php on line 111

Code of the page:

<?php
require ("includes/config.php");
//if not logged in redirect to login page
if(!$user->is_logged_in()){ header('Location: login.php'); } 
$nomeutente=$_SESSION['username'];
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<title>Visualizza Record</title>
<!--Import Google Icon Font-->
<link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet">
<!--Import materialize.css-->
<link type="text/css" rel="stylesheet" href="css/materialize.min.css"  media="screen,projection"/>
<!--Let browser know website is optimized for mobile-->
<meta name="viewport" content="width=device-width, initial-scale=1.0"/>
</head>
<body>
<!--Import jQuery before materialize.js-->
<script type="text/javascript" src="https://code.jquery.com/jquery-2.1.1.min.js"></script>
<script type="text/javascript" src="js/materialize.min.js"></script>
<div class="container">
<p align="right">Ciao, <?php echo $nomeutente; ?><br>
<a href='logout.php'class="waves-effect waves-light btn"><i class="material-icons left">power_settings_new</i>Logout</a></p>
<form method="get">
Visualizza voti di:
<div class="input-field inline">
<?php

// load database values
require 'connect-db.php';

// connect to database
$conn = new mysqli($server, $user, $pass, $db);
if ($conn->connect_error) die("Connection failed: ".$conn->connect_error);

// perform query
$sql = "show tables LIKE '$nomeutente%'";
$result = $conn->query($sql) or die($conn->error);

// start dropdown select
echo "<select name='materia'>";
echo "<option value='allmaterie'>Tutte le materie</option>";

// for each table in database create an entry
while ($row = $result->fetch_assoc()) {
  $table = $row["Tables_in_members ($nomeutente%)"];
  $mat=ucfirst(substr($table, strpos($table, "_") + 1));
  echo "<option value='$table'>$mat</option>";
}
echo "</select>";
?>
</div>
<script>
  $(document).ready(function() {
    $('select').material_select();
  });
</script>
<button class="btn waves-effect waves-light" type="submit" name="action">Visualizza
<i class="material-icons left">search</i>
</button>
</form>
<br><br>
<?php
/*
        VIEW-PAGINATED.PHP
        Views all the data of a MySQL table
        This modified version has impagination
*/
 
        //database connection already done before
 
        echo "<a href='view.php' class='waves-effect waves-light btn' type='button'><i class='material-icons left'>search</i>Visualizza Tutto</a> | <a class='waves-effect waves-light btn' href='view-paginated.php?page=1' disabled><i class='material-icons left'>find_in_page</i>Visualizza impaginato</a>";
        echo "</p>";
 
        // view data in a table
        echo "<table class='centered striped responsive-table' border='1' cellpadding='10'>";
        echo "<tr> <th>Voto</th> <th>Data</th> <th>Materia</th> <th>Peso</th> <th>Descrizione</th></tr>";
 
        $iRows = 10; //Show 10 rows at a time
		$iStart = mysqli_real_escape_string($connection,$_GET['start']); 
		$iEnd = $iStart + $iRows; 

		$rData = mysqli_query($connection,"SELECT * FROM table LIMIT $iStart,$iEnd"); 

		while ($aRow = mysqli_fetch_assoc($rData)) 
		{		
            // printing content of all rows in a table
            echo "<tr>";
            echo '<td>' .$aRow["voto"]. '</td>';
            echo '<td>' .$aRow["data"]. '</td>';
            echo '<td>' .$aRow["materia"]. '</td>';
			echo '<td>' .$aRow["peso"]. '</td>';
			echo '<td>' .$aRow["descrizione"]. '</td>';
            echo '<td><a class="waves-effect waves-light btn" href="edit.php?id=' . mysql_result($result, $i, 'id') . '"><i class="material-icons left">mode_edit</i>Modifica</a></td>';
            echo '<td><a class="waves-effect waves-light btn" href="delete.php?id=' . mysql_result($result, $i, 'id') . '"><i class="material-icons left">delete</i>Elimina</a></td>';
            echo "</tr>";
        }
        // close the table
        echo "</table>";

        // impagination
		echo "<a href=\"".$_SERVER['PHP_SELF']."?start=$iStart-$iRows\">Pagina precedente</a>"; 
		echo "<a href=\"".$_SERVER['PHP_SELF']."?start=$iStart+$iRows\">Pagina successiva</a>";
		
		// Calculating average
		$num=0;
		$den=0;
		while ($aRow = mysqli_fetch_assoc($rData)) 
		{
			$num+=($aRow["voto"]*$aRow["peso"]);
			$den+=$aRow["peso"];
		}
		$media=$num/$den;
		echo '<div align="center" style="padding:4px; border:2px solid blue; color:black;">MEDIA: '.$media.'</div>';
?>
  <div class="fixed-action-btn">
    <a class="btn-floating btn-large red"><i class="large material-icons">add</i>Aggiungi</a>
    <ul>
      <li><a href="new_voto.php" class="btn-floating blue"><i class="material-icons">note_add</i></a>Aggiungi voto</li>
      <li><a href="new_materia.php" class="btn-floating yellow darken-1"><i class="material-icons">class</i></a>Aggiungi Materia</li>
    </ul>
  </div>
</div>
</body>
</html>

PROBLEM 2: In the file above and in the file below the table rows don't show... I don't know why... can you please help me?

<?php
require ("includes/config.php");
//if not logged in redirect to login page
if(!$user->is_logged_in()){ header('Location: login.php'); } 
$nomeutente=$_SESSION['username'];
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<title>Visualizza Voti</title>
<!--Import Google Icon Font-->
<link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet">
<!--Import materialize.css-->
<link type="text/css" rel="stylesheet" href="css/materialize.min.css"  media="screen,projection"/>
<!--Let browser know website is optimized for mobile-->
<meta name="viewport" content="width=device-width, initial-scale=1.0"/>
</head>
<body>
<!--Import jQuery before materialize.js-->
<script type="text/javascript" src="https://code.jquery.com/jquery-2.1.1.min.js"></script>
<script type="text/javascript" src="js/materialize.min.js"></script>
<div class="container">
<p align="right">Ciao, <?php echo $nomeutente; ?><br>
<a href='logout.php'class="waves-effect waves-light btn"><i class="material-icons left">power_settings_new</i>Logout</a></p>
  <div class="fixed-action-btn">
    <a class="btn-floating btn-large red"><i class="large material-icons">add</i>Aggiungi</a>
    <ul>
      <li><a href="new_voto.php" class="btn-floating blue"><i class="material-icons">note_add</i></a>Aggiungi voto</li>
      <li><a href="new_materia.php" class="btn-floating yellow darken-1"><i class="material-icons">class</i></a>Aggiungi Materia</li>
    </ul>
  </div>
<form method="get">
Visualizza voti di:
<div class="input-field inline">
<?php

// load database values
require 'connect-db.php';

// connect to database
$conn = new mysqli($server, $user, $pass, $db);
if ($conn->connect_error) die("Connection failed: ".$conn->connect_error);

// perform query
$sql = "show tables LIKE '$nomeutente%'";
$result = $conn->query($sql) or die($conn->error);
if (isset($_GET['materia'])) {
	$mat_sel=$_GET['materia'];
	// start dropdown select
	echo "<select id='materia' name='materia' value='$mat_sel'>";
}
else {
	echo "<select id='materia' name='materia'>";
}
echo "<option value='allmaterie'>Tutte le materie</option>";

// for each table in database create an entry
while ($row = $result->fetch_assoc()) {
  $table = $row["Tables_in_members ($nomeutente%)"];
  $mat=ucfirst(substr($table, strpos($table, "_") + 1));
  echo "<option value='$table'>$mat</option>";
}
echo "</select>";
?>
</div>
<script>
  $(document).ready(function() {
    $('select').material_select();
  });
</script>
<button class="btn waves-effect waves-light" type="submit" name="action">Visualizza
<i class="material-icons left">search</i>
</button>
</form>

<br><br>
<?php
// Is a subject selected or no?
if (isset($_GET['materia'])) {
	$allmaterie=False;
	if ($_GET['materia']=='allmaterie') {
		$allmaterie=True;
	}
}
else {
	$allmaterie=True;
}
// view the data in a table
echo "<button class='waves-effect waves-light btn' type='button' disabled><i class='material-icons left'>search</i>Visualizza Tutto</button> | <a class='waves-effect waves-light btn' href='view-paginated.php?start=1'><i class='material-icons left'>find_in_page</i>Visualizza impaginato</a>";
echo "<br><br>";
 
echo "<table class='centered striped responsive-table' border='1' cellpadding='10'>";
echo "<thead>";
echo "<tr> <th>Voto</th> <th>Data</th> <th>Materia</th> <th>Peso</th> <th>Descrizione</th></tr>";
echo "</thead>";
echo "<tbody>";
if ($allmaterie=True) {
	$sql = "show tables LIKE '$nomeutente%'";
	$result = $conn->query($sql) or die($conn->error);
	while ($row = $result->fetch_assoc()) {
		$row_result=$row["Tables_in_members ($nomeutente%)"];
		$result = mysqli_query($conn,"SELECT * FROM '$row_result'")
		or die(mysqli_error($connection));
		// loop table result
		while($row = mysqli_fetch_array( $result )) {
 
			// printing table
			echo "<tr>";
			echo '<td>' . $row['voto'] . '</td>';
			echo '<td>' . $row['data'] . '</td>';
			echo '<td>' . $row['materia'] . '</td>';
			echo '<td>' . $row['peso'] . '%</td>';
			echo '<td>' . $row['descrizione'] . '</td>';
			echo '<td><a class="waves-effect waves-light btn" href="edit.php?id=' . $row['id'] . '><i class="material-icons left">mode_edit</i>Modifica</a></td>';
			echo '<td><a class="waves-effect waves-light btn" href="delete.php?id=' . $row['id'] . '&materia='.$row['materia'].'><i class="material-icons left">delete</i>Elimina</a>';
			echo "</tr>";
		}
	}
	// close table
	echo "</tbody>";
	echo "</table>";
}
else {
	$materia=$_GET['materia'];
	$result = mysqli_query($connection, "SELECT * FROM $materia")
	or die(mysqli_error($connection));
	// loop table results
	while($row = mysqli_fetch_array( $result )) {
 
	// printing contents of all rows in a table
	echo "<tr>";
	echo '<td>' . $row['voto'] . '</td>';
	echo '<td>' . $row['data'] . '</td>';
	echo '<td>' . $row['materia'] . '</td>';
	echo '<td>' . $row['peso'] . '%</td>';
	echo '<td>' . $row['descrizione'] . '</td>';
	echo '<td><a class="waves-effect waves-light btn" href="edit.php?id=' . $row['id'] . '><i class="material-icons left">mode_edit</i>Modifica</a></td>';
	echo '<td><a class="waves-effect waves-light btn" href="delete.php?id=' . $row['id'] . '&materia='.$row['materia'].'><i class="material-icons left">delete</i>Elimina</a>';
	echo "</tr>";
}
 
// Close the table
echo "</table>";
}
// Calculating average
$num=0;
$den=0;
while ($aRow = mysqli_fetch_assoc($rData)) 
{
	$num+=($aRow["voto"]*$aRow["peso"]);
	$den+=$aRow["peso"];
}
$media=$num/$den;
echo '<div align="center" style="padding:4px; border:2px solid blue; color:black;">MEDIA: '.$media.'</div>';
?>
</div>
</body>
</html>

Thanks

wolstech · July 21, 2017

Your forgot to change the table name in my example:

$rData = mysqli_query($connection,"SELECT * FROM table LIMIT $iStart,$iEnd");

You need to change "table" to your table name.

The table is blank because the mysqli_fetch_assoc() failed. The mysqli_fetch_assoc() failed and produced those errors because the mysqli_query() failed. The mysqli_query() likely failed because of two reasons: The connection isn't open (I don't see where you opened one in the view_paginated.php), and the query also tried to select from a non-existent table called 'table'.

A lack of error checking code means you have no way of seeing the error message the mysqli_query() generates when it fails. The easiest way to check for the query failing is to put an if statement after mysqli_query() to check the value that was returned. If the returned value is false instead of a result, it failed...you can then mysqli_error() to return a string saying what the error was.

$result = mysqli_query($connection,"Whatever my query is");

//Add the below if statement
//This makes your script show the SQL error and stop running if the query fails. Put it after each mysqli_query() line, and be sure to change $result to whatever
//variable your mysqli_query() puts its result in.
if (!$result) { die(mysqli_error($conn)); }

maicol07 · July 21, 2017

Ok thanks, I solved all the errors, but the table doesn't show... in any file...

I edited the view-paginated.php file with some changes... here's the code:

<?php
require ("includes/config.php");
//if not logged in redirect to login page
if(!$user->is_logged_in()){ header('Location: login.php'); } 
$nomeutente=$_SESSION['username'];
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<title>Visualizza Record</title>
<!--Import Google Icon Font-->
<link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet">
<!--Import materialize.css-->
<link type="text/css" rel="stylesheet" href="css/materialize.min.css"  media="screen,projection"/>
<!--Let browser know website is optimized for mobile-->
<meta name="viewport" content="width=device-width, initial-scale=1.0"/>
</head>
<body>
<!--Import jQuery before materialize.js-->
<script type="text/javascript" src="https://code.jquery.com/jquery-2.1.1.min.js"></script>
<script type="text/javascript" src="js/materialize.min.js"></script>
<div class="container">
<p align="right">Ciao, <?php echo $nomeutente; ?><br>
<a href='logout.php'class="waves-effect waves-light btn"><i class="material-icons left">power_settings_new</i>Logout</a></p>
<form method="get">
Visualizza voti di:
<div class="input-field inline">
<?php

// load database values
require 'connect-db.php';

// connect to database
$conn = new mysqli($server, $user, $pass, $db);
if ($conn->connect_error) die("Connection failed: ".$conn->connect_error);

// perform query
$sql = "show tables LIKE '$nomeutente%'";
$result = $conn->query($sql) or die($conn->error);

// start dropdown select
echo "<select name='materia'>";
echo "<option value='allmaterie'>Tutte le materie</option>";

// for each table in database create an entry
while ($row = $result->fetch_assoc()) {
  $table = $row["Tables_in_members ($nomeutente%)"];
  $mat=ucfirst(substr($table, strpos($table, "_") + 1));
  echo "<option value='$table'>$mat</option>";
}
echo "</select>";
?>
</div>
<script>
  $(document).ready(function() {
    $('select').material_select();
  });
</script>
<button class="btn waves-effect waves-light" type="submit" name="action">Visualizza
<i class="material-icons left">search</i>
</button>
</form>
<br><br>
<?php
/*
        VIEW-PAGINATED.PHP
        View all the data of a MySQL table
        This is a view.php file that contains impagination
*/
 
		// load database values
		require 'connect-db.php';

		// connect to database
		$connection = new mysqli($server, $user, $pass, $db);
		if ($connection->connect_error) die("Connection failed: ".$connection->connect_error);
        
		// view impagination
 
        echo "<a href='view.php' class='waves-effect waves-light btn' type='button'><i class='material-icons left'>search</i>Visualizza Tutto</a> | <a class='waves-effect waves-light btn' href='view-paginated.php?page=1' disabled><i class='material-icons left'>find_in_page</i>Visualizza impaginato</a>";
        echo "</p>";
 
        // view table data
        echo "<table class='centered striped responsive-table' border='1' cellpadding='10'>";
        echo "<tr> <th>Voto</th> <th>Data</th> <th>Materia</th> <th>Peso</th> <th>Descrizione</th></tr>";
 
        $iRows = 10; //Show 10 rows at a time
		$iStart = mysqli_real_escape_string($connection,$_GET['start']); 
		$iEnd = $iStart + $iRows; 
		
		if (isset($_GET['materia'])) {
			$materia=$_GET["materia"];
			$rData = mysqli_query($connection,"SELECT * FROM $materia LIMIT $iStart,$iEnd"); 
		}
		else {
			$sql = "show tables LIKE '$nomeutente%'";
			$result = $conn->query($sql) or die($conn->error);
			while ($row = $result->fetch_assoc()) {
				$row_result=$row["Tables_in_members ($nomeutente%)"];
				$rData = mysqli_query($conn,"SELECT * FROM '$row_result' LIMIT $iStart,$iEnd")
				or die(mysqli_error($connection));
			}
		}
		if (!$rData) { die(mysqli_error($conn)); }
		
		while ($aRow = mysqli_fetch_assoc($rData)) 
		{		
            // printing table contents
            echo "<tr>";
            echo '<td>' .$aRow["voto"]. '</td>';
            echo '<td>' .$aRow["data"]. '</td>';
            echo '<td>' .$aRow["materia"]. '</td>';
			echo '<td>' .$aRow["peso"]. '</td>';
			echo '<td>' .$aRow["descrizione"]. '</td>';
            echo '<td><a class="waves-effect waves-light btn" href="edit.php?id=' . $aRow["id"] . '&materia='.$aRow["materia"].'"><i class="material-icons left">mode_edit</i>Modifica</a></td>';
            echo '<td><a class="waves-effect waves-light btn" href="delete.php?id=' . $aRow["id"] . '&materia='.$aRow["materia"].'"><i class="material-icons left">delete</i>Elimina</a></td>';
            echo "</tr>";
        }
        // close the table
        echo "</table>";

        // impagination
		if ($iStart==10) {
			$disableback="disabled";
		}
		else {
			$disableback="";
		}
		if ($iStart==$iEnd) {
			$disablefwd="disabled";
		}
		else {
			$disablefwd="";
		}
		echo "<br>";
		echo "<a ".$disableback. " class='btn waves-effect waves-light' href=\"".$_SERVER['PHP_SELF']."?start=$iStart-$iRows\"><i class='material-icons left'>arrow_back</i>Pagina precedente</a>"; 
		echo "    ";
		echo "<a ".$disablefwd." class='btn waves-effect waves-light' href=\"".$_SERVER['PHP_SELF']."?start=$iStart+$iRows\"><i class='material-icons right'>arrow_forward</i>Pagina successiva</a>";
		
		// Calculating average
		$num=0;
		$den=0;
		while ($aRow = mysqli_fetch_assoc($rData)) 
		{
			$num+=($aRow["voto"]*$aRow["peso"]);
			$den+=$aRow["peso"];
		}
		$media=$num/$den;
		echo '<div align="center" style="padding:4px; border:2px solid blue; color:black;">MEDIA: '.$media.'</div>';
?>
  <div class="fixed-action-btn">
    <a class="btn-floating btn-large red"><i class="large material-icons">add</i>Aggiungi</a>
    <ul>
      <li><a href="new_voto.php" class="btn-floating blue"><i class="material-icons">note_add</i></a>Aggiungi voto</li>
      <li><a href="new_materia.php" class="btn-floating yellow darken-1"><i class="material-icons">class</i></a>Aggiungi Materia</li>
    </ul>
  </div>
</div>
</body>
</html>

Why doesn't the table show?

Thanks

maicol07 · July 24, 2017

Any solutions?

Also, the dropdown doesn't work... it is blank... https://apps.maicol07.tk/app/sld/voti/test_dropdown.php

The code is on this post: https://www.helionet.org/index/topic/28805-dropdown-with-mysql-tables-names-of-a-database-as-options-and-php-mysql-errorssuggestions/page-2?do=findComment&comment=131657

Thanks

Edited July 24, 2017 by maicol07

maicol07 · July 25, 2017

I moved the topic here: https://www.helionet.org/index/topic/29002-help-with-php-mysql-script/

Sign In

Dropdown With Mysql Tables' Names Of A Database As Options And Php-Mysql Errors/suggestions

Recommended Posts

maicol07

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

wolstech

Link to comment

Share on other sites

maicol07

Link to comment

Share on other sites

maicol07

Link to comment

Share on other sites

maicol07

Link to comment

Share on other sites

Join the conversation

HelioHost

Donate

Forums

Activity