I am trying to enter an author's name (or part) into a form then list entries from the table which match.
To reduce typing I want to allow entry of only 2 or 3 letters and to select ALL authors who start with those letters.
If I alter the mysql query to enter it via phpmyadmin, it works but not when run via php. Possibly the problem is lack of quotation marks?
If anybody can put me straight, I'd be grateful. I've copied the actual html/php from both parts, rather than edit and perhaps lose the faulty contruction in the process.
using php 5.2.4, mysql 5.0.45 and xampp version 1.6.4 (all on a local machine.
In the second part, I've commented out most of the lines and displayed the results of each entry, trying to identify the problem.
If I enter for (trying for forester), I get the following from the second part:
You said: for
You said: SELECT author, authorno FROM authors where substring(author,1,char_length(for)) = for
Error in query: SELECT author, authorno FROM authors where substring(author,1,char_length(for)) = for
first part:
<html>
<head></head>
<body>
<form action="get_author.php" method="post">
Enter Author's name (or first part): <input type="text" name="author" size="30">
<input type="submit" value="Send">
</form>
<?php
include 'config.php';
include 'opendb.php';
// ... do something like insert or select, etc
echo '<h2><i>config and open - now going to close</i></h2>';
//
include 'closedb.php';
echo '<h2><i>finished close</i></h2>';
?>
</body>
</html>
second part:
<?php
// open connection
$conn=mysql_connect("dyall.drummond.home", "library", "library");
// pick database
mysql_select_db("library", $conn);
// retrieve form data in a variable
$input=$_POST['author'];
//print it
echo "You said: <i>$input</i><br>";
// $query = "SELECT author, authorno FROM authors where substring(author,1,char_length('for')) = $input";
$query = "SELECT author, authorno FROM authors where substring(author,1,char_length($input)) = $input";
echo "You said: <i>$query</i><br>";
// $result = mysql_query("SELECT author, authorno FROM authors where substring(author,1,char_length($input)) = $input") or die ("Error in query: $query ");
$result = mysql_query($query, $conn) or die ("Error in query: $query ");
echo "rows = mysql_num_rows($result)";
// if (mysql_num_rows($result) > 0)
// while($row = mysql_fetch_row($result))
// {
// echo "author :{$row["author"]} " ,
// "author no : {$row["authorno"]} <br>";
// }
// rtrim($input)
mysql_close($conn);
?>
