[Resolved] using php to enter mysql

[artist-ink]

I am using the following code to:

enter a new members data into a table (MemberInfo)
send an email to the new member
*direct the new member to New_member.php.

the code is working and is as follows:

else
	{
	$today = time("Y-m-d");
	$Approved = "N";
	$sql = "INSERT INTO MemberInfo (LoginName, Password, LastName, FirstName, Home, Email, Joined, Approved)
			VALUES ('$newname',password('$newpass'),'$LastName','$FirstName','$Home','$Email',CURRENT_TIMESTAMP(),'$Approved')";
	mysql_query($sql);



$auth="yes";
$logname=$newname;
/*send email to new member*/

$emess1="A new member account has been set up for. Once it is approved, you will be able to upload your work. An email
			will be sent to notify you of this.";
$emess2="Your username and password are:";
$emess3="\n\n\t$newname\n\t$newpass\n\n";
$emess= $emess1.$emess2.$emess3;
$ehead="From: [email][/email]\r\n";
$subject="Your new member account at Artist-Ink.com";
$mailsend=mail("$Email","$subject","$emess","$ehead");








header("Location:New_member.php");

}

I would also like to add a bunch of information to another table at the same time(only three entries are listed below).

I modified the code above, but it is not working:

else
	{
	$today = time("Y-m-d");
	$Approved = "N";
	$sql = "INSERT INTO MemberInfo (LoginName, Password, LastName, FirstName, Home, Email, Joined, Approved)
			VALUES ('$newname',password('$newpass'),'$LastName','$FirstName','$Home','$Email',CURRENT_TIMESTAMP(),'$Approved')";
	mysql_query($sql);



//enter default links into database


   $sql_default_links_entry = "INSERT INTO Link_Info
		VALUES ('', '$newname', 'Internet Movie Database', 'Database of movies and actors. Has discussion boards to discuss movies.', 'http://www.imdb.com', 'Entertainment')
		 INSERT INTO `Link_Info` VALUES ('', '$newname', 'Petersons Education Portal', 'The most comprehensive and heavily traveled education resource on the Internet.', 'http://petersons.com', 'Education')
		INSERT INTO `Link_Info` VALUES ('', '$newname', 'mba.com', 'Info regarding the GMAT and MBA programs, provided by the Graduate Management Admission Council.', 'http://www.mba.com', 'Education')";

mysql_query($sql_default_links_entry);



$auth="yes";
$logname=$newname;
/*send email to new member*/

$emess1="A new member account has been set up for. Once it is approved, you will be able to upload your work. An email
			will be sent to notify you of this.";
$emess2="Your username and password are:";
$emess3="\n\n\t$newname\n\t$newpass\n\n";
$emess= $emess1.$emess2.$emess3;
$ehead="From: [email][/email]\r\n";
$subject="Your new member account at Artist-Ink.com";
$mailsend=mail("$Email","$subject","$emess","$ehead");






header("Location:New_member.php");

}

The code does everythign that the first bunch of code does, but it does not enter the information into Link_Info.

I also tried doing it this way:

	$sql_default_links_entry = "INSERT INTO Link_Info(Link_ID, UserName, Link_Display, Link_Description, Link_URL, Category)
		VALUES ('', '$newname', 'Internet Movie Database', 'Database of movies and actors. Has discussion boards to discuss movies.', 'http://www.imdb.com', 'Entertainment')";
mysql_query($sql_default_links_entry);

but that didn't work either.

can anyone help?

[bradgrafelman]

Your "also tried doing it this way" looks more correct. Always nice to specify column names then fill in VALUES, so you can eyeball it to make sure they're in the correct spot.

Also, not sure if it's required, but go ahead and terminate your individual INSERT queries with semicolons, i.e. ...

$query = "INSERT INTO Link_info (...) VALUES (...);
INSERT INTO Link_info (...) VALUES (...);";

etc.

[artist-ink]

$sql_default_links_entry = "INSERT INTO Link_Info(Link_ID, UserName, Link_Display, Link_Description, Link_URL, Category)
        VALUES ('', '$newname', 'Internet Movie Database', 'Database of movies and actors. Has discussion boards to discuss movies.', 'http://www.imdb.com', 'Entertainment')";
mysql_query($sql_default_links_entry);

i tired just this entry (i figure if i can get one to work, then i can try to get a bunch of them to), but it isn't working.

any other ideas?

[bradgrafelman]

Strange indeed.

Could you by any chance do a dump of the structure (not data, if it's secure/confidential/etc) of Link_Info and post it here (via an attachment)?

Also, can you give us the error MySQL is returning? (add echo mysql_error(); if it doesnt tell you)

[artist-ink]

I changed this:

mysql_query($sql_default_links_entry);

to this:

mysql_query($sql_default_links_entry) or die mysql_error();

but i didn't get an error message. it sent the email and then it send me to New_member.php

here is the structure for Link_Info

# Table structure for table `Link_Info`
#

CREATE TABLE `Link_Info` (
  `Link_ID` tinyint(8) NOT NULL auto_increment,
  `UserName` varchar(255) NOT NULL default '',
  `Link_Display` varchar(255) NOT NULL default 'Not_Entered',
  `Link_Description` varchar(255) NOT NULL default 'Not_Entered',
  `Link_URL` varchar(255) NOT NULL default 'Not_Entered',
  `Category` varchar(255) NOT NULL default 'Not_Entered',
  PRIMARY KEY  (`Link_ID`)
) TYPE=MyISAM AUTO_INCREMENT=119 ;

#
# Dumping data for table `Link_Info`
#

thanks for the help.

[bradgrafelman]

I'm going to attempt to reproduce this situation on my servers.

Do you have AIM? That would be quicker for some minor configuration questions than posting.

[artist-ink]

i don't have AIM at this comp(Work). I wont be home until 10pm Pacific time....

here is the structure for the other two table involved

# Table structure for table `MemberInfo`
#

CREATE TABLE `MemberInfo` (
  `LoginName` varchar(20) NOT NULL default '',
  `Password` varchar(255) NOT NULL default '',
  `LastName` varchar(50) default NULL,
  `FirstName` varchar(50) default NULL,
  `Home` varchar(50) default NULL,
  `Email` varchar(50) default NULL,
  `Joined` datetime NOT NULL default '0000-00-00 00:00:00',
  `Approved` char(1) default 'N',
  `ArtDirectory` mediumtext NOT NULL,
  `MusicDirectory` tinytext NOT NULL,
  `WritingDirectory` tinytext NOT NULL,
  PRIMARY KEY  (`LoginName`)
) TYPE=MyISAM;

#
# Dumping data for table `MemberInfo`

and

# Table structure for table `MemberLogin`
#

CREATE TABLE `MemberLogin` (
  `LoginName` varchar(20) NOT NULL default '',
  `LoginTime` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`LoginName`,`LoginTime`)
) TYPE=MyISAM;

#
# Dumping data for table `MemberLogin`

do you need anything else?

[bradgrafelman]

Well, I'm making progress. I've got to run and grab a bite of supper, else parents will start raising a fuss 😛, but from what I gathered, MySQL isn't liking:

1. Use of multiple INSERT's per query. I solved this by splitting them up into separate queries, running mysql_query() after each one.

2. After doing the above step, I'm getting "Duplicate entry '127' for key 1". It's not auto_incrementing! o_O I can probably solve this by getting the last insert ID and just adding 1... though I thought auto_increment meant it would automatically increment the unique ID #... shrug

More on this later, when I get back!

EDIT: I'm back! I need to look up the exact MySQL function.. it's like last_insert_id() or something. Curse my memory! 🙁

EDIT2: D'oh! PHP has it's own function! That was easy 😛

[bradgrafelman]

Sorry for the double post, wanted to make it this obvious from the "EDIT"s from the previous post.

I believe I've fixed it. It seems to work with the small snippet I'm using on my servers.. I'd like to implement it into yours.

Going back to the first code example you provided, with the multiple inserts, change it like so:

         $sql_default_links_entry = "INSERT INTO Link_Info 
        VALUES ('', '$newname', 'Internet Movie Database', 'Database of movies and actors. Has discussion boards to discuss movies.', 'http://www.imdb.com', 'Entertainment')"; mysql_query($sql_default_links_entry); 

     $sql_default_links_entry = "INSERT INTO `Link_Info` VALUES ('".(mysql_insert_id()+1)."', '$newname', 'Petersons Education Portal', 'The most comprehensive and heavily traveled education resource on the Internet.', 'http://petersons.com', 'Education')"; mysql_query($sql_default_links_entry);

     $sql_default_links_entry = "INSERT INTO `Link_Info` VALUES ('".(mysql_insert_id()+1)."', '$newname', 'mba.com', 'Info regarding the GMAT and MBA programs, provided by the Graduate Management Admission Council.', 'http://www.mba.com', 'Education')"; mysql_query($sql_default_links_entry); 

If you're confused or have any questions, let me know. I'll stay subscribed to this thread untill you mark it "Resolved".

EDIT: I'll also keep the code/MySQL tables on my servers untill you mark it such, too.

[artist-ink]

huh....i finally got this figured out.

there were two files that i was using(that i did not write the inital code for):
login.php
login_form.php

login started with:

switch (@$do)

and then ran through the different cases:
login
new
default

the form that was being used, was set up in login_form.php and the submit actions ie:

<form action="login.php?do=login" method="post">)

set up the different cases on login.php

to test adding the mysql statements to enter default items into the database, i just copied login.php and renamed it to login_test.php. I wasn't smart enough to look at login_form.php, until this morning ( i guess it serves me right, for not writing my own code!).

i was running into problems (no errors were showing up with mysql_error()😉 b/c whenever i submitted info, it was referring back to login.form not login_test.form.

after i saw this, i renamed the actions and any other references to login.php or login_form.php and was able to figure out how to add multiple items, based on the errors generated.

this is what i came up with:

	$sql = "INSERT INTO MemberInfo (LoginName, Password, LastName, FirstName, Home, Email, Joined, Approved)
			VALUES ('$newname',password('$newpass'),'$LastName','$FirstName','$Home','$Email',CURRENT_TIMESTAMP(),'$Approved')";
	mysql_query($sql);




	$sql_default_links_entry1 = "
	INSERT INTO Link_Info(Link_ID, UserName, Link_Display, Link_Description, Link_URL, Category) 
	VALUES ('', '$newname', 'Petersons Education Portal', 'The most comprehensive and heavily traveled education resource on the Internet.', 'http://petersons.com', 'Education')";
	mysql_query($sql_default_links_entry1) or die (mysql_error());

	$sql_default_links_entry2 = "
	INSERT INTO Link_Info(Link_ID, UserName, Link_Display, Link_Description, Link_URL, Category)
	VALUES ('', '$newname', 'mba.com', 'Info regarding the GMAT and MBA programs, provided by the Graduate Management Admission Council.', 'http://www.mba.com', 'Education')";
	mysql_query($sql_default_links_entry2) or die (mysql_error());

	$sql_default_links_entry3 = "
	INSERT INTO Link_Info(Link_ID, UserName, Link_Display, Link_Description, Link_URL, Category)
	VALUES ('', '$newname', 'PHPbuilder.com', 'Resource for PHP tutorials, templates, PHP manuals, content management systems, scripts, classes and more for the PHP developer.', 'http://www.phpbuilder.com/', 'Computers & Internet')";
	mysql_query($sql_default_links_entry3) or die (mysql_error());

it seems to be working. i've signed up a few test members and have entered a couple hundred entries into the database as a result.

i would like to move this query to another file, to keep the lines on this file down... maybe i'll try using an include.

thanks for your help. i really appreciate it.

now, on to the next problem.....

cheers.