PHP & MYSQL Multiple Query Problem! 1

[pain4u]

I need some help with the below PHP/SQL code.

What I'm trying to do is: use a line break delimited text file (containg email address's) and have PHP look up the email address in a mySQL database table (containing email address, email_id, and a user_id column/fields).

Then I want to copy/move the email & the email_id fields to another separate table (email_list_removed) in the same database. And then delete the record from the first table (email_list). And then move on to the next address in the text file, until the end of the file.

For some reason it does the first email address fine (copies it & removes it), but when it gets to the second address, it only copies the email address but not the email_id...it sets the email_id to 0! And it also inserts an empty record inbetween them (in the 2nd database) with an email_id of 0 also...but no info. It looks kinda weird actually...I think it is messing up somewhere with the looping?? -- PLEASE HELP!

[Database table schema: create table email_list(email_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, email VARCHAR(50) NOT NULL, user_id INT NOT NULL); create table email_list_removed(email_id INT NOT NULL PRIMARY KEY, email VARCHAR(50) NOT NULL, user_id INT NOT NULL); ] --I also have created index's of the email column/field in both tables!

HERE IS THE CODE:

<?php
$lines = array();

function fileBreakupLines()
{global $lines;
$file = &quot;removeme.txt&quot;;
$fp = fopen($file,&quot;r&quot or die(&quot;Couldn't open $file&quot;
while(!feof($fp))
{$line = fgets($fp,1024);
if(stristr($line,&quot;@&quot)
$lines[] = $line;
else
continue;
}
fclose($fp);
return true;
}

if(fileBreakupLines())
{foreach($lines as $line)
{$dba = array(&quot;host&quot;,&quot;user&quot;,&quot;passw&quot;,&quot;dbname&quot;
$link = mysql_connect($dba[0],$dba[1],$dba[2]);
if(!$link)
die(&quot;Couldn't connect to mysqld!&quot;

mysql_select_db($dba[3],$link) or die(&quot;Couldn't open $dba[3]: &quot;.mysql_error());

$query1 = &quot;LOCK TABLES email_list WRITE, email_list_removed WRITE&quot;;
mysql_query($query1,$link);
$query2 = &quot;SELECT * FROM email_list WHERE email='$line'&quot;;
$result = mysql_query($query2,$link);
if(!$result)
{$dberror = mysql_error();
print $dberror.&quot;<BR><BR>&quot;;
print mysql_affected_rows();
}
else
$row = mysql_fetch_array($result);

$query3 = &quot;INSERT INTO email_list_removed(email,email_id) VALUES('$line','$row[email_id]')&quot;;
if(!mysql_query($query3,$link))
{$dberror = mysql_error();
print $dberror.&quot;<BR><BR>&quot;;
print mysql_affected_rows();
}
$rmv_query1 = &quot;DELETE FROM email_list WHERE email='$line'&quot;;
if(!mysql_query($rmv_query1,$link))
{$dberror = mysql_error();
print $dberror.&quot;<BR><BR>&quot;;
print mysql_affected_rows();
}
$query3 = &quot;UNLOCK TABLES&quot;;
mysql_query($query3,$link);
}
print mysql_affected_rows();
}
?>

I HOPE SOMEONE CAN HELP ME! - PAINKILLER

 

[piti]

well i think this part of your code:

function fileBreakupLines()
{global $lines;
$file = &quot;removeme.txt&quot;;
$fp = fopen($file,&quot;r&quot or die(&quot;Couldn't open $file&quot;
while(!feof($fp))
{$line = fgets($fp,1024);
if(stristr($line,&quot;@&quot)
$lines[] = $line;
else
continue;
}
fclose($fp);
return true;
}

could be replaced with something like this:

function fileBreakupLines()
{global $lines;
$file = &quot;removeme.txt&quot;;
$lines = file($file);
return true;
}

the returned line breaks are probably causing the empty records, ...
or use:
$line = trim(fgets($fp,1024));
in your function
i was not looking at the following db code, so try this and
i'll look at rest now

 

[piti]

now to the db code

why don't you do the job in 2 simple db queries instead of doing that weird loop?
try this

fisrt:

INSERT INTO email_list_removed (email,email_id)
SELECT email_list.email, email_list.email_id FROM email_list WHERE email_list.email IN (
/*
insert here all the values from the email array separated by commas
*/
)

second:

DELETE FROM email_list WHERE email_list.email IN (
/*
insert here all the values from the email array you got from the file separated by commas
*/

)

hope that helps a bit

 

[pain4u]

THANK YOU SO MUCH! All of the problems I was having was because of the /n new line characters.
*Modifed my fileBreakupLines() function to this:

$lines = array();

function fileBreakupLines()
{global $lines;
$file = &quot;removeme2.txt&quot;;
$fcontents = file($file);

while (list ($line_num, $line) = each ($fcontents))
{$lines[] = rtrim($line);}
return true;
}

And it worked like a charm! --THANK YOU!

As for my SQL queries & loops, I know it's not the best way to do it...but I was just trying too many different things (pulling my hair out and all) trying to figure out why the information was not being inserted & deleted correctly. And now I know why...darn new lines!!

I APPRECIATE YOUR HELP VERY MUCH! AND AGAIN, THANK YOU! - PAINKILLER

 

[pain4u]

Oh and another problem I was having was because I was using the mysql_fetch_array() function in my DB code. I changed that to mysql_fetch_assoc() ...it was inserting & trying to delete the index/key values because the mysql_fetch_array() returns both associated arrays & numeric.

THANK YOU AGAIN MAN! YOUR HELP SAVED ME ALOT OF TIME!

I voted for you for tip master, hehe.

- PAINKILLER