SQL database and PHP search engine

Our organization is provided 12 different fax lists in excel each month. Each list contains different data and headings etc. We need an online database to store the data and upload the new data into. We also need an extensive search engine front end created with a couple extra functions and download search results options. See brief attached for more detail and have attached screen shots of desired design. ## Deliverables **_Brief for online list management database and search engine_** **Summary** Faxem International is provided with fresh data in the following Master Lists each month. Aged Care National Early Learning Australia Hong Kong Schools Local Government NZ Libraries NZ Post-Secondary Education NZ Schools Schools Australia Singapore Schools Uni TAFE Educations Libraries Australian Schools Washed Unwashed Early Learning Washed and Unwashed Faxem International use these lists to send fax broadcasts for customers and manually prepare and download the required fax data from a secure connection to the Faxem **php** website **sql** database. **Part 1** Create simple to use upload tool to allow Faxem to upload new Master Lists each month to the Faxem website **SQL database** and view current record info such as current records and date last updated. The old data will need to be deleted. Confirmation of the number of records uploaded ie '4506 records removed successfully - 4508 records added successfully' Note Master Lists have different column headings from each other and may be in Excel or CSV format when uploaded. Column Headings will not change. Example GUI layout (may upload several lists at a time or just one) WASHED LISTS UNWASHED LISTS | **Existing Database** | **Current Records** | **Date Last Updated** | **Choose New File to Upload** | **<>** | | Australian Schools Washed Unwashed | 9907 | 1 Oct 2012 | <> | <> | | Early Learning Washed and Unwashed | 4709 | 1 Oct 2012 | <> | <> | **Part 2** Create easy to use tabular search engine GUI with full search criteria to search database lists. **Step 1** Choose Master List ('WASHED List Selection', then ' UNWASHED List Selection) Tick Box's Default (lists are deselected). When a list is selected a new tab is made available to search that list. By default all records are available until Step 2 (refine Search). I.e. if run search after choosing Aged Care Master List, then all records in that list will be displayed and available for download. Depending on Master Lists chosen only the relevant fields and relevant variables will be displayed for refining search in step 2 **Step 2** Refine Search (separate tab for each list) Each selection will limit options applicable in each field below where relevant. All fields will have drop down field for selecting variables. For example if State = NSW then only NSW postcodes will be available in the postcode drop down field. For drop down fields containing more than 10 variables then need to include an option to deselect ALL. For example if search requires only 1 postcode then need to deselect all and then choose the 1 postcode required rather than deselecting all individually. **Aged Care Tab** STATE METCNTRY Default to All (one only) ALL or METRO or COUNTRY TOWN Default to All (one or many) Options only relevant to State and or METCNTRY selection PCODE Default to All (one or many) Options only relevant to State and or METCNTRY selection **Early Learning Tab** TYPE Default to All (one or many) STATE Default to All (one or many) METCNTRY Default to All (one only) ALL or METRO or COUNTRY LGA Default to All (one or many) Options only relevant to State DISTSPREAD Default to All (one or many) Options only relevant to State TOWN Default to All (one or many) Options only relevant to State and or METCNTRY selection PCODE Default to All (one or many) Options only relevant to State and or METCNTRY selection **Hong Kong Schools Tab** TYPE Default to All (one or many) REGION Default to All (one or many) GENDER Default to All (one or many) ALL or COED or BOYS or GIRLS **Local Government Tab** STATE Default to All (one or many) METRO_COUNTRY Default to All (one only) ALL or METRO or COUNTRY TOWN Default to All (one or many) Options only relevant to State and or METCNTRY selection PCODE Default to All (one or many) Options only relevant to State and or METCNTRY selection COUNCIL Default to All (one or many) Options only relevant to State and or METCNTRY selection **NZ Libraries Tab** TYPE Default to All (one or many) **NZ Post Secondary Tab** TYPE Default to All (one or many) REGION Default to All (one or many) LEVEL Default to All (one or many) CITY Default to All (one or many) Options only relevant to State and or METCNTRY selection SUBJECTS Default to All (one or many) **NZ Schools Tab** TYPE Default to All (one or many) LEVEL Default to All (one or many) REGION Default to All (one or many) GENDER Default to All (one or many) TOWN Default to All (one or many) Options only relevant to Region selection ENROLMENT Default to greater than 1 (Greater Than or less than entered number) **Public Libraries Tab** TYPE Default to All (one or many) STATE Default to All (one or many) METCNTRY Default to All (one only) ALL or METRO or COUNTRY TOWN Default to All (one or many) Options only relevant to State and or METCNTRY selection PCODE Default to All (one or many) Options only relevant to State and or METCNTRY selection **Schools Australia Tab** LVL Default to All (one or many) STATE Default to All (one or many) METCNTRY Default to All (one only) ALL or METRO or COUNTRY TYPE Default to All (one or many) GENDER Default to All (one or many) DISTRICT Default to All (one or many) Options only relevant to State LGA Default to All (one or many) Options only relevant to State ROLL Default to All (one or many) LOYEAR Default to All (one or many) HIYEAR Default to All (one or many) TOWN Default to All (one or many) Options only relevant to State and or METCNTRY selection PCODE Default to All (one or many) Options only relevant to State and or METCNTRY selection **Singapore Schools Tab** LEVEL Default to All (one or many) REGION Default to All (one or many) **UNI Tafe Tab** TYPE STATE METCNTRY Default to All (one only) ALL or METRO or COUNTRY TOWN Default to All (one or many) Options only relevant to State and or METCNTRY selection PCODE Default to All (one or many) Options only relevant to State and or METCNTRY selection **Results Tab** An always visible tab (furthest right) which will show the number of records matching the searches for each list chosen. And total the number of records if more than 1 list is chosen. A download/email button next to each list and a download all/email all option **Part 3** Download data should allow to choose the fields (columns) of data to download or choose from templates with pre saved settings. I.e. Template Name = Basic School (Includes Fax Number, Name of Recipient, Master List and RecNum) Also have the ability to download custom settings and save as template i.e. for a specific customer may want specific data that other customers don't usually require so could save the template as 'Customer A List' All lists to be downloaded in CSV format with Fax Number in first column with column heading 'FAX' Option to email list or download as CSV. **Part 4** (Primary List) to be washed against other lists (Block Lists). It will remove numbers in column A (FAX) form the Primary list that are in column A (FAX) of the block list or block lists. This tool will be on the same website and can be used when downloading lists from the database or as a stand-alone tool where the Primary list is uploaded