Help with Joke Database (implementing joke visibility) php &mysql book 5

Hello
I’m working on the book php & mysql novice to ninja edition 5. What i’m trying to do is add the visibility to my CMS. I have added to the database fine can search for jokes that are tagged ‘NO’ and display them in the admin area. But when it comes to updating the column to give them a ‘YES’ instead i can’t get it to update the database.
This is the code from my admin/jokes/index.php

     try
        {
    $sql = 'UPDATE joke SET visible = :visible WHERE id = :id';
    $s = $pdo->prepare($sql);
    $s->bindValue(':id', $_POST['id']);
    $s->bindValue(':visible', $visible);
    $s->execute();
  }
  catch (PDOException $e)
  {
    $output = 'Error updating visibility.' . $e->getMessage();
    include '../output.html.php';
    exit();
  }

and this is the code from form.html.php

          <div>
		<label for="visible">visibility:</label>
		<select name="visible" id="visible">
			<option value="">Any visibility</option>
			<option value="yes">Yes</option>
			<option value="no">No</option>
		</select>
	  </div>

I can get it to remove the ‘NO’ from the database but not update it.
Thank you for your help.

Where do you get the value for $visible from?

Not sure what you mean by that. If it is removing the “No” from the database, then it is updating it.

I forgot to add this this is at the top of my index.php page i’m getting the value of $visible from there it is the name of the column in the table and the name of my select. What i need to do is update the no to a yes so when you use the select and select yes it will update the visible column in the table.

try
  {
    $sql = 'SELECT id, joketext, authorid, visible FROM joke WHERE id = :id';
    $s = $pdo->prepare($sql);
    $s->bindValue(':id', $_POST['id']);
    $s->execute();
  }
  catch (PDOException $e)
  {
    $output = 'Error fetching joke details.';
    include '../output.html.php';
    exit();
  }
  $row = $s->fetch();
  $pageTitle = 'Edit Joke';
  $action = 'editform';
  $text = $row['joketext'];
  $authorid = $row['authorid'];
  $visible = $row['visible'];
  $id = $row['id'];
  $button = 'Update joke';

I hope i have made it a bit easier to understand
Thank You

(I haven’t seen the book, so some of this is a guess)

In your index.php you display the joke, and to do that you retrieve it from the database, populate those variables, then echo it all out. Presumably then you have a HTML form, which then sends the id to the PHP code you posted in your first post to change the visible flag. That’s a common way to do it. But, all the variables that you created in index.php cease to exist as soon as that script stops executing*, which is generally just at the point that your browser displays the page. So if you want to pass stuff through to the script that does the update, you have to send them in the form as you seem to be doing with the id value.

In this case, another approach would be to retrieve the current value of visible and change it to whatever it currently isn’t. That might not work for your scenario, and if the only thing that your second PHP script does is to make a joke as “not visible”, then there’s no point doing either, you could just hard-code it to set the value of $visible to be “no” prior to using it in the query.

( * Even if your “update” code is in the beginning of index.php and only executes if it sees that it has been called from your form, the variables you used to display the form in the first place aren’t there any more because the script finished.)

ok thank you well when the visitor add a joke they are set to ‘NO’ so they don’t show on the index page straight away. So i need to be able to update the visible to ‘yes’ to display them. I understand what you are saying about them only lasting for the time the script is run. Yes i had the the visible like this $s->bindValue(':visible', $_POST['visible'}); but it just removed the ‘NO’ and left the column blank that’s why i went back to the variable.

Thank you

Can you show the html form that the update is called from?

<h1><?php htmlout($pageTitle); ?></h1>
	<form action="?<?php htmlout($action); ?>" method="post">
		<div>
			<label for="text">Type your joke here:</label>
			<textarea id="text" name="text" rows="3" cols="40"><?php htmlout($text); ?></textarea>
		</div>
		<div>
			<label for="author">Author:</label>
			<select name="author" id="author">
				<option value="">Select one</option>
				<?php foreach ($authors as $author): ?>
					<option value="<?php htmlout($author['id']); ?>"<?php if ($author['id'] == $authorid){ echo ' selected'; } ?>>
						<?php htmlout($author['name']); ?>
					</option>
				<?php endforeach; ?>
			</select>
		</div>
		<fieldset>
		<legend>Categories:</legend>
		<?php foreach ($categories as $category): ?>
			<div>
				<label for="category<?php htmlout($category['id']); ?>"><input type="checkbox" name="categories[]" id="category<?php htmlout($category['id']); ?>" value="<?php htmlout($category['id']); ?>"<?php if ($category['selected']) {
					echo ' checked'; } ?>><?php htmlout($category['name']); ?></label>
			</div>
		<?php endforeach; ?>
	</fieldset>
	<div>
		<label for="visible">visibility:</label>
		<select name="visible" id="visible">
			<option value="">Any visibility</option>
			<option value="<?php htmlout($joke['visible']); ?>">Yes</option>
			<option value="no">No</option>
		</select>
	</div>
	<div>
		<input type="hidden" name="id" value="<?php htmlout($id); ?>">
		<input type="submit" value="<?php htmlout($button); ?>">
	</div>
	</form>
