Query Problem

[boxxertrumps]

Im Puzzled By this error, happens when i add an entry
Unknown column '(inputted title)' in 'field list'

Heres The relevant Code

-----------Catches Data------------------
    if ($_POST["post_user"] != "") {
        $user = $_POST[post_user];
        $pass = $_POST[post_pass];
        $title = $_POST["post_title"];
        $entry = $_POST["post_entrys"];
        $statement = "INSERT INTO `$user` ( `title` , `entry` ) VALUES ( `$title` , `$entry` )";
        login( $statement , $user , $pass );
        echo" Entry added";
    } else { echo"Input Username"; }; 
---------------Login Function--------------
function login($action , $user , $password) {

$userInfo = mysql_query("SELECT * FROM `login` where `user`='$user'");
if (!mysql_num_rows($userInfo)) {echo 'No such user!';}
else {
    $q = mysql_fetch_array($userInfo);
    if ($password != $q[password]) {echo 'wrong password';}
    else { mysql_query($action) or die(mysql_error()); echo"Query Successful:"; };
}; }; 

i Should Explain How My Blog Works...
i typed up an entire paragraph, looked at it for a bit, then decided to just list the junk.
Include.php

1. Contains Doctype, head section
2. Connects To Database, Defines The Login Function
3. includes menu.php

Menu.php

1. Contains layout
2. left column links to the root, registration page, and the pages to add entries and change your css.
3. Right Column lists users
4. middle column contains different junk depending on the page.

index.php

1. Includes include.php
2. has different pages that Adds Entries
3. Adds Users
4. Changes Personal CSS

action.php handels the data from index.php's forms

Display.php interprets the bbcode,then displays the blog of "$_GET[un]" EG display.php?un=boxxertrumps

[djr33]

Well, you should verify the user first, considering the table you are selecting is based on the variable $user, which seems to be based on uncontrolled input ($_POST['post_user'])

[boxxertrumps]

the username is also a personal table in which the entries for the blog are stored.
heres the registration...

$new_UN = $_POST["post_user_new"];
    $new_PW = $_POST["post_pass_new"];
    $new_EM = $_POST["post_mail_new"];
    if($new_UN != "" && $new_PW != "")
    {
        mysql_query("INSERT INTO `login` ( `user` , `password` , `email` ) VALUES ( `$new_UN`, `$new_PW`, `$new_EM` )");
        mysql_query("CREATE TABLE `$new_UN` (`date` INT UNSIGNED NOT NULL , `title` VARCHAR( 64 ) NOT NULL , `entry` TEXT NOT NULL , `autoID` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY)");
        echo "Account Created";
    } else { echo"<h2>A Feild Is Blank</h2>"; }; 

and the login function does verify the user before the query is sent

[mike_p]

Why use separate tables for each user?

A better design would have a single table for all users' blog entries with a separate field for the user's unique id.

[boxxertrumps]

so that i dont have to rewrite the displaying code.
To Make it Easier To Administrate from PHPMyAdmin.

i just need to delete their private table and their entry in the users table. i dont want to search through one bloated table to kill a user.

[mike_p]

so that i dont have to rewrite the displaying code.
To Make it Easier To Administrate from PHPMyAdmin.

The display code should require very little adjustment.

To administrate PHPMyadmin should be no more difficult. But you will have fewer tables in the database - which should make it easier.

Also you may want to extend it at some time. If you have 50 user's tables and suddenly feel you want to add a field (eg to record the IP address used for each blog entry), you will need to change the structure of 50 tables instead of one.

i just need to delete their private table and their entry in the users table. i dont want to search through one bloated table to kill a user.

Search through a bloated table? A one line query will do it for you:

delete from blog_entries where userid=n

the danger with the system of a table per user is that users may try to create user names that conflict with other database table names or with mysql protected words. (imagine the complications where a user calls himself 'select')

(This is just a small piece of advice from someone who has a lot of experience in designing databases!)

[djr33]

You aren't saving anything by querying part of a database.
From my very basic understanding of the whole setup, a database isn't much more than a text file which is used in a complex way.
So in searching for the user's table among many tables then within that, you're doing just as much as with searching for the all-users table, then into the user's row in that table.

If nothing else, use: 'username-[usernamehere]' for the name of each table, not just the straight username. Or, perhaps a valid symbol, like "%username", but I'm sure % is reserved.... but there must be some symbol available to you... if nothing else, just X will do, or something.

[mike_p]

a database isn't much more than a text file

It's not a text file. It's a binary file that may contain text.

The most important feature of a database in this context is the index. With an index, identifying particular entries is a fast and well optimised process.

So in searching for the user's table among many tables

You are correct because the database holds a table that contains a list of tables within the database. Effectively you are using the database to find your particular user's table.

If nothing else, use: 'username-[usernamehere]' for the name of each table

That is a very sensible and legitimate way around that particular problem!
( I would even use such a simple prefix as 'u_'). Using prefixes for database table names is a great aid for providing semantic distinction of tables.

Hopefully by drip feeding small bits of information about databases to a new comer, it will encourage them to look deeper into how databases can help them: {soap box mode= ON} databases are incredibly powerful tools for programmers and until you understand them, you're unlikely to realise how much easier they can make certain tasks. {soap box mode= OFF}

A further benefit of using a single blog entries table is that you could create a single query that would, for example, extract all blog entries made during a particular month. With seperate tables you would have to query each table individually.

[djr33]

It's not a text file. It's a binary file that may contain text.

Whatever. Same thing, in relation to what I'm saying, and I said 'text file' as that is something everyone is familiar with. My point is that it's just a file, not a maze or network or set of files that efficiency can be used within. Either way, the whole thing is being searched.

A further benefit of using a single blog entries table is that you could create a single query that would, for example, extract all blog entries made during a particular month. With seperate tables you would have to query each table individually.

Agreed.

[boxxertrumps]

so, aside from my less than desirable database format, Can You Tell Me why it Refuses to Post the information into the user table?