gpt4 book ai didi

php - 从多个 mySQL 表中检索数据

转载 作者:行者123 更新时间:2023-11-29 14:40:42 24 4
gpt4 key购买 nike

我会尝试尽可能简洁地解释这一点,因为我对此还很陌生。

我有 3 个表:文章;类别;文章分类

完整的 Article 类如下。我的目标也是通过函数 getById 来拉动类别。

<?php

/**
* Class to handle articles
*/

class Article
{

// Properties

/**
* @var int The article ID from the database
*/
public $id = null;

/**
* @var int When the article was published
*/
public $publicationDate = null;

/**
* @var string Full title of the article
*/
public $title = null;

/**
* @var string A short summary of the article
*/
public $summary = null;

/**
* @var string The HTML content of the article
*/
public $content = null;


/**
* Sets the object's properties using the values in the supplied array
*
* @param assoc The property values
*/

public function __construct( $data=array() ) {
if ( isset( $data['article_id'] ) ) $this->id = (int) $data['article_id'];
if ( isset( $data['publicationDate'] ) ) $this->publicationDate = (int) $data['publicationDate'];
if ( isset( $data['title'] ) ) $this->title = preg_replace ( "/[^\.\,\-\_\'\"\@\?\!\:\$ a-zA-Z0-9()]/", "", $data['title'] );
if ( isset( $data['summary'] ) ) $this->summary = preg_replace ( "/[^\.\,\-\_\'\"\@\?\!\:\$ a-zA-Z0-9()]/", "", $data['summary'] );
if ( isset( $data['content'] ) ) $this->content = $data['content'];
}


/**
* Sets the object's properties using the edit form post values in the supplied array
*
* @param assoc The form post values
*/

public function storeFormValues ( $params ) {

// Store all the parameters
$this->__construct( $params );

// Parse and store the publication date
if ( isset($params['publicationDate']) ) {
$publicationDate = explode ( '-', $params['publicationDate'] );

if ( count($publicationDate) == 3 ) {
list ( $d, $m, $y ) = $publicationDate;
$this->publicationDate = mktime ( 0, 0, 0, $m, $d, $y );
}
}
}


/**
* Returns an Article object matching the given article ID
*
* @param int The article ID
* @return Article|false The article object, or false if the record was not found or there was a problem
*/

public static function getById( $id ) {
$conn = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD );
$sql = "SELECT *, UNIX_TIMESTAMP(publicationDate) AS publicationDate FROM articles WHERE article_id = :id";
$st = $conn->prepare( $sql );
$st->bindValue( ":id", $id, PDO::PARAM_INT );
$st->execute();
$row = $st->fetch();
$conn = null;
if ( $row ) return new Article( $row );
}


/**
* Returns all (or a range of) Article objects in the DB
*
* @param int Optional The number of rows to return (default=all)
* @param string Optional column by which to order the articles (default="publicationDate DESC")
* @return Array|false A two-element array : results => array, a list of Article objects; totalRows => Total number of articles
*/

public static function getList( $numRows=1000000, $order="publicationDate DESC" ) {
$conn = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD );
$sql = "SELECT SQL_CALC_FOUND_ROWS *, UNIX_TIMESTAMP(publicationDate) AS publicationDate FROM articles
ORDER BY " . mysql_escape_string($order) . " LIMIT :numRows";

$st = $conn->prepare( $sql );
$st->bindValue( ":numRows", $numRows, PDO::PARAM_INT );
$st->execute();
$list = array();

while ( $row = $st->fetch() ) {
$article = new Article( $row );
$list[] = $article;
}

// Now get the total number of articles that matched the criteria
$sql = "SELECT FOUND_ROWS() AS totalRows";
$totalRows = $conn->query( $sql )->fetch();
$conn = null;
return ( array ( "results" => $list, "totalRows" => $totalRows[0] ) );
}


/**
* Inserts the current Article object into the database, and sets its ID property.
*/

public function insert() {

// Does the Article object already have an ID?
if ( !is_null( $this->id ) ) trigger_error ( "Article::insert(): Attempt to insert an Article object that already has its ID property set (to $this->id).", E_USER_ERROR );

// Insert the Article
$conn = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD );
$sql = "INSERT INTO articles ( publicationDate, title, summary, content ) VALUES ( FROM_UNIXTIME(:publicationDate), :title, :summary, :content )";



//PDO prepare escapes string like mysql_real_escape_string
//PDO Allows you to enforce a datatypye ie. Sting, Integer etc
$st = $conn->prepare ( $sql );
$st->bindValue( ":publicationDate", $this->publicationDate, PDO::PARAM_INT );
$st->bindValue( ":title", $this->title, PDO::PARAM_STR );
$st->bindValue( ":summary", $this->summary, PDO::PARAM_STR );
$st->bindValue( ":content", $this->content, PDO::PARAM_STR );
$st->execute();
$this->id = $conn->lastInsertId();
$conn = null;
}


/**
* Updates the current Article object in the database.
*/

public function update() {

// Does the Article object have an ID?
if ( is_null( $this->id ) ) trigger_error ( "Article::update(): Attempt to update an Article object that does not have its ID property set.", E_USER_ERROR );

// Update the Article
$conn = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD );
$sql = "UPDATE articles SET publicationDate=FROM_UNIXTIME(:publicationDate), title=:title, summary=:summary, content=:content WHERE id = :id";
$st = $conn->prepare ( $sql );
$st->bindValue( ":publicationDate", $this->publicationDate, PDO::PARAM_INT );
$st->bindValue( ":title", $this->title, PDO::PARAM_STR );
$st->bindValue( ":summary", $this->summary, PDO::PARAM_STR );
$st->bindValue( ":content", $this->content, PDO::PARAM_STR );
$st->bindValue( ":id", $this->id, PDO::PARAM_INT );
$st->execute();
$conn = null;
}


/**
* Deletes the current Article object from the database.
*/

public function delete() {

// Does the Article object have an ID?
if ( is_null( $this->id ) ) trigger_error ( "Article::delete(): Attempt to delete an Article object that does not have its ID property set.", E_USER_ERROR );

// Delete the Article
$conn = new PDO( DB_DSN, DB_USERNAME, DB_PASSWORD );
$st = $conn->prepare ( "DELETE FROM articles WHERE id = :id LIMIT 1" );
$st->bindValue( ":id", $this->id, PDO::PARAM_INT );
$st->execute();
$conn = null;
}

}

?>

我需要做的是输出文章属于哪个多个类别。我是否必须在这个函数中创建另一个 SQL 语句,或者我可以在一个语句中完成它。

表/列概述:文章 - id、出版日期、标题、摘要、内容类别 - id、类别标题article_to_categories -article_id、category_id

我希望这些信息足以让有人在我的道路上为我提供帮助。

谢谢。

最佳答案

我认为你应该修改你的类并添加

public $categorie = null; //because you will add new column to table article 

并修改依赖于它的所有代码,将该列添加到您的数据库中。等等

关于php - 从多个 mySQL 表中检索数据,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/8041491/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com