排序多个表MYSQL DateStamp [英] Sorting multiple tables MYSQL DateStamp

问题描述

我正在构建一个简单的买卖网站，并希望在第一页上列出最新的广告.在我的数据库中，我有 4 个表.USERS、CARS、ELECTRONICS 和 COMPUTERS.

I'm building a simple sell&buy-site and want to list the latest ads on the first pages. In my database I have 4 tables. USERS , CARS, ELECTRONICS and COMPUTERS.

三个项目表(CARS、ELECTRONICS 和COMPUTERS)中的每一个都有DATESTAMP，但是我无法在使用 MySQL 和 PHP 的 DATESTAMP 之后轻松地对它们进行排序.

Each of the three tables of items (CARS, ELECTRONICS and COMPUTERS) have DATESTAMP in them, but I can't manage to easily sort them after this DATESTAMP using MySQL and PHP.

它们都包含不同的列，但具有共同的 DATESTAMP 和 SSN(标识用户如何拥有广告).

They all contain different columns but have DATESTAMP and SSN (identifying the user how owns the ad) in common.

我试过加入表格，在 php 中使用 strtotime 对它们进行排序......但我似乎无法理解语法.玩过这段代码..但它变得非常复杂，我希望我可以在纯 SQL 中做得更容易.它不完整但是..你可以看到我在想什么..

I've tried Joining the tables, sorting them using strtotime in php.. but I can't seem to get the syntax to play along. Played with this code.. but It got very complicated and I was hoping I could do it easier in pure SQL.It's not complete but.. you can see what I was thinking..

 <?php

   mysql_connect("localhost","root","");
   mysql_select_db("project");
   $SSN = utf8_decode(strip_tags($_GET['ssn'])); 

   //firstname,lastname, email, phone, address, zipcode, district
   $result = mysql_query("SELECT * 
                            FROM CARS 
                           WHERE CARS.SSN = '$SSN'");

   $result2 = mysql_query("SELECT * 
                             FROM ELECTRONICS 
                            WHERE ELECTRONICS s.SSN = '$SSN'");

   if (!$result) {
     echo 'Could not run query: ' . mysql_error();
     exit;
   }

   while($obj = mysql_fetch_object($result)) {
     $arr[] = $obj;
   }

   echo '{"users":'.json_encode($arr).'}';

   if (!$result2) {
     echo 'Could not run query: ' . mysql_error();
     exit;
   }

   while($obj = mysql_fetch_object($result2)) {
     $arr[] = $obj;
   }

   echo '{"users":'.json_encode($arr).'}';

   function mysort($a, $b) { 
     return(strtotime($b['datePosted']) - strtotime($a['datePosted'])); 
   } 

   // pre-sort: 
   echo "<pre>Before:\n"; 
   print_r($arr); 
   // do the sort: 
   usort($arr, 'mysort'); 
   // show the result: 
   echo "After:\n"; 
   print_r($arr); 
   echo "</pre>"; 
?>

推荐答案

从设计的角度来看，您可以拥有一个 ITEMS 表，其中包含与所有项目类型相关的属性，例如 ID、DATESTAMP、SSN.在其他每个与项目相关的表上都有与 ITEMS 表中相关项目的 ID 匹配的 ID PK.

From a design standpoint, you could have an ITEMS table that contains attributes that relate to all types of item, for example ID, DATESTAMP, SSN. Have ID PKs on each of the other item-related tables that match the ID of the relevant item from the ITEMS table.

ITEMS

ID  DATESTAMP  SSN
1   2011-01-01 12345
2   2011-01-02 12345
3   2011-01-04 54321

CARS

ID MANUFACTURER MODEL 
1  Volvo        V40
3  Volkswagen   Beetle

COMPUTERS

ID BRAND PROCESSOR
2  Dell  386

这篇关于排序多个表MYSQL DateStamp的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！