My first databse...

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
WithHisStripes
Forum Contributor
Posts: 131
Joined: Tue Sep 13, 2005 7:48 pm

My first databse...

Post 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
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

Post 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');
WithHisStripes
Forum Contributor
Posts: 131
Joined: Tue Sep 13, 2005 7:48 pm

Post 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
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

Post 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.
WithHisStripes
Forum Contributor
Posts: 131
Joined: Tue Sep 13, 2005 7:48 pm

Post 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
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

Post 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. ;)
WithHisStripes
Forum Contributor
Posts: 131
Joined: Tue Sep 13, 2005 7:48 pm

Post 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
Post Reply