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

maicol07 · July 18, 2017

Hi, I tried but it doesn't work... it doesn't also show the button to send the form...

page: https://apps.maicol07.tk/app/sld/voti/test_dropdown.php

Code:

<html>
<body>
<form>
<?php
$username="maicol07";
// 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 '$username_%";
$result = $conn->query($sql);

// start dropdown select
echo "<select>";

// for each table in database create an entry
while ($row = $result->fetch_assoc()) {
  $table = $row['Tables_in_maicol07_sld_voti'];
  echo "<option value='$table'>$table</option>";
}
echo "</select>";
?>
<input type="submit" class="btn waves-effect waves-light" value="VISUALIZZA">
</form>
</body>
</html>

Thanks

Edited July 18, 2017 by maicol07

Krydos · July 18, 2017

$sql = "show tables like '$username_%";
                         ^           ^

You're missing a single quote.

Just read the error messages, and figure out why it's displaying that error.

maicol07 · July 18, 2017

Thanks, but there isn't any error... Now I apply that fix and I'll see...

Krydos · July 18, 2017

there isn't any error...

Have you looked in the error_log file that php creates when there's an error?

maicol07 · July 18, 2017

I've looked now... Sincerly I didn't know of error_log file...

[18-Jul-2017 17:51:22 UTC] PHP Fatal error: Call to a member function fetch_assoc() on boolean in /home/maicol07/public_html/apps/app/sld/voti/test_dropdown.php on line 21

maicol07 · July 18, 2017

I tried to edit something, but now it shows 2 blank options... In the error file: [18-Jul-2017 19:47:07 UTC] PHP Warning: session_start(): Cannot send session cache limiter - headers already sent (output started at /home/maicol07/public_html/apps/app/sld/voti/test_dropdown.php:2) in /home/maicol07/public_html/apps/app/sld/voti/includes/config.php on line 3

Edited July 18, 2017 by maicol07

wolstech · July 18, 2017

That error means something else sent output to the client before the session_start(). When output is sent for the first time (e.g. any HTML, an error message, an echo, etc.), the headers are sent along with it. You can't set a session cookie in an HTTP header that's already been sent, so the session_start() fails with that error.

Odds are you either: have whitespace or HTML before the session_start() command, have PHP code that produces output (like echo) before the session_start() command, or have PHP that is generating an error (the error message that prints on the screen is output) before the session_start().

maicol07 · July 19, 2017

Ok solved, I 've moved session_start() from under html to above html. Now the dropdown doesn't show the options, instead it gives a blank option...

EDIT: On XAMPP it works perfectly but not on Tommy...
CODE:

<?php
include ("includes/config.php");
//if not logged in redirect to login page
if(!$user->is_logged_in()){ header('Location: login.php'); } 
$username=$_SESSION['username'];
?>
<html>
<body>
<form method="get">
<?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 '$username%'";
$result = $conn->query($sql) or die($conn->error);

// start dropdown select
echo "<select>";

// for each table in database create an entry
while ($row = $result->fetch_assoc()) {
  $table = $row["Tables_in_members ($username%)"];
  echo "<option value='$table'>$table</option>";
}
echo "</select>";
?> 
<input type="submit" class="btn waves-effect waves-light" value="VISUALIZZA">
</form>
</body>
</html>

Why?
PROBLEM 2: I'm trying to create a table named $username_$subject but it doesn't work... I created before a variable, see below...

$usersub=$username+"_"+$subject;
mysqli_query($connection,"CREATE TABLE $usersub ( `voto` FLOAT NOT NULL , `data` CHAR NOT NULL , `materia` CHAR NOT NULL , `peso` INT NOT NULL , `descrizione` TEXT NOT NULL )")
or die(mysqli_error($connection));

Thanks for your help!

Edited July 19, 2017 by maicol07

Krydos · July 19, 2017

$usersub=$username+"_"+$subject;

The + sign in php is a mathematical operator not a concatenation operator. $username+"_"+$subject = 0 not a string.

maicol07 · July 19, 2017

Ah ok, it's because I'm very familiar with Python and so I use it...

I searched on php.net and I found that the PHP operator for concatenation is . and it works!

Another MySQL-PHP problem: I'm trying to convert to MySQLi the function

mysql_result($result, $i, 'voto')

but in mysqli there isn't any result function... how can I convert this?

Thanks

P.S.: I tried searching on Google but I can't use the code I found for this functions...

Edited July 19, 2017 by maicol07

wolstech · July 19, 2017

Using mysqli_fetch_assoc() instead is probably easiest. You get the whole row as an array instead of one field. The array indexes are the field names, so just look at the voto index of the array. Field names are case sensitive!

The function returns null instead of an array if there are no rows left to get (or if the query returned no matching rows), so if using the output somewhere that an unexpected null will cause an error, you should check that the value returned is_array().

Details at: http://php.net/manual/en/mysqli-result.fetch-assoc.php

In the below, assume $result contains the results of a mysqli_query() call, and that $result is not false (returned when the query fails).

$aData = mysqli_fetch_assoc($result);
//$aData['voto'] contains the value of the voto column for the first row in the result

If your result returns multiple rows, you can call this in a while loop to get each row in turn and do something with it. The below would show the value of the 'voto' field for every row returned by the query:

while ($aData = mysqli_fetch_assoc($result))
{
echo $aData['voto'];
}

maicol07 · July 19, 2017

I don't think it works with my code and my variables, because I want to show a paginated mysql table in php.

This is my code:

<?php
/*
        VIEW-PAGINATED.PHP
        Visualizza tutti i dati della tabella 'players'
        Questa è una versione modificata del file view.php che include l'impaginazione
*/
 
        //connessione al database già effettuata sopra
 
        // numero di risultati da visualizzare per pagina
        $per_page = 10;
 
        // calcola le pagine totali
        $result = mysqli_query($connection, "SELECT * FROM members WHERE TABLE_NAME LIKE $nomeutente");
        $total_results = mysqli_num_rows($result);
        $total_pages = ceil($total_results / $per_page);
 
        // controlla se la variabile 'page' è impostata nell'URL (es: view-paginated.php?page=1)
        if (isset($_GET['page']) && is_numeric($_GET['page']))
        {
                $show_page = $_GET['page'];
 
                // mi assicuro che il valore di $show_page sia valido
                if ($show_page > 0 && $show_page <= $total_pages)
                {
                        $start = ($show_page -1) * $per_page;
                        $end = $start + $per_page;
                }
                else
                {
                        // errore - mostra il primo set di risultati
                        $start = 0;
                        $end = $per_page;
                }
        }
        else
        {
                // se la pagina non è impostata, mostra il primo set di risultati
                $start = 0;
                $end = $per_page;
        }
 
        // visualizza impaginazione
 
        echo "<button class='waves-effect waves-light btn' type='button'><i class='material-icons left'>search</i>Visualizza Tutto</button> | <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>";
        for ($i = 1; $i <= $total_pages; $i++)
        {
                echo "<a href='view-paginated.php?page=$i'>$i</a> ";
        }
        echo "</p>";
 
        // visualizza i dati in tabella
        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>";
 
        // loop tra i risultati della query del database, visualizzandoli in tabella
        for ($i = $start; $i < $end; $i++)
        {
                // mi assicuro che PHP non cerchi di mostrare risultati che non esistono
                if ($i == $total_results) { break; }
 
                // emissione del contenuto di ogni riga in una tabella
                echo "<tr>";
                echo '<td>' . mysql_result($result, $i, 'voto') . '</td>';
                echo '<td>' . mysql_result($result, $i, 'data') . '</td>';
                echo '<td>' . mysql_result($result, $i, 'materia') . '</td>';
				echo '<td>' . mysql_result($result, $i, 'peso') . '</td>';
				echo '<td>' . mysql_result($result, $i, '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>";
        }
        // chiude la tabella
        echo "</table>";
 
        // impaginazione
 
?>

Thanks

Also, if you have time, I ask you why I get this error:

Parse error: syntax error, unexpected end of file in C:\xampp\htdocs\sitesld\view.php on line 141

In this 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 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" action="">
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 name='materia' value='$mat_sel'>";
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
// identifica se è stata selezionata una materia o no
if (isset($_GET['materia'])) {
	$allmaterie=False;
	if ($_GET['materia']=='allmaterie') {
		$allmaterie=True;
	}
}
else {
	$allmaterie=True;
}
// visualizza i dati in tabella
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?page=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 "<tr> <th>Voto</th> <th>Data</th> <th>Materia</th> <th>Peso</th> <th>Descrizione</th></tr>";
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 tra i risultati della query del database, visualizzandoli in tabella
while($row = mysqli_fetch_array( $result )) {
 
// emissione del contenuto di ogni riga in una tabella
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'] . '><i class="material-icons left">delete</i>Elimina</a>';
echo "</tr>";
}
 
// chiude la tabella
echo "</table>";
	}
}
else {
	$materia=$_GET['materia'];
	$result = mysqli_query($connection, "SELECT * FROM $materia")
	or die(mysqli_error($connection));
// loop tra i risultati della query del database, visualizzandoli in tabella
while($row = mysqli_fetch_array( $result )) {
 
// emissione del contenuto di ogni riga in una tabella
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'] . '><i class="material-icons left">delete</i>Elimina</a>';
echo "</tr>";
}
 
// chiude la tabella
echo "</table>";
}

?>
</div>
</body>
</html>

Thanks

Krydos · July 19, 2017

Unexpected end of file usually means you missed closing something, like ", ', or } or something.

maicol07 · July 20, 2017

Got it! Thanks, how can I use the mysql_result function converted for my code?

Thanks

wolstech · July 20, 2017

I usually do it in the SQL query with a LIMIT statement so you only retrieve certain rows at a time.

SELECT * FROM table LIMIT $start,$end

$start and $end are numbers of the first and last row to return. Your "next page" link would increment the $start variable. $end is usually something like ($start + 25) or however many rows you want shown at a time. The first row of the table is 0.

Some example code, not tested but you should get the idea:

<?php
//open a DB connection called $conn up here

$iRows = 25; //Show 25 rows at a time
$iStart = mysqli_real_escape_string($conn,$_GET['start']); 
$iEnd = $iStart + $iRows; 

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

while ($aRow = mysqli_fetch_assoc($rData)) 
{ 
//echo out your table rows in here
} 

echo "<a href=\"".$_SERVER['PHP_SELF']."?start=$iStart-$iRows\">Previous Page</a>"; 
echo "<a href=\"".$_SERVER['PHP_SELF']."?start=$iStart+$iRows\">Next Page</a>";

 ?>

Edited July 20, 2017 by wolstech
Syntax...

Sign In

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

Recommended Posts

maicol07

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

Krydos

maicol07

Krydos

maicol07

maicol07

wolstech

maicol07

Krydos

maicol07

wolstech

maicol07

Krydos

maicol07

wolstech

Create an account or sign in to comment

Create an account

Sign in

HelioHost

Donate

Forums

Activity