Jump to content

DB Error: no such field


dmaar

Recommended Posts

I'm having a bit of trouble using a simple form I created in conjunction with phpMyAdmin.  I have buttons on the form that do different things, but I have one in particular that is giving me a bit of trouble.  The button is used to input new products into the table using fields above the buttons, but I keep getting "DB Error: no such field".  I'm an extreme newbie, so if someone could take a look at the code and help me out I would appreciate it.  I'm having some other problems with the form as well, but can't even try to do anything about those until I get this one fixed.

 

HTML:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<title>Mar Audio</title>


</head>

<body>
<form action="database.php" method="post">

<h1 align="center">Table Creation / Editing Form</h1>

Product Name: <br/>
<input type="text" name="name"/> <br/>

Category: <br/>
<input type="text" name="category"/> <br/>

Price: <br/>
<input type="text" name="price"/> <br/>

<input type="submit" value="Create Product Table" name="create"/> <br/>

<input type="submit" value="Delete Table" name="delete"/> <br/>

<input type="submit" value="Input Product" name="input"/> <br/>

<input type="submit" value="Update Product" name="update"/> <br/>

<input type="sumbit" value="Display Table" name="display"/> <br/>

<input type="reset" value="Clear All Fields"/>

</form>
</body>
</html>

 

 

