How To Search Through Multiple Json Files?

[codingbenn]

So basically want to have a search box and I want users to type in a name and then hit search. It then needs to search through multiple JSON files(11,480) to be precise. I want it to find the name inside the JSON and then pull other information from that JSON file. ATM I only have it where you put in the json file and then it reads the data.

 

The code I currently have is;

 

<?php


$string = file_get_contents("2.json");
$json_a=json_decode($string,true);


echo "First Name: ", $json_a['Item'][FirstName];
echo "<br>Surname: ", $json_a['Item'][LastName];
echo "<Br>Height: ", $json_a['Item'][Height], "cm";
echo "<br>Rating: ", $json_a['Item'][Rating], " OVRL";
echo "<br>Pace: ", $json_a['Item'][Attribute1];
echo "<br>Shooting: ", $json_a['Item'][Attribute2];
echo "<br>Passing: ", $json_a['Item'][Attribute3];
echo "<br>Dribbling: ", $json_a['Item'][Attribute4];
echo "<br>Defending: ", $json_a['Item'][Attribute5];
echo "<br>Heading: ", $json_a['Item'][Attribute6];
?>

 

Thanks

Edited October 30, 2012 by codingbenn

[Psycho]

Wow, that seems like a huge waste of resources. Have you considered dumping the data into a database? That would be the logical approach.

 

But, to answer your question you would have to loop through each of the files. If all the files are in the same directory it is simple enough - but a huge waste of resources. But, you did not specify "how" the search should work. There are two fields for name in the JSON data. So, the below solution (which would be a waste of resources) may not work as you intend, but could be updated.

 

Here is some sample code to get you started

//Set path to foilder with json files
$jsonDir = "path/to/json/files/";

$search = isset($_POST['search']) ? strtolower(trim($_POST['search'])) : '';

if(empty($search))
{
   echo "Enter a search value";
}
else
{
   //Create variable to store match (if found)
   $match = false;
   //Get list of json files
   $jsonFiles = scandir($jsonDir);
   foreach($jsonFiles as $file)
   {
    $fileContents = file_get_contents($file);
    $data = json_decode($fileContents, true);

    //Search first and last names separately
    $fnameSearch = strtolower($data['Item'][FirstName]);
    $lnameSearch = strtolower($data['Item'][LastName]);
    if(strpos($fnameSearch, $search)!==false || strpos($lnameSearch, $search)!==false)
    {
	    //match was found, set variable and break foreach loop
	    $match = $data['Item'];
	    break;
    }
   }

   if(!$match)
   {
    //No match was found, display error
    echo "No match found for '$search'";
   }
   else
   {
    //Display data from the match
    echo "First Name: {$match[FirstName]}<br>\n";
    echo "Surname: {$match[LastName]}<br>\n";
    echo "Height: {$match[Height]}cm<br>\n";
    echo "Rating: {$match[Rating]} OVRL<br>\n";
    echo "Pace: {$match[Attribute1]}<br>\n";
    echo "Shooting: {$match[Attribute2]}<br>\n";
    echo "Passing: {$match[Attribute3]}<br>\n";
    echo "Dribbling: {$match[Attribute4]}<br>\n";
    echo "Defending: {$match[Attribute5]}<br>\n";
    echo "Heading: {$match[Attribute6]}<br>\n";
   }
}

 

Did I mention this would be a waste of resources?

[Christian F.]

Huge waste of time, indeed. Though, to lessen it you can use find, if you're on a Linux server. That said, I too strongly recommend using a database for this.

[codingbenn]

How would I dump the files into a database though?

[codingbenn]

-snip-

How would I add a textbox for them to search? would I use

<input type="text">

 

but how would I link it? also how would I dump all these jsons into a database?

Edited October 30, 2012 by codingbenn

[jcbones]

Dumping into a database, you would think along the lines of:

 

A. Using PHP, create a SQL file with the values correctly formatted to insert into the database via a 'local data infile';

B. Using PHP, create a CSV file, and insert with a 'local data infile';

C. Use PHP to build a SQL query string with the JSON data, and insert via a call to the database.

 

Example of C: (untested, and noting that this file may take a long time to execute, but you only have to do it once, right?)

<?php
$path = '/path/to/json/files';
$files = glob($path . '/*.json'); //get all json file names.
$chunks = array_chunk($files,500); //split these filenames up into groups of 500 (you said 11k+ right).
foreach($chunks as $file) { //for each group of 500 files.
$queryParts = array(); //set an empty array.
foreach($file as $filename) { //foreach file
$data = file_get_contents($path . '/' . $filename);
list($firstname, $lastname, $height, $rating, $pace, $shooting, $passing, $dribbling, $heading) = json_decode($data); //I'm supposing that there are only person per file?
$queryParts[] = "('$firstname','$lastname','$height','$rating','$pace','$shooting','$passing','$dribbling','$heading')"; //build a value string.
} //through with all the current files. (up to 500).
$queryStrings[] = "INSERT INTO table (firstname,lastname,height,rating,pace,shooting,passing,dribbling,heading) VALUES " . implode(' , ',$queryParts); //build a query string, this should contain at most 500 rows of data.
} //all files have been looked at.
include('connection.php'); //make a database connection.
foreach($queryStrings as $value) { //go through the query strings should be 23 strings.
mysql_query($value); //execute each one. (by this time, you should be staying clear of mysql in favor of mysqli or PDO).
}

Edited October 31, 2012 by jcbones

[codingbenn]

Dumping into a database, you would think along the lines of:

 

A. Using PHP, create a SQL file with the values correctly formatted to insert into the database via a 'local data infile';

B. Using PHP, create a CSV file, and insert with a 'local data infile';

C. Use PHP to build a SQL query string with the JSON data, and insert via a call to the database.

 

Example of C: (untested, and noting that this file may take a long time to execute, but you only have to do it once, right?)

<?php
$path = '/path/to/json/files';
$files = glob($path . '/*.json'); //get all json file names.
$chunks = array_chunk($files,500); //split these filenames up into groups of 500 (you said 11k+ right).
foreach($chunks as $file) { //for each group of 500 files.
$queryParts = array(); //set an empty array.
foreach($file as $filename) { //foreach file
$data = file_get_contents($path . '/' . $filename);
list($firstname, $lastname, $height, $rating, $pace, $shooting, $passing, $dribbling, $heading) = json_decode($data); //I'm supposing that there are only person per file?
$queryParts[] = "('$firstname','$lastname','$height','$rating','$pace','$shooting','$passing','$dribbling','$heading')"; //build a value string.
} //through with all the current files. (up to 500).
$queryStrings[] = "INSERT INTO table (firstname,lastname,height,rating,pace,shooting,passing,dribbling,heading) VALUES " . implode(' , ',$queryParts); //build a query string, this should contain at most 500 rows of data.
} //all files have been looked at.
include('connection.php'); //make a database connection.
foreach($queryStrings as $value) { //go through the query strings should be 23 strings.
mysql_query($value); //execute each one. (by this time, you should be staying clear of mysql in favor of mysqli or PDO).
}

is this code formatting the jsons for a database? Also I have managed to get all the jsons into a text file..would it be easier to search through it and then when it finds a name read the information from the same line?

[smoseley]

Given your original architecture, grep would probably be the fastest way to do what you want:

 

Something like:

$name = $_GET["name"];
exec("grep '{$name}' /path/to/json/files/*.json", $result);
foreach ($result as $line) {
  $filename = substr($line, 0, strpos($line, ':'));
}

 

However, I agree that a database is the way to go. You have enough data to justify it.

Edited October 31, 2012 by smoseley

[codingbenn]

Given your original architecture, grep would probably be the fastest way to do what you want:

 

Something like:

$name = $_GET["name"];
exec("grep '{$name}' /path/to/json/files/*.json", $result);
foreach ($result as $line) {
$filename = substr($line, 0, strpos($line, ':'));
}

 

However, I agree that a database is the way to go. You have enough data to justify it.

I have merged all the json files into one text file? wont it be easier just to search the text file?

[Psycho]

I have merged all the json files into one text file? wont it be easier just to search the text file?

 

No, put the data into a database. I don't know all the data that exists in those files or how much of the data you need. But, a bigger question is whether you know how to use a database or not. If not, then you need to learn how to do that first - can't really teach it through a forum post. But, assuming you do know how to use a database and are just having a hard time understanding how to put this data into the DB, here's quick explanation.

 

Following the same logic I provided to loop through all the files you would change the processing logic to extract the data you need from each file and run an INSERT into the database. You, of course, need to create the appropriate tables and fields first. But, let's say all you are going to store is the basic information you have above. It might look something like this:

 

//Set path to foilder with json files to import into DB
$jsonDir = "path/to/json/files/";


//Get list of json files
$jsonFiles = scandir($jsonDir);

$insertValues = array();
//Read each file and add insert value to array
foreach($jsonFiles as $file)
{
   $fileContents = file_get_contents($file);
   $data = json_decode($fileContents, true);
   $insertValues[] = "('{$data[FirstName]}', '{$data[LastName]}', '{$data[Height]}', '{$data[Rating]}',
				    '{$data[Attribute1]}', '{$data[Attribute2]}', '{$data[Attribute3]}',
				    '{$data[Attribute4]}', '{$data[Attribute5]}', '{$data[Attribute6]}')";
}

//Create single insert statement to add data to DB
$query = "INSERT into players
		  (firstname, surname, height, rating, pace, shooting, passing, dribbling, defending, heading)
	  VALUES " . implode(', ', $insertValues);
$result = mysql_query($query) or die(mysql_error());

[smoseley]

Actually, if you're gonna keep it file-based, I think keeping it as separate files and using grep would be the fastest solution. Cheers.

[codingbenn]

No, put the data into a database. I don't know all the data that exists in those files or how much of the data you need. But, a bigger question is whether you know how to use a database or not. If not, then you need to learn how to do that first - can't really teach it through a forum post. But, assuming you do know how to use a database and are just having a hard time understanding how to put this data into the DB, here's quick explanation.

 

Following the same logic I provided to loop through all the files you would change the processing logic to extract the data you need from each file and run an INSERT into the database. You, of course, need to create the appropriate tables and fields first. But, let's say all you are going to store is the basic information you have above. It might look something like this:

 

//Set path to foilder with json files to import into DB
$jsonDir = "path/to/json/files/";


//Get list of json files
$jsonFiles = scandir($jsonDir);

$insertValues = array();
//Read each file and add insert value to array
foreach($jsonFiles as $file)
{
$fileContents = file_get_contents($file);
$data = json_decode($fileContents, true);
$insertValues[] = "('{$data[FirstName]}', '{$data[LastName]}', '{$data[Height]}', '{$data[Rating]}',
				 '{$data[Attribute1]}', '{$data[Attribute2]}', '{$data[Attribute3]}',
				 '{$data[Attribute4]}', '{$data[Attribute5]}', '{$data[Attribute6]}')";
}

//Create single insert statement to add data to DB
$query = "INSERT into players
		 (firstname, surname, height, rating, pace, shooting, passing, dribbling, defending, heading)
	 VALUES " . implode(', ', $insertValues);
$result = mysql_query($query) or die(mysql_error());

the information inside the files is like this;

 

{"Item":{"FirstName":"Frederik","LastName":"Boi","CommonName":null,"Height":"184","DateOfBirth":{"Year":"1981","Month":"10","Day":"25"},"PreferredFoot":"Right","ClubId":"100087","LeagueId":"4","NationId":"7","Rating":"64","Attribute1":"60","Attribute2":"65","Attribute3":"64","Attribute4":"59","Attribute5":"61","Attribute6":"64","Rare":"1","ItemType":"PlayerM"}}

 

also do i add this into the sql execute thing?

Edited November 1, 2012 by codingbenn

[codingbenn]

this is working a treat at the moment;

<?php
$search = 'Rooney';
$lines = file('allfutplayers.txt');
// Store true when the text is found
$found = false;
foreach($lines as $line)
{
 if(strpos($line, $search) !== false)
 {
   $found = true;
   echo $line;
 }
}
// If the text was not found, show a message
if(!$found)
{
 echo "<i>No match found";
}
?>

but this displays ALL of the data for all the rooney's like this;

so how do i filter is out now? Also how do I add a searchbox for users to search in? and link it to the php?

ive tried adding

<input type="text" id="$search">

but it doesnt work.