Jump to content

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


Recommended Posts

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 by maicol07
Link to comment
Share on other sites

  • Replies 34
  • Created
  • Last Reply

Top Posters In This Topic

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

Link to comment
Share on other sites

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 by maicol07
Link to comment
Share on other sites

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().

Link to comment
Share on other sites

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 by maicol07
Link to comment
Share on other sites

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 by maicol07
Link to comment
Share on other sites

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'];
}
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by wolstech
Syntax...
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...