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); ?> Link to comment https://forums.phpfreaks.com/topic/82322-solved-php-and-mysql-query-form-ok-query-fails/ 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. Link to comment https://forums.phpfreaks.com/topic/82322-solved-php-and-mysql-query-form-ok-query-fails/#findComment-418957 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. Link to comment https://forums.phpfreaks.com/topic/82322-solved-php-and-mysql-query-form-ok-query-fails/#findComment-419009 Share on other sites More sharing options...
fenway Posted December 19, 2007 Share Posted December 19, 2007 Sorry... then you need '$input'. Link to comment https://forums.phpfreaks.com/topic/82322-solved-php-and-mysql-query-form-ok-query-fails/#findComment-419034 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. Link to comment https://forums.phpfreaks.com/topic/82322-solved-php-and-mysql-query-form-ok-query-fails/#findComment-425958 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. Link to comment https://forums.phpfreaks.com/topic/82322-solved-php-and-mysql-query-form-ok-query-fails/#findComment-426920 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. Link to comment https://forums.phpfreaks.com/topic/82322-solved-php-and-mysql-query-form-ok-query-fails/#findComment-428384 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. Link to comment https://forums.phpfreaks.com/topic/82322-solved-php-and-mysql-query-form-ok-query-fails/#findComment-428393 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'! Link to comment https://forums.phpfreaks.com/topic/82322-solved-php-and-mysql-query-form-ok-query-fails/#findComment-428489 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.