|
That's ok Richard, I posted the message 'cause i haven't seen yours.....
As a matter of fact. I'm really new to MySQL, my DB background is MSSQL, so, in the MSSQL Books Online I found this:
A. Use the IDENTITY property with CREATE TABLE
This example creates a new table using the IDENTITY property for an automatically incrementing identification number. USE pubs
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'new_employees')
DROP TABLE new_employees
GO
CREATE TABLE new_employees
(
id_num int IDENTITY(1,1),
fname varchar (20),
minit char(1),
lname varchar(30)
)
INSERT new_employees
(fname, minit, lname)
VALUES
('Karin', 'F', 'Josephs')
INSERT new_employees
(fname, minit, lname)
VALUES
('Pirkko', 'O', 'Koskitalo')
The only one thing i have to do was to try it in MySQL and fortunately it works.
Anyway, Jeff have 2 options to select, right?...
eLFeR....
-------Original Message-------
Date: Viernes, 05 de Septiembre de 2003 02:19:23 p.m.
Subject: RE: [PHP-DB] Modify MySQL Record
Yup, Fernando is correct. But it is not incorrect to specify a NULL value in
your query for an autoincrement column. Right out of the MySQL manual:
"If you insert NULL into an AUTO_INCREMENT column, the next number in the
sequence is inserted."
Although the MySQL manual implies it by giving examples where rows are
inserted into tables with an autoincrement column, I could not find a spot
where it explicitly states that you don't have to provide a value for that
column in an INSERT query.
Probably just falls into the personal preference category.
-----Original Message-----
Sent: Friday, September 05, 2003 4:00 PM
Subject: Re: Modify MySQL Record
When you have an auntoincremente field in your table, you don't have to
especify the field name in the query, as the following:
$query = "INSERT INTO cnrl_db (kwo, lsd, date, well, field, uni,
license, formation, perfs, event, fluid, mode, type, vhd, file, kb, grd,
open, sour, tube, landed, casing, landed2, shut_date, shut_time, pres, tag)
VALUES ('$kwo', '$lsd', '$date', '$well', '$field', '$uni',
'$license', '$formation', '$perfs', '$event', '$fluid', '$mode', '$type',
'$vhd', '$file', '$kb', '$grd', '$open', '$sour', '$tube', '$landed',
'$casing', '$landed2', '$shut_date', '$shut_time', '$pres', '$tag')";
hope it helps
eLFeR.....
-------Original Message-------
Date: Viernes, 05 de Septiembre de 2003 01:15:21 p.m.
Subject: Re: Modify MySQL Record
Ok, so I added what you suggested, but it still doesn't assign any numbers
to the records.
$query = "INSERT INTO cnrl_db (gradient, kwo, lsd, date, well, field, uni,
license, formation, perfs, event, fluid, mode, type, vhd, file, kb, grd,
open, sour, tube, landed, casing, landed2, shut_date, shut_time, pres, tag)
VALUES ('NULL', '$kwo', '$lsd', '$date', '$well', '$field', '$uni',
'$license', '$formation', '$perfs', '$event', '$fluid', '$mode', '$type',
'$vhd', '$file', '$kb', '$grd', '$open', '$sour', '$tube', '$landed',
'$casing', '$landed2', '$shut_date', '$shut_time', '$pres', '$tag')";
$result = mysql_query($query, $link_id) OR die(mysql_error());
gradient - the auto increment field.
news:1EA7D3AE70ACD511BE6D006097A78C1E033C92B8@USROCEXC...
> Jeff,
>
> When you want to increment an autoincrement field, you just put NULL into
> the query. For example:
>
> INSERT (NULL,'Jeff','php list','Modify MySQL Record') INTO mail;
>
> Where NULL is in the same spot in the field list as your autoincrement
> field.
>
> That concept is covered in the MySQL manual. Pretty simple stuff. If
you're
> in a situation where you have an autoincrement field that you sometimes
> autoincrement and sometimes manually assign a value to, I think you're
> misusing the autoincrement field (IMHO). If you have that situation, use
the
> autoincrement field to assign a unique ID to the table row and use a
> separate field with another name for the manually assigned data.
>
> As far as the stuff you mentioned about clicking things and getting the
> messages you expected, I'm kinda' lost. But if you use the autoincrement
> field as it is intended, the logic should present itself.
>
> > -----Original Message-----
> > Sent: Friday, September 05, 2003 2:07 PM
> > Subject: Re: Modify MySQL Record
> >
> >
> > Ok, so it sorta works now *grin*
> >
> > Two new problems
> >
> > I added an auto increment field in my table and changed the
> > php to reflect
> > that field - But for some reason, it doesn't work..
> > When I submit a new record, do I need to tell SQL to add one to the
> > greatest, or should it do it automatically when a new record
> > is created?
> > gradient MEDIUMINT(10) DEFAULT '0' NOT NULL AUTO_INCREMENT,
> > and I made it the primary.
> >
> > Problem two,
> >
> > If I manually enter a number into that field, I can make
> > changes to the
> > record no problem! but... it goes into some kind of
> > continuous loop. It
> > comes up with a window that says what I told it to say --
> > user_message("All
> > records regarding $kwo have been changed!",
> >
> > "$PHP_SELF?action="" --
> > I hit OK and it jumps to -- if(empty($kwo)) error_message('Empty
> > Gradient!!'); -- if you hit ok again, it just loops.
> >
> > This might be solved as soon as I get that one field to auto
> > populate. :P
> >
> >
> > news:1EA7D3AE70ACD511BE6D006097A78C1E033C92B2@USROCEXC...
> > > Jeff,
> > >
> > > No problem. Happens to everyone, trust me.
> > >
> > > Anyway, I see you're using mysql_affected_rows($link_id) to
> > get see how
> > many
> > > rows were affected by the UPDATE. Try getting rid of the
> > $link_id in the
> > > declaration and just use mysql_affected_rows(). Personally,
> > I have always
> > > had trouble when specifying a resource in a mysql function
> > like this (it's
> > > probably just me). But, the mysql_affected_rows() function will, by
> > default,
> > > use the last connection used by your script.
> > >
> > > Also, you're checking to see if it's not set e.g.,:
> > >
> > > > > > > $num_rows = mysql_affected_rows($link_id);
> > > > > > > if(!$num_rows) error_message("Nothing changed!");
> > >
> > > According to the documentation, mysql_affected_rows()
> > returns a -1 if the
> > > query failed. And I'm pretty sure it returns 0 if your query was
> > successful
> > > but didn't affect any rows. I'm pretty sure ! checks for
> > FALSE which is
> > not
> > > one of mysql_affected_rows() valid return values. So your
> > check isn't
> > doing
> > > what you think it should.
> > >
> > > Give that a shot and see if it helps out at all.
> > >
> > > Good luck.
> > >
> > > Rich
> > >
> > > > -----Original Message-----
> > > > Sent: Friday, September 05, 2003 12:44 PM
> > > > Subject: Re: Modify MySQL Record
> > > >
> > > >
> > > > K, my tired eyes last night missed a few stupid mistakes...
> > > >
> > > > First: The Commas
> > > > Second: I removed the period form the first line of the array
> > > > -- $field_str
> > > > = " kwo = '$kwo', ";
> > > > Third: two typos.
> > > >
> > > > *hangs head in shame*
> > > >
> > > > Sorry for bothering everyone. :P
> > > >
> > > > BUT! I'm still having a problem - when I try to submit the
> > > > change it comes
> > > > back and says "Nothing Changed!" even if I change every field.
> > > >
> > > > Gonna do some more hunting - I'm doubting myself now. ;)
> > > >
> > > >
> > > >
> > > >
> > wrote in message
> > > > news:1EA7D3AE70ACD511BE6D006097A78C1E033C92B1@USROCEXC...
> > > > > I'd suggest you first output your SQL statement to the
> > > > browser to see
> > > > > exactly what it's sending to the database. Might give you
> > > > the answer right
> > > > > there.
> > > > >
> > > > > > -----Original Message-----
> > > > > > Sent: Friday, September 05, 2003 12:35 PM
> > > > > > Subject: Re: Modify MySQL Record
> > > > > >
> > > > > >
> > > > > > Yah, I noticed that after I had submitted this, and fixed it
> > > > > > - still no
> > > > > > change - gives the same exact error.
> > > > > >
> > > > > > >
> > > > > > > looks like you stopped putting commas after your fields at
> > > > > > > $field_str .= " formation = '$formation' ";
> > > > > > > $field_str .= " perfs = '$perfs' ";
> > > > > > > $field_str .= " event = '$event' ";
> > > > > > > $field_str .= " fluid = '$fluid' ";
> > > > > > >
> > > > > > > hth
> > > > > > > Jeff
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > "Jeff"
> > > > > > > > cc:
> > > > > > > Subject:
> > > > > > Modify
> > > > > > MySQL Record
> > > > > > > 09/05/2003 12:28
> > > > > > > PM
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > Helloooo!
> > > > > > >
> > > > > > > I'm having a small problem with modifying the data in a
> > > > MySQL DB.
> > > > > > >
> > > > > > > And here's the error:
> > > > > > >
> > > > > > > (After I click the "Submit Changes" button) Error: 1064:
> > > > > > You have an error
> > > > > > > in your SQL syntax near '(10-10-10-10)' kwo='1235',
> > > > > > lsd='10-10-10-10',
> > > > > > > date='2003-05-05', well' at line 1
> > > > > > >
> > > > > > > Here's the code:
> > > > > > >
> > > > > > > function edit_record() {
> > > > > > > global $default_dbname, $gradient_tablename,
> > > > > > $access_log_tablename;
> > > > > > > global $new_lsd, $kwo, $lsd, $date, $well, $field,
> > > > > > > $uni, $license, $formation, $perfs, $event,
> > $fluid, $mode,
> > > > > > > $type, $vhd, $file, $kb, $grd, $open, $sour, $tube,
> > > > > > > $landed, $casing, $landed2, $shut_date, $shut_time,
> > > > $pres, $tag;
> > > > > > >
> > > > > > > if(empty($kwo)) error_message('Empty Gradient!');
> > > > > > >
> > > > > > > $link_id = db_connect($default_dbname);
> > > > > > > if(!$link_id) error_message(sql_error());
> > > > > > >
> > > > > > > $field_str = '';
> > > > > > > if($kwo != $new_kwo) $field_str = " kwo = '$new_kwo', ";
> > > > > > > if(!empty($lsd)) {
> > > > > > > $field_str .= " lsd = other_lsd('$lsd') ";
> > > > > > > }
> > > > > > >
> > > > > > > $field_str .= " kwo = '$kwo', ";
> > > > > > > $field_str .= " lsd = '$lsd', ";
> > > > > > > $field_str .= " date = '$date', ";
> > > > > > > $field_str .= " well = '$well', ";
> > > > > > > $field_str .= " field = '$field', ";
> > > > > > > $field_str .= " uni = '$uni', ";
> > > > > > > $field_str .= " license = '$license', ";
> > > > > > > $field_str .= " formation = '$formation' ";
> > > > > > > $field_str .= " perfs = '$perfs' ";
> > > > > > > $field_str .= " event = '$event' ";
> > > > > > > $field_str .= " fluid = '$fluid' ";
> > > > > > > $field_str .= " mode = '$mode' ";
> > > > > > > $field_str .= " type = '$type' ";
> > > > > > > $field_str .= " vhd = '$vhd' ";
> > > > > > > $field_str .= " file = '$file' ";
> > > > > > > $field_str .= " kb = '$kb' ";
> > > > > > > $field_str .= " grd = '$grd' ";
> > > > > > > $field_str .= " open = '$open' ";
> > > > > > > $field_str .= " sour = '$open' ";
> > > > > > > $field_str .= " tubing = '$tubing' ";
> > > > > > > $field_str .= " landed = '$landed' ";
> > > > > > > $field_str .= " casing = '$casing' ";
> > > > > > > $field_str .= " landed2 = '$landed2' ";
> > > > > > > $field_str .= " shut_date = '$shut_date' ";
> > > > > > > $field_str .= " sut_time = '$shut_time' ";
> > > > > > > $field_str .= " pres = '$pres' ";
> > > > > > > $field_str .= " tag = '$tag' ";
> > > > > > >
> > > > > > >
> > > > > > > $query = "UPDATE $gradient_tablename SET $field_str WHERE
> > > > > > kwo = '$kwo'";
> > > > > > >
> > > > > > > $result = mysql_query($query);
> > > > > > > if(!$result) error_message(sql_error());
> > > > > > >
> > > > > > > $num_rows = mysql_affected_rows($link_id);
> > > > > > > if(!$num_rows) error_message("Nothing changed!");
> > > > > > > if($lsd != $new_kwo) {
> > > > > > > $query = "UPDATE $access_log_tablename SET kwo =
> > > > > > '$new_kwo' WHERE kwo
> > > > > > =
> > > > > > > '$kwo'";
> > > > > > > $result = mysql_query($query);
> > > > > > > if(!$result) error_message(sql_error());
> > > > > > >
> > > > > > > user_message("All records regarding $lsd have been
> > > > changed!",
> > > > > > >
> > "$PHP_SELF?action="">
> > > > > > > }
> > > > > > > else {
> > > > > > > user_message("All records regarding $lsd have been
> > > > changed!");
> > > > > > > }
> > > > > > > }
> > > > > > >
> > > > > > > --
> > > > > >
> > > > > > --
> > > > > >
> > > >
> > > > --
> > > >
> >
> > --
> >
--
..
____________________________________________________
Email has finally evolved -
| 