Databases
StackOverflow2013
Postsdbo
POHow to combine PHP array from user input with MySQL table for calculation (conceptually)?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to combine PHP array from user input with MySQL table for calculation (conceptually)?
    primarykey
    data
    text
    <p>I have a website, where a user can enter a time series (date; series) into a text area. The data is sent to the server using the POST method. </p> <p>I have a few tables with different time series already stored in the MySQL database. The user specified time series and those stored in the database should be combined for statistical calculations.</p> <p>I store the user input into an array using explode and want to select the time series from the database using the date as the selector (<code>WHERE date = $datefromuserinput</code>). I have two problems here: </p> <ul> <li>The date format for the array does not match the one in MySQL (ISO, o-m-d) after several transformations (e.g. <code>strtotime</code>). </li> <li>Also, the time series data type in MySQL is in DECIMAL (15,4) format, but the one in PHP is string (transformed to <code>float</code>). </li> </ul> <p>Before trying to find solutions, I would like to know if my concept is right and best. Putting the user input into a MySQL table would make calculations faster (as I understand). However, several users can use the form simultaneously that would result in overwriting the entries in the new table in MySQL, right? Should I load the complete MySQL table into a PHP array (session necessary)? </p> <p>I have read many solutions but could not find these answer. Any hint would be highly appreciated.</p> <pre><code>&lt;?php date_default_timezone_set('UTC'); //session_start(); //$datum = []; if ($_POST['usersubmit']) { if ($_POST['userinput1'] !=""){ if ($_POST['separator'] != ""){ if ($_POST['separator'] == comma){ $separatorsign = ","; } elseif ($_POST['separator'] == semicolon){ $separatorsign = ";"; } else { $separatorsign = "\t"; } } $userinput1 = $_POST['userinput1']; $zeilen= explode("\n", $userinput1); $daten = array(); foreach($zeilen as $zeile) { list($datum, $zeitreihe) = explode($separatorsign, $zeile,2); $datumkonv = strtotime($datum); $daten[]['datum'] = date('o-m-d',$datumkonv); $daten[]['zeitreihe'] = $zeitreihe; } $con = mysql_connect('localhost','username','pw'); if (!$con) { die('Could not connect: ' . mysql_error($con)); } mysql_select_db("dbname"); $sql = "SELECT series FROM mytimeseries1 WHERE date = $daten[0]['datum']"; //[0] just for trying working with the first row $result = mysql_query($sql); $row = mysql_fetch_object($result); echo "$row-&gt;series";//["series"].'&lt;br&gt;'; $row = floatval($row); $ergebnis = $row + 22; echo $ergebnis; // $row is 100.5000 but $ergebnis returns 100.5023 ?? mysql_free_result($result); mysql_close($con); echo "&lt;pre&gt;"; //print_r ($result); print_r ($row); var_dump($row); var_dump($ergebnis); } } ?&gt; &lt;table width="800"&gt; &lt;tr&gt; &lt;td width="400"&gt; &lt;form action="test.php" method="post"&gt; &lt;p&gt;&lt;textarea style="overflow-y:scroll;resize:none;" name="userinput1" id="userinput1" cols="28" rows="12"&gt;&lt;/textarea&gt;&lt;/p&gt; &lt;p&gt;Separator&lt;br&gt; &lt;input type="radio" name="separator" value="comma"&gt;Comma&lt;/p&gt; &lt;p&gt;&lt;input type="radio" name="separator" value="tab"&gt;Tab&lt;/p&gt; &lt;p&gt;&lt;input type="radio" name="separator" value="semicolon"&gt;Semicolon&lt;/p&gt; &lt;/td&gt; &lt;td width="400"&gt; &lt;p&gt;Date Format&lt;br&gt; &lt;input type="radio" name="dateformat" value="yyyy-mm-dd"&gt;YYYY-MM-DD&lt;/p&gt; &lt;p&gt;&lt;input type="radio" name="dateformat" value="yyyy-dd-mm"&gt;YYYY-DD-MM&lt;/p&gt; &lt;p&gt;&lt;input type="radio" name="dateformat" value="dd-mm-yyyy"&gt;DD-MM-YYYY&lt;/p&gt; &lt;p&gt;&lt;input type="radio" name="dateformat" value="mm-dd-yyyy"&gt;MM-DD-YYYY&lt;/p&gt; &lt;p&gt;&lt;input type="radio" name="dateformat" value="ddmmyyyy"&gt;DD.MM.YYYY&lt;/p&gt; &lt;p&gt;&lt;input type="submit" name="usersubmit" value="GO"&gt;&lt;/p&gt; &lt;/form&gt; &lt;/td&gt; &lt;/tr&gt; &lt;/table&gt; &lt;?php </code></pre> <p><strong>EDIT:</strong> After trying for hours I think that it makes more sense to upload the user input array into a temporary table in MySQL (date format is accepted etc). I had to change the foreach()-part in the code above to:</p> <pre><code>for($y = 0;$y &lt; count($zeilen);$y++){ list($datum, $zeitreihe) = explode($separatorsign, $zeilen[$y], 2); $daten[$y]['datum'] = $datum; $daten[$y]['zeitreihe'] = $zeitreihe; } </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USchriscross
    1. USchriscross
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. POHow to combine PHP array from user input with MySQL table for calculation (conceptually)?
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    2. VO
      singulars
      1. POHow to combine PHP array from user input with MySQL table for calculation (conceptually)?
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. COgot the question , would you mind showing us what have you tried so far
      singulars
      1. POHow to combine PHP array from user input with MySQL table for calculation (conceptually)?
      1. USSatya
    2. COTry, try and try again... use a decent profiler tool, write tests, or use `microtime` to measure which approach is the faster... we don't know what kind of setup you have, nor what your code looks like. Just _try_ stuff. If you read on site X that function _f_ was slower thand function _g_ 2 years ago, that doesn't mean that still holds true today... just see for yourself
      singulars
      1. POHow to combine PHP array from user input with MySQL table for calculation (conceptually)?
      1. USElias Van Ootegem
    3. CO@Satya: hope code helps. the radio field for dateformat is not in use so far.@Elias: I am not at the point to test with microtime because I am not able to have a working sample
      singulars
      1. POHow to combine PHP array from user input with MySQL table for calculation (conceptually)?
      1. USchriscross
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload