Jump to content

dave1950

Members
  • Posts

    16
  • Joined

  • Last visited

dave1950's Achievements

Newbie

Newbie (1/5)

0

Reputation

  1. Yes, that solved it. Thanks so much.
  2. I have a MySql database (Server Version 5.6.12) with a fulltext index on the SectionText field in the FD table with this structure: CREATE TABLE `FD` ( `FTS_DOC_ID` bigint(20) unsigned NOT NULL, `ReadingNo` int(11) DEFAULT NULL, `SequenceNo` int(11) DEFAULT NULL, `SectionTypeID` int(11) DEFAULT NULL, `SectionText` longtext, `FullDocNo` varchar(11) CHARACTER SET utf8 NOT NULL, `DocType` varchar(11) CHARACTER SET utf8 DEFAULT NULL, UNIQUE KEY `FTS_DOC_ID_INDEX` (`FTS_DOC_ID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; I am able to do a very accurate fulltext proximity search (92 documents found) in PHPMyAdmin using this syntax: SELECT SectionText FROM fd WHERE DocType = "text" AND MATCH (SectionText) AGAINST ('"liver hepatic" @3' IN BOOLEAN MODE) ORDER BY FTS_DOC_ID But when I try to use this query on a webpage I get this error message: Parse error: syntax error, unexpected 'liver' (T_STRING) in C:\wamp\www\ecr\proximity.php on line 77 When I remove the quotes like this: MATCH (SectionText) AGAINST ('liver hepatic @3' IN BOOLEAN MODE) There is no error, but there are no documents found. Realistically, to do searches on the web, I need to use search variables provided via an input form (and preferably a parameterized query) such as: MATCH (SectionText) AGAINST (':search1 :search2 @:proximity' IN BOOLEAN MODE) But this also returns no documents found. I need help getting this implemented on a web page. If this needs to be posted in another forum (PHP), please advise. But since the proximity search feature seems to be relatively new and somewhat obscure (couldn’t find anything about implementing it on a webpage via Google), I thought it best to start here. Any help in solving this is greatly appreciated.
  3. Very informative. As noted, a lot to learn. I really appreciate your additional (and very explicit) input on this important issue.
  4. Thanks for the explanation and warning.
  5. Thanks for the abundance of help you have provided. To my initial question, the answer was simple and direct and fixed the problem. Specifically with regard to points 1 and 2, I will focus on avoiding repetitions in my code and using variables in my queries based on your equivalent code, which is very instructive. 3. Yes the $order_results comes from user supplied values, but it is a forced choice (order by document number or date) in a dropdown menu, so I don't think it presents a risk for sql injection. The data should be valid with regard to expected values since it works. 4 & 5: Yes the database that I inherited is messy and I am working on cleaning up both those issues (and several others). A lot to learn here.
  6. Thanks for laying out my options so clearly. I have assigned a value of "all" in the form for Select All Documents and am attempting the first option via if/else. The correct doctype shows up in the url from GET and I am using the same $doctype variable as before: $doctype = isset($_GET['doctype']) ? $_GET['doctype'] : ''; Then my if/else is this: if ($doctype = 'all') { $dbh = new PDO("mysql:host=$dbhost; dbname=$dbname", $dbuser, $dbpass); $stmt = $dbh->prepare("SELECT Full_Documents.FullDocumentID, Full_Documents.DocType, CONCAT(Full_Documents.ReadingNo, '-', Full_Documents.SequenceNo) AS full_doc_number, CONCAT(Readings.Month, '/', Readings.Day, '/', Readings.Year) AS date, CONCAT(Readings.Year, '-', Readings.Month, '-', Readings.Day) AS DateOrder FROM Full_Documents, Readings WHERE Full_Documents.ReadingNo = Readings.ReadingNo AND Full_Documents.SequenceNo = Readings.SequenceNo AND Full_Documents.SectionText LIKE ? ORDER BY $order_results "); $stmt->execute(array("%$search%")); $num_records = $stmt->rowCount(); if($num_records > 0) $links = array();{ while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) $links[] = $row; } } else { $dbh = new PDO("mysql:host=$dbhost; dbname=$dbname", $dbuser, $dbpass); $stmt = $dbh->prepare("SELECT Full_Documents.FullDocumentID, Full_Documents.DocType, CONCAT(Full_Documents.ReadingNo, '-', Full_Documents.SequenceNo) AS full_doc_number, CONCAT(Readings.Month, '/', Readings.Day, '/', Readings.Year) AS date, CONCAT(Readings.Year, '-', Readings.Month, '-', Readings.Day) AS DateOrder FROM Full_Documents, Readings WHERE Full_Documents.ReadingNo = Readings.ReadingNo AND Full_Documents.SequenceNo = Readings.SequenceNo AND Full_Documents.SectionText LIKE ? AND Full_Documents.SectionTypeID = ? ORDER BY $order_results "); $stmt->execute(array("%$search%", $doctype)); $num_records = $stmt->rowCount(); if($num_records > 0) $links = array();{ while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) $links[] = $row; } } The code runs without error but the search results always reflect the "all" value, even when I have selected a different different doctype in the form and the URL reflects the correct doctype. Therefore I am assuming that I have a mistake in my if/else, but cannot find anything wrong there. I am php newbie, so I just want to make sure I am not missing anything obvious. 1. Essentially, is this the approach that you described in your first option? 2. Do you see any problems with the way the if/else is coded? Thanks again for your assistance.
  7. I have a form for a search application that allows a user to select a document type. There are four document types with values based on the integer value in a mysql db table: <form method="get" action="" id="search_criteria"> <select name="doctype"> <option value="1">Text</option> <option value="3">Background</option> <option value="2">Reports</option> <option value="4">Index</option> </select></br><br> <input type="submit" name="submit" value="Submit"> </form> This works fine but only allows for choosing one document type at a time. I have added the attribute “multiple” which does make the list entirely visible with the option of choosing more than one doctype, but when I run the application, it only displays one of the doctypes – the last one. For example, if I choose all four, only results with the Index doctype are displayed. Ideally, I would like for the user to be able to choose “Select All Documents” by default and get search results for all four types. The form would look like this, but I don’t know what the value for “Select All Documents” should be: <form method="get" action="" id="search_criteria"> <select name="doctype"> <option value="?">Select All Documents</option> <option value="1">Text</option> <option value="3">Background</option> <option value="2">Reports</option> <option value="4">Index</option> </select></br><br> <input type="submit" name="submit" value="Submit"> </form> In the php code, the GET value from the form (doctype) is used in the $doctype variable that is part of the WHERE clause in my sql query. $doctype = isset($_GET['doctype']) ? $_GET['doctype'] : ''; I have tried entering a value in the form for “Select All Documents” that would fit into the WHERE clause of the sql query, with numerous variations, but no luck. I need some feedback/guidance on how to make this work. Is there a simple solution that I am overlooking? Or will I have to create some complex IF/ELSE type of code that will work with the sql query? Any help solving this would be greatly appreciated.
  8. Thanks for helping me create a solid conceptual design foundation that I can work with moving forward. I will now focus on learning about debugging and error reporting, and once this basic phase is working add refinements such as pagination, highlighting text, etc.
  9. I moved the } down as you suggested and there is no error message when the page loads, so that is resovled. But the search results are still blank with the variables from the form input visible in the url as before.
  10. I inserted a comma in the Select statement like this: $result = mysql_query( "SELECT FullDocumentID, CONCAT(ReadingNo, '-', SequenceNo) AS full_doc_number FROM Full_Documents WHERE SectionText LIKE '%$search%' AND SectionTypeID = '$doctype' Order By FullDocumentID"); } but got the same error message as before when the page loads. Interestingly, if I type in and submit a search term that I know to be in the documents, the error message goes away and the text below the form reads: "Select a document to view:" No error message, but no items in the list either. The search term was "migraine" (it's a medical database) and the end of the url after submitting the form was "... test.php?search=migraine&doctype=1&submit=Submit" So apparently the passing of the variables via Get is working. Any further thoughts?
  11. Thanks, I put the additional code in the Business Logic section after the first query and I changed it back to $result since that is what is referenced in the new code. When I run the file I get this error message along with the "No matching documents found." Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in /home/content/36/10807936/html/ecr/test2.php on line 24 Search Term Document Type Text Background Reports Index No matching documents found. Here is my latest code. I hope I used the bbcode tags correctly. I have removed the unnecessary fields from the Select statement and will look into PDO. Thanks again for your help. <?php //BUSINESS LOGIC SECTION // get the data to produce the document links $search=$_GET['search']; $doctype=$_GET['doctype']; $search = isset($_GET['search']) ? $_GET['search'] : ''; if($search){ mysql_connect("xx", "xx", "xx") or die(mysql_error()); mysql_select_db("xx") or die(mysql_error()); $result = mysql_query( "SELECT FullDocumentID CONCAT(ReadingNo, '-', SequenceNo) AS full_doc_number FROM Full_Documents WHERE SectionText LIKE '%$search%' AND SectionTypeID = '$doctype' Order By FullDocumentID"); } // this code expects the result resource from the query to be in $result if(mysql_num_rows($result) > 0){ $links = array(); while ($row = mysql_fetch_assoc($result)){ $links[] = $row; } } $id = isset($_GET['Full_Document_ID']) ? $_GET['Full_Document_ID'] : 0; if($id){ // get the data/file for the requested document id $doc = mysql_query( "SELECT SectionText FROM Full_Documents WHERE FullDocumentID = " . $_GET[$id]);; } // PRESENTATION LOGIC SECTION // the search form if(!$search){ echo 'Enter search term: '; } ?> <form method="get" action="" id="search_criteria"> Search Term <input type="text" id="" name="search"></br> Document Type <select name="doctype"></br> <option value="1">Text</option> <option value="3">Background</option> <option value="2">Reports</option> <option value="4">Index</option> </select></br> <input type="submit" name="submit" value="Submit"> </form> <?php // the document links if($search){ if(isset($links)){ // output document links echo 'Select a document to view:<br>'; foreach($links as $link){ $_GET['Full_Document_ID'] = $link['Full_Document_ID']; // add document id to (any) existing get parameters $qs = http_build_query($_GET, '', '&'); // build the current url query string echo "<a href='?$qs'>{$link['full_document_number']}</a><br>"; } } else { echo 'No matching documents found.'; } } // the document display if(isset($doc)){ echo 'The requested document:<br>'; echo $doc; } ?>
  12. Thanks, I really appreciate your patience. Yes, when the example is run as standalone code, with the hardcoded sample data, it does work just fine. I can begin to grasp the concept behind the design principle you are suggesting. In the implementation, I have tried to replace the hardcoded sample data with the working code from my previous efforts as follows: <?php //BUSINESS LOGIC SECTION // get the data to produce the document links $search=$_GET['search']; $doctype=$_GET['doctype']; $search = isset($_GET['search']) ? $_GET['search'] : ''; if($search){ mysql_connect("xx", "xx", "xx") or die( mysql_error()); mysql_select_db("xx") or die(mysql_error()); $links = mysql_query( "SELECT SectionText, FullDocumentID, ReadingNo, SectionTypeID, CONCAT(ReadingNo, '-', SequenceNo) AS full_doc_number FROM Full_Documents WHERE SectionText LIKE '%$search%' AND SectionTypeID = '$doctype' Order By FullDocumentID"); } $id = isset($_GET['Full_Document_ID']) ? $_GET['Full_Document_ID'] : 0; if($id){ // get the data/file for the requested document id $doc = mysql_query( "SELECT SectionText, FullDocumentID FROM Full_Documents WHERE FullDocumentID = " . $_GET[$id]);; } // PRESENTATION LOGIC SECTION // the search form if(!$search){ echo 'Enter search term: '; } ?> <form method="get" action="" id="search_criteria"> Search Term <input type="text" id="" name="search"></br> Document Type <select name="doctype"></br> <option value="1">Text</option> <option value="3">Background</option> <option value="2">Reports</option> <option value="4">Index</option> </select></br> <input type="submit" name="submit" value="Submit"> </form> <?php // the document links if($search){ if(isset($links)){ // output document links echo 'Select a document to view:<br>'; foreach($links as $link){ $_GET['Full_Document_ID'] = $link['Full_Document_ID']; // add document id to (any) existing get parameters $qs = http_build_query($_GET, '', '&'); // build the current url query string echo "<a href='?$qs'>{$link['full_document_number']}</a><br>"; } } else { echo 'No matching documents found.'; } } // the document display if(isset($doc)){ echo 'The requested document:<br>'; echo $doc; } ?> I get an error message: "Warning: Invalid argument supplied for foreach() in /home/content/36/10807936/html/ecr/test2.php on line 66" Line 66 is: foreach($links as $link){ I think trying to Get "id" or "$id" from the "FullDocumentID" may be the problem, but don't see any way to fix it based on my current knowledge. Do you see anything obvious that I have missed or distorted? I really do like the way this approach passes the data between the sections without destroying it - it remains visible in the search box and results list - and no Warning message at the beginning of the document display before the search term is provided. So, again, I am thankful for your guidance with this specific project and the way this kind of approach can transfer to future projects once I master it.
  13. Thanks for your feedback and suggestions. This is just the kind of specific information I need at this early point in the project (and my continuing education). Based on you advice: 1. I have chosed to use a single page for the application. 2. Use the GET method to pass data. I have been doing some research on the "business logic" and "application logic" and I think I understand the rationale and overall concept, but am not sure how to do it with this specific application. Here is the code that I have created based on two of your suggestions (single page with GET method): <HTML> <HEAD> </HEAD> <BODY> <h1>Search Form</h1> <form method="get" action="search.php?go" id="searchform"> Search Term <input type="text" id="" name="searchterm"> Document Type <select name="doctype"> <option value="1">Text</option> <option value="3">Background</option> <option value="2">Reports</option> <option value="4">Index</option> </select> <input type="submit" name="submit" value="Submit"> </form> <h1>Search Results</h1> <? $searchterm=$_GET['searchterm']; $doctype=$_GET['doctype']; ?> <p>for: <span style="font-size: 14px; font-weight: bold; color: red;"><? echo $searchterm; ?></span> </p> <? mysql_connect("xx", "xx", "xx") or die( mysql_error()); mysql_select_db("xx") or die(mysql_error()); $result1 = mysql_query( "SELECT SectionText, FullDocumentID, ReadingNo, SectionTypeID, CONCAT(ReadingNo, '-', SequenceNo) AS full_doc_number FROM Full_Documents WHERE SectionText LIKE '%$searchterm%' AND SectionTypeID = '$doctype' Order By FullDocumentID"); $num_records = mysql_num_rows($result1); ?> <p>documents found: <span style="font-size: 14px; font-weight: bold; color: red;"><? echo $num_records; ?></span></p> <? while ($row = mysql_fetch_array($result1, MYSQL_ASSOC)) { $full_doc_number=$row ['full_doc_number']; $FullDocumentID=$row ['FullDocumentID']; echo '<a href="\ecr/search.php?ID=' . $FullDocumentID . '">' . $full_doc_number . '</a><br />'; } ?> <h1>Document Display</h1> <? $result2 = mysql_query( "SELECT SectionText, FullDocumentID FROM Full_Documents WHERE FullDocumentID = " . $_GET['ID']);; echo "<table border='0' width='800px'> <tr class=\"style3\"> "; while($row = mysql_fetch_array($result2)) { echo "<tr class=\"style2\">"; echo "<td style=\"width: 800px;\"><p>" . preg_replace("/\r\n|\r|\n/", "</p><p>", $row['SectionText']) . "</p></td>"; echo "</tr>"; } echo "</table>"; ?> </BODY> </HTML> The code does work crudely, but presents two immediate issues: ISSUE 1. When I click on the document number links in the SEARCH RESULTS list, it passes the $FullDocumentID through a GET method to the DOCUMENTS TEXT section by reloading the page and erasing the data in the SEARCH RESULTS list. I would like the list data to remain visible (without having to resort to the browser Back button to reload the search results). ISSUE 2. When the page loads for the first time (before any search or search results are available), there is no $FullDocumentID for the third step, therefore an error message is displayed: “Warning: mysql_fetch_array() expects parameter 1 to be resource, Boolean …” Of course this warning goes away once a link in the SEARCH RESULTS is clicked and the page is reloaded with the document ID in the URL – the GET allows the passing of the ID for the final db query that loads the document text. I would like for the warning statement not to be visible during the first two steps of the search and display process. Just a blank space or perhaps a default document could be loaded when the application starts? Would these two issues be solved by using the business logic/presentation logic model as you have outlined it? Thanks again for your helpful suggestions. Any further guidance is appreciated.
  14. I am creating a php search application on a mysql db with three php files: advanced_search.php search_results.php sectiontext.php It works but is awkward, since they open up as separate pages. advanced_search.php contains a form with this code: <form method="post" action="search_results.php?go" id="searchform"> This opens up search_results.php with a list of a summary of search results (document numbers) that are links to the full document text in sectiontext.php, like this: echo '<a href="\ecr/sectiontext.php?ID=' . $FullDocumentID . '">' . $full_doc_number . '</a><br />'; As noted, this all works but is awkward since it changes pages. It would be preferable to have all of this on one page so that the user can easily choose another document or change the search parameters. I am thinking along two lines, but am open to other options if anyone has suggestions: 1. Display all three php files on one web page (i.e., index.php) with includes. I have created the page with the includes, and they are there, but they don’t link to each other when I do the search. They keep opening up new web pages instead of staying on the same index.php page. I am a newbie to php, so still learning about includes. 2. Just put all the code into one php page in different divs and find a way to transfer the variables between steps in the process. This is easy for steps 1 and 2 (form data) but is more difficult for me (as a newbie) for step three (which seems to be more of a master-detail relationship between the search_results list and the sectiontext table. Maybe some javascript to link the selected item on the list to the full document text in step 3? This is just a simple application for myself (not for production), but I would like to make it as good as I can and learn by doing. Any thoughts on how to approach this project?
  15. Thanks so much. That is exactly what I needed and I really appreciate that you provided two excellent options.
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.