DB Error: no such field

dmaar · April 30, 2010

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.

April 30, 2010

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.

dmaar · April 30, 2010

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.

April 30, 2010

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.

dmaar · April 30, 2010

That shouldn't mess up the other sections to my knowledge, but to be safe I commented it out. Still does the same thing. That part is messy though, I have to clean that up after I get this figured out.

April 30, 2010

I didn't think it would, but it never hurts to check. Could you post the exact error message you get so I can see what you're seeing?

Thanks.

dmaar · April 30, 2010

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.

dmaar · May 1, 2010

I have to have this done my midnight....anyone know what the problem could be?

MidOhioIT · May 1, 2010

try this:

$q=$db->query("INSERT INTO products (product_name, product_category, price) VALUES('$name', '$category', '$price')");

Notice the single quote around the varables in the values.. let me know if that fixes it

MidOhioIT · May 1, 2010

fyi, doing a describe on the table is as simple as clicking on the "sql" link and then doing the following command:

desc TABLE_NAME;

mikesta707 · May 1, 2010

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.

dmaar · May 1, 2010

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?

MidOhioIT · May 1, 2010

Don't forget to mark as solved.

So the issue was the table itself and not inserting records in it?

dmaar · May 1, 2010

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.

dmaar · May 1, 2010

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)
)");

MidOhioIT · May 1, 2010

As sugguest by mike... I don't think your table creation is right for what you want. Do that table creation in phpAdmin and give me the error

dmaar · May 1, 2010

It just says "DB Error: syntax error"

dmaar · May 1, 2010

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

mikesta707 · May 1, 2010

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

dmaar · May 1, 2010

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.

mikesta707 · May 1, 2010

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

dmaar · May 1, 2010

That worked great. Thank a lot, I really appreciate it. This just goes to show how much I have to learn.

MidOhioIT · May 1, 2010

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?

dmaar · May 1, 2010

Yeah I figured it out. I don't know what I was was thinking pasting the PHP code directly in there haha. It ended up all working out though.

Sign In

DB Error: no such field

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information