PHP (current problem occurs under the "else if (isset($input))" part:

$db = DB::connect('mysql://sequela:777956@localhost/sequela');

$db->setErrorHandling(PEAR_ERROR_DIE);

if (DB::isError($db))
die("Can't connect:".$db->getMessage());
else
print("Was able to connect");



extract($_POST);


if(isset($create))
{

$q=$db->query("CREATE TABLE products
(
auto_increment INT,
product_name VARCHAR(225),
product_category VARCHAR(225),
price DECIMAL (4,2)
)");

if ($q)
print('<br/>Products Table was Created');
}


else if (isset($delete))
{

$q=$db->query("DROP TABLE products");


if($q)
print('<br/>Products Table was Deleted');

}


else if (isset($input))
{

$q=$db->query("INSERT INTO products (product_name, product_category, price)
	VALUES($name, $category, $price)");
if($q)
print('<br/> Product Was Added');

}

else if (isset($update))

$q=$db->query("UPDATE products SET price = price - 100 WHERE price > 999.00");


else if (isset($display))

?>

 

Link to form:

http://mktg-content.bsa.kent.edu/~sequela/insertproducts.html

 

Thanks guys, I appreciate it.

 

Link to comment
Share on other sites

That error along with the info you provided would indicate to me that one of these: (product_name, product_category, price) is not a field in the database.  Go to phpmyadmin or your mysql prompt and do a describe table query and compare those fields with what's actually in the table.

 

It could be very something very little.

Link to comment
Share on other sites

Couldn't figure out how to do a describe table query, but in phpMyAdmin under "field" for the products table is:

 

auto_increment

product_name

product_category

price

 

So it seems to be good as far as actually having the fields.  Could it have something to do with the way I set up auto_increment in the code?  Not sure I did that right.

Link to comment
Share on other sites

I don't know...

 

I perused all of your code and this part sticks out to me:

 

else if (isset($update))
$q=$db->query("UPDATE products SET price = price - 100 WHERE price > 999.00");

 

I think you at least need parentheses around price - 100...but double check the rest of the query as well.

Link to comment
Share on other sites

The error doesn't do a very good job of explaining what is going wrong, all it says is "DB Error: no such field".  I posted a link to the form in my original post, if that helps.

 

I don't know much, but I would guess that there is some discrepancy between my original "create" code and the "input" code...but I really can't find it.  That may not even be it though, I have no clue.

 

Link to comment
Share on other sites

one thing that sticks out to me is your creation code

if(isset($create))
{

$q=$db->query("CREATE TABLE products
(
auto_increment INT,
product_name VARCHAR(225),
product_category VARCHAR(225),
price DECIMAL (4,2)
)");

if ($q)
print('<br/>Products Table was Created');
}

 

you never gave your auto_increment field a name (or specify the length of the INT field. Perhaps you should check PHPMYADMIN to verify the table is created, and then look at your auto_increment field. If all else fails, try dropping the table, and change your creation code to have a name for that column. Something like

if(isset($create))
{

$q=$db->query("CREATE TABLE products
(
id auto_increment INT(10),
product_name VARCHAR(225),
product_category VARCHAR(225),
price DECIMAL (4,2)
)");

if ($q)
print('<br/>Products Table was Created');
}

 

should do. After you change the creation code, try creating the table again with the code, and then try inserting data into the table.

 

Link to comment
Share on other sites

the second thing you said gave me a syntax error, but the first worked perfectly. Thanks a lot, you really saved my ass.

 

One quick question...my display button is showing everything except the ID numbers...and auto_increment shows as null in phpMyAdmin....I'm guessing that has something to do with what you said?  Is there a way to fix this?

 

 

Link to comment
Share on other sites

I think it was the fact that I didn't have those single quotes like you said.

 

One quick question...my display button is showing everything except the ID numbers...and auto_increment shows as null in phpMyAdmin....I'm guessing that has something to do with what you said about not having it named?  Is there a way to fix this?  What you told me to do before was giving me a syntax error.

Link to comment
Share on other sites

I tried this, but it was giving me a syntax error as well:

if(isset($create))
{

$q=$db->query("CREATE TABLE products
(
id MEDUIMINT NOT NULL AUTO_INCREMENT,
product_name VARCHAR(225),
product_category VARCHAR(225),
price DECIMAL (4,2)
)");

 

Link to comment
Share on other sites

Oh wait, do you mean in the SQL tab in phpMyAdmin?  Sorry, this is pretty much my first day using phpadmin, so not all that familiar with how it works.  Here's what it spits out:

Error
There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem

ERROR: Unknown Punctuation String @ 30
STR: ->
SQL: if(isset($create))
{

$q=$db->query("CREATE TABLE products
(
id auto_increment INT(10),
product_name VARCHAR(225),
product_category VARCHAR(225),
price DECIMAL (4,2)
)");if(isset($create))
{

$q=$db->query("CREATE TABLE products
(
id auto_increment INT(10),
product_name VARCHAR(225),
product_category VARCHAR(225),
price DECIMAL (4,2)
)");if(isset($create))
{

$q=$db->query("CREATE TABLE products
(
id auto_increment INT(10),
product_name VARCHAR(225),
product_category VARCHAR(225),
price DECIMAL (4,2)
)");if(isset($create))
{

$q=$db->query("CREATE TABLE products
(
id auto_increment INT(10),
product_name VARCHAR(225),
product_category VARCHAR(225),
price DECIMAL (4,2)
)");

SQL query:

if(isset($create)) { $q=$db->query("CREATE TABLE products ( id auto_increment INT(10), product_name VARCHAR(225), product_category VARCHAR(225), price DECIMAL (4,2) )");

MySQL said: 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'if(isset($create))
{

    $q=$db->query("CREATE TABLE products
    (
    id auto_inc' at line 1 

 

Link to comment
Share on other sites

Your probably getting that error because there was a syntax error in your SQL. I believe that for mediumint data types, you have to specify the length (remember what I said in my post earlier?) so perhaps your creation code should look like

$q=$db->query("CREATE TABLE products
(
id MEDUIMINT(10) NOT NULL AUTO_INCREMENT,
product_name VARCHAR(225),
product_category VARCHAR(225),
price DECIMAL (4,2)
)");

 

If thats not it, then im not entirely sure, as my SQL is a little rusty.

 

 

EDIT:

 

um.. if your trying to copy paste that PHP code into the SQL console it wont work. PHP is NOT SQL. try just copying the query part into your SQL console

Link to comment
Share on other sites

Haha, sorry...you can probably tell how much of a newbie I am.  Anyways, this is what I got when i switched it to what you said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MEDUIMINT(10) NOT NULL AUTO_INCREMENT,
    product_name VARCHAR(225),
    product_ca' at line 3 

 

Seriously though if you don't know what it could be don't even worry about it.  It's not that big of a deal, I fixed the biggest problems.  I appreciate all the help you guys have given me, you've saved my ass more than once.

 

Link to comment
Share on other sites

Yeah I was wrong, you don't need to specify length.

 

Well I tried your query in my phpmyadmin console, and got the same error. There seems to be 2 problems. One, when you create an auto increment column, it must be defined as a key also (like primary key, unique key, etc.)

 

However, this isn't whats causing your error. It has something to do with the mediumint data type. When I try this

CREATE TABLE products
(
id INT(10) NOT NULL AUTO_INCREMENT,
product_name VARCHAR(225),
product_category VARCHAR(225),
price DECIMAL (4,2)
)

I get a different error (the one pertaining to a primary key)

 

when I use this

CREATE TABLE products
(
id INT(10) NOT NULL AUTO_INCREMENT primary key,
product_name VARCHAR(225),
product_category VARCHAR(225),
price DECIMAL (4,2)
)

 

it works. I'm not sure why, but for some reason, using MEDIUMINT doesn't work. I don't see why you are using medium int anyways. it will save a little bit of space because mediumint has a smaller range (so the storage space in memory will be smaller)  but INTs are usually used for primary id keys in my experience

Link to comment
Share on other sites

dmaar,

 

 

We are all on different levels.  We all need help, even the expierenced programers need another eye sometimes.  I even have a few posts with some questiosn.  Keep trying you will get it, it will just take time.  so did you figure out what I meant by clicking on the sql link?

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.