PDA

View Full Version : Query Problem



boxxertrumps
12-30-2006, 04:28 PM
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
Contains Doctype, head section
Connects To Database, Defines The Login Function
includes menu.php
Menu.php
Contains layout
left column links to the root, registration page, and the pages to add entries and change your css.
Right Column lists users
middle column contains different junk depending on the page.
index.php
Includes include.php
has different pages that Adds Entries
Adds Users
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
12-30-2006, 04:36 PM
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
12-30-2006, 04:54 PM
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
01-03-2007, 11:37 AM
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
01-05-2007, 01:57 AM
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
01-05-2007, 07:57 AM
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
01-06-2007, 06:08 AM
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
01-07-2007, 09:41 AM
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
01-07-2007, 09:36 PM
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
01-09-2007, 01:01 AM
so, aside from my less than desirable database format, Can You Tell Me why it Refuses to Post the information into the user table?

mike_p
01-09-2007, 04:28 PM
I haven't spotted any particluar problems.

What does happen? Does it report any of these?
Entry added?
Input Username?
No such user?
wrong password?
Query successful?

Or does it just die quietly?
Or.....?

boxxertrumps
01-10-2007, 12:04 AM
it says "Unknown column" puts the title inputed here, then "In feild list"
For Example: this,

UN:boxxertrumps
PW:********
Title:WHY WONT YOU WORK!!!
Entry:Please... Help Me.....

Give This text in Action.php

Unknown column 'WHY WONT YOU WORK!!!' in 'field list'

mike_p
01-10-2007, 08:58 PM
Can you confirm that the blog entry form doesn't have the same name/id assigned to both the title and the username input fields (often happens when cutting and pasting text)?

Can you post the entire contents of action.php? (or provide a link to somewhere we can view it?

boxxertrumps
01-10-2007, 09:49 PM
http://www.freewebs.com/boxxertrumps/phpcode.txt