...

View Full Version : Only Showing 1 User



CoolAsCarlito
11-27-2010, 04:58 PM
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;
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.

I've also tried A JOIN but it produced no results like this:


$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 )";

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.

mlseim
11-27-2010, 06:20 PM
Is that part in red (below) supposed to be a variable, like $datecreated?


DATE_FORMAT(datecreated, '%M %d, %Y')

StrangeCoder
11-27-2010, 06:23 PM
If I understood right....

Try GROUP_CONCAT_WS()?

CoolAsCarlito
11-27-2010, 08:15 PM
Is that part in red (below) supposed to be a variable, like $datecreated?


DATE_FORMAT(datecreated, '%M %d, %Y')

Its a field in my database.

CoolAsCarlito
11-27-2010, 08:17 PM
If I understood right....

Try GROUP_CONCAT_WS()?

How would I write it with this?

StrangeCoder
11-27-2010, 08:31 PM
Test this one:

"SELECT GROUP_CONCAT_WS(' ', firstname, lastname) AS name, GROUP_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"

CoolAsCarlito
11-29-2010, 11:51 PM
It won't let me edit this post but this was fixed thank you.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum