I've gotten a lot better at PHP since I last posted, but I still need some help here and there. (If you help me, you will be credited in the eventually GPL-ed source code)

There's a project that I'm working on that is largely database-driven.

Here's how the database will eventually be set up:

There are multiple educational classes offered by the provider of the website.

Each class will have its own table in the database. Originally, everything would have been kept in one big table, but I scrapped that idea to improve performance. (by keeping everything in separate tables, I figure that queries will run faster since the database engine won't have to slog through everything to find a particular record.

However, I'm having trouble setting up the targeting system.

I'm planning for the user to be able to select which table a query will be applied to by choosing the table from a drop-down list that will automatically update as tables are added/removed.

However, here is my current problem:

1. I'm not sure how to list all tables in the database in a HTML drop down menu. I'm certain that it involves using while , but I'm not really sure how to go about it.

I'm also not sure how to do this as with plain text, either. I wrote a mini script to test it, but it didn't work either (output was: "Resource ID #3)



FYI, I tried to hide the database login stuff in an external PHP file kept in a non-public directory like someone told me to do once, but I wasn't sure how to reference a protected directory in the script, and my attempt didn't work. (I tried to use one that was above the public_html directory in the file tree) Help with that would also be appreciated.

Thanks in advance.

As with the JS thread, I'm not so familiar, but couldn't you just make a reference table with pointers? If I recall, that's a handy method of maximizing performance. If this isn't even close, ignore this post. I really don't know what I'm talking about.

You need to actually fetch the data.
the mysql_query command returns a result set.

(I've never actually done this with the show command but it should work.)

Something like:

mysql_query returns a handle. You need to pass this handle to other functions (such as mysql_fetch_row) to get meaningful data.

Once you have printing a list as plain text down, you use a <select> tag with a closing </select> to indicate a drop down list, and inside that <option>TEXT</option> tags for items. I suggest you STFW (Google) for more details on the tags and their usage.

Aren't you like a web administrator mcguy?

You may get a performance boost from splitting into multiple tables but it means you're moving away from a decent database schema and the performance boost will be negligable unless your queries are extremely complex. Generally if you're having to hard code part of your configuration you're probably doing something wrong.

How would you go about doing this? The database is practically empty right now and I have yet to build most of the multi-table infrastructure, so it wouldn't be a problem to change things around at this point.



Thanks for the code snippet. I'll try it and then report back when I get a chance.

Well that depends, if in your scenario, each class will have different fields then yes multiple tables are the way to go. Though you'd still want to keep any common fields in their own table, then create tables linked to that. It will make querying much easier, the data better organised and generally make the database easier to work with..


If all the tables are going to have the same fields.. then don't split into multiple tables.

Queries may run faster on tables with less rows, but when you need to combine multiple tables to compare different classes the query will actually run slower.


And how many records are you talking about here? At work I deal with tables with hundreds of thousands of records and the performance is acceptable.

The best way to make tables is object oriented.

That is, every "object" type should get it's own table.

Example: If you make a forum DB, you'll want a table for posts, one for members, one for threads, and one for subforums. Then maybe some others like a group privilege table and some other misc stuff.

Making a "customerandorder" table doesn't fit, and it would be better to make a "customer" table and an "order" table and link them with a foreign key.

Basically, if you end up storing redundant data, you should reconsider your database design. It's best to store data once so you only need to change it once if need be.

What you said is correct... mostly... however,

You can't have an object oriented design in a relational database... it just doesn't work. Now, if you use an object database such as Matisse or O2 you can get object like functionality (Although don't use an object database because none of them are actually good for non-trivial systems (yet)).

Much less than that. I'm only expecting several hundred records, up to a thousand at the most.

From what several people have said, it looks like it would be better to go back to the original plan, so I'll probably end up doing that soon.

Another unrelated problem that I just can't get rid of:

I've been having trouble setting up Mysql escape strings. (as you know, these are needed to prevent SQL injection, IIRC) Here's my current code, which causes errors for some reason:



Here is the error:



Did I get the syntax wrong? I basically copied it straight from the PHP website.

Is there a better way to do it?

Ok, there are a number of problems with your code

1) having mysql_query() inside mysql_fetch_*. This is fine if you are only expecting one record to be returned and not using it in a loop. The problem is that if you have it in a loop, every time it's called it will re-run the query so if you're using it in a while loop it will run infinitely.

2) mysql_fetch_assoc only takes one parameter you're sending it three.

3) using select * is not really considered good practice, but that's a completely different issue.

What you're trying to do is this:




I don't think you need the quotes around after escaping, but i'm not sure about that.

The code you provided works without interpreter errors, but it still won't login properly. (Usernames/passwords that I know are valid still won't work)

You should absolutely (and in many cases must) have quotes around your mysql_real_...()'ed data in the query.

Pagewizard: just for kicks, run it without ANY protective functions at all first and see if it works. If it does, you're having issues with the way it is being escaped. If it doesn't, you're having issues with how you're passing your username and password to it.

It has always worked fine without any protection at all, I can't just leave it like that for the final deployment. Right now the database is empty, (apart from a few fake entries for testing purposes) but after there are several hundred actual records in it, I don't want to be the one that has to tell my client that someone nuked the database and my code allowed it to happen.

I didn't tell you to run it in production like that. I said to test it, and fix it. >.>

Have you treid echo mysql_error($con); after the query?

Test your query out to see if it really is good. If you are expecting results but not getting them, it can be a fault with the query. If the query is not at fault, check to see if your variables are passing data.

Does MySQL have like a tracing utility? SQL Server has "Query Profiler" which allows you to see what queries are being executed. This is really useful for websites and what not.

MySQL has Query Browser, which comes with the standard win32 mysql download.

Ah good. Turn that on and trace the queries sent to MySQL. This will tell you what exactly PHP is sending to MySQL. I'll bet you a cookie and a can of beans that $user and/or $pass are not being passed properly.

I'll check the server mysql logs and report back.

I just found out something else:

Using mysql_real_escape_string($variable); all by itself outside of a query (placed a few lines before the mysql statement itself) produces the same result as it did when embedded in the query.

If I could get this working, would this method give me the same security as the other way?

You'd have to call it like[php]$variable = mysql_real_escape_string($variable);[/php] to set the variable or if you want to keep it tidy [php]$safe_variable = mysql_real_escape_string($variable);[/php].
I always do it like this, makes it easier to read, IMHO.

exactly what Impi said.. and for testing purposes just echo your variables, no need to go out of your way to see what mysql is doing.

I always throw my queries in a string so I can echo the query and manually put it in my SQL program (SQLyog, and nice DB management prog) and test the query.. like recently I've been working on scripts that have many variables in place and the query changes often. This helped out a lot during my troubleshooting when I had issues.

here's an idea I had..

[error]

That will cause an error because it will escape the single quotes.

I know..