PHP MySQL communication basics

[ParallelLogic]

I'm new to MySQL and PHP and would appreciate it if you could point me in the right direction becausee I do not know the required functions to program in these languages.

If I have created a MySQL database (let's call it users) with a table (let's call that names) and would like a PHP script to search through the database and find records that match a search string, what PHP methods should I use?

I know Java rather well, but am a little lost in exactly what I am dealing with here. How does MySQL compare to Excel? Is the database name like the spreadsheet name and the table name is like the column title?

If you have any links to PHP-MySQL communication tutorials, that would be appreacited as well, thank you.

 

[xadrieth]

Here is a basic script for PHP/MySQL script.

In the table "names", there will be two column, "firstName" and "lastName".

<?php
define('DB_HOST', 'localhost');
define('DB_USER', ''); // Input the username for a database user
define('DB_PASS', ''); // The password for the respective DB_USER
define('DB_NAME', 'users');

$connection = mysqli_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME);

$query = "SELECT * FROM users";

$getUsers = mysqli_query($connection, $query);

echo '<h1>List of people in users.</h1>';

while ($row = mysqli_fetch_array($getUsers) {
     echo '<table><tr><td><b>First Name<b>td><td><b>Last Name<b></td></tr>';
     echo '<tr><td>' . $row['firstName'] . '</td><td>' . $row['lastName'] . '</td></tr>';
     echo '</table>';
}

mysqli_close($connection);
?>

And thats all this to it for a simple PHP/MySQL script.

I would suggest taking a look at the PHP tutorial here:
http://www.tizag.com/phpT/

And maybe picking up a copy of O'rielly's "Learning SQL".

Or if you want to knock two birds with one stone try Sam's "PHP and MySQL Web Development 4th Ed.", is a great book.

One you feel comfortable with PHP and MySQL, be sure to use PHP.net for references on PHP, and the dev.mysql.com for MySQL information.


One more thing, the "mysqli_()" functions are only an interface to have PHP talk to MySQL, take a look at them here:
http://www.php.net/mysqli

Hope I helped.

 

[misson]

I know Java rather well, but am a little lost in exactly what I am dealing with here. How does MySQL compare to Excel? Is the database name like the spreadsheet name and the table name is like the column title?

While you could view tables like spreadsheet sheets, the model behind MySQL is quite different and you'd probably make many mistakes.

MySQL is based on relations (well, relationships, but it's a technical difference that isn't too important) and operations on relations. In the model, relations are defined using a first order logic. The claim is that SQL is a first order logic, of which I'm almost convinced but am still looking for a proof.

For learning material, read over the sources I suggested in response to a similar question.

 

[ParallelLogic]

define('DB_HOST', 'localhost');
works in all cases correct? I don't have to figure out the local address
127.0.0.1 or something do I?

$query = "SELECT * FROM users";
So I would replace that with
define('SEARCH', 'bob');
$query = "SELECT "+$SEARCH+" FROM users";
and that would return users with the the name bob somewhere in their firstName or lastName fields? I'm not quite sure how to search just the firstName field in this example?

Wow, very though explanation, thank you very much. I'm looking through the tizag tutorials and those look wonderful, thank you for that link as well.

Also, thank you misson, I shall check out those resources soon.

 

[misson]

define('DB_HOST', 'localhost');
works in all cases correct? I don't have to figure out the local address
127.0.0.1 or something do I?

You can use a hostname of "localhost" to connect to MySQL server running on the local computer, yes. Note that "localhost" is not treated exactly the same as "127.0.0.1", though the difference is usually unnoticeable, and beneficial when it is noticeable. When you connect to "localhost", the MySQL driver probably will use UNIX sockets rather than TCP/IP. If you use "127.0.0.1", the driver will use TCP/IP.

define('SEARCH', 'bob');
$query = "SELECT "+$SEARCH+" FROM users";

define introduces a constant, not a variable, so you don't reference it with a "$" prefix. Also, the terms after "SELECT" are field names, not values. Also also, variables are interpolated within double quotes, so you don't need to use string concatenation:

$field="name";
$userName="bob";
$query="SELECT * FROM users WHERE $field='$userName'";

Concatenation is marginally faster than interpolation when used once or twice, but interpolation is more readable and marginally faster when there are multiple values you want to construct the string from. Readability trumps micro-optimization.

In addition to the mysql and mysqli drivers, there's also a MySQL driver for a database abstraction layer called PDO (PHP Data Objects). One nice aspect of PDO is it presents a consistent interface for accessing MySQL, MSSQL, PostgreSQL, SQLite, Oracle and other databases. Not every host's copy of PHP has PDO enabled.

Note that xadrieth's example is intended to illustrate a basic way of connecting to and querying a database. It doesn't perform input validation or sanitization to prevent SQL injection, nor does it handle errors. The approach illustrated by that example is also a little out of date. The more modern approach is to use prepared statements (via mysqli:repare or PDO:repare, for example). With prepared statements, you don't need to sanitize data and you can reuse statements.

function throwOnFalse($result, $stmt) {
    if (! $result) {
        $err = $stmt->errorInfo();
        throw new RuntimeException("$err[2] [$err[0]]", $err[1]);
    }
}
try {
    $dbConnection = new PDO("mysql:host=localhost;dbname=$dbName", $dbUser, $dbPassword);
    $stmt = $dbConnection->prepare('SELECT * FROM users WHERE name=:uname');
    throwOnFalse(!$stmt->bindParam('uname', $userName), $stmt);
        
    $userName='bob';
    throwOnFalse(!$stmt->execute(), $stmt);
    $bob = $stmt->fetch();

    $userName='alice';
    throwOnFalse(!$stmt->execute(), $stmt);
    $alice = $stmt->fetch();
} catch (PDOExeption $exc) {
    error_log('PDOException: ' . $exc->getMessage());
} catch (RuntimeException $exc) {
    error_log($exc->getMessage());
}

You don't want to have multiple world-readable scripts with your MySQL username and password scattered about. The safest thing to do is define them within a single script with permissions set to mode 0600 and include the script wherever you need a database connection. One really safe implementation is to define a function or object that creates the connection. This way, the username and password are never exposed to any script but the connection script. Here's a sample implementation of this approach:

function db_connect($driver, $user="default user", $password="dflt p/w", $options=array()) {
  $host = isset($options['host']) ? $options['host'] : 'localhost';
  switch ($driver) {
    case 'mysql':
        return mysql_connect($host, $user, $password);
    case 'PDO':
        return new PDO($options['dsn'], $user, $password, $options);
    default:
        $dbName = isset($options['db']) ? $options['db'] : '';
        return new $driver($host, $user, $password, $dbName);
  }
}

Lastly, when you post source code in these forums, enclose it within

, [php] or [html] tags (whichever is most appropriate) to make it easier to read.

 

[xav0989]

Concerning xadrieth's example, Zend (if I remember well) posted an entry on database connections stating that putting database connection information in constants what not good, security-wise since constants are not subject to the same scoping rules as variables.

 

[ParallelLogic]

:biggrin: Wow, I'm really blown away by all the info posted in here thus far, I've spent the better part of the last 24 hours just reviewing it. The tizag tutorial are awesome, I feel like I'm starting to get the hang of the PHP syntax & functions.

To: Mission
I'll stick with localhost, somehow I was expecting finding the actual server the file is hosted on would be more complicated because of my other misguided dabbles in internet communication.

The more modern approach is to use prepared statements (via mysqli:repare or PDO:repare, for example). With prepared statements, you don't need to sanitize data and you can reuse statements.

Alright, I think the -> arrows threw me off a little, it seems like some kind of backward referencing. If I'm reading this correctly, you are suggesting replacing

$dbConnection = new PDO("mysql:host=localhost;dbname=$dbName", $dbUser, $dbPassword);

from the first PHP code segment with

$dbConnection = db_connect("mysql:host=localhost;dbname=$dbName", $dbUser, $dbPassword);

from the second example. However, the second example code would be in its own PHP file (the file would also have the username and password combos for the database) and I would set its permissions to 0600. (I'm not sure how to set permissions, I presume that is a setting I can change in the cPanel somewhere?). I don't see the 0600 permission described in the Linux Permissions page you linked to, will the permission prevent the PHP files from communicating from one another? So if the first PHP file had standard permission, it would have no problem connecting with the file with the db_connect methods & passwords in it correct?

If I am reading the code correctly, it appears that $bob is a list of all users with 'bob' in the name somewhere, and the same goes with the alice case. However, I do plan on refining the search results, and I am unsure how to do this precisely:

For example
I plan to have several fields in the main table of the MySQL database. The ones I am looking at right now are: name, description, posts . posts is a number field and the other two fields are strings. I would like to interpret the search string "bob alice" as finding all users who have the word "bob" in either their name or description and then from the list returned there, I only want to see the users who also have "alice" in their name or description. I would then like to sort the results by number of posts. I am unsure if I need to somehow cross check the two results lists $bob and $alice to find records that appear in both lists or if there may be a built in function to do this? Also, from what I have heard online, there is a function that can sort the list returned from a database query (by number of posts) correct?

To: xav0989

stating that putting database connection information in constants what not good

Alright, doubly noted. I'll look for the thread you are referring to when I am back on a stable internet connection. I don't know how I would define the contents of a variable without using constants somewhere however...

 

[xadrieth]

Well, I'm glad that your learning how to use PHP and MySQL.

I would suggest that you don't use define that much, and try to use strings (ex. $user = bob).

About the "->" arrows, they are used for OOP style of programing.

With http://php.net/mysqli, there are two ways that you can use the mysqli functions, the first is "Procedural style" like:

mysqli_query($connection, "SELECT * FROM products WHERE productID = '1');
mysqli_fetch_array($query);
mysqli_close($connection);

and there is OOP style that uses classes:

$db = new mysqli('localhost', 'username', 'password', 'database_name');

$db->query("SELCT * FROM products WHERE productID = '1');

Both are OK to use, I'm more used to using the procedural style because i write most of my PHP code by making it class driven.

But if your not creating your own classes for your PHP application, it's fine to use either style, procedural style might be better if your first learning how to code PHP.


I would also say to pick up a good PHP/MySQL book, like Web Development 4th ed. from Sams.

 

[misson]

(I'm not sure how to set permissions, I presume that is a setting I can change in the cPanel somewhere?).

You presume correctly. Many FTP clients will also let you set file permissions.

I don't see the 0600 permission described in the Linux Permissions page you linked to,

It's covered by the section on numeric permissions.

will the permission prevent the PHP files from communicating from one another? So if the first PHP file had standard permission, it would have no problem connecting with the file with the db_connect methods & passwords in it correct?

Mode 0600 means only the file owner has read & write access. This prevents other users from reading the source and discovering you password. On X10 (and other hosts), the web server process for your site runs with your credentials, so any other scripts of yours will be able to access the DB connection script.

If I am reading the code correctly, it appears that $bob is a list of all users with 'bob' in the name somewhere, and the same goes with the alice case.

Close. Since fetch() is called only once every time a query is executed, $bob holds a single record for a user whose name is exactly 'bob'; the case for $alice is similar. If you want to search for a record that has 'bob' somewhere in the name, use LIKE '%bob%' rather than ='bob' in the WHERE clause. For more complex queries (such as finding a user who has 'bob' or 'alice' in the name field), you can use REGEXP: name LIKE 'bob|alice'.

Keep in mind this warning from the MySQL REGEXP manual page:

The REGEXP and RLIKE operators work in byte-wise fashion, so they are not multi-byte safe and may produce unexpected results with multi-byte character sets. In addition, these operators compare characters by their byte values and accented characters may not compare as equal even if a given collation treats them as equal.

This may make REGEXP an unsuitable operator to apply to a field holding people's names.

However, I do plan on refining the search results, and I am unsure how to do this precisely:

For example
I plan to have several fields in the main table of the MySQL database. The ones I am looking at right now are: name, description, posts . posts is a number field and the other two fields are strings. I would like to interpret the search string "bob alice" as finding all users who have the word "bob" in either their name or description and then from the list returned there, I only want to see the users who also have "alice" in their name or description.

Are you sure you want the `name` and `description` columns to hold similar data? That model sounds a little messy. Of course, it might be appropriate for whatever entity you're modeling.

The WHERE clause in SQL supports boolean operators. A single query is more efficient than multiple queries and shorter to code:

SELECT * FROM users 
    WHERE (name LIKE '%bob%' OR description LIKE '%bob%') 
        AND (name LIKE '%alice%' OR description LIKE '%alice%')

MySQL also supports fulltext searches on columns of type FULLTEXT.

I would then like to sort the results by number of posts. ... Also, from what I have heard online, there is a function that can sort the list returned from a database query (by number of posts) correct?

Not a function, exactly. Use the ORDER BY clause:

SELECT * FROM users 
    WHERE (name LIKE '%bob%' OR description LIKE '%bob%') 
        AND (name LIKE '%alice%' OR description LIKE '%alice%')
    ORDER BY posts

I don't know how I would define the contents of a variable without using constants somewhere however...

Literals (e.g. "foo", 42) are different from constants.

 

[xav0989]

@xadrieth:
You forgot the closing " in you select statements... (Usually I'm the one which errors are pointed out by misson, so don't worry!)

@ParallelLogic:
You might also want to look at http://www.w3schools.com It's really nice.

 

[ParallelLogic]

To mission:

It's covered by the section on numeric permissions.

Ah, I see it now. Are you sure I should go with 0600 and not 0700? It seems like what I'm trying to do is read the password.php file and then execute the method getUserPassword() to return the password from it, so I would need execution privileges in that case?

Are you sure you want the `name` and `description` columns to hold similar data?

It's the only way I know how to separate the two entities, so no, I'm not really sure, I'm just unaware of any other methods.

Perhaps it would be best if I explained what I am trying to do:
I want to start out with three systems (tables). The first is a list (so only one column "userName") of usernames (2 million of them that I already have). I'm hoping the size of the list won't slow anything down too much, I'm still unsure how exactly to get the names into the database at the moment since I haven't researched that yet, and the names are all stored in text files on my computer. The usernames have characters 0-9, a-z and - in them only (so a byte for byte comparison shouldn't be a problem there).

The second table will have three fields: 'userName', 'description' and 'keyWords'. Each of these fields is a string, and description and keyWords is pretty much unlimited for what kinds of characters may appear. This table is mostly for users who want to enter their own info, I currently have about 400 users lined up for this table, so I don't expect storage space to be too much of a problem here.

The third table is a stats list (data from the user's profile page). So things like #posts, #pictures, #profileVisits, as well as userType ('Artist', 'Programmer'... though these titles are fixed [there are only about 200 possible titles], so I was thinking of assigning the title a number to save space in the database), and tagLine (a short exclamation in string format), location, gender, userType (Admin, Mod, Standard), among others (i can list all the fields if that would help). This table is here to provide the data used to sort the users by (though they can also be sorted by username).

So this is the backend, the front end is a Flash application that you can enter in a search string ("bob alice") and choose which fields to search through (any combination of username, description, key words, tag line), then you can remove users from the results (so you will only see users from one location, of one gender, one category, or one userType) then you can sort the results by #posts, #profileViews, etc and finally you will have the option to reverse the results list (so people with the least number of posts come upfirst rather than last -- although I was thinking of limiting the number of results returned to 1000 like Google since I have a limited amount of bandwidth).

From that last paragraph, I have a couple questions if I may: I am curious if there is a way to only return 50 results from a query at a time. So a user can search for 'bob', see the results page in the Flash app, but only 50 results will be sent at a time to reduce bandwidth (so when they get the 40th record or so, the app will send out a signal to the PHP file to retrieve the next 50 results). The other problem with that strategy of 50 results at a time that concerns me is processing power. I plan to have many users accessing this database at once, so that already is a strain (I suppose) if they are searching 2 million users. However, I am worried about how to store PHP info temporarily without sending it to the user. If I have the PHP file search the database and get the first 1000 results, then only send the first 50 to the user, when the user asks for the next 50 results, I would rather have the 1000 results stored temporarily somewhere so that the user is not forced to wait for the database query to execute again (in order to find the same set of 1000 users, but this time return users 51-100).

Also, I'm not entirely sure, but if I want to find the users with the least number of posts first, should I use 'DESC'?

SELECT * FROM users
    WHERE (name LIKE '%bob%' OR description LIKE '%bob%')
        AND (name LIKE '%alice%' OR description LIKE '%alice%')
    ORDER BY posts DESC

Awesome, thank you very much your code is very strightfoward.

Literals (e.g. "foo", 42) are different from constants.

Ah. So I should define the variable $password and then assign it the literal "password"? I'm curious if this distinction is pertinent if I am using the 0600 or 0700 permission to restrict access already.

To xav0989:
Thank you, I'll peruse w3schools as well, it appears very well organized from the first run though.

To xadrieth:

they are used for OOP style of programing.

Ah, funny there should be two completely different ways of doing something like this.

I would also say to pick up a good PHP/MySQL book, like Web Development 4th ed. from Sams.

Thank you, I will try to stop by my local bookstore soon to see what they have in stock.

procedural style might be better if your first learning how to code PHP.

Yeah, I think I'll try to stick to procedural style where I can because it makes more sense in my mind.

 

[xav0989]

Ah, I see it now. Are you sure I should go with 0600 and not 0700? It seems like what I'm trying to do is read the password.php file and then execute the method getUserPassword() to return the password from it, so I would need execution privileges in that case?

You misunderstood something here. Execute permissions are meant for programs. On Windows, it's the exe files. Since php is an interpreted language, the code in files in simply read and complied by the php executable, which has, execute permission. A simple trick for *nix ports: if your program needs to be compiled first, it requires execute permission. Else you programs only require read permission.

Perhaps it would be best if I explained what I am trying to do:
I want to start out with three systems (tables). The first is a list (so only one column "userName") of usernames (2 million of them that I already have). I'm hoping the size of the list won't slow anything down too much, I'm still unsure how exactly to get the names into the database at the moment since I haven't researched that yet, and the names are all stored in text files on my computer. The usernames have characters 0-9, a-z and - in them only (so a byte for byte comparison shouldn't be a problem there).

The second table will have three fields: 'userName', 'description' and 'keyWords'. Each of these fields is a string, and description and keyWords is pretty much unlimited for what kinds of characters may appear. This table is mostly for users who want to enter their own info, I currently have about 400 users lined up for this table, so I don't expect storage space to be too much of a problem here.

The third table is a stats list (data from the user's profile page). So things like #posts, #pictures, #profileVisits, as well as userType ('Artist', 'Programmer'... though these titles are fixed [there are only about 200 possible titles], so I was thinking of assigning the title a number to save space in the database), and tagLine (a short exclamation in string format), location, gender, userType (Admin, Mod, Standard), among others (i can list all the fields if that would help). This table is here to provide the data used to sort the users by (though they can also be sorted by username).

You could easily combine the 3 tables into one. This will make searching, updating, deleting, managing way more simple, both from the code perspective and the administration perspective. If an user opts-out from the keyword and description fields, simply insert null instead of data. If you give us a list of all the fields, we might be able to create an SQL query to create the table.

You also had concerns regarding the number of results that your databases will contain. Well, let me tell you that the more results are in a database, the more significant the speed improvement is over regular flat files.

So this is the backend, the front end is a Flash application that you can enter in a search string ("bob alice") and choose which fields to search through (any combination of username, description, key words, tag line), then you can remove users from the results (so you will only see users from one location, of one gender, one category, or one userType) then you can sort the results by #posts, #profileViews, etc and finally you will have the option to reverse the results list (so people with the least number of posts come upfirst rather than last -- although I was thinking of limiting the number of results returned to 1000 like Google since I have a limited amount of bandwidth).

I was about to warn you that flash was client-side, but you seem to have found a work around for that problem.

From that last paragraph, I have a couple questions if I may: I am curious if there is a way to only return 50 results from a query at a time. So a user can search for 'bob', see the results page in the Flash app, but only 50 results will be sent at a time to reduce bandwidth (so when they get the 40th record or so, the app will send out a signal to the PHP file to retrieve the next 50 results).

After the ORDER BY clause, add

LIMIT starting_number, 50

where starting_number is the first result you want to display: 0 for 0-50, 50 for 51-100, 100 for 101-150, etc.

The other problem with that strategy of 50 results at a time that concerns me is processing power. I plan to have many users accessing this database at once, so that already is a strain (I suppose) if they are searching 2 million users. However, I am worried about how to store PHP info temporarily without sending it to the user. If I have the PHP file search the database and get the first 1000 results, then only send the first 50 to the user, when the user asks for the next 50 results, I would rather have the 1000 results stored temporarily somewhere so that the user is not forced to wait for the database query to execute again (in order to find the same set of 1000 users, but this time return users 51-100).

You could try caching the results in raw format. Basically, when a user queries some data, you order results by id. You save the results as serialized arrays in a file. This way, each time the user makes a request, your script checks the last modification time of the associated cache file and if the file is too old, it queries the server again and saves the results to the file again. Once the script has the results, it orders it by the requested parameter and return results, possibly as xml, as it is easily read and processed.

Ah. So I should define the variable $password and then assign it the literal "password"? I'm curious if this distinction is pertinent if I am using the 0600 or 0700 permission to restrict access already.

Security should always be a concern. I am concerned about security in files that are not even in the publi_html folder. The 0600 and 0700 won't do a difference since the script is not executing by itself. PHP reads it and executes it.

 

[misson]

You misunderstood something here. Execute permissions are meant for programs. On Windows, it's the exe files. Since php is an interpreted language, the code in files in simply read and complied by the php executable, which has, execute permission. A simple trick for *nix ports: if your program needs to be compiled first, it requires execute permission. Else you programs only require read permission.

Adding a little: Setting up a PHP page as executable is not only useless, it's harmful. The execute permission lets you use the name of a file as a command name, though the system still needs to figure out how to execute the file, which involves looking for magic numbers. For scripts, the magic number corresponds to the ASCII code for the shebang characters "#!". For your PHP script to be executable as a command, it must start with a shebang, which PHP will end up printing if you don't run the script as a command. The Apache PHP handlers don't run the script as a command, so the shebang line will be included in the output.

If you're running a PHP script as a cron job, you might end up adding a shebang line and setting +x on the script, but Perl and Python are better choices (IMO) for general scripting.


As for MySQL performance, adding indexes on the fields you search and sort on can also help, at the cost of greater disk usage. The MySQL forum has a sticky with links to MySQL performance tuning documents.

Flex might help you develop this web app quicker, though it can be tricky to develop without Flex Builder (free 60 day trial, $250 for license). You can do it with Eclipse, but it's not as useable. If you want to see what's possible, check out Comparatio's Prophet 21 framework demo.

 

[ParallelLogic]

To: xav0989

A simple trick for *nix ports: if your program needs to be compiled first, it requires execute permission.

Ah, I think I see what you are saying. I think my misunderstanding was in thinking the PHP code I'm writing was a stand alone program, but from my understanding of what you're saying it needs to be read/interpreted by a PHP reader.

simply insert null instead of data

Hmm, I hadn't really considered that, I was afraid that too many null values would take up more space I think.

If you give us a list of all the fields, we might be able to create an SQL query to create the table.

Here's a link to it: http://parallellogic.deviantart.com/art/deviantSEARCH-WIP-113454170 . I'm planning on having a field in the database for each searchable/sortable/filter listed there. There are also a couple hidden ones you can't search for, like special awards that will appear at the top right of the results page for each user (I was thinking of storing that as a number since that should be faily compact). I was also planning to include a field where users who link to my project get promoted and appear ealier in the search results (so if a user is sorting by user age, 1-year-old users who promote the project appear before ones who have not) (stored as a boolean ideally). I will also need a field for the location of the user's avatar (to check to see if it's a jpg, gif, png...). I also need to add in a field for #Critiques under the Sort By menu.

I also talk a little bit about it here (like how much space I was allotting for each user in the middle of that post): http://comments.deviantart.com/5/24593814/1058676412 (though that post is a couple months out of date, so I have made some modifications to my plans since then).

the more significant the speed improvement is over regular flat files.

Haha, that's the main reason I'm going with the database, and there's no real way for me to access that kind of information from multiple users at once besides including it in the Flash file itself, which isn't possible given the quantity of data in question.

I was about to warn you that flash was client-side, but you seem to have found a work around for that problem.

I have run some tests and know I can communicate between Flash and PHP, I'm just concerned about communicating between PHP and MySQL.

You save the results as serialized arrays in a file.

Perhaps I should save the search results as an entry in a new table in the database? I'd prefer not have to deal with text files stored in unknown locations if I can. And an entry in a table would make finding old search strings easier to find and remove...

The 0600 and 0700 won't do a difference since the script is not executing by itself.

Ah I see, I'll be sure to use the literal then.

To misson:

Setting up a PHP page as executable is not only useless, it's harmful.

Alright that makes sense, I think you lost me a little with the higher principles there, but I get the general idea of what you're saying, I'll stick with the 600 permission set

adding indexes on the fields you search and sort on can also help

Hmm. Well, I can assign a sequential numbering system to each record added, but each username also has a unique username, so would it really be that inefficient to use the username as a key? From the link you provided, I only saw one presentation about indexes and from that it looks like sequential numbering may not be that desirable since it's clustered where names would not be.

Flex might help you develop this web app quicker

^^; Thank you for the offer, but this is more or less a one-time project and I wouldn't have a use for the program after this. I may return to this if I find something in the future that requires the same level of work, but for now, I'd rather just muddle through this the best I can.

 

[misson]

Hmm. Well, I can assign a sequential numbering system to each record added, but each username also has a unique username, so would it really be that inefficient to use the username as a key?

Your choice of primary key doesn't affect which other columns to index (indices on other columns are sometimes called "secondary indices"). You can use the username column as the primary key and index the other columns for performance.

You can create secondary indices when you create the table:

CREATE TABLE users (
  name VARCHAR(16) PRIMARY KEY,
  description VARCHAR(256),
  ...
  INDEX (description(48)) -- index only the first 48 characters
  ...
);

or after the table has been created:

ALTER TABLE users ADD INDEX (description(48));
-- Equivalent statement:
CREATE INDEX ON users (description(48))

Note that for some tests, an index won't help. "How MySQL Uses Indexes" gives more details and is particularly helpful in learning how to ensure you're using indices properly. The rest of the MySQL manual section on optimization should prove enlightening.

From the link you provided, I only saw one presentation about indexes and from that it looks like sequential numbering may not be that desirable since it's clustered where names would not be.

"MySQL Performance Tuning - Best Practices:" (listed on that forum sticky) also discusses indexes. Some of the others may as well--I haven't checked.