read random lines from a record

[devvicky]

Hello, i will keep it simple to understand :

 

1) i have a database with 2 columns - word and text

2) Each word has approx. 600,000 lines of text associated with it.

3) Iam a .net person shifting to php and mysql - so a little knowledge.

 

MY requirement :

 

1) I will pass the word through a form

2) The form will connect to the database and should display 2000 random lines from those 600,000 which should be non-repetitive

 

My current progress :

 

<?php
$con = mysql_connect("localhost","text_minx","pwd");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_db", $con);

$result = mysql_query("SELECT * FROM Data
WHERE word='health'");

while($row = mysql_fetch_array($result))
  {
  echo $row['lines'];
  echo "<br />";
  }
?> 

 

This shows all the lines. What i want is to read this $row['lines'] in a possible array and randomly select 2000 lines from it - and they should be non-repetitive.

 

Any help please?

[Zane]

I'm not exactly sure what $row['lines'] holds..

 

Try this and post the output.

while($row = mysql_fetch_array($result))
  {
  $data[] = $row['lines'];
  //echo "
";
  }
echo "", print_r($data), "";
[/code

[devvicky]

Hello, i think the question was not clear. I will make it clear.

 

The database looks like this :

 

WORD                      TEXT

 

Health                      10 tips for your health

                                  20 tips for your health

 

Body                          10 tips for your body

                                  20 tips for your body

 

 

The only exception is that in my database each record in text column has 600,000 lines. With my code what i get is all those 600,000 lines of text on my web-page. What i actually want is randomly selected 2000 lines from these 600,000 on my webpage (and these should not be duplicate).

 

So iam sorry but i don't see the 2000 part in your code 

[Zane]

I don't believe there is a way to select 2000 random lines from a single field.  I also can't understand why you have such a database design.

I'm only assuming, in the below example, that there is an actual newline in between each 600,000 set.

 

 

Anyway, since I'm not an SQL genious, I can only show you how I'd use PHP to do this.  Perhaps fenway knows a much more efficient way.

Note: this isn't tested whatsoever....

while($row = mysql_fetch_array($result))
  {
  $x = shuffle(  explode("\n", row['lines'])  );
  $data[] = array_rand($x, 2000);
  //echo "
";
  }
echo "", print_r($data), "";

 

What I'm doing is breaking the 600,00 lines into an array

Then I shuffle that... making it randomized.

From that shuffled array, I then grab 2000 random entries from it.

[devvicky]

hello, with a test DB this works perfectly. Now i need to know one thing - When inserting text in the database, i simple copy-paste the content from the text file into the record. But new line feed does not get inserted. I mean in the text file all lines are on separate lines, but in mysql DB it is not the case and everything changes. Could you tell me how to insert data into mysql database with new line character?

 

Thanks

[Zane]

Again, why do you have 600.000 lines inside one field?

 

If you're c/p'ing this from a text file with newlines,... why don't you just use the file function?

Instead of putting all those lines in the database, just reference the text file's name.

[devvicky]

hmmm i come from .net desktop background. I thought if i use file then only one person can open it at once. I mean if file is opened in a session, then it can't be accessed by someone else.

 

I suppose multiple users can then send request without any error like : "file in use" right??

 

There is no need for database but i got confused with file. 

[Zane]

So long as your only using PHP to access that file, I don't think it should matter.

 

Now if you somehow mixed PHP code with ASP code (through AJAX most likely) and used them both to access the same file, then you might have access issues.

 

Also, it's just a txt file..  Think of how many millions of users access the same index page when they go to Google..there's no access issues there