B0b

June 29, 2010

Mchl,

My goal is to transfer a .txt list to a mySQL database, then remove duplicate entries from it, shuffle the list and finally separate the table into 20 sub-tables.

I managed to separate it fairly quickly using:

SELECT * FROM tableName LIMIT ...

DELETE FROM tableName LIMIT ...

Where LIMIT split it in equal parts based on quantity of entries in the original table.

I then remove duplicate entries using a SELECT DISTINCT and shuffle using ORDER BY RAND() on each sub-table.

The issue with this method is that there may still be duplicate entries as only the sub-tables are cleared and the mixing isn't as good as it could be.

Also, an excessively large original table that would result in sub-tables greater than 250K would lead to a huge load time.

If you have any idea, please let me know.

June 29, 2010

I am having the same problem as well with this:

CREATE TABLE $TMPtableName AS

SELECT DISTINCT columnName

FROM $tableName

If I set a limit to 250K, it will take few seconds while a limit to 500K will require about a minute. Not limit... errr.

Why is mySQL ALWAYS slowing down beyond ~250K?

:confused:

June 28, 2010

Hum, I am having the exact same problem as I am trying to separate a table into parts:

for ( $i = 1; $i <= 20; $i++ ) // In 20 parts.
{
$TMPtableName = $i . '_' . $tableName;
mysql_query( 
		"CREATE TABLE $TMPtableName
		(
			ID int auto_increment not null,
			columnName varchar( 255 ) Unique,
			primary key ( ID )
		)" );
}

// Determine total of entries.
$total = mysql_query(
				"SELECT MAX( ID )
				FROM $tableName" );				
$total = mysql_fetch_row( $total );
$total = $total[0];

$counter = 1;
for ( $i = 1; $i <= $total; $i++ )
{
$tmpQuery = mysql_query(
					"SELECT columnName
					FROM $tableName
					WHERE ID = $i" );				
$tmpQuery = mysql_fetch_row( $tmpQuery );
$tmpQuery = $tmpQuery[0];

$TMPtableName = $counter . '_' . $ableName;
mysql_query(
		"INSERT INTO $TMPtableName
		(
			columnName
		)
		VALUES
		(
			'$$tmpQuery'
		)" );

$counter++;
if ( $counter > 20 )
{
	$counter = 1;
}
}

Any clue?

June 28, 2010

#1, thanks!

June 28, 2010

$dbCon = mysql_connect( 'localhost', 'root', 'password' );
mysql_select_db( 'database', $dbCon );

$tableName = 'tableName';
mysql_query( 
	"CREATE TABLE $tableName
	(
		columnName varchar( 255 )
	)" );


mysql_query( "LOAD DATA INFILE '/var/www/docs/myDomain/hugeFile.txt' INTO TABLE $tableName" );

echo mysql_error();

June 28, 2010

Thanks guys.

The following error is returned:

Can't get stat of '...' (Errcode: 13)

I am using the absolute path to the file. Any clue?

June 28, 2010

Thank you Mchl.

Would this be the proper syntax for LOAD DATA INFILE?:

mysql_query( "LOAD DATA INFILE 'hugeFile.txt' INTO TABLE $tableName LINES TERMINATED BY" . "\r\n" );

Doesn't work?

June 28, 2010

Hi everyone,

I'm trying to transfer a large .txt list of 2M+ entries into a mySQL databse.

Everything works fine until it reaches about 250K entries, from there, the performance dramatically drops and it takes ages to complete the rest.

Using top command, I may clearly see the CPU doesn't seem to work as much as it did for the first 250K.

Most of the time, it idles and the entries are very slowly transfered (about 10 per second), then it spins for a second to transfer at 10K+/sec.

What's going on?

$handle = fopen( 'largeFile.txt', 'r' );

$dbCon = mysql_connect( 'localhost', 'root', 'password' );
mysql_select_db( 'database', $dbCon );

mysql_query( 
        "CREATE TABLE tableName
        (
            jack varchar( 255 ) Unique
        )" );
    
while ( !feof( $handle ) )
{
    $line = trim( fgets( $handle ) );
    
    if ( $line != '' )
    {
        mysql_query(
                "INSERT INTO tableName
                VALUES
                (
                    '$line'
                )" );
    }
}

Thanks so much!

June 23, 2010

I wish text not to be highlighted when we click and drag our mouse on it (ie. what you do when you wish to copy/paste a block of text).

June 23, 2010

if ( $row['personale'] != '1' && $row['personale'] != '2' )
{
// Is not 1 and is not 2: not admin.
}
else
{
// Is admin.
}

June 23, 2010

Hi everyone,

I would like to know how, via CSS, we may make text not clickable?

For instance, if you click on my message and move your mouse, it will highlight it in blue: that's what I'd like to remove!

Thanks!

June 16, 2010

You should go through W3Schools tutorials: http://www.w3schools.com/

<body>
<div id='counter'></div>
</body>

<script type="text/javascript">

theArray = new Array( '1', '2', '3', '4', '5', '6', '7', '8', '9', '10' );

for ( counter in theArray )
{
document.getElementById( 'counter' ).innerHTML = theArray[counter];
}

</script>

June 16, 2010

Thanks for your input Ken. I don't know why there's a base64_encode there, but my situation has evolved since my last message.

My goal is simply to send out an email from an Hotmail account (it. through SMTP authentication).

Here's how the code looks like right now:

<?php

$sock = socket_create( AF_INET, SOCK_STREAM, SOL_TCP );
socket_bind( $sock, 'XX.XX.XX.XX' );

socket_connect( $sock, 'smtp.live.com', 25 );

socket_write( $sock, 'EHLO mydomain' . "\r\n" );
socket_write( $sock, 'STARTTLS' . "\r\n"  );
socket_write( $sock, 'EHLO mydomain' . "\r\n" );
socket_write( $sock, 'AUTH LOGIN' . "\r\n" );
socket_write( $sock, 'myemail' . "\r\n" );
socket_write( $sock, 'mypassword' . "\r\n" );
socket_write( $sock, 'MAIL FROM: <anotheremail>' . "\r\n" ); // And this will output this error: "Connection reset by peer".

I've been told Hotmail requires an SSL connection and fsockopen would solve this, but I got to bind an IP address to my socket, so here I am.

June 15, 2010

Connecting to your mySQL database is done this way:

$dbUser = 'your_database_username';
$dbPass = 'your_database_password';
$database = 'your_database_name';

$dbCon = mysql_connect( 'localhost', $dbUser, $dbPass );
mysql_select_db( $database, $dbCon );

You should refer to this:

http://www.w3schools.com/php/php_mysql_connect.asp

June 15, 2010

<textarea cols=20 rows=20><?php echo 'this' . "\n" . 'that' ?></textarea>

June 15, 2010

This would display 12345678910. What you would be looking for is:

echo count( $array ) . '';

Or:

$counter = 0;

foreach ( $array as $element )
{
    $counter++;
}

echo $counter;

You're welcome.

June 15, 2010

<?php

$dbUser = 'database_username';
$dbPass = 'database_password';
$database = 'database_name';

$dbCon = mysql_connect( 'localhost', $dbUser, $dbPass );
mysql_select_db( $database, $dbCon );

$tmpQuery = mysql_query( "SELECT count(*) FROM buyers" );
$tmpQuery = mysql_fetch_row( $tmpQuery );
$buyersCount = $tmpQuery[0];

$tmpQuery = mysql_query( "SELECT count(*) FROM suppliers" );
$tmpQuery = mysql_fetch_row( $tmpQuery );
$suppliersCount = $tmpQuery[0];

$tmpQuery = mysql_query( "SELECT count(*) FROM products" );
$tmpQuery = mysql_fetch_row( $tmpQuery );
$productsCount = $tmpQuery[0];

?>

<table width="500" border="0">
    <tr>
      <td width="62"># Buyers</td>
      <td width="110"><?php echo $buyersCount; ?></td>
      <td width="93"># Suppliers</td>
      <td width="55"><?php echo $suppliersCount; ?></td>
      <td width="111"># Products</td>
      <td width="43"><?php echo $productsCount; ?></td>
      </tr>
</table>

June 15, 2010

:confused:

$counter = 0;

foreach ( $array as $element )
{
    echo $element;
    $counter++;
}

June 15, 2010

June 15, 2010

Hello,

I am trying to send an email from an Hotmail account, but hotmail doesn't recognise "AUTH LOGIN" command? May you tell me why?

Here's how it looks like:

getmxrr( 'hotmail.com', $MXs );
$sock = socket_create( AF_INET, SOCK_STREAM, SOL_TCP );
socket_connect( $sock, $MXs[0], 25 );
echo socket_read( $sock, 2082 ); // 220 bay0-mc1-f27.Bay0.hotmail.com...
socket_write( $sock, 'EHLO mydomain.com' . "\r\n" );
echo socket_read( $sock, 2082 ); // 250-bay0-mc1-f27.Bay0.hotmail.com (3.11.0.113)...
socket_write( $sock, base64_encode( 'AUTH LOGIN' ) . "\r\n" );
echo socket_read( $sock, 2082 ); // This returns 500 Unrecognized command

June 3, 2010

Bump :-\

June 3, 2010

Let's be more specific: I know the column may be added using:

<?php
mysql_query(
"ALTER TABLE $tmpName
ADD column type" );

But I don't know how I may fill it since I may not refer to anything using WHERE.

June 3, 2010

Hi everyone,

I'm trying to copy a column from a table to another while removing duplicate entries then add 3 other columns and fill one of them with numbers from 1 to X -> that's what I'm unable to do.

Here's my goal:

Table 1:

1 bob1

2 bob2

3 bob2

4 bob3

5 bob4

Table 2:

1 bob1 jack marc

2 bob2 frank stu

3 bob3 some dude

4 bob4 foo bar

Here's what I got:

<?php
$new = 'tmp_' . $original;

mysql_query(
	"CREATE TABLE $new AS
	SELECT DISTINCT email
	FROM $original" );

I'm confused as to how to add the first column.

Thanks!

May 29, 2010

Thanks for the input guys. Great idea Crayon Violent, but I'm worried that reading a random line wouldn't be efficient at all: in a 200k+ lines file, finding the last unread line would take ages assuming I replace read ones by spaces. The question is: would it be possible to delete these bytes directly within the original file? Quite impossible I guess.

ex:

dummy1

bob___

hello_

I randomly read "bob___" and delete it:

dummy1

hello_

Otherwise I'd have to do:

dummy1

______

hello_

Then randomly find a line and verify if it's not only spaces.

Seems like a database would be the most efficient way.

May 29, 2010

Holy crab. I've end up with a 100+ lines function for this only to realise it takes over 1 second per kb, at 100% CPU usage, which means over an hour for a file.

What to do now? Any suggestion?

function shuffleList( $theList, $listName, $replace, $quality )
{
// $theList: path to original list to shuffle
// $listName: name of the original list
// $replace: whether the original list is overwritten or saved under a new name (bool)
// $quality: shuffle iterations to determine quality of the mix (int)

if ( file_exists( $theList ) )
{
	$originalList = fopen( $theList, 'r' );
	$tmpList1 = fopen( 'mix1-' . $listName, 'w+' );
	$tmpList2 = fopen( 'mix2-' . $listName, 'w+' );

	// Count entries total.
	$total = 0;
	while ( !feof( $originalList ) )
	{
		fgets( $originalList );
		$total++;
	}
	rewind( $originalList );

	// Shuffle.
	for ( $i = 1; $i <= $quality; $i++ )
	{
		// Determine which file to shuffle.
		if ( $i == 1 )
		{
			$listRead = $originalList;
			$listWrite = $tmpList1;
		}
		elseif ( isPair( $i ) )
		{
			$listRead = $tmpList1;
			$listWrite = $tmpList2;
		}
		else
		{
			$listRead = $tmpList2;
			$listWrite = $tmpList1;
		}

		$counter = 0;
		$top = 1;
		$bot = 0;
		while ( $counter != $total )
		{
			$tmpCounter = 0;
			if ( isPair( $counter ) )
			{
				// Pick an entry at the bottom.
				while ( $tmpCounter != $total - $bot )
				{
					$tmpCounter++;
					$tmpEmail = fgets( $listRead );
				}
				$bot++;
				$counter++;
				fwrite( $listWrite, trim( $tmpEmail ) . "\r\n" );
				rewind( $listRead );
			}
			else
			{
				// Pick an entry at the top.
				while ( $tmpCounter != $top )
				{
					$tmpCounter++;
					$tmpEmail = fgets( $listRead );
				}
				$top++;
				$counter++;
				fwrite( $listWrite, trim( $tmpEmail ) . "\r\n" );
				rewind( $listRead );
			}
		}
		rewind( $listWrite );
	}

	fclose( $originalList );

	// Save mixed list.
	if ( $replace == true )
	{
		$listFinal = $theList;
	}
	else
	{
		$listFinal = 'mix-' . $listName;
	}

	$listFinal = fopen( $listFinal, 'w' );

	while ( !feof( $listWrite ) )
	{
		fwrite( $listFinal, fgets( $listWrite ) );
	}

	fclose( $tmpList1 );
	fclose( $tmpList2 );
}
}

function isPair( $number )
{
// true: pair, false: not pair.

if ( $number == 0 )
{
	return true;
}
else
{
	if ( ( $number - 1 )%2 == 1 )
	{
		return true;
	}
	else
	{
		return false;
	}
}
}

Sign In

B0b

Posts

Joined

Last visited

Content Type

Profiles

Forums

Posts posted by B0b

mySQL poor performance?

mySQL poor performance?

mySQL poor performance?

mySQL poor performance?

mySQL poor performance?

mySQL poor performance?

mySQL poor performance?

mySQL poor performance?

CSS: Make text not clickable?

PHP if

CSS: Make text not clickable?

Counter Quesiton

SMTP authentication?

hi need help

html/css boxes around php code

Counter Quesiton

How do I display database count on main page?

Counter Quesiton

SMTP authentication?

SMTP authentication?

SELECT DISTINCT?

SELECT DISTINCT?

SELECT DISTINCT?

PHP shuffle without array?

PHP shuffle without array?

Browse

Activity

Important Information