Determining what my MySQL tables need

[cgilover]

I bought a premade site years ago and decided to put it back up. Sad to say, I don't have the original database and the original programmer changed their email address.

I can't get my CGI scripts to work anymore because they require special MySQL "stuff". Sorry for my impressive knowledge on this topic! As you can see, I know NOTHING about MySQL.

I'm going to post all the lines relating to my MySQL syntaxed lines.. can someone tell me from these what exactly I need in my table(s)?

I realize this seems to be a lot of code but ANY help would be appreciated. Remember, this was stripped from my code, this isn't the actual complete script.

And to be more helpful, this is the error I get and this is what my tables look like right now.

Error: Column count doesn't match value count at row 1 (this is what happens when using a web-based form to add a new category with category name, icon and description (possibly id))

Database structure:
Table: edt_categories
Field: name varchar(25)
Field: descrption varchar(100)
Field: icon varchar(100)
Field: id varchar(20)

Table: edt_users
Field: username varchar(5)
Field: user_password varchar(32)

my $dbh = DBI->connect("DBI:mysql:$edt_dbase", $mysql_user, $mysql_pass) or print DBI->errstr;
my $sth = $dbh->prepare("SELECT * FROM $users_table WHERE user_id = '$user_id' AND user_password = '$userpass'");
$sth->execute;


my $sth = $dbh->prepare("INSERT INTO $categories_table VALUES (NULL, '$catname', '$catdesc', NULL, NULL, '$caticon')");
$sth->execute;


my $sth = $dbh->prepare("SELECT LAST_INSERT_ID()");
$sth->execute;


my $sth = $dbh->prepare("UPDATE $categories_table SET name= '$catname', description = '$catdesc', icon='$icon' WHERE id = $catid");
$sth->execute;


my $sth = $dbh->prepare("DELETE FROM $categories_table WHERE id = $catid");
$sth->execute;


my $sth = $dbh->prepare("UPDATE $pictures_table SET status = 2 WHERE id = ?");
my $sth_d = $dbh->prepare("DELETE FROM $pictures_table WHERE id = ?");


$sth = $dbh->prepare("UPDATE $pictures_table SET category_id = $cat_id, subject_name = '$subject_name', subject_email = '$subject_email', subject_age = 

'$subject_age', description = '$description', poster_name = '$poster_name', email_public = '$email_public' WHERE id = $pic_id");
$sth->execute;


$dbh->do("DELETE FROM $pictures_table WHERE id = $pic_id") or print "Database error: " . $dbh->errstr . "<br>\n";


$sql = "SELECT user_password FROM $users_table WHERE user_id = $user_id";
$sth = $dbh->prepare($sql);
$sth->execute;



$sql = "INSERT INTO $users_table VALUES('NULL', '$username', '$pw', '$email', '1')";

$dbh->do("DELETE FROM $users_table WHERE user_id = '$delete_id'");

$dbh->do("UPDATE $users_table SET username = '$edited_name', user_email = '$edited_email' WHERE user_id = $temp_id");

my $sth = $dbh->prepare("SELECT MAX(link_order) AS maxorder FROM $links_table");
$sth->execute;

my $sql = "INSERT INTO $links_table VALUES (NULL, '$name', '$url', '$info', '$email', $order)";
$dbh->do($sql);


$sql = "DELETE FROM $links_table WHERE id = '$delete_id'";
$dbh->do($sql);
$dbh->do("UPDATE $links_table set link_order = (link_order - 1) WHERE link_order > $deleted_order");


$sql = "UPDATE $links_table SET name='$name', url='$url', info='$info', contact_email = '$email'  WHERE id = $edit_id";
$dbh->do($sql);

$dbh->do("UPDATE $links_table SET link_order = " . $changed_orders{"$id"} . " WHERE id = $id");

 

[Deleted]

Column count doesn't match value count at row 1"

Check your INSERT's.. Make sure that the column count is equal to the data being entered.

EX:

INSERT INTO EXAMPLE (A, B, C, D) VALUES (1, 2, 3, 4)

http://unixjunky.com

 

[cgilover]

Yep.

I know what the error means, I just don't know what's wrong and what to add/change.

This is the actual code that's failing

<code>
my $sth = $dbh->prepare("INSERT INTO $categories_table VALUES (NULL, '$catname', '$catdesc', NULL, NULL, '$caticon')");
$sth->execute;

if ($sth->errstr)
{
print "burp<br>";
print $sth->errstr;
exit;
}
</code>

This is the one that when executed complains about the column count not matching up. I have a categories table as you can see from my original post and I tried adding the fields inside the table by guessing. Can you tell by this snippet how many fields is needed?

 

[sleipnir214]

Your INSERT query reads as

INSERT INTO <table name> VALUES (....)

and there are 6 values listed.

But the definition of the table only shows 4 columns. If you use an INSERT query and don't specify the columns into which the data must be inserted, you must specify data for every column.

So, either your table needs 6 columns, your INSERT query needs 4 pieces of data, or you need to change the INSERT query to specify column names.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[cgilover]

So I need 6 fields? Well that's a start, thanks for that information!

But now I feel very discouraged because if it's adding NULL to some fields and values to some others (without names), that means everything has to be in a set proper order. There's no way I can figure all that out

Thanks for your help.

 

[sleipnir214]

To be specific, that one INSERT query implies that the table should have a minimum of 6 columns. Other queries may require additional columns in that table.

Want the best answers? Ask the best questions!

TANSTAAFL!!