在同一表中回显具有相同列名的多个值 [英] Echo multiple values with same column name in same table

问题描述

我有2个表，一个用户表和一个交易表.

I have 2 tables, a users table and a trade table.

外观如下:

我现在的代码结构为:

<?php 
$history = mysqli_query($con, "SELECT * FROM .......");

while($row = mysqli_fetch_array($history)) { 
echo("The sentence");
} ?>

我面临的问题是我试图回显user_name，在某种情况下，user_name必须是接收者，而其他人是提供它的人.

Problem I'm facing is that I'm trying to echo the user_name which in one case has to be the receiver and other the person giving it.

推荐答案

提示:切勿在软件中使用SELECT *，除非您确切知道这样做的原因.就您而言，这是有害的.

Pro tip: Never use SELECT * in software unless you know exactly why you are doing so. In your case it is harmful.

我假设您的查询确实针对您在问题中提到的user和trade表.

I'm assuming your query is really against the user and trade tables you mentioned in your question.

首先，使用21世纪SQL重播您的查询，如下所示:

First, recast your query using 21st century SQL, as follows:

SELECT * 
  FROM trade AS t
  JOIN user AS s ON  s.user_id = t.user_id_sender
 WHERE s.facebook_id = $fbid 

第二，使用它来检索您的用户名和交易的商品ID.

Second, use this to retrieve your user's names and the item id traded.

SELECT s.user_name AS sender,
       r.user_name AS receiver,
       t.trade_id AS item_id
  FROM trade AS t
  JOIN user AS s ON  s.user_id = t.user_id_sender
  JOIN user AS r ON  r.user_id = t.user_id_receiver
 WHERE s.facebook_id = $fbid 

看看我们如何两次JOIN user表，并使用两个不同的别名s(对于发送方)和r(对于接收方)?这是从ID提取两个名称的窍门.

See how we JOIN the user table twice, with two different aliases s (for sender) and r (for receiver)? That's the trick to fetching both names from IDs.

看看我们如何使用别名sender和receiver来消除结果集中的两个user_name列的歧义吗?

See how we employ the aliases sender and receiver to disambiguate the two user_name columns in the result set?

现在，当您使用php fetch_array函数时，最终将在数组中包含这些元素.

Now, when you use the php fetch_array function, you'll end up with these elements in the array.

$history['sender']
$history['receiver']
$history['item_id']

数组索引字符串与您在查询的SELECT子句中指定的别名相对应.

The array index strings correspond to the alias names you specified in your SELECT clause in your query.

因此，避免使用SELECT *的一个原因是，您可以获得多个具有相同名称的列，这意味着fetch_array将消除这些重复项，因此将从您的结果集中丢失有用的信息.

So, one reason to avoid SELECT * is that you can get more than one column with the same name, and that means fetch_array will eliminate those duplicates and so it will lose useful information from your result set.

这篇关于在同一表中回显具有相同列名的多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！