Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder
    Join Date
    Jun 2008
    Posts
    682
    Thanks
    114
    Thanked 2 Times in 2 Posts

    Only Showing 1 User

    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

    PHP Code:
    $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 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 $resultMYSQL_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:

    PHP Code:
     $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.

  • #2
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,495
    Thanks
    8
    Thanked 1,089 Times in 1,080 Posts
    Is that part in red (below) supposed to be a variable, like $datecreated?


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

  • #3
    New Coder
    Join Date
    Nov 2010
    Posts
    30
    Thanks
    2
    Thanked 2 Times in 2 Posts
    If I understood right....

    Try GROUP_CONCAT_WS()?

  • #4
    Regular Coder
    Join Date
    Jun 2008
    Posts
    682
    Thanks
    114
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by mlseim View Post
    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.

  • #5
    Regular Coder
    Join Date
    Jun 2008
    Posts
    682
    Thanks
    114
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by StrangeCoder View Post
    If I understood right....

    Try GROUP_CONCAT_WS()?
    How would I write it with this?

  • #6
    New Coder
    Join Date
    Nov 2010
    Posts
    30
    Thanks
    2
    Thanked 2 Times in 2 Posts
    Test this one:
    Code:
    "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"

  • #7
    Regular Coder
    Join Date
    Jun 2008
    Posts
    682
    Thanks
    114
    Thanked 2 Times in 2 Posts
    It won't let me edit this post but this was fixed thank you.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •