Custom MySQL Insert Script - PHP

[DW]

This script is for v1.77 to v1.84, the user database may change in future versions and the script will need to be updated.

Copy and paste the following code into a file named LMinsert.php

Please read the notes near the top of the file for information about usage.

Code: [Select]

<?php
//
// v1.77 lm_users MySQL Table Format
//
// id  mediumint(9) - auto incrementing id
// uid  varchar(7) - unique user id, must be generated
// list  smallint(5) - list user belongs to
// fname  tinytext - first name
// lname  tinytext - last name
// email  tinytext - email address
// user1-user10 text - custom fields 1 thru 10
// cseq  smallint(6) - user's current seq # (next followup)
// cdel  smallint(6) - days to delay to next followup (0 is next dailymail)
// cnf  char(1) - 0=unconfirmed 1=confirmed 2=removed 3=bounced
// dateadd  date - YYYY-MM-DD date
// ipaddr  varchar(15) - ip address
// refurl  varchar(75) - referring URL
// htmail  char(1) - 1=HTML+Text 2=Just Text
// bounces  tinytext - dont mess with this one :/
//
//


// ListMail Insert User Function

// The first 2 parameters are required: list and email
// The rest are optional and their defaults specified: first name,last name, 10 custom fields, current seq #, current delay #, ip address, referring URL.
// The function will generate the UID and Date Added automatically

// Example usage:
// include('LMinsert.php');
// LMinsert.php('1',$email);

// CONFIG (can also be set outside the include in your script)
$sqlhost = 'localhost';
$sqluser = 'YOURUSERNAME';
$sqlpass = 'YOURPASSWORD';
$sqldb = 'YOURDATABASE';

// You -must- set the path to the ListMail files below
// -NO- trailing slash.  "." is current directory ".." is up one

// begin function
 function LMinsert($li,$em,$fn='',$ln='',$u1='',$u2='',$u3='',$u4='',$u5='',$u6='',$u7='',$u8='',$u9='',$u10='',$sq='1',$de='0',$ip='Unknown',$ref='Unknown'){

global $utable;
global $ltable;
global $sqlhost;
global $sqluser;
global $sqlpass;
global $sqldb;
global $lmp;


// echo "running LMinsert: list=$li email=$em<br>";

// Additional Options for duplicate email addresses on the same list
// Unset or comment to disable

$allow_dupes = 1;
// $overwrite_dupes = 1;
// $ignore_dupes = 1;

// End Config

// Begin Script
// connect to MySQL
$link2 = mysql_connect($sqlhost, $sqluser, $sqlpass);
mysql_selectdb($sqldb,$link2);


// check for duplicates
if(!$allow_dupes){
$cmd = "select id,cnf from $utable where list = '$li' and email like '$em'";
$result = mysql_query($cmd,$link2);

// duplicate found..
if(mysql_num_rows($result)>0){
list($xid,$xcnf)=mysql_fetch_row($result);

// if they were removed or unconfirmed, remove them and reinsert
if($xcnf=='2' || $xcnf=='0'){
$dupe = '';
mysql_query("delete from $utable where id = '$xid'");

// otherwise check if we're overwriting dupes
} else {
if($overwrite_dupes){
mysql_query("delete from $utable where id = '$xid'");
$dupe = '';

// this is a duplicate that won't be allowed in
} else {
$dupe = 1;
}
}
}
// allowing dupes
} else {
$dupe = '';
}

// exit without inserting if a dupe
if($dupe) return false;

// generate unique UID

$uniq = '';
while(!$uniq){
$uid = strtolower(substr(md5(rand(0,9999999)), 0,7));
$ucmd = "select id from $utable where uid = '$uid'";
$urow = mysql_query($ucmd);
if(@mysql_num_rows($urow)==0) $uniq=1;
}

// generate date
$da = date("Y-m-d");

// prep text for MySQL
$fn = addslashes($fn);  $ln = addslashes($ln);  $u1 = addslashes($u1);
$u2 = addslashes($u2);  $u3 = addslashes($u3);  $u4 = addslashes($u4);
$u5 = addslashes($u5);  $u6 = addslashes($u6);  $u7 = addslashes($u7);
$u8 = addslashes($u8);  $u9 = addslashes($u9);  $u10 = addslashes($u10);

// insert data - follows format of table at top of this file
// echo "inserting into LM list=$li email=$em<br>";
if(!mysql_query("INSERT INTO $utable VALUES ('null','$uid','$li','$fn','$ln','$em','$u1','$u2','$u3','$u4','$u5','$u6','$u7','$u8','$u9','$u10','$sq','$de','1','$da','$ip','$ref',1,'0');")) echo "problem inserting user into utable($utable)<br>";

$userid = mysql_insert_id();

// send welcome message if enabled on the list (note, this section must be uncommented and you must copy mimeclass.php and mimepart.php from ListMail into the same directory your custom script that includes this file is in.

/*
list($welcact,$cnfact) = @mysql_fetch_row(mysql_query("select welcact,cnfact from $ltable where listnum = '$li'"));
// echo "userid=$userid cnfact=$cnfact welcact=$welcact<br>";

if($cnfact=='1' || ($cnfact<>'1' && $welcact == '1')){
sendwelcome($userid);
}
 */
}
// end function

[ad]

Sorry, from my limited knowledge I cant see how this script would store any data in a database.
I always thought the syntax is:

$store = mysql_query("INSERT INTO table (field1, field2, field3 etc.) VALUES ('$param1','$param2','$param3') or die ....;

I am probably missing a lot of information here. Can anyone enlighten me?

Wolfgang

[DW]

MySQL will work with either method of query.  You do not need to define the fields you will be inserting.  When you define the fields as you show in your example it allows you to make shorter queries.  This is because when you do not define the fields you must specify data for every single field in the values() construct.

ie:

INSERT INTO table values('1','2','3');

and

INSERT INTO table (field1,field2) values('1','2');

In the second command, field3 will assume the default value.  I believe (possibly incorrectly) that the following is the same and will result in the default value for field3:

INSERT INTO table values('1','2','');

[melanie]

Dean,

I have limited knowledge in php coding, and have been experimenting with this all night and cannot make it work.  I created a test php page in the listmail directory that calls the function, which is stored in the same directory, but it isn't working and I'm lost!  

In the code above, I have our configuration information (host, username, etc.) in correctly.  That is the only change I made.  Do I need to do anything with the global variables?

TIA

[melanie]

Nevermind  Perserverance pays off!

[dane]

What did you do to make it work?

Nevermind  Perserverance pays off!