[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

PHP to display MySQL data with multiple relationships

Posted on 2007-10-21
5
Medium Priority
?
687 Views
Last Modified: 2013-12-13
I have a PHP page that queries a MySQL DB and displays the proper data however, I'm having a problem displaying in the format I wish.

As an example let's say I have a table that lists users with an article they have written and I want to display each user and each article they have written and the date.  Using my current method this is the output I get:

user1  article1 date1
user1  article2 date2
user1  article3 date3
user4  article1 date1
user4  article2 date2
,
,
,
and so on.

What I really want is:

user1
date1 article 1
date2 article 2
date3 article 3

user4
date1 article1
date2 article2

Is there any easy way to accomplish this?

Thanks,

- dan -
0
Comment
Question by:dansoto
  • 2
  • 2
5 Comments
 
LVL 13

Expert Comment

by:MasonWolf
ID: 20119707
sure, just say:

SELECT * FROM articles WHERE user = 1

This will return a result set for every article that the user with id "1" has saved
0
 
LVL 5

Expert Comment

by:jericotolentino
ID: 20119755
If your list of users and the list of articles are in separate tables, you'll want to do a JOIN query or a subquery at least. If they're in the same table, that would mean less code for the moment (but you'll find it hard to organize if it gets too big).

But for formatting purposes, I'd suggest the use of a table:

// In here, $result is your SQL query
while ($row = mysql_fetch_array($result))
echo '<table>';
echo '<tr><td colspan="2">'.$row['user'].'</td></tr>';
while (there_are_documents_for_User_X)
{
echo '<tr><td>'.$row['date'].'</td><td>'.$row['article'].'</td></tr>';
}
echo '</table>';

You'll still need to put this inside another loop though if you'd want to include all users. Hope this helps!
0
 
LVL 7

Author Comment

by:dansoto
ID: 20120048
Jerico...

Your solution is on track but I'm still missing how to complete formatting the loops but I can see where you're going.  

Two questions:

1) How do I define "while (there_are_documents_for_User_X)"
2) How do I format the outer loop?

Thanks,

- dan -
0
 
LVL 5

Accepted Solution

by:
jericotolentino earned 960 total points
ID: 20122312
Sure. Here's what's basically happening in the code below:

1. I send a query to the database that gets results from two tables: articles and user.
2. I've modified the algorithm, so it would just use one loop. The inner loop I was referring to earlier is replaced by if-then statements.

The code....
===================
$result = mysql_query('select users.user as user, articles.articledate as date, articles.articletitle as title from users left join articles on articles.owner = users.id order by user,articledate asc');

while ($row = mysql_fetch_array($result))
{
        // Current user is the author we're displaying at the moment
      if ($CurrentUser == $row['user'])
      {
            $myPage .= '<tr><td>'.$row['date'].'</td><td>'.$row['title'].'</td></tr>';
      }
      else
      {
            if (isset($myPage))
            {
                  $myPage .= '</table>';
            }
            $myPage .= '<table border="1"><tr>';
            $myPage .= '<td colspan="2">'.$row['user'].'</td></tr>';
            $myPage .= '<tr><td>'.$row['date'].'</td><td>'.$row['title'].'</td></tr>';

            // Let's change the value of current user, since it did not match the last author
                $CurrentUser = $row['user'];
      }
}
=====================
Then when you're done, just do an "echo $myPage" statement, or if you prefer, you can execute the echo statements while you're going through the results.

If you're wondering about the database structure used, here it is. You can modify it as you see fit.

Table: articles
---------------------------------------------
id: smallint(5), primary key, auto increment
articledate: date
articletitle: char(128)
owner: smallint(5)

Table: users
---------------------------------------------
id: smallint(5), primary key, auto increment
user: char(128)
0
 
LVL 7

Author Comment

by:dansoto
ID: 20123165
J,

Thanks for your help.  I should be able to work this.

- dan -
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month18 days, 20 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question