Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>after reading your problem several times I'm assuming that your require a way to compare data from two different tables.</p> <p>For demonstration purpose and simplicity, assume that you have two table <code>official</code> and <code>unofficial</code>.</p> <p><code>official</code> and <code>unofficial</code> tables share the same structure which is-</p> <pre><code>------------+-------------+----------+----------+----------- mapid | authid | name | time | date ------------+-------------+----------+----------+----------- map1name | 1 | bob | 03:40:17 | 2013-08-25 ------------+-------------+----------+----------+----------- map2name | 2 | hop | 01:10:56 | 2012-04-12 ------------+-------------+----------+----------+----------- map3name | 3 | cherry | 11:08:14 | 2013-05-14 ------------+-------------+----------+----------+----------- map4name | 4 | adam | 09:16:43 | 2013-07-10 ------------+-------------+----------+----------+----------- map5name | 5 | zyl | 06:01:53 | 2012-09-30 ------------+-------------+----------+----------+----------- </code></pre> <p>Now what you can do is perform some advanced mysqlqueries to find data from both tables.</p> <pre><code>SELECT * FROM `unofficial` JOIN `official` WHERE `official.mapid` = `unofficial.mapid` AND TIME_TO_SEC(`official.time`) &gt; TIME_TO_SEC(`unofficial.time`) AND ABS( TIME_TO_SEC(`official.time`) - TIME_TO_SEC(`unofficial.time`) ) &lt; 1800 </code></pre> <p>WHAT IS DONE HERE:</p> <p>Here we have performed a mysql query joining both table and fetching results after performing three conditions- </p> <p>(i) their map ids have to be same. From your codes above I think you want to match the map ids from both tables in order to find the unofficial world records.</p> <p>(ii) time from <code>unofficial</code> table has to be less than the time of the <code>official</code> table. Again I'm assuming it from your code above. <code>TIME_TO_SEC</code> is a <code>mysql</code> function which converts time to seconds. Like TIME_TO_SEC('01:01:06') returns 3666.</p> <p>(iii) And the third is the difference between the time is lower than 30 seconds. Again its in your code.</p> <p>My query might have some errors like spelling mistake or selecting wrong columns etc as i didn't test it against a real database. But the idea is pretty much same. You can search for the appropriate query or read the documentation.</p> <p>I hope that will help you.</p>
    singulars
    1. This table or related slice is empty.
    1. POComparing Time Values To Find New Records Taking Forever
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USmaksbd19
    1. USmaksbd19
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POComparing Time Values To Find New Records Taking Forever
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COgood to point out the existence of sql for this problem. You are, however, converting the mapids to seconds :)
      singulars
      1. PO
      1. USxtofl
    2. CORight now I am using a task/cronjob to parse a .txt file once per day (to keep the official records up-to-date) and store it in json format in a file on my server. My PHP is currently reading from this json file when I run my script. Would it be beneficial to store the official world records in another table in my database so I could test your method using pure SQL?
      singulars
      1. PO
      1. UStenub
    3. COit should. Databases are designed to store and retrieve data and they can do it immense powerfully. you should directly update the database table for your official records. You can use a php script to parse the json file and update it to the database and again in reverse return data from the database in json object. In that way you can just use php to deliver your data and use sql to fetch your data based on any condition you like. I think its more appropriate.
      singulars
      1. PO
      1. USmaksbd19
 

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