Table of Contents
回复讨论(解决方案)
Home Backend Development PHP Tutorial 查询2张表并输出

查询2张表并输出

Jun 23, 2016 pm 02:05 PM

整个情况是这样的:
 A表结构
 id name   info
 1  栏目一  (存储样式为a:{s:6:"picid";s:2:"74";s:9:"text";s:1:"存储内容区";})
 2  栏目二  (存储样式为a:{s:6:"picid";s:2:"75";s:9:"text";s:1:"存储内容区";})
 B表结构
 id url
 74 图片路径
 首先通过查询获取到了A表info中picid的id号为74,75的数组
 输出样式为:
 Array
 (
     [0] => 74
     [1] => 75
 )
 然后在查询B表id为74,75的数据
 然后如何关联输出这2张表的内容呢?
 输出结果如何为:
 Array
 (
     [0] => Array
         (
             [id] => 1
             [name] => 栏目一
             [url] => 图片路径
             [info] => info中text的内容
         )
 
    [1] => Array
         (
             [id] => 2
             [name] => 栏目一
               [url] => 图片路径
               [info] => info中text的内容
         )
 .... 


回复讨论(解决方案)

直接sql不好查,建议用程序来实现。

直接sql不好查,建议用程序来实现。
怎么弄呢

a:{s:6:"picid";s:2:"74";s:9:"text";s:1:"存储内容区";} 这个序列化后的串吧,不过不对。

 首先通过查询获取到了A表info中picid的id号为74,75的数组
  这个又是怎么来的,栏目一不是只有74 吗?

首先通过查询获取到了A表info中picid的id号为74,75的数组
  这个又是怎么来的,栏目一不是只有74 吗?
少打个..略了刚刚
a:{s:6:"picid";s:2:"74";s:9:"text";s:1:"存储内容区";} 这个序列化后的串吧,不过不对。 
我没复制完全

查询两次,你用的json连表查询不行的。

取出 info 中的 text 的内容的表达式
substring_index(substring_index(substr(info, locate('"text"', info)), '"', 4), '"', -1) as info

两表的关联条件
B表.id = substring_index(substring_index(substr(info, locate('"picid"', info)), '"', 4), '"', -1)

表达式很长,你可以用存储过程来缩短他

原理很简单
a:{s:6:"picid";s:2:"74";s:9:"text";s:1:"存储内容区";} 
substr(info, locate('"picid"', info)) 用于取得 "picid" 看是的一段
"picid";s:2:"74";s:9:"text";s:1:"存储内容区";}
内层的 substring_index 用于取得 " 第4次出现以前的内容
"picid";s:2:"74
外层的 substring_index 用于取得 " 最后一次出现以后的内容
74

Statement of this Website
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

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Explain JSON Web Tokens (JWT) and their use case in PHP APIs. Explain JSON Web Tokens (JWT) and their use case in PHP APIs. Apr 05, 2025 am 12:04 AM

JWT is an open standard based on JSON, used to securely transmit information between parties, mainly for identity authentication and information exchange. 1. JWT consists of three parts: Header, Payload and Signature. 2. The working principle of JWT includes three steps: generating JWT, verifying JWT and parsing Payload. 3. When using JWT for authentication in PHP, JWT can be generated and verified, and user role and permission information can be included in advanced usage. 4. Common errors include signature verification failure, token expiration, and payload oversized. Debugging skills include using debugging tools and logging. 5. Performance optimization and best practices include using appropriate signature algorithms, setting validity periods reasonably,

Explain the concept of late static binding in PHP. Explain the concept of late static binding in PHP. Mar 21, 2025 pm 01:33 PM

Article discusses late static binding (LSB) in PHP, introduced in PHP 5.3, allowing runtime resolution of static method calls for more flexible inheritance.Main issue: LSB vs. traditional polymorphism; LSB's practical applications and potential perfo

Describe the SOLID principles and how they apply to PHP development. Describe the SOLID principles and how they apply to PHP development. Apr 03, 2025 am 12:04 AM

The application of SOLID principle in PHP development includes: 1. Single responsibility principle (SRP): Each class is responsible for only one function. 2. Open and close principle (OCP): Changes are achieved through extension rather than modification. 3. Lisch's Substitution Principle (LSP): Subclasses can replace base classes without affecting program accuracy. 4. Interface isolation principle (ISP): Use fine-grained interfaces to avoid dependencies and unused methods. 5. Dependency inversion principle (DIP): High and low-level modules rely on abstraction and are implemented through dependency injection.

How to automatically set permissions of unixsocket after system restart? How to automatically set permissions of unixsocket after system restart? Mar 31, 2025 pm 11:54 PM

How to automatically set the permissions of unixsocket after the system restarts. Every time the system restarts, we need to execute the following command to modify the permissions of unixsocket: sudo...

How does session hijacking work and how can you mitigate it in PHP? How does session hijacking work and how can you mitigate it in PHP? Apr 06, 2025 am 12:02 AM

Session hijacking can be achieved through the following steps: 1. Obtain the session ID, 2. Use the session ID, 3. Keep the session active. The methods to prevent session hijacking in PHP include: 1. Use the session_regenerate_id() function to regenerate the session ID, 2. Store session data through the database, 3. Ensure that all session data is transmitted through HTTPS.

Explain late static binding in PHP (static::). Explain late static binding in PHP (static::). Apr 03, 2025 am 12:04 AM

Static binding (static::) implements late static binding (LSB) in PHP, allowing calling classes to be referenced in static contexts rather than defining classes. 1) The parsing process is performed at runtime, 2) Look up the call class in the inheritance relationship, 3) It may bring performance overhead.

How to debug CLI mode in PHPStorm? How to debug CLI mode in PHPStorm? Apr 01, 2025 pm 02:57 PM

How to debug CLI mode in PHPStorm? When developing with PHPStorm, sometimes we need to debug PHP in command line interface (CLI) mode...

See all articles