Page 1 of 1

PHP Jquery and Datatables

Posted: Thu Jan 16, 2014 9:51 am
by jjf3
Hello All,

I am new to the forums and I have a couple of questions. I have a PHP script that is needed to take information from a text file and converts that to needed information for our dealers. Our dealers mainly use ipads to check the inventory in our warehouses to show their customers, which is what the information is. The CSS and Jquery works fine, but the problem I am running into is that the script takes way to long to load the information that it is pulling from the text file. Which is
huge!

I know I will get a lot of responses that say I should put this into a PHP/SQL datatabase, but I'm just not sure how that will communicate with the program that generates this data. I do not think it is compatible. Also, is there anyway to make this more Ipad friendly? It takes even longer to load on the mobile devices.

Code: Select all

<?php

set_time_limit(0);
function csv_split($line,$delim=',',$removeQuotes=true) { 
#$line: the csv line to be split 
#$delim: the delimiter to split by 
#$removeQuotes: if this is false, the quotation marks won't be removed from the fields 
   $fields = array(); 
   $fldCount = 0; 
   $inQuotes = false; 
   for ($i = 0; $i < strlen($line); $i++) { 
       if (!isset($fields[$fldCount])) $fields[$fldCount] = ""; 
       $tmp = substr($line,$i,strlen($delim)); 
       if ($tmp === $delim && !$inQuotes) { 
           $fldCount++; 
           $i += strlen($delim)-1; 
       } else if ($fields[$fldCount] == "" && $line[$i] == '"' && !$inQuotes) { 
           if (!$removeQuotes) $fields[$fldCount] .= $line[$i]; 
           $inQuotes = true; 
       } else if ($line[$i] == '"') { 
           if ($line[$i+1] == '"') { 
               $i++; 
               $fields[$fldCount] .= $line[$i]; 
           } else { 
               if (!$removeQuotes) $fields[$fldCount] .= $line[$i]; 
               $inQuotes = false; 
           } 
       } else { 
           $fields[$fldCount] .= $line[$i]; 
       } 
   } 
   return $fields; 
} 
$html_body = '<html>....<tbody>'
$fp=fopen("csv/inventory4.html",'w');
$write=fputs($fp,$html_body,strlen($html_body));
$i=0;
$content = file("webinvt.txt");
foreach($content as $line)
{
  $l=csv_split($line);
  if(!strstr($l[11],"SET"))
  {
  if($i==7000)
  {
    $tmp = '<tr>';
    $write=fputs($fp,$tmp,strlen($tmp));
    $i=0;
  }
  $onhand = (int)$l[15];
  $committed = (int)$l[16];
  $avail = $onhand - $committed;
  $wcdate = substr($l[23],4);
  $eastdate = substr($l[19],4);

  if(strstr($l[1],"DISC"))
  {
    $html_body ='<tr style="color:#FF0000">
    <td>'.$l[0].'</td>
    <td>'.$l[1].'</td>
    <td>'.$l[12].'</td>
    <td align="center">'.$avail.'</td>
    <td>'.$l[17].'</td>
    <td>'.$l[18].'</td>
    <td>'.$eastdate.'</td>
    <td>'.$l[21].'</td>
    <td>'.$l[22].'</td>
    <td>'.$wcdate.'</td>
      </tr>';
    }
  else
  {
    $html_body ='<tr>
    <td>'.$l[0].'</td>
    <td>'.$l[1].'</td>
    <td>'.$l[12].'</td>
    <td align="center">'.$avail.'</td>
    <td>'.$l[17].'</td>
    <td>'.$l[18].'</td>
    <td>'.$eastdate.'</td>
    <td>'.$l[21].'</td>
    <td>'.$l[22].'</td>
    <td>'.$wcdate.'</td>
      </tr> 
';
  }
  
  $write=fputs($fp,$html_body,strlen($html_body));
  $i++;
  }
}

$html_body='
 
</tbody>
</table>
</div>
</body>
</html>';

 $write=fputs($fp,$html_body,strlen($html_body));

fclose($fp);



Re: PHP Jquery and Datatables

Posted: Thu Jan 16, 2014 9:55 am
by Celauran
jjf3 wrote:I know I will get a lot of responses that say I should put this into a PHP/SQL datatabase
Absolutely. Use the right tool for the job.
I do not think it is compatible.
You don't think what is compatible with what? Is this not simply a web page being viewed with an iPad?

Re: PHP Jquery and Datatables