type or paste code here

This actually looks like quite a deviation from the book.

Though I don’t discourage anyone from experimenting and trying things out for themselves, you seem to be missing some of the points taught.
Why rewrite all this code for preparing, binding and executing instead of reusing all the database methods created for these purposes?

Back on topic.

Surely $visible should be coming from $_POST['visible'] in your form result.
Taking it from what is already in the database is not going to change anything.

Edit ^ Started typing after post #4.

This bit

<label for="visible">visibility:</label>
		<select name="visible" id="visible">
			<option value="">Any visibility</option>
			<option value="<?php htmlout($joke['visible']); ?>">Yes</option>
			<option value="no">No</option>
		</select>

seems a bit strange. If you say that they default to being stored as “no”, then it suggests that when you first draw this form, the value for the first option will be “no”, which would mean you could never change it to “yes”. If it’s going to have any interaction with the current value for that field, it would normally be to decide which option should have "selected against it.

Either should be OK, as long as the first has the value that it needs, or the second is assigned from the $_POST variable as it should be.

1 Like

Ok that’s the problem i’m not getting the value from the form page to the control script i did the same thing with the form on the search page and it works fine. I just dont know why it’s not getting the value from the form page.

Thank you

If you bring up a joke on that form you posted the code to, right-click the browser and “view source code”, what value does it have in for the options on the “visible” field?

Can you show the code from the query to retrieve that data, and how the result is transferred into the variables you used to draw the form? You use $text for the joke text, for example, which is presumably extracted from the query results array.

ETA - if it’s the code from post #3, you seem to be using an array called $row there, but use an array called $joke in the code that draws the form.

i have hard coded the select with ‘YES’ and ‘NO’ i meant to take that out sorry when i check the page source it say value=‘yes’. I know i have missed something somewhere but this is the only $sql statement i can’t get to work
this is the page source

<!DOCTYPE html>
<html lang="en">
<head>
	<meta charset="utf-8">
	<title>Edit Joke</title>
	<style type="text/css">
		textarea {
			display: block;
			width: 100%;
		}
	</style>
</head>
<body>
<h1>Edit Joke</h1>
	<form action="?editform" method="post">
		<div>
			<label for="text">Type your joke here:</label>
			<textarea id="text" name="text" rows="3" cols="40">You are like dandruff because I just cannot get you out of my head no matter how hard I try.
mark@mark.com
[Hello](mysite.com)</textarea>
		</div>
		<div>
			<label for="author">Author:</label>
			<select name="author" id="author">
				<option value="">Select one</option>
									<option value="7" selected>
						storme jones					</option>
									<option value="9">
						peter parker					</option>
							</select>
		</div>
		<fieldset>
		<legend>Categories:</legend>
					<div>
				<label for="category1"><input type="checkbox" name="categories[]" id="category1" value="1" checked>Cross Roads</label>
			</div>
					<div>
				<label for="category2"><input type="checkbox" name="categories[]" id="category2" value="2">Knock knock</label>
			</div>
					<div>
				<label for="category3"><input type="checkbox" name="categories[]" id="category3" value="3" checked>Lawyers</label>
			</div>
					<div>
				<label for="category4"><input type="checkbox" name="categories[]" id="category4" value="4">Walk the Bar</label>
			</div>
			</fieldset>
	<div>
		<label for="visible">visibility:</label>
		<select name="visible" id="visible">
			<option value="">Any visibility</option>
			<option value="yes">Yes</option>
			<option value="no">No</option>
		</select>
	</div>
	<div>
		<input type="hidden" name="id" value="15">
		<input type="submit" value="Update joke">
	</div>
	</form>
</body>
</html>

this is all the code inside the button click edit

