RE: [PHP-DB] Transaction suddenly not working
--- [EMAIL PROTECTED] wrote: > > --- "Ford, Mike" <[EMAIL PROTECTED]> wrote: > Don't know about other database engines, but in > MySQL, it doesn't matter > if an int is inserted as a string, i.e., with the > quotes. My recollection > is that MySQL handles the type conversion > internally. So, assuming you are > validating the entries before inserting, if a value > is missing, assign to > it ''. Whatever the field has as a default value > will then be inserted > into the table. > > dave Well most of these fields are optional and in the db set to allow null. I went ahead and put '' around all the values so just in case. Though I know for a fact it was working without them before. Oh and yes it seems to be working now. Stuart -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Transaction suddenly not working
> --- "Ford, Mike" <[EMAIL PROTECTED]> wrote: > > > 0: INSERT INTO Table1 (LurkID, ProfileName, Edu, > > > WorkAuth, WorkExp, CarLev, Secu, Confi, Relo, > > > Telecomu, City1, State1, City2, State2, > > TravelPref, > > > SalaryAnnual, SalaryHourly, Available) VALUES (47, > > > '', 7, 2, 1015, 5, , '', '', '', 'fds', 5, > > '', > > > > You have 2 successive commas in this last line, with > > no value in between -- > > this is your syntax error. > > > > > , 4, 26, , 2004-02-02)01062 : Duplicate entry > > '0-f' for key 1 > > > > And again on this line -- twice, in fact, since the > > previous line ends with > > a comma, too. > > > Mike are you saying that I should put single quotes > around all values regardless of the type ? > The double commas are happening because around an int > I don't use 'value' the single quotes . ? Don't know about other database engines, but in MySQL, it doesn't matter if an int is inserted as a string, i.e., with the quotes. My recollection is that MySQL handles the type conversion internally. So, assuming you are validating the entries before inserting, if a value is missing, assign to it ''. Whatever the field has as a default value will then be inserted into the table. dave
RE: [PHP-DB] Transaction suddenly not working
--- "Ford, Mike" <[EMAIL PROTECTED]> wrote: > > 0: INSERT INTO Table1 (LurkID, ProfileName, Edu, > > WorkAuth, WorkExp, CarLev, Secu, Confi, Relo, > > Telecomu, City1, State1, City2, State2, > TravelPref, > > SalaryAnnual, SalaryHourly, Available) VALUES (47, > > '', 7, 2, 1015, 5, , '', '', '', 'fds', 5, > '', > > You have 2 successive commas in this last line, with > no value in between -- > this is your syntax error. > > > , 4, 26, , 2004-02-02)01062 : Duplicate entry > '0-f' for key 1 > > And again on this line -- twice, in fact, since the > previous line ends with > a comma, too. > Mike are you saying that I should put single quotes around all values regardless of the type ? The double commas are happening because around an int I don't use 'value' the single quotes . ? Stuart -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Transaction suddenly not working
To view the terms under which this email is distributed, please go to http://disclaimer.leedsmet.ac.uk/email.htm On 29 November 2004 13:19, Stuart Felenstein wrote: > Now the printout of the query is this: > > 0: INSERT INTO Table1 (LurkID, ProfileName, Edu, > WorkAuth, WorkExp, CarLev, Secu, Confi, Relo, > Telecomu, City1, State1, City2, State2, TravelPref, > SalaryAnnual, SalaryHourly, Available) VALUES (47, > '', 7, 2, 1015, 5, , '', '', '', 'fds', 5, '', You have 2 successive commas in this last line, with no value in between -- this is your syntax error. > , 4, 26, , 2004-02-02)01062 : Duplicate entry '0-f' for key 1 And again on this line -- twice, in fact, since the previous line ends with a comma, too. Track down whatever is outputting these null values, fix it, and you should be good to go (well, at least less bad!). Cheers! Mike - Mike Ford, Electronic Information Services Adviser, Learning Support Services, Learning & Information Services, JG125, James Graham Building, Leeds Metropolitan University, Headingley Campus, LEEDS, LS6 3QS, United Kingdom Email: [EMAIL PROTECTED] Tel: +44 113 283 2600 extn 4730 Fax: +44 113 283 3211 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Transaction suddenly not working
Stuart Felenstein a écrit :
--- Stuart Felenstein <[EMAIL PROTECTED]> wrote:
I know recently the server was update from PHP 4.3.8
to 4.3.9 but that shouldn't have effected anything.
Code:
$query = "INSERT INTO table1 (ProfileID, LurkID,
ProfileName, Edu,..."
VALUES (null,..."
ProfileID is the PrimaryID (autoinc) - I've set the
input value to null for the auto-inc to properly
work.
I assume this is correct and it did work at one
time.
Then before the next query:
$LID = mysql_insert_id();
Then each subsequent query uses $LID in the
subsequent
tables.
However $LID is now returning 0 and the transaction
fails.
At the end of the transaction I follow through with
this:
//resx = all the queries
if($res1 && $res2 && $res3 && $res4 && $res5 &&
$res6
&& res7 ) // If all results are true
{
commit();
//echo "your insertions were successful";
unset($all session variable, $LID, );
}else{
...
Puzzling since this script was working great. We did
also upgrad MySQL from 4.0.21 to 4.0.22-standard
I wanted to add - I realize that if there is an error
in the first query (where the id is generated) it
would result in a value of 0
I've echoed out the query and am seeing this:
"Duplicate entry '0-i' for key 1 transaction failed"
Never seen a 0-i before.
Stuart
hi!
what if you try to ignore the primary key in fields and values list ?
MySQL should assign a valid primary key. It's what I'm doing on 4.0.22
and it's working If not, try to echo your query and write it in
MySQL( GUI or console).
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Transaction suddenly not working
--- Stuart Felenstein <[EMAIL PROTECTED]> wrote: One last thing to add on this issue. This table was created using tabletype InnoDB. Now I discovered earlier that this table and (many others) had switched over to MyISAM. I'm still trying to trace down the cause with my ISP. Anyway, using a mysql gui, i put an index, today on the primaryID. Now I"m looking at the database in PHP MYADMIN and under indexes it's showing this : Indexes : Keyname TypeCardinality Field PRIMARY PRIMARY 2 ProfileID ProfileID_2 UNIQUE 2 ProfileID ProfileID_4 UNIQUE 2 ProfileID PID INDEX 2 ProfileID ProfileID INDEX 2 ProfileID ProfileID_3 INDEX 2 ProfileID -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Transaction suddenly not working
--- denys <[EMAIL PROTECTED]> wrote: > hi! > what if you try to ignore the primary key in fields > and values list ? > MySQL should assign a valid primary key. It's what > I'm doing on 4.0.22 > and it's working If not, try to echo your query > and write it in > MySQL( GUI or console). > Tried ignoring , first removing the null and ProfileID column name - result still failure. Now the printout of the query is this: 0: INSERT INTO Table1 (LurkID, ProfileName, Edu, WorkAuth, WorkExp, CarLev, Secu, Confi, Relo, Telecomu, City1, State1, City2, State2, TravelPref, SalaryAnnual, SalaryHourly, Available) VALUES (47, '', 7, 2, 1015, 5, , '', '', '', 'fds', 5, '', , 4, 26, , 2004-02-02)01062 : Duplicate entry '0-f' for key 1 transaction failed When I tried the Insert statement into a mysql gui it spit back a syntax error , apparently for the blank values ..,'', yet those are set to allow null. I went ahead and tried just an insert on the first 4 columns , that worked. Stuart -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Transaction suddenly not working
--- Stuart Felenstein <[EMAIL PROTECTED]> wrote:
> I know recently the server was update from PHP 4.3.8
> to 4.3.9 but that shouldn't have effected anything.
>
> Code:
>
> $query = "INSERT INTO table1 (ProfileID, LurkID,
> ProfileName, Edu,..."
> VALUES (null,..."
>
> ProfileID is the PrimaryID (autoinc) - I've set the
> input value to null for the auto-inc to properly
> work.
> I assume this is correct and it did work at one
> time.
>
> Then before the next query:
>
> $LID = mysql_insert_id();
>
> Then each subsequent query uses $LID in the
> subsequent
> tables.
>
> However $LID is now returning 0 and the transaction
> fails.
>
> At the end of the transaction I follow through with
> this:
>
> //resx = all the queries
> if($res1 && $res2 && $res3 && $res4 && $res5 &&
> $res6
> && res7 ) // If all results are true
> {
> commit();
> //echo "your insertions were successful";
> unset($all session variable, $LID, );
>
> }else{
>
> ...
>
> Puzzling since this script was working great. We did
> also upgrad MySQL from 4.0.21 to 4.0.22-standard
>
I wanted to add - I realize that if there is an error
in the first query (where the id is generated) it
would result in a value of 0
I've echoed out the query and am seeing this:
"Duplicate entry '0-i' for key 1 transaction failed"
Never seen a 0-i before.
Stuart
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
