johnm Posted December 19, 2007 Share Posted December 19, 2007 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); ?> Quote Link to comment Share on other sites More sharing options...
fenway Posted December 19, 2007 Share Posted December 19, 2007 Your column name is for? That's a reserved keyword -- you'll need to wrap it in backticks (which phpmyadmin does for you), or better yet, change it. Quote Link to comment Share on other sites More sharing options...
johnm Posted December 19, 2007 Author Share Posted December 19, 2007 No, just happened to be the characters I typed in to obtain an author. I have tried other character combinations with same result. Quote Link to comment Share on other sites More sharing options...
fenway Posted December 19, 2007 Share Posted December 19, 2007 Sorry... then you need '$input'. Quote Link to comment Share on other sites More sharing options...
johnm Posted December 30, 2007 Author Share Posted December 30, 2007 I changed the query as follows: $query = "SELECT author, authorno FROM authors where substring(author,1,char_length($input$)) = $input$"; and entered 'for' as author in form. Results as follows: 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$ apparently same error? I have not been able to reply earlier as my connection to the net was unavailable. Quote Link to comment Share on other sites More sharing options...
fenway Posted December 31, 2007 Share Posted December 31, 2007 Where are the quotes? With '$input', you should still see them. Quote Link to comment Share on other sites More sharing options...
johnm Posted January 2, 2008 Author Share Posted January 2, 2008 Sorry, I don't understand which quotes you mean I typed it exactly like this: $query = "SELECT author, authorno FROM authors where substring(author,1,char_length($input$)) = $input$"; i.e I am trying to strip the author's name in the database to the same length as the typed input. That is if I type four letters, I compare the four letters with the first four letters of each table entry to retrieve matching entries so that if I type 'for'. it would select forester, forrester and forest for example. It would then return all matches for me to select the one I wanted. Quote Link to comment Share on other sites More sharing options...
fenway Posted January 2, 2008 Share Posted January 2, 2008 $query = "SELECT author, authorno FROM authors where substring(author,1,char_length('$input')) = '$input'"; I don't know where the last $ came from. Quote Link to comment Share on other sites More sharing options...
johnm Posted January 2, 2008 Author Share Posted January 2, 2008 cracked it -- thanks for your help all I need to do now is get the list of selected items to display. I can call this one 'solved'! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.