Only Showing 1 User

[Xtremer360]

I have a really strange query for you all to figure out why I'm not coming up with the right SELECT statement. I've echoed it and I'll show you what I get for a result from the echo as well. 

 

Table- Users

Fields- id,creator_id,username,password,firstname,lastname,email,status_id,isadmin,datecreated

 

$query = "SELECT CONCAT_WS(' ', firstname, lastname) AS name, CONCAT_WS(' ', firstname, lastname) AS handler, DATE_FORMAT(datecreated, '%M %d, %Y') AS datecreated, id, username, email FROM handlers WHERE handlers.id = handlers.creator_id";

 

produced this result...

 

SELECT CONCAT_WS(' ', firstname, lastname) AS name, CONCAT_WS(' ', firstname, lastname) AS handler, DATE_FORMAT(datecreated, '%M %d, %Y') AS datecreated, id, username, email FROM handlers WHERE handlers.id = handlers.creator_id

 

php data table code:

<?php 
$query = "SELECT CONCAT_WS(' ', firstname, lastname) AS name, CONCAT_WS(' ', firstname, lastname) AS handler, DATE_FORMAT(datecreated, '%M %d, %Y') AS datecreated, id, username, email FROM handlers WHERE handlers.id = handlers.creator_id";
    $result = mysqli_query ( $dbc, $query ); // Run The Query
    $rows = mysqli_num_rows($result);
    echo $query;	        
<?php if ($rows > 0) { ?>
<table cellspacing="0" class="listTable" id="handlersPageList">
	<!-- Thead -->
	<thead>
		<tr>
			<th class="first"><div></div></th>
			<th><a href="#" title="Handler Name">Handler Name</a></th>
                <th><a href="#" title="Handler Username">Handler Username</a></th>
                <th><a href="#" title="Handler Emal">Handler Email</a></th>
			<th><a href="#" title="Creator">Creator</a></th>
			<th class="last"><a href="#" title="Date Created">Date Created</a></th>
		</tr>
	</thead>
while ( $row = mysqli_fetch_array ( $result, MYSQL_ASSOC ) ) {
              echo '
              <tr>
              <td><input type=checkbox class=checkbox value="' . $row['id'] . '" /></td>
		  <td>' . $row['handler'] . '</td>
		  <td>' . $row['username'] . '</td>
              <td><a href="mailto:' . $row['email'] . '>' . $row['email'] . '</a></td>
		  <td>' . $row['name'] . '</td>
		  <td class=last>' . $row['datecreated'] . '</td>
		  </tr>';
            }
        ?>

 

I just decided to take out the parts of the page that would be needed to answer this problem instead of the whole file. This is a little confusing okay lets say the first user is the Administrator with an id of 1 obviously has a first name and last name and username and email and the date he registered is his datecreated and his creator_id is going to be preset because he created it himself so its going to be 1.

 

The 1st row in the database displays fine however I have 4 other rows that for some reason aren't displaying. And don't know why. For handlers 2-4 they all have their own first and last names and usernames and everything else however when it comes to the creator_id those 3 have a creator_id of 1 representing that the Administrator created it so in the data table instead of it showing the value of 1 for the creator_id I just want it to get the CONCCAT version of the first and last name of the Administrator.

 

In the past I have not done a very good job of explaining things so I hope this is more than clarified my intention with my code and what it should do and what it is doing wrong right now. If you have any other questions please ask.

[requinix]

Ah, that explains the weird query.

 

You need to JOIN in the handlers table against itself. Just pretend it's a copy of the table.

Do a SELECT on the first copy to get the person's information, then JOIN in the second copy (using the creator/created relationship) to get the creator's information.

[Xtremer360]

 

You need to JOIN in the handlers table against itself. Just pretend it's a copy of the table.

Do a SELECT on the first copy to get the person's information, then JOIN in the second copy (using the creator/created relationship) to get the creator's information.

 

I tired this and it produced 0 rows now.

[Xtremer360]

It won't let me edit my last post because I forgot to include the query statement.

 

 $query = "SELECT CONCAT_WS(' ', firstname, lastname) AS name, CONCAT_WS(' ', firstname, lastname) AS handler, DATE_FORMAT(datecreated, '%M %d, %Y') AS datecreated, id, username, email FROM handlers JOIN ON ( handlers.id = creator_id )";