[SOLVED] MySql query problems

[MrBillybob]

Hello

 

      This code gets 1000 results out of a csv file and then puts it into a database. My Problem....it only inserts one row of data then it just stops inserting the data into the database.

 

 

<?php

$args['userid']  = 'blah';
$args['charid']  = 'blahblah';
$args['userkey'] = 'blahblahblahblahblahblahblahblahblahblahblahblahblahblahblah';


$auth  = "userid="       .$args['userid'];
$auth .= "&apikey="      .$args['userkey'];
$auth .= "&characterID=" .$args['charid'];


$head = "POST /corp/WalletJournal.csv.aspx HTTP/1.0\r\n";
$head .= "Host: api.eve-online.com\r\n";
$head .= "Content-Type: application/x-www-form-urlencoded\r\n";
$head .= "Content-Length: " . strlen($auth) . "\r\n";
$head .= "Connection: close\r\n\r\n";

$fp = fsockopen ('api.eve-online.com', 80, $errno, $errstr, 30);

if (!$fp) {
	echo 'fsock failed; damn muppets! '.$errstr;
	exit();
}

fputs($fp, $head);
fputs($fp, $auth);

$header = 1;
$count = 0;
    while ($line = fgets ($fp))
    {
    
        if ($line == "\r\n")
        {
            $header = 0;
        }
        if ($header)
        {
            $http_header .= $line;
        }
        else
        {
            $count = $count + 1;
		list($date, $refID, $refType, $ownerName1, $ownerName2, $argName1, $amount, $balance, $reason) = explode(",", fgets ($fp));
		$data_in[$count]['date'] = $date;
		$data_in[$count]['refID'] = $refID;	
		$data_in[$count]['refType'] = $refType;
		$data_in[$count]['ownerName1'] = $ownerName1;
		$data_in[$count]['ownerName2'] = $ownerName2;
		$data_in[$count]['argName1'] = $argName1;
		$data_in[$count]['amount'] = $amount;
		$data_in[$count]['balance'] = $balance;
		$data_in[$count]['reason'] = $reason;
        }
    } 

fclose ($fp);

////////
//////// mysql conncet stuff
////////
////////

$server1 = mysql_connect($sname, $suname ,$supname);
$server1 = mysql_select_db($dbname, $server1);

for ($i = 3; $i <= $count; $i++) {

	$sql = 'INSERT INTO `eve_wallet` (`date`, `refID`, `refType`, `ownerName1`, `ownerName2`, `argName1`, `amount`, `balance`, `reason`) VALUES (\'' . $data_in[$i][date] . '\', \'' . $data_in[$i]['refID'] . '\', \'' . $data_in[$i]['refType'] . '\', \'' . $data_in[$i]['ownerName1'] . '\', \'' . $data_in[$i]['ownerName2'] . '\', \'' . $data_in[$i]['argName1'] . '\', \'' . $data_in[$i]['amount'] . '\', \'' . $data_in[$i]['balance'] . '\', \'' . $data_in[$i]['reason'] . '\');';
	$results = mysql_query($sql);
	if (!$results) {
		echo $i . " didnt work <br />";
	} else {
		echo $i . " worked <br />";
	}
	$sql = "";
}
mysql_close();
?>

 

If you can find anything wrong with my code please help me fix it. This is using the eve-online API.

[hitman6003]

When you say "stops inserting data into the server" do you mean, the queries fail, or it stops processing data?

 

Change

$results = mysql_query($sql);

to

$results = mysql_query($sql) or die(mysql_error());

[MrBillybob]

It only puts one row in the table but finishes the loop....im entering duplicates.

 

Duplicate entry '32767' for key 1

[hitman6003]

You have a primary key (or unique index) on that column...which prevents it from inserting further records.  Change your PK to a different column, or eliminate the unique index (a PK is a type of unique index)

[MrBillybob]

Thank you

[MrBillybob]

I just got an error...

 

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Club', 'Rampage Eternal', 'EVE System', '400000000.00', '946537944.29', ' ')' at line 1"

 

What may cause this?

[hitman6003]

What's the full text of the query that's failing?

[MrBillybob]

$sql = 'INSERT INTO `eve_wallet` (`date`, `refID`, `refType`, `ownerName1`, `ownerName2`, `argName1`, `amount`, `balance`, `reason`) VALUES (\'2007-07-24 19:21:00\', \'1137834593\', \'Office Rental Fee\', \'The Capitals' Club\', \'Rampage Eternal\', \'EVE System\', \'400000000.00\', \'946537944.29\', \'\');';

[hitman6003]

\'The Capitals' Club\'

 

See the unescaped single quote in the middle there?

 

Use mysql_real_escape_string on each of your variables before putting it into the final query string.

 

http://www.php.net/mysql_real_escape_string

[MrBillybob]

works great