dudleylearning

Hi, I am in the process of creating a search on my website. It has two search criteria (joke_text as a text field and author as a list box). When I try to search using just the joke_text field it presents no data. But when I add data to both joke_text and author the search presents the correct data. It also works when I select just an author. Here is a snippet of the code that I have used: <div> <form action="search.php" method="POST"> <div class="control-group"> <label class="control-label">Joke</label> <div class="controls"> <input type="text" name="text" id="text"> </div> </div> <div class="control-group"> <label class="control-label">Author</label> <div class="controls"> <label for="author">author</label> <select name="author" id="author"> <option>select</option> <option value="1">1</option> <option value="2">2</option> <option value="3">3</option> </select> </div> </div> <button type="submit" class="btn btn-orange">Go</button> </form> </div> <?php # retrieve text from search field if (!empty($_GET['text']) || !empty($_POST)) { $joke_text_search = $_REQUEST['text']; $joke_text_search_filter = str_replace(array('%', '_'),'\\',$joke_text_search); $joke_text = '%' . $joke_text_search_filter . '%'; $author_select = $_REQUEST['author']; $author = '%' . $author_select . '%'; $search = $dbConnection->prepare("SELECT * FROM joke WHERE author_id LIKE ? AND joke_text LIKE ?"); $search->execute([$author, $joke_text]); $result = $search->fetchAll(); Could someone take a look and give me some guidance to what I should be looking at

that worked like a charm, exactly what I wanted. thanks Jacques1.

ok, didn't know that. thanks for the tip. I tried it in another commercial website and it gave all the data

Hi, so I've had an attempt at creating a search form I can get data from it so that's a positive. This is the relevant snippet: $joke_text_search = $_REQUEST['text']; $joke_text = '%' . $joke_text_search . '%'; $search = $dbConnection->prepare("SELECT * FROM joke WHERE joke_text LIKE ?"); $search->execute([$joke_text]); $result = $search->fetchAll(); but if I type in % it shows all the data. Having a read of the PHP manual, seems that I should use str_replace. I have had an attempt with it but the % symbol still shows all the data: $joke_text_search = $_REQUEST['text']; $joke_text_search_filter = str_replace(array('%','_'),'',$joke_text_search); $joke_text = '%' . $joke_text_search_filter . '%'; $search = $dbConnection->prepare("SELECT * FROM joke WHERE joke_text LIKE ?"); $search->execute([$joke_text]); $result = $search->fetchAll(); Could someone lend a hand?

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>

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?

Hi All, which is the better way to do the database connection: <?php # display all php errors error_reporting(-1); ini_set('display_errors', 1); try { $dbConnection = new PDO('mysql: host=localhost; dbname=jokesdb', 'root', 'password'); $dbConnection -> setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $dbConnection -> exec('SET NAMES "utf8"'); } catch (PDOException $e) { $status = 'Unable to connect to the database server.' . '<br />' . $e -> getMessage(); include 'status.php'; exit(); } $status = 'Database connection established'; include 'status.php'; ?> or is it better to use: <?php # display all php errors error_reporting(-1); ini_set('display_errors', 1); $host = 'localhost'; $database = 'db'; $username = 'root'; $password = 'password'; $charset = 'utf8'; $option = array( PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC ); $dataSource = 'mysql:host=' . $host . ';dbname=' . $database . ';charset=' . $charset; $dbConnection = new PDO($dataSource, $username, $password, $option); ?> Thoughts?

thanks for the tips. I got it working

Yes it does have data when I inspect it. When I manually change: $query->bindValue(':id', $_POST['id']); to $query->bindValue(':id', 1); I can update record 1 without a problem.

ok, forgot to post that one. There is a link for the user to click on from index.php which then opens the edit page: <form action="edit_author.php" method="post"> <input type="hidden" name="id" value="<?php echo $data['id']; ?>"> <input type="submit" value="Edit"> </form>

Hi All, I'm having a go at making an edit page and have come across a slight problem. I get this error on the edit page when the submit button is pressed: Notice: Undefined index: id this is the script that I have attempted: <?php # display all php errors error_reporting(-1); ini_set('display_errors', 1); # include dbConnection details include '../includes/dbconn.php'; # set form input fields $sql = 'SELECT id, name, email FROM author WHERE id = :id'; $query = $dbConnection->prepare($sql); $query->bindValue(':id', $_POST['id']); $query->execute(); $row = $query->fetch(); $name = $row['name']; $email = $row['email']; $id = $row['id']; # if the form has been posted if (isset($_GET['update_details'])) { $sql = 'UPDATE author SET name = :name, email = :email WHERE id = :id'; $query = $dbConnection->prepare($sql); $query->bindValue(':id', $id); $query->bindValue(':name', $_POST['name']); $query->bindValue(':email', $_POST['email']); $query->execute(); $message = 'Author details successfully updated'; header('Location: index.php?message=' . $message); } ?> the error makes reference to this line: $query->bindValue(':id', $_POST['id']); I can't see where I have gone wrong with it. Any tips on what I should be looking at?

thanks for the tips. I did it through a URL parameter

I was looking at using $_POST if possible, but couldn't figure it out from my trial.

Hi All, couldn't seem to figure how to pass data from page 2 to page 1. On page 2 I have this code block: # if the form has been posted if ($_SERVER["REQUEST_METHOD"] == "POST") { # check for empty fields if (!empty(trim(($_POST['name'] == '')))) { $nameErr = "<span class=\"error\">Name is required</span>"; } if (!empty(trim(($_POST['email'] == '')))) { $emailErr = "<span class=\"error\">Email is required</span>"; } # if all is fine, then add data to the database else { $sql = 'INSERT INTO author SET name = :name, email = :email'; $query = $dbConnection -> prepare($sql); $query -> bindValue(':name', $_POST["name"]); $query -> bindValue(':email', $_POST["email"]); $query -> execute(); $message = 'done'; header('Location: index.php'); } } The variable $message I wanted to pass to page 1, where I have this in: <?php if(isset($_POST['message'])) { echo $_POST['message']; } ?> I can't seem to figure out the problem? Any tips? Thanks

thanks requinix, I liked how you gave me hints to the solution rather than just the solution