help with inserting into MySQL Db.

[bazz]

OK, I have used phpMyAdmin to add a new record to the DB.

I have then used the query code that it showed me and added it to my perl script, to try to get my perl file to insert the new record. It seems to do nothing and I think this is a perl question more than a mySQL one.



my $dbh = DBI->connect("DBI:mysql:$db:$server:$port", $user, $pass,
{RaiseError => 1, PrintError => 0, AutoCommit => 1})
or die "Can't connect: DBI->errstr()";

# all is well!
#print "Success: connected!<br />\n";
my $sql = INSERT INTO `Account` ( `AccID` , `User` , `Password` , `Email` , `First_Name` , `Last_Name` , `My_Team_Name` , `League_Name` , `Budget` , `Rider_1_Name` , `Rider_1_Points` , `Rider_2_Name` , `Rider_2_Points` , `Team` , `Team_Points` , `Tyre` , `Tyre_Points` , `First_Retire` , `First_Retire_Points` , `Pole` , `Pole_Points` , `Total_Points` )
VALUES (
NULL , '', '', 'johnny@mydomain.com', 'johnny', 'Rotten', '', '', '45', '', '0', '', '0', '', '0', '', '0', '', '0', '', '0', '0'
);

$dbh->disconnect;




Two questions: -

1. Why does this throw up an error? after all, I copied and pasted from phpMyAdmin,
2. Is it the shortest code for the job? It looks a bit verbose.

bazz

[Rakish]

you have to prepare the query and then execute it .

here is an example:

my $q1 = "insert into test (id, name, subject, num) values (
'', '$qname', $subject, $num)";



my $in = $dbh->prepare($q1);
$in->execute;

[FishMonger]

Rakish's example can be also be done like this:

my $in = $dbh->prepare("insert into test (id, name, subject, num) values ('', '$qname', '$subject', '$num')");
$in->execute;

or like this:

$dbh->do("insert into test (id, name, subject, num) values ('', '$qname', '$subject', '$num')");

Using $dbh->do combines the prepare and execute into 1 statement.

[FishMonger]

Bazz,

Without knowing anything about your database, I can't be 100% positive, but looking at your insert statement, I'd say that there's about a 98% assurance that your database is not normailized. Based solely on your field names, I'd say that this table should be split up into at the very least 2 tables and more likely 3 or 4 tables.

Also, you may want to structure the database so that some of those fields are assigned default values, such as 0 or NULL, if they're not specified in the insert statement. By having a default value you could drop a large portion of those fields (all the ones that are '' or '0') from that insert statement.

[Rakish]

As Said by FishMonger, the database doesnt seem to be normalized,

Please read this article before going further with the coding:

http://www.geekgirls.com/database_dictionary.htm


-Rakesh

[bazz]

Thank you both.

re: query: FishMonger I used your code, which works perfectly. YO!!


$dbh->do("insert into test (id, name, subject, num) values ('', '$qname', '$subject', '$num')");



re default field values: the zeroes are default values and with FishMonger's code, they are entered automatically to the new record.

re: normalisation. well, I have wondered about this and thought I had it sorted.
The project that I am learning on is for a fantasy F1 game.

The accounts table contains data for each member: Acc-ID, User, Password, Email, First_Name, Last_Name, My_Team_Name, Budget, Driver_1_Name, Driver_1_Points, Driver_2_Name, Driver_2_Points, Team, Team_Points, Tyre, Tyre_Points, First_Retirement, First_Retirement_Points, Pole, Pole_Points, Total_Points.

There are other tables but if I can get this one normalised I will be capable of knowing, with certainty, whether they are normalised. KI think they are eg:

Driver_Table
DriverID|Driver_Name|Driver_Cost


Are the 'non-normalised' fields, all those fields after Budget? I thought initially, that I should use 'join' to other tables to compile the results at the time of sending a query but then, after a question in the MySQL forum, I worked out that this won't always work accurately, especially if a player changes drivers, for example, mid-way through the season.

So I thought that retrieving the actual data (fields after Budget), from within the accounts table, could actually make for a more efficient Db because it would reduce the number of 'things' the Db had to do to provide the result. However, I am prepared to stand corrected by those of you, who are much more experienced. :)

At the risk of this being in the wrong forum, now, how would you suggest that I split this table?

The Driver_1_Name could be returned by using a foreign key but, is that not going to be duplicated anyway, where more than one person chooses the same driver?

Each of the points fields are cumulative numbers ie they are increased after each race by the amount of points that driver won. The number is not relative specifically, to any one driver because a player may decide to change drivers mid way through the season. Your advice here is requested also.

Apologies for the long post and my lack of ability to put into practice, normalisation.

bazz

[bazz]

Rakish, I have looked at the link you posted, re. normalisatin and for some reason, today, I am starting to make sense of it. Yesterday it seemed just like gobbledegook :(

Thanks.