Excel VBA tutorials to enhance WordPress pages - #3 - Searching data files #1

Coding for Part III video below

Providing CSV text files and a search box for WordPress site

Copy these notes to NotePad and save.

1. Create / Open Microsoft Excel Workbook;

Copy the data for Excel Sheet2 from the video (or create own). Top left cell of table ('University'): B6.
Wikipedia text is Copied / PasteSpecial - Text from first paragraph of university Wikipedia entry;
URLs Copied from university sites and Wikipedia entries;
Add a list of Keyword tags in Column H eg

the university of leeds university leeds uni of leeds university uk university of leeds uk university of leeds law school university of leeds medical school university of leeds management school university of leeds postgraduate courses west yorkshire russell group etc

NOTE: no punctuation only spaces

2. Open Excel's Visual Basic Editor, insert a Visual Basic Module and add this sub-routine;

Set up a folder 'phpFolder' on your Desktop.

Sub csv_TagFile()

Dim n As Integer

Dim strUniName As String
Dim strKeywordTags As String

Dim output_Text As String
Dim fileName, filePath As String

Worksheets("Sheet2").Select

For n = 1 To 5

strUniName = Cells(6 + n, 2)
strKeywordTags = Cells(6 + n, 8)

outputText = outputText & strUniName & "," & strKeywordTags & Chr$(10)

Next

fileName = "phpTagFile"

' NOTE: adjust the file path below to match your own PC folder layout


Open filePath For Output As #1
Print #1, outputText
Close #1

End Sub

3. Download and or activate FileZilla FTP client (or your own choice).

Log on to your WordPress web site and open 'wp-content' directory (directory is same as 'folder' on Windows);
If not already done create New Directory called 'phpFolder' - keep same name as own PC;

4. Add New Page to your WordPress pages for practice (no links to other pages so unseen by outsiders);

Make Permalink page title 'Search CSV File' which will create URL address 'your site / search-csv-file '
Add and activate Plug-in called 'Insert PHP';
Copy following lines in Editor Text View of new page;

start ------------------------------

This is a page set up for testing a search box and PHP query to find files linked to search.

NOTE: this Youtube 'Description' does not allow HTML characters so change ++ into the opening left pointing arrow and +++ into the closing right pointing arrow (delete this note)

++form action="/search-csv-file" method="POST"+++
++input type="text" name="search"+++
++input type="submit" VALUE = "Search"+++
++/form+++
++P+++

NOTE in PHP below: square brackets, single quotes, full-stops / periods, semi-colons at the end of lines and slash in end brackets. (delete this note)

START ........................

[insert_php]

// BASIC VERSION

$search_string=htmlspecialchars($_POST["search"]) ;
$count=0 ;
if ($search_string=="") {$search_string="null";}

// NOTE: Youtube 'Description' does not allow HTML characters so change ++ into the opening left pointing arrow and +++ into the closing right pointing arrow (delete this note)

print "++br+++ Records found which include search term: '" . $search_string . "'++p+++";

while ($row = fgets($open_file)) {

$string_position=stripos($row,$search_string) ;
if($string_position!==false) {
$array_parts = explode(',', $row) ;
$count=$count+1 ;

print "++a href='" . $strURL . "/php-text-files/?uni=" . $array_parts[0] . "'+++" . $array_parts[0] . "++/a+++ ++p+++";

} // end of if conditional
} // end of while loop

if ($count==0){ print "No files found for search item '" . $search_string . "'"; }

fclose($open_file);
[/insert_php]

........................ END

View Page;
'Null' to start until search box used and search term found - try 'sheffield', 'leeds', 'russell group', 'Yorkshire', 'medical' etc.

End Part III

[sg]

Hey, first of all thank you for an amazing tutorial, really difficult to find good info on excel and php. I have a issue with my keywords not being checked somehow (i have made several attempts with various combination in both changing the filepath, keywords and recreating the whole thing again. I get either "null" or that the new search term is displayed but no matching results. More over your code in the video and the one written in the description do not match, i.e in the (search) function and when linking the second php site (how the are you supposed to write url to your site).
Thank you once again and i hope you make more of these videos.

/ Moe