Page 1 of 1

My first databse...

Posted: Fri Sep 30, 2005 3:53 pm
by WithHisStripes
Heya,
I am trying to develop a database where a user can upload an html file that is then indexed so when you visit a particular page you can choose that file from an "a-z" index or simply search for it. Can anyone tell me where to begin?! This is my first databse.

-Spence

Posted: Fri Sep 30, 2005 6:42 pm
by Skara
Assuming you want a user system as well... I'd have two tables.

user table:
int id
varchar name
-- any other stuff you want...

html files table:
int user
varchar filename
fulltext filename

to search, use.. MATCH (filename) AGAINST ('text').

Then to display the pages, you could do something like (assuming you want to show the username as well)...
SELECT * FROM files LEFT JOIN users ON files.user=user.id ORDER BY files.filename;
so to search them...
SELECT * FROM files LEFT JOIN users ON files.user=user.id WHERE MATCH (files.filename) AGAINST ('$text');

Posted: Fri Sep 30, 2005 6:56 pm
by WithHisStripes
Thanks for the reply. I don't even know where to begin with building a database, I have zero experience. So I am pretty much starting from the ground up. I don't need anything too complex, at least right now, so maybe something that involves a simple html table and then a php form that uploads files to that table? Does that make sense?

-spence

Posted: Fri Sep 30, 2005 10:11 pm
by Skara
Does that make sense?
no. html is markup. i.e. formatting. It doesn't do anything. A database is something that holds data (obviously :P). Exaples are MySQL, MSSQL, Oracle...
Go get phpMyAdmin if you don't already have it. It makes MySQL easy.
If you have hosting somewhere, then they probably make you create a database somewhere else, then edit it in phpMyAdmin. Here's a good example database..thing.

- First, create a database (somehow). Your host should have a "Create Database" link somewhere. Name it testing.
- Second, go into phpMyAdmin and click the dropdown on the left and choose testing.
- Third, where it says "Create new table on database testing:" enter the name testtable and 3 rows.
- Fourth:
-- name the fields id, number, and data. Choose types smallint, int, and varchar. Input lengths of 3, 10, and 40.
This means that id will be limited to three characters long, number to 10, and data to 40. varchar is a string. For id, choose unsigned from the attributes dropdown, select auto_increment from the extra dropdown, and click the radio under the icon that has a key on it. unsigned means it's a positive integer. auto_increment means it will inc by one for each row you add. the key icon means it's a primary index.
-- click create.
-- Look at the code it shows you (the CREATE TABLE code). Compare it with the rows in the table.

- NEXT, we insert data... Instead of using phpMyAdmin's insert tab, do this in the little query box below the table. (By default it should say "SELECT * FROM testtable WHERE 1.")
-- type in:
INSERT INTO testtable VALUES('','12345','test string');
and click Go.
-- Now type in:
SELECT * FROM testtable;
-- Look at the data there and compare it with the INSERT string.

- Alrighty! So you should have a basic idea of what a database is. yay.

To insert data from a php file, you have to first connect, then select the database, then query it.

Code: Select all

<?php
mysql_connect('localhost','username','password'); // fill in the variables appropriately
mysql_select_db('testing');
mysql_query("INSERT INTO testtable VALUES('','54321','another string')"); // the auto_increment field is empty, but there!
$result = mysql_query("SELECT * FROM testtable ORDER BY number");
while ($row = mysql_fetch_assoc($result)) {
  print("$row['id'] = " . $row['id'] . "\n" .
        "$row['number'] = " . $row['number'] . "\n" .
        "$row['data'] = " . $row['data'] . "\n\n");
}
mysql_close(); // always have this.
?>
so to input data from an html form...

Code: Select all

<?php
if (isset($_GET['txt'])) {
  $txt = mysql_escape_string($_GET['txt']); // ALWAYS have this for user entered data.  You should normally do other checks as well.
  $time = time();
  //assuming we're already connected to the database, and that we'll close later...
  mysql_query("INSERT INTO testtable VALUES('','$time','$txt')");
}
else {
  print("<form><input type='text' name='txt' /><input type='submit' value='Add Data' /></form>");
}
?>

wow. databases 101. *shrugs* hopefully you find it helpful.

Posted: Sat Oct 01, 2005 4:35 pm
by WithHisStripes
Okay, great, only problem now is I can't get myadmin configured, it either says (when using config) that there is an error, or (when its using http) that my login is incorrect. I thought I followed the install instruction correctly but maybe I'm overlooking something. Thanks so much for you time!

-Spence

Posted: Sat Oct 01, 2005 8:01 pm
by Skara
edit the file... *looks* config.inc.php.
The things that have to be set as so are the following:

Code: Select all

$cfg['PmaAbsoluteUri'] = 'http://youraddy.ext/pathtophpMyAdmin/';
// e.g. http://mysite.com/phpMyAdmin/, http://localhost/admin/misc/phpMyAdmin/

$cfg['Servers'][$i]['host']          = 'localhost';
$cfg['Servers'][$i]['controluser']   = '';  // blank for http auth
$cfg['Servers'][$i]['controlpass']   = '';  // ditto
$cfg['Servers'][$i]['auth_type']     = 'http';
$cfg['Servers'][$i]['user']          = '';  // ditto again
$cfg['Servers'][$i]['password']      = '';  // and again
Of course, there are many others that need to be right (all of them), but those are the ones that you need to actually set. ;)

Posted: Mon Oct 03, 2005 3:47 pm
by WithHisStripes
Actually, it is already installed, I think that's why I was having problems. My old server didn't have it, so I just assumed :/. Anyway, okay, so I created all those table fields and varchars that you told me to, what's the next step?

Thanks so much!
-Spence