mysql一行对应多列的关联查询问题,有实例及详细描述,-PHP Tutorial-php.cn

Home

Backend Development

PHP Tutorial

mysql一行对应多列的关联查询问题,有实例及详细描述,

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 23, 2016 pm 01:49 PM

associationExampleInquire

有两个表

t1表有 4个字段  id,city_id1,city_id2,city_id3

t2表有2个字段 id,city_name

t1表中的city_id1,2,3都是和t2表中的id对应

例如:

t1表有一条记录   1,26,29,50

t2表有三条记录
26  北京
29  上海
50  天津

现在我的需求是  希望能一条sql语句查出来t1中这条记录,并显示city_id1,2,3对应的城市例如
结果: 1,北京,上海,天津

sql语句应该怎么写?

回复讨论(解决方案)

select id,  (select city_name from t2 where id=t1.city_id1) as city_name1,  (select city_name from t2 where id=t1.city_id2) as city_name2,  (select city_name from t2 where id=t1.city_id3) as city_name3 from t1

sql斑竹说了，我说另一种解决思路~
写一个函数，定义一个static变量$city数组，让它以城市id为键，城市名称为值
每次在后台编辑城市数据，就生成一个序列化城市数据的文件
系统启动时让那个静态变量读取文件
这个函数，传入城市id，就根据$city立刻返回城市名称
因为是静态变量，在单个请求中多次调用的开销不大，就是初始化需要读一个文件
这样可以解决你网站中所有靠城市id转换名称的需求

事实上我现在是每次查出一个这样的城市数组备用然后传入city_id得到城市名

你说的方法相当于将这个数组缓存挺不错的

xu大的方法可以实现但是看起来效率不会很高我之前还以为mysql能高效的处理这种匹配关系~~~ 看来只能先用缓存了

如果城市太多，这样缓存感觉也不是办法
每个请求都要读文件，反序列化，装入内存
没有实际测试过这个办法和sql比各有哪方面的强项
灵光一闪，又有个点子
在需要打印城市名称时，先写成{CITY:123}这种形式
在页面底部ob_get_clean，用正则把这些东西挖出来，去除重复id，然后根据这一组不重复id查询数据库，再把名称替换回去
这个办法可能消耗多一点CPU，但不用读取硬盘，关于城市的数据库操作也是一次完成

两种方法都尝试了最后还是用了老xu的方法效率可以接受或许是表比较小的缘故

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

How does PHP identify a user's session?May 01, 2025 am 12:23 AM

PHPidentifiesauser'ssessionusingsessioncookiesandsessionIDs.1)Whensession_start()iscalled,PHPgeneratesauniquesessionIDstoredinacookienamedPHPSESSIDontheuser'sbrowser.2)ThisIDallowsPHPtoretrievesessiondatafromtheserver.

What are some best practices for securing PHP sessions?May 01, 2025 am 12:22 AM

The security of PHP sessions can be achieved through the following measures: 1. Use session_regenerate_id() to regenerate the session ID when the user logs in or is an important operation. 2. Encrypt the transmission session ID through the HTTPS protocol. 3. Use session_save_path() to specify the secure directory to store session data and set permissions correctly.

Where are PHP session files stored by default?May 01, 2025 am 12:15 AM

PHPsessionfilesarestoredinthedirectoryspecifiedbysession.save_path,typically/tmponUnix-likesystemsorC:\Windows\TemponWindows.Tocustomizethis:1)Usesession_save_path()tosetacustomdirectory,ensuringit'swritable;2)Verifythecustomdirectoryexistsandiswrita

How do you retrieve data from a PHP session?May 01, 2025 am 12:11 AM

ToretrievedatafromaPHPsession,startthesessionwithsession_start()andaccessvariablesinthe$_SESSIONarray.Forexample:1)Startthesession:session_start().2)Retrievedata:$username=$_SESSION['username'];echo"Welcome,".$username;.Sessionsareserver-si

How can you use sessions to implement a shopping cart?May 01, 2025 am 12:10 AM

The steps to build an efficient shopping cart system using sessions include: 1) Understand the definition and function of the session. The session is a server-side storage mechanism used to maintain user status across requests; 2) Implement basic session management, such as adding products to the shopping cart; 3) Expand to advanced usage, supporting product quantity management and deletion; 4) Optimize performance and security, by persisting session data and using secure session identifiers.

How do you create and use an interface in PHP?Apr 30, 2025 pm 03:40 PM

The article explains how to create, implement, and use interfaces in PHP, focusing on their benefits for code organization and maintainability.

What is the difference between crypt() and password_hash()?Apr 30, 2025 pm 03:39 PM

The article discusses the differences between crypt() and password_hash() in PHP for password hashing, focusing on their implementation, security, and suitability for modern web applications.

How can you prevent Cross-Site Scripting (XSS) in PHP?Apr 30, 2025 pm 03:38 PM

Article discusses preventing Cross-Site Scripting (XSS) in PHP through input validation, output encoding, and using tools like OWASP ESAPI and HTML Purifier.

See all articles