[RESOLVED] Trouble displaying multiple info from database

@Design-is-BSMay 27.2008

I am creating an admin area, where the admin can view the activities of the users.

There are two tables which display information. I am only finding success in displaying information of ONE USER.

This is my code:

[code=php] <?php $result = mysql_query(“SELECT * FROM users”); while($row = mysql_fetch_array($result)) { $id = $row[‘id’]; $username = $row[‘username’];

if ($id == 1) {

echo “<td valign=top>” . $row[‘givenname’] . ” ” . $row[‘surname’] . “<br>” . $row[‘homeaddy’] . “<br>” . $row[‘homecity’] . ” ” . $row[‘homeprov’] . ” ” . $row[‘homepost’] . “<br>” . $row[‘resphone’] . “</td>”; echo “<td valign=top>” . $row[‘secspurch’] . “Common Shares </td>”; echo “<td valign=top> $” . $row[‘agreamount’] . “.00</td>”; echo “<td width=300px><table>”;

//Connect to Table $query = “SELECT * FROM table2purchstats WHERE userid='{$username}'”; $result = mysql_query($query, $link); while($row = mysql_fetch_array($result)) {

//display checked boxes if equal to one include (‘ABC_Completed_purchstats.php’);

echo “</table></td>”; echo “<td valign=top>” . $row[‘date_completed’] . “</td>”;//not working yet echo “</tr>”; echo “<tr>”; echo “</tr><tr>”;

}//closes while statement

} elseif($id == 2){

//Connect to Table $query = “SELECT * FROM table2purchstats WHERE userid='{$username}'”; $result = mysql_query($query, $link); while($row = mysql_fetch_array($result)) {

//display checked boxes if equal to one include (‘ABC_Completed_purchstats.php’);

echo “</table></td>”; echo “<td valign=top>” . $row[‘date_completed’] . “</td>”;//not working yet echo “</tr>”; echo “<tr>”; echo “</tr><tr>”;

}//closes while statement

} echo “</tr><tr>”;

}//closes while statement ?> [/code]

The id’s check out, there are 2 of them, 1 and 2.

Thanks in advance!

to post a comment

PHP

7 Comments(s) _↴

@furnissgMay 27.2008 — #are you trying to display both users records at the same time?

if so it looks like your overwriting the $row variable in the if statement. therefor changing the data. try changing the var name in the second sql staement.

@Design-is-BSauthorMay 27.2008 — #I found a quick fix in creating separate <?php ?> for each statement. So that definitely seems possible. Lol.

I am trying to display their information simultaneously, yes.

This is the page I am displaying them: http://designisbs.ca/johnbursic/ABC_Completed.php if you have to login it's user: admin, pass: test

Isn't pretty, I know. Lol but we're just testing the system.

Here is my updated code:

[code=php]
 <?php 
 $result = mysql_query("SELECT * FROM users");
 while($row = mysql_fetch_array($result))  {
 $id = $row['id'];
 $username = $row['username'];
 
   if($id == 1)  {
   echo "<td valign=top>" . $row['givenname'] . " " . $row['surname'] . "<br>" . $row['homeaddy'] . "<br>" . $row['homecity'] . " " . $row['homeprov'] . " " . $row['homepost'] . "<br>" . $row['resphone'] . "</td>";
   echo "<td valign=top>" . $row['secspurch'] . "Common Shares </td>";
   echo "<td valign=top> $" . $row['agreamount'] . ".00</td>";
   echo "<td width=300px><table>"; 
 
 
   //Connect to Table
   $query = "SELECT * FROM table2purchstats WHERE id='{$id}'";
   $result = mysql_query($query, $link);
   while($row = mysql_fetch_array($result))  {
 
   //display checked boxes if equal to one
   include ('ABC_Completed_purchstats.php');
 
   echo "</table></td>";
   echo "<td valign=top>" . $row['date_completed'] . "</td>";//not working yet
   echo "</tr>";
   echo "<tr>";
   echo "</tr><tr>";
 
   }//closes while statement
 
   } else  {
   //display nothing
   }
 
   }//closes while statement 
   ?>
   </tr>
   <tr bgcolor="#CCFFCC">
   <?php 
   $result = mysql_query("SELECT * FROM users");
   while($row = mysql_fetch_array($result))  {
   $id = $row['id'];
   $username = $row['username'];
 
   if($id == 2)  {
   echo "<td valign=top>" . $row['givenname'] . " " . $row['surname'] . "<br>" . $row['homeaddy'] . "<br>" . $row['homecity'] . " " . $row['homeprov'] . " " . $row['homepost'] . "<br>" . $row['resphone'] . "</td>";
   echo "<td valign=top>" . $row['secspurch'] . "Common Shares </td>";
   echo "<td valign=top> $" . $row['agreamount'] . ".00</td>";
   echo "<td width=300px><table>"; 
 
 
   //Connect to Table
   $query = "SELECT * FROM table2purchstats WHERE id='{$id}'";
   $result = mysql_query($query, $link);
   while($row = mysql_fetch_array($result))  {
 
   //display checked boxes if equal to one
   include ('ABC_Completed_purchstats.php');
 
   echo "</table></td>";
   echo "<td valign=top>" . $row['date_completed'] . "</td>";//not working yet
   echo "</tr>";
   echo "<tr>";
   echo "</tr><tr>";
 
   }//closes while statement
 
   } else  {
   //display nothing
   }
 
   }//closes while statement 
   ?>
   </tr>
 [/code]

@SyCoMay 27.2008 — #Right now you're running a SQL query (select * from users) inside a loop.

When your site becomes popular and you have 50,000 users this page will require 50,000 SQL queries to run, every time this page is hit by every user. You SQL server will fail, you may even get booted off your host (if shared).

You can get all the info you need in a single SQL statment by joining the tables, then loop through the results.

select * from users
 outer join table2purchstats on users.userid=table2purchstats.userid

Read wikipedia's joins info. It's very nicely explained.

http://en.wikipedia.org/wiki/Join_(SQL)

Then you have a single statement and a single data set to loop through. If you run the query in putty you'll see how the data looks and you can then work out how best to display it.

@Design-is-BSauthorMay 27.2008 — #Thanks SyCo,

I followed along with the Wiki link and came up with this:

[code=php]
 <?php 
 $result = mysql_query("SELECT * FROM users, table2purchstats");
 
   while($row = mysql_fetch_array($result))  {
   $id = $row['id'];
   $username = $row['username'];
 
   echo "<td valign=top>" . $row['givenname'] . " " . $row['surname'] . "<br>" . $row['homeaddy'] . "<br>" . $row['homecity'] . " " . $row['homeprov'] . " " . $row['homepost'] . "<br>" . $row['resphone'] . "</td>";
   echo "<td valign=top>" . $row['secspurch'] . "Common Shares </td>";
   echo "<td valign=top> $" . $row['agreamount'] . ".00</td>";
   echo "<td width=300px><table>"; 
 
   //display checked boxes if equal to one
   include ('ABC_Completed_purchstats.php');
 
   echo "</table></td>";
   echo "<td valign=top>" . $row['date_completed'] . "</td>";//not working yet
   echo "</tr>";
   echo "<tr>";
   echo "</tr><tr>";
 
   }//closes while statement
 
   echo "</tr><tr>";
 
   ?>
 [/code]

Now everything is displayed twice. I think I know why, is it because both tables have a row called "id"? But then again, $row['id] isn't being used anywhere...ugh. lol

@SyCoMay 27.2008 — #SELECT * FROM users, table2purchstats

Thats a cross join which returns all the possible combinations and a ton of redundant data.

Take employee Steinberg from the wiki page, which department is he in?

Steinberg 33 Sales 31

Steinberg 33 Engineering 33

Steinberg 33 Clerical 34

Steinberg 33 Marketing 35

notice it returns a department for every employee. 4 departments and 4 sets of employee data. None of which is any use.

Read it again. It took me ages to understand it the first time and I still don't consider myself 100% OK on complex table joins.

@Design-is-BSauthorMay 27.2008 — #WOO HOO!

It works!

[code=php]
 $result = mysql_query("
 SELECT * FROM   users
 INNER JOIN table2purchstats 
 ON users.id = table2purchstats.id
 ");
 [/code]

Thanks so much SyCo! I can always count on you!

@SyCoMay 27.2008 — #lol, you're welcome.

As a note, inner join will return results only when there is a corresponding record in table2purchstats. if there isn't a record in table2purchstats then you wont get the userid from 'users' either.

eg

[CODE]select * from users
 
 userid	name
 1	bob
 2	jon
 3	tom[/CODE]

[CODE]
 SELECT * FROM   users
 INNER JOIN table2purchstats
 ON users.id = table2purchstats.id
 
 userid	name	purchstatsid	data 
 1	bob	  12		xyz
 3	tom	  43		abc
 [/CODE]

outer join will return the userid even if there isn't a record in table2purchstats with nulls where the missing records would be.

[CODE]SELECT * FROM   users
 OUTER JOIN table2purchstats
 ON users.id = table2purchstats.id
 
 userid	name	purchstatsid	data 
 1	bob	  12		xyz
 2	jon	  null		null
 3	tom	  43		abc[/CODE]

Also in #PHP _↴

showing date from unix date from database....Issues with Arrays Login Question

Success!

Help @Design-is-BS spread the word by sharing this article on Twitter...

Tweet This

[RESOLVED] Trouble displaying multiple info from database

7 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

[RESOLVED] Trouble displaying multiple info from database

7 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

7 Comments(s) _↴

Also in #PHP _↴