Posted: Thu Jan 16, 2014 10:41 am
by jjf3
NO, it also pulls information out of our unix system and I'm not sure if that would interfere with that program. I would have no clue how to start converting this to work with SQL since there are a lot more other options that are on the text file not shown on the website.

Re: PHP Jquery and Datatables

Posted: Thu Jan 16, 2014 6:41 pm
by Celauran
jjf3 wrote:NO, it also pulls information out of our unix system and I'm not sure if that would interfere with that program. I would have no clue how to start converting this to work with SQL since there are a lot more other options that are on the text file not shown on the website.
I have to ask: if it's not a web page, what is it?

I'd take some time to read up on SQL. It really sounds like you need a DB here. The fact that there are fields that won't be displayed is irrelevant.

Re: PHP Jquery and Datatables

Posted: Fri Jan 17, 2014 8:23 am
by jjf3
Celauran wrote:
jjf3 wrote:NO, it also pulls information out of our unix system and I'm not sure if that would interfere with that program. I would have no clue how to start converting this to work with SQL since there are a lot more other options that are on the text file not shown on the website.
I have to ask: if it's not a web page, what is it?

I'd take some time to read up on SQL. It really sounds like you need a DB here. The fact that there are fields that won't be displayed is irrelevant.
In My OP I explained that it is an inventory list which is pulled from multiple areas and different warehouses. I know where to look for for general information about SQL but can anyone point me to where I should go to look for information in which I can start to convert this code into SQL? A general direction is fine, like what I should be really focusing on.

Re: PHP Jquery and Datatables

Posted: Fri Jan 17, 2014 8:39 am
by Celauran
What format is the data in? If it's CSV or similar, you can probably import it directly via phpMyAdmin or whatever desktop client you're using. You'll need to have the database table created first, but that's also relatively straightforward. If it's a different format, you may want need to come up with a script that parses it and does a mass insert.

Re: PHP Jquery and Datatables

Posted: Fri Jan 17, 2014 9:45 am
by jjf3
Celauran wrote:What format is the data in? If it's CSV or similar, you can probably import it directly via phpMyAdmin or whatever desktop client you're using. You'll need to have the database table created first, but that's also relatively straightforward. If it's a different format, you may want need to come up with a script that parses it and does a mass insert.

I believe it is a csv. Though it is actually a txt file. With a bunch of data separated by quotes and commas. So the raw data looks like,[text] "data," "data," "data," etc.... [/text]What I have now is a script that parses the raw data into an HTML table. So I guess I am halfway there. The original programmer did not include an SQL database. This has about 4600 items loading into the table. Is the SQL table faster than the HTML one? What is the difference between what I have now, and what people are telling me? That I need a database?

Re: PHP Jquery and Datatables

Posted: Fri Jan 17, 2014 9:53 am
by Celauran
The biggest difference is indexing, really. Retrieving a particular subset of data from a well-indexed SQL table will be exponentially faster than parsing an entire text file and searching through that to find what you want. The more your data set grows, the more evident this will become. You're facing speed issues on a tiny data set while SQL can handle hundreds of thousands of records with ease.

Re: PHP Jquery and Datatables

Posted: Fri Jan 17, 2014 10:58 am
by jjf3
Celauran wrote:The biggest difference is indexing, really. Retrieving a particular subset of data from a well-indexed SQL table will be exponentially faster than parsing an entire text file and searching through that to find what you want. The more your data set grows, the more evident this will become. You're facing speed issues on a tiny data set while SQL can handle hundreds of thousands of records with ease.

Thank you for clearing that up. I know more about PHP than SQL. I used PHP in one of my other jobs, but never SQL. If I were to run some SQL tests to make sure it can actually work to fulfill our needs where should I start and what programs should I use that are free?

Re: PHP Jquery and Datatables

Posted: Fri Jan 17, 2014 11:13 am
by Celauran
MySQL is free for all platforms. What to install where really depends on your OS and current setup. MAMP (OS X) and XAMPP (Windows) will provide you with Apache, PHP, MySQL, and phpMyAdmin, which should be enough to get you started. If you're on Linux, you can install all of these directly through your package manager. If you're looking for a GUI client for MySQL, Sequel Pro (OS X) really can't be beat. HeidiSQL (Windows) is pretty good, too. emma (Linux/GTK) gets the job done but is less pretty IMO. phpMyAdmin gives you a web interface which tries to approximate the functionality of the above and may be good enough while you're getting started, to avoid having to learn too many different bits of software at the same time.

To get you started with MySQL, maybe take a look here? http://www.mysqltutorial.org/