Overclock.net › Forums › Software, Programming and Coding › Coding and Programming › Web Coding › Noob question about searchable databases
New Posts  All Forums:Forum Nav:

Noob question about searchable databases

post #1 of 6
Thread Starter 
A friend has asked me to help with something that to be blunt I don't have a clue how to do. However as it's for a good cause I have agreed to at least try to help.

Basically he needs to create a searchable database. I have been told the data is currently on an excel spreadsheet and that the webpage will need some kind of searchbox that can search for a first name or a last name (column A is 1st names, column B is last names). Each entry will have other info in other columns (D.O.B, place of death etc) I haven't yet been told how many other columns. What I have been told is due to the sheer number of entries (40000+) there will be duplicate names so searches may need to return more than 1 result.

I've been googling away the past hour or so and only really found paid for solutions (not an option the charity can barely afford the hosting costs as it is). The few explanations on how to do this myself have basically flown over my head.

My current abilities in this area go as far as editing templates using the MS expression bundle. So I guess I am asking is there an easy way I can incorporate a search box in to a webpage and that the searchbox will search a spreadsheet and return the results in some kind of nice looking way?

I appreciate any help offered, please keep in mind this is not my forte so I may need to have things explained as if I was stupid.
Intel
(14 items)
 
AMD
(16 items)
 
 
CPUMotherboardGraphicsRAM
i5 3570k Gbyte Z77X-UD3H MSI GTX670 16GB (4X4) Corsair Vengance 1600MHz Low Profile 
Hard DriveHard DriveHard DriveOptical Drive
1Tb Seagate Barracuda 7200RPM 1Tb Seagate Barracuda 7200RPM 120GB OCZ Agility 3 SSD Cheapo DVDRW 
CoolingOSKeyboardPower
CM Hyper 212 EVO Push/Pull Win7 Pro Sidewinder X4 OCZ ZS 650W 
CaseMouse
CM HAF X RAT 5 
CPUMotherboardGraphicsGraphics
Phenom II 1600T GA990XA-UD3 Palit 550ti Palit 550ti (sli) 
RAMHard DriveHard DriveHard Drive
16GB (4X4) Gskill OCZ Agility 3 120GB Seagate Barracuda 500GB WD Black 500GB 
Optical DriveCoolingOSOS
Cheapo DVDRW AC Freezer 7 Pro Win 7 Pro Server 2008 
OSOSPowerCase
Ubuntu Win 8 OCZ ZS 650w Self modded cheapo thing 
  hide details  
Reply
Intel
(14 items)
 
AMD
(16 items)
 
 
CPUMotherboardGraphicsRAM
i5 3570k Gbyte Z77X-UD3H MSI GTX670 16GB (4X4) Corsair Vengance 1600MHz Low Profile 
Hard DriveHard DriveHard DriveOptical Drive
1Tb Seagate Barracuda 7200RPM 1Tb Seagate Barracuda 7200RPM 120GB OCZ Agility 3 SSD Cheapo DVDRW 
CoolingOSKeyboardPower
CM Hyper 212 EVO Push/Pull Win7 Pro Sidewinder X4 OCZ ZS 650W 
CaseMouse
CM HAF X RAT 5 
CPUMotherboardGraphicsGraphics
Phenom II 1600T GA990XA-UD3 Palit 550ti Palit 550ti (sli) 
RAMHard DriveHard DriveHard Drive
16GB (4X4) Gskill OCZ Agility 3 120GB Seagate Barracuda 500GB WD Black 500GB 
Optical DriveCoolingOSOS
Cheapo DVDRW AC Freezer 7 Pro Win 7 Pro Server 2008 
OSOSPowerCase
Ubuntu Win 8 OCZ ZS 650w Self modded cheapo thing 
  hide details  
Reply
post #2 of 6
The most sensible thing would be to get the data from the spreadsheet into a real database. Searching a large excel file is going to be slow and tedious.

It should be fairly easy to get all of the rows into a MySQL table or something similar, especially if the document is well-structured.

Next you need a script to interact with the database. I don't know anything about MS Expression. You'll take the values that the user entered into the search form, and use those terms to run a query on the database. With the result from that query, you'll display the matches to the user.

Do you have a web server available to you? What languages can you use?
post #3 of 6
Thread Starter 
Quote:
Originally Posted by rediaf View Post

The most sensible thing would be to get the data from the spreadsheet into a real database. Searching a large excel file is going to be slow and tedious.

It should be fairly easy to get all of the rows into a MySQL table or something similar, especially if the document is well-structured.

Well structured maybe an issue. I have heard since my OP the data is "mostly" organised in excel spreadsheets. I won't get to actually see the data for another 7-10 days. The organisation wanting this are completely tech illiterate so "mostly" organised may mean I spend hours, hours and hours organising it myself.
Quote:
Next you need a script to interact with the database. I don't know anything about MS Expression. You'll take the values that the user entered into the search form, and use those terms to run a query on the database. With the result from that query, you'll display the matches to the user.

