Insert AutoNumber

[doubledee]

How do I INSERT a record - using PHP - that has an AutoNumber as the first field?

 

I tried this but got an id=0...

// Build Query.
$q4 = "INSERT INTO prior_email(member_id, email, created_on)
					VALUES (?, ?, NOW())";

// Prepare statement.
$stmt4 = mysqli_prepare($dbc, $q4);

 

Thanks,

 

 

Debbie

 

[darkfreaks]

you left out mysqil_bind_param

 

 

mysqli_bind_param($q4,'isi'); 
//bind as integer string integer or could try using double or float instead of integer

 

[doubledee]

you left out mysqil_bind_param

 

 

mysqli_bind_param($q4,'isi'); 
//bind as interger string integer or coulf try using double or float instead of interger

 

No, I just didn't post it here since I didn't think that was where the problem is.

 

Here is more of my Prepared Statement...

// ******************
// Log Old Email.		*
// ******************

// Build Query.
$q4 = "INSERT INTO prior_email(member_id, email, created_on)
							VALUES (?, ?, NOW())";

// Prepare statement.
$stmt4 = mysqli_prepare($dbc, $q4);

// Bind variables to query.
mysqli_stmt_bind_param($stmt4, 'is', $memberID, $newEmail);

// Execute query.
mysqli_stmt_execute($stmt4);

// Verify Insert.
if (mysqli_stmt_affected_rows($stmt4)==1){
	// Insert Succeeded.

 

 

Debbie

 

[creata.physics]

You won't need to define the unique id if you set the field in mysql to auto increment. So basically you'd remove member_id from your query and change member_id in mysql to auto increment.

 

ALTER TABLE `members` CHANGE `member_id` `member_id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT

[doubledee]

You won't need to define the unique id if you set the field in mysql to auto increment. So basically you'd remove member_id from your query and change member_id in mysql to auto increment.

 

ALTER TABLE `members` CHANGE `member_id` `member_id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT

 

id is my primary key

 

member_id is my foreign key back to the member table, so I do need that.

 

I thought I was doing things correctly by leaving out the id above...

 

 

Debbie

 

[doubledee]

When I ran my Change Email script the first time I got this in phpMyAdmin...

id 	member_id 	email 	ip 	host_name 	created_on 	updated_on
0 	21 	[email protected] 	NULL 	NULL 	2012-03-16 20:12:33 	NULL

 

But when I ran it again, my script errored out to a default, catch-all error, leading em to believe the SQL failed.

 

And since I see no new records, that must be it?!

 

So I assume the issue is with my id autoincrement field and my Prepared Statement syntax...

 

 

Debbie

 

[darkfreaks]

The value of the AUTO_INCREMENT field that was updated by the previous query. Returns zero if there was no previous query on the connection or if the query did not update an AUTO_INCREMENT value.

[doubledee]

I think there is something wrong in MySQL, because when I ran a manual query in phpMyAdmin I got this...

Error

SQL query:

INSERT INTO prior_email( member_id, email, created_on )
VALUES ( 21, '[email protected]', NOW( ) )

MySQL said: Documentation
#1062 - Duplicate entry '0' for key 1 

 

 

So the database isn't auto-incrementing the "id" field?!

 

 

Debbie

 

[doubledee]

Okay, I figured it out...

 

Apparently I set a "Primary Key INDEX" in phpMyAdmin instead of a "Primary Key CONSTRAINT" (right term??)

 

Duh!! 

 

Looks like my code is working now.

 

Thanks,

 

 

Debbie