listbox to echo data

[dudleylearning]

Hi All,

I have redeveloped my code, but I have come to a problem in getting the listbox to echo the data retrieved from the database.

 

I have two tables which hold the data:

TABLE joke (id, joke_text, joke_date, author_id)
TABLE author(id, name, email)

This is what I have attempted so far:

<?php 
# display all php errors
error_reporting(-1);
ini_set('display_errors', 1);

# include dbConnection details
require '../includes/dbconn.php';

	# initially set $id to empty
	$id = null;
	
	# if $id is not empty, GET the id
	if ( !empty($_GET['id'])) {
		$id = $_REQUEST['id'];
	}
	
	# if $id is empty then send the user back to index.php
	if ( null==$id ) {
		header("Location: index.php");
		exit();
	}
	
	if ( !empty($_POST)) {
		// keep track validation errors
		$joke_textError = null;
		$authorError = null;
		
		// keep track post values
		$joke_text = $_POST['joke_text'];
		$author_id = $_POST['author_id'];
		
		// validate input
		$valid = true;
		if (empty($joke_text)) {
			$joke_textError = 'Please enter joke text';
			$valid = false;
		}
		
		
		// update data
		if ($valid) {
			$sql = "UPDATE joke set joke_text = ?, author_id = ? WHERE id = ?";
			$update = $dbConnection->prepare($sql);
			$update->execute(array($joke_text,$author_id,$id));
			header("Location: index.php");
			exit();
		}
	} else {
		$sql = "SELECT id, joke_text, joke_date, author_id
				FROM joke  
				WHERE id = ?";
		$select = $dbConnection->prepare($sql);
		$select->execute(array($id));
		$data = $select->fetch();
		$joke_id = $data['id'];
		$joke_text = $data['joke_text'];
		$joke_date = $data['joke_date'];
			}
?>

[display of joke_text div]

<select name="author_id" id="author_id">
<option value="">Select one</option>
<?php 
	$sql2 = 'SELECT id, name FROM author';
	foreach ($dbConnection->query($sql2) as $data2) { ?>
	<option value="<?php echo $data2['id']; ?>" 
		<?php if(isset($_POST['author_id']) && 
		($_POST['author_id'] == $data['author_id'])) 
		{ echo 'selected'; } ?>>
		<?php echo $data2['name']; ?>
	</option>
	<?php } ?>
</select>

When I use "inspect" from Chrome, it populates the listbox with all the authors in HTML, but can't figure how to retrieve the current author. Any suggestions? 

Edited January 3, 2017 by dudleylearning

[dudleylearning]

nevermind, I figured it out

 <select name="author_id" id="author_id">
 <option value="">Select one</option>
<?php 
$sql2 = 'SELECT id, name FROM author';
foreach ($dbConnection->query($sql2) as $data2) { ?>
<option value="<?php echo $data2['id']; ?>" 
<?php if($data['author_id'] == $data2['id']) 
{ echo 'selected'; } ?>>
<?php echo $data2['name']; ?>
 </option>
<?php } ?>
</select>

Edited January 3, 2017 by dudleylearning

[Barand]

I like to use a function in cases like this. Example

$current_author = isset($_POST['author_id']) ? $_POST['author_id'] : '';

function authorOptions($db, $author)
{
    $sql = "SELECT id, name
            FROM author
            ORDER BY name";
    $options = "<option value=''>Select one</option>\n";
    foreach ($db->query($sql) as $data) {
        $sel = $data['id']==$author ? 'selected' : '';
        $options .= "<option value='{$data['id']}' $sel>{$data['name']}</option>\n";
    }
    return $options;
}

Then

    <select name="author_id" id="author_id">
        <?= authorOptions($db, $current_author)?>
    </select>

Edited January 3, 2017 by Barand