So if I figured out/managed to put their data in to a MySQL table (I will have to learn this). I would just need a script that allows the user to enter details in either or both of 2 text boxes (1st name and last name), this would then search the database. Any idea if I can find a cut n paste type script to do this? (even if I have to modify it)
Quote:
Do you have a web server available to you? What languages can you use?

I don't have constant access to a webserver but I can get access. I learned Java once it made my head want to explode and I haven't retained much at all. That said I'm generally confident to edit/alter code to my needs just not to write from scratch. CSS/HTML I am confident to edit and muck about with too (if these count as languages)..... oh and I can swear like a trooper in Chinese, Arabic, Pashtu, French, German, Spanish and Welsh biggrin.gif

Thanks for the help. Just to be clear to I'm attempting this for a charitable organisation, I'm not profiting in anyway so am not trying to get work done free by OCN.
Intel
(14 items)
 
AMD
(16 items)
 
 
CPUMotherboardGraphicsRAM
i5 3570k Gbyte Z77X-UD3H MSI GTX670 16GB (4X4) Corsair Vengance 1600MHz Low Profile 
Hard DriveHard DriveHard DriveOptical Drive
1Tb Seagate Barracuda 7200RPM 1Tb Seagate Barracuda 7200RPM 120GB OCZ Agility 3 SSD Cheapo DVDRW 
CoolingOSKeyboardPower
CM Hyper 212 EVO Push/Pull Win7 Pro Sidewinder X4 OCZ ZS 650W 
CaseMouse
CM HAF X RAT 5 
CPUMotherboardGraphicsGraphics
Phenom II 1600T GA990XA-UD3 Palit 550ti Palit 550ti (sli) 
RAMHard DriveHard DriveHard Drive
16GB (4X4) Gskill OCZ Agility 3 120GB Seagate Barracuda 500GB WD Black 500GB 
Optical DriveCoolingOSOS
Cheapo DVDRW AC Freezer 7 Pro Win 7 Pro Server 2008 
OSOSPowerCase
Ubuntu Win 8 OCZ ZS 650w Self modded cheapo thing 
  hide details  
Reply
Intel
(14 items)
 
AMD
(16 items)
 
 
CPUMotherboardGraphicsRAM
i5 3570k Gbyte Z77X-UD3H MSI GTX670 16GB (4X4) Corsair Vengance 1600MHz Low Profile 
Hard DriveHard DriveHard DriveOptical Drive
1Tb Seagate Barracuda 7200RPM 1Tb Seagate Barracuda 7200RPM 120GB OCZ Agility 3 SSD Cheapo DVDRW 
CoolingOSKeyboardPower
CM Hyper 212 EVO Push/Pull Win7 Pro Sidewinder X4 OCZ ZS 650W 
CaseMouse
CM HAF X RAT 5 
CPUMotherboardGraphicsGraphics
Phenom II 1600T GA990XA-UD3 Palit 550ti Palit 550ti (sli) 
RAMHard DriveHard DriveHard Drive
16GB (4X4) Gskill OCZ Agility 3 120GB Seagate Barracuda 500GB WD Black 500GB 
Optical DriveCoolingOSOS
Cheapo DVDRW AC Freezer 7 Pro Win 7 Pro Server 2008 
OSOSPowerCase
Ubuntu Win 8 OCZ ZS 650w Self modded cheapo thing 
  hide details  
Reply
post #4 of 6
Is this an internal tool, or will it need to be open to the public?

I have a few basic examples (in PHP). These are very rough outlines of what you would do.

You'd start with a HTML form. I've just put in two example fields.
Code:
<form method="POST" action="search.php">
  First name: <input type="text" name="first_name" />
  Last name: <input type="text" name="last_name" />
  <input type="submit" value="submit" />
</form>

In your script you will take those values - they can be accessed from the $_POST variable.
Code:

// put the values into variables to make them easier to work with
$firstName = (isset($_POST['first_name'])) ? htmlspecialchars($_POST['first_name']) : '';
$lastName = (isset($_POST['last_name'])) ? htmlspecialchars($_POST['last_name']) : ''l;

// open the database
try {  
  $db = new PDO("mysql:host=localhost;dbname=people", 'username', 'password');

  $stmt = $db->prepare("SELECT * FROM people WHERE first_name = ? AND last_name = ?");
  $stmt->bindParam(1, $firstName);
  $stmt->bindParam(2, $lastName);

  // would probably print out rows of a table to make it look decent
  if ($stmt->execute()) {
    while ($row = $stmt->fetch()) {
      print_r($row);
    }
  }
}  
catch(PDOException $e) {  
  echo $e->getMessage();  
  exit;
}  