if (isset($_POST['action']) and $_POST['action'] == 'Edit')
{
  include $_SERVER['DOCUMENT_ROOT'] . '/include/db.inc.php';
  try
  {
    $sql = 'SELECT id, joketext, authorid, visible FROM joke WHERE id = :id';
    $s = $pdo->prepare($sql);
    $s->bindValue(':id', $_POST['id']);
    $s->execute();
  }
  catch (PDOException $e)
  {
    $output = 'Error fetching joke details.';
    include '../output.html.php';
    exit();
  }
  $row = $s->fetch();
  $pageTitle = 'Edit Joke';
  $action = 'editform';
  $text = $row['joketext'];
  $authorid = $row['authorid'];
  $visible = $row['visible'];
  $id = $row['id'];
  $button = 'Update joke';
  // Build the list of authors
  try
  {
    $result = $pdo->query('SELECT id, name FROM author');
  }
  catch (PDOException $e)
  {
    $output = 'Error fetching list of authors.';
    include '../output.html.php';
    exit();
  }
  foreach ($result as $row)
  {
    $authors[] = array('id' => $row['id'], 'name' => $row['name']);
  }
  // Get list of categories containing this joke
  try
  {
    $sql = 'SELECT categoryid FROM jokecategroy WHERE jokeid = :id';
    $s = $pdo->prepare($sql);
    $s->bindValue(':id', $id);
    $s->execute();
  }
  catch (PDOException $e)
  {
    $output = 'Error fetching list of selected categories.';
    include '../output.html.php';
    exit();
  }
  foreach ($s as $row)
  {
    $selectedCategories[] = $row['categoryid'];
  }
  // Build the list of all categories
  try
  {
    $result = $pdo->query('SELECT id, name FROM category');
  }
  catch (PDOException $e)
  {
    $output = 'Error fetching list of categories.';
    include '../output.html.php';
    exit();
  }
  foreach ($result as $row)
  {
    $categories[] = array(
        'id' => $row['id'],
        'name' => $row['name'],
        'selected' => in_array($row['id'], $selectedCategories));
  }

//update visibility to YES or NO
  try
  {
    $sql = 'UPDATE joke SET visible = :visible WHERE id = :id';
    $s = $pdo->prepare($sql);
    $s->bindValue(':id', $_POST['id']);
    $s->bindValue(':visible', $visible);
    $s->execute();
  }
  catch (PDOException $e)
  {
    $output = 'Error updating visibility.' . $e->getMessage();
    include '../output.html.php';
    exit();
  }


  include 'form.html.php';
  exit();
}

I don’t understand that PHP code. Why do you run all those SELECT queries if the only thing you do at the end is change the ‘visible’ flag?

In the code at the end, you use the $visible variable, which you have retrieved from the SELECT query at the start of the code. You’re not using the value from the form, so it surely won’t change regardless of what you select?

Why is there a third empty value? Surely “visible” is either “yes” or “no”? What’s the difference between “yes” and “” in terms of whether the joke is visible or not? You’d be better off using a Boolean value for that column and setting it to true or false.

Presumably something somewhere else is setting $_POST['action'] as I don’t see that in your form.

1 Like

Yes the part of the script runs a few different processes not just the update yes i did think about using a Boolean for it.
The third value=‘’ " is just a heading in the select it does nothing at all just say Any visibility that’s all. So if i was to use the value from the select which would be the best way to do that.

Thanks for your help guy’s

Exactly the same way as you use the “id” field from the select.

Yes, it just doesn’t make any sense, visibility is either one or the other. Having that selection means someone can choose it, and blank the value in your database, once you update the code to use it.

Yes, but if its only job is to store the edited joke, all the rest isn’t necessary at this point. If you’re allowing users to edit the joke, you need to get the new value for the joke text as well, and the other fields.

You could add the selected attribute to whichever value was currently set for the joke.

BTW, I see this is version 5 of the book, I did not see that one. Earlier I assumed it to be the most recent version 6. Not sure how different they are.

Ok cool i’ll try that yes there is a lot of difference in the way he does things in the books i have both 5 and 6 but i have to use 5 for the moment s the use’s i’m doing uses it.

the script runs a few different action this is in the admin area so the visitor can only add jokes to the database that’s it.

Thanks again for all your help

Yes, but surely it should have some code to decide which of those different actions it will run? Your script is just running all of them, one after the other.

ok well that’s how it’s done in the book

Fair enough, as I said earlier I haven’t seen the book, just seems a bit weird.