从两个表中选择数据到两个数组[关闭]

I have 2 tables: Articles and Images. Each article has 5 linked images. I need to select all articles and all images, grouped by article ID in one query.

Simple query to get articles:

SELECT * FROM articles WHERE Del=0 AND Tip=10

To get all images for that article

SELECT * FROM images WHERE UID=articles.UID AND Tiket=articles.P_Num AND Del=0

Articles linked with Images by User_ID (UID) and User_Post_Number (P_Num) P_Num - not unique! It's not Article ID. UID + P_Num = unique.

What i need to get - Array of articles with field=array of images for each article.

To show articles with images via

foreach(Articles)
{
     show_article_content; 
     for(article[images_count])
     {
        show_image;
     }
}

//QUERY
$q = "SELECT * FROM Articles JOIN Images on Articles.article_id = Images.articles_article_id";

$result = mysql_query($sql);

$article_id = 0;//article id being proccessed

while ($row = mysql_fetch_assoc($result))
{
    /*
     * example: article with id 1 --> 5 images
     *          article with id 2 --> 5 images
     * this code prints the article once with its first image
     * then on each loop prints only the image till its 4th image
     * then reassigns the $article_id to the new article id. 
     */
    if($article_id != $row['article_id'])//if its the first time to print the article
    {
        $article_id = $row['article_id'];
        /*
         * SHOW OTHER STUFF RELATED TO THE ARTICLE
         */
        echo $row['image'];//show the first image related to this article here
    }
    else
    {
        echo $row['image'];//show other 4 images here
    }
}

I suggest something like that:

SELECT * FROM articles 
JOIN images on articles.ID = images.articleId 
ORDER BY articles.ID;

And then use foreach or while loop to build array.

For example:

foreach ($dbData as $row){
    $output[$row.ID][] = $row['image'];
}

You don't present your code, so this is only a suggestion.

Too late, but I like share this with you using PDO, as I already tested in PHP5.5:

//
$database = "mydatabase";
$host = "localhost";
$user = "my-mysql-username";
$pass = "mypassword";
//
// PDO
//
try
{
  $pdo = new PDO("mysql:host=$host;dbname=$database", $user, $pass);
}
catch (PDOException $e)
{
  echo $e->getMessage();
  exit;
}
//
$strSQL = "SELECT articles.UID,articles.title,articles.content,images.imageurl
  FROM articles 
  INNER JOIN images ON articles.UID=images.UID AND images.Tiket=articles.P_Num
  WHERE articles.Del=0 AND articles.Tip=10
  ORDER BY articles.UID,images.UID;";
//
$rst = $pdo->prepare($strSQL);
$rst->execute();
//
// initialize previous ID:
// $i: counter
// $id0: previous ID
// $id: current ID
//
$id0 = -1;
//
while($row = $rst->fetch())
{
  $id = $row["UID"];
  //
  // print once the article content or title:
  //
  if($id != $id0)
  {
    //
    // close open the previous div if existent:
    //
    if($id0 != -1)
    {
      echo "</div>";
    }
    //
    // open new div:
    //
    echo "<div>" . $row["content"];
    $id0 = $id;
  }
  //
  // show all images linked to the article:
  //
  echo "<img src=\"" . $row["imageurl"] . "\" />";
  //
}
//
// close last open div:
//
if($id0 != -1)
{
  echo "</div>";
}
//
$rst->closeCursor();

Hope this helps.