// close connection
$db = null;

post #5 of 6
Thread Starter 
This is hugely appreciated. The search will be open to the public.
Intel
(14 items)
 
AMD
(16 items)
 
 
CPUMotherboardGraphicsRAM
i5 3570k Gbyte Z77X-UD3H MSI GTX670 16GB (4X4) Corsair Vengance 1600MHz Low Profile 
Hard DriveHard DriveHard DriveOptical Drive
1Tb Seagate Barracuda 7200RPM 1Tb Seagate Barracuda 7200RPM 120GB OCZ Agility 3 SSD Cheapo DVDRW 
CoolingOSKeyboardPower
CM Hyper 212 EVO Push/Pull Win7 Pro Sidewinder X4 OCZ ZS 650W 
CaseMouse
CM HAF X RAT 5 
CPUMotherboardGraphicsGraphics
Phenom II 1600T GA990XA-UD3 Palit 550ti Palit 550ti (sli) 
RAMHard DriveHard DriveHard Drive
16GB (4X4) Gskill OCZ Agility 3 120GB Seagate Barracuda 500GB WD Black 500GB 
Optical DriveCoolingOSOS
Cheapo DVDRW AC Freezer 7 Pro Win 7 Pro Server 2008 
OSOSPowerCase
Ubuntu Win 8 OCZ ZS 650w Self modded cheapo thing 
  hide details  
Reply
Intel
(14 items)
 
AMD
(16 items)
 
 
CPUMotherboardGraphicsRAM
i5 3570k Gbyte Z77X-UD3H MSI GTX670 16GB (4X4) Corsair Vengance 1600MHz Low Profile 
Hard DriveHard DriveHard DriveOptical Drive
1Tb Seagate Barracuda 7200RPM 1Tb Seagate Barracuda 7200RPM 120GB OCZ Agility 3 SSD Cheapo DVDRW 
CoolingOSKeyboardPower
CM Hyper 212 EVO Push/Pull Win7 Pro Sidewinder X4 OCZ ZS 650W 
CaseMouse
CM HAF X RAT 5 
CPUMotherboardGraphicsGraphics
Phenom II 1600T GA990XA-UD3 Palit 550ti Palit 550ti (sli) 
RAMHard DriveHard DriveHard Drive
16GB (4X4) Gskill OCZ Agility 3 120GB Seagate Barracuda 500GB WD Black 500GB 
Optical DriveCoolingOSOS
Cheapo DVDRW AC Freezer 7 Pro Win 7 Pro Server 2008 
OSOSPowerCase
Ubuntu Win 8 OCZ ZS 650w Self modded cheapo thing 
  hide details  
Reply
post #6 of 6
Quote:
Originally Posted by rediaf View Post

Is this an internal tool, or will it need to be open to the public?

I have a few basic examples (in PHP). These are very rough outlines of what you would do.

You'd start with a HTML form. I've just put in two example fields.
Code:
<form method="POST" action="search.php">
  First name: <input type="text" name="first_name" />
  Last name: <input type="text" name="last_name" />
  <input type="submit" value="submit" />
</form>

In your script you will take those values - they can be accessed from the $_POST variable.
Code:

// put the values into variables to make them easier to work with
$firstName = (isset($_POST['first_name'])) ? htmlspecialchars($_POST['first_name']) : '';
$lastName = (isset($_POST['last_name'])) ? htmlspecialchars($_POST['last_name']) : ''l;

// open the database
try {  
  $db = new PDO("mysql:host=localhost;dbname=people", 'username', 'password');

  $stmt = $db->prepare("SELECT * FROM people WHERE first_name = ? AND last_name = ?");
  $stmt->bindParam(1, $firstName);
  $stmt->bindParam(2, $lastName);

  // would probably print out rows of a table to make it look decent
  if ($stmt->execute()) {
    while ($row = $stmt->fetch()) {
      print_r($row);
    }
  }
}  
catch(PDOException $e) {  
  echo $e->getMessage();  
  exit;
}  

// close connection
$db = null;


Always be careful with code like this due to SQL injection. http://stackoverflow.com/questions/1314521/how-safe-are-pdo-prepared-statements.

If you can use a Windows Server, there is also Asp.Net MVC available as a backend (my favorite) which I feel is a bit easier to use (and faster), though Windows Server is generally more expensive.

There are also other languages available that I feel (just my opinion as a long time Web Developer) that are easier to use and just as (if not more) capable than php, such as Ruby on Rails and Python. There is no *best* language, just your favorite.

My personal recommendation would be Asp.Net MVC if you are on a Windows machine, and Ruby on Rails if you are on a Linux box.
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: Web Coding
Overclock.net › Forums › Software, Programming and Coding › Coding and Programming › Web Coding › Noob question about searchable databases