PHP MySQL communication basics

[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/...-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.