How to show username not id?


#1

how can i join the user id from list and show the username from users table
into this script i dont know how joins work i have looked and try
to do it in this but it confusing me

07%20am


       <?php
         $sql = "SELECT list.*,count(username) as faucet  FROM list GROUP BY username";
         $result = mysqli_query($link, $sql);
         if( $result )    
         while($user = mysqli_fetch_assoc($result)) {
        ?> 
               <table class="table table-striped" id="faucetlist" width="100%" cellspacing="0">
                    <thead>
                    <tr>
                    <th><center>Faucetlist</center></th>    
                    <th><center>Created</center></th>                          
                    <th><center>Last updated</center></th>
                    <th><center>faucets</center></th> 
                     </tr>
                    </thead>                      
                 <tr>             
                <td><center><?php echo $user["username"]; ?></center></td>   
                 <td><center><?php echo date('l jS F Y' , strtotime($user['Published']));?></center></td>              
                <td><center><?php echo date('h:i A' , strtotime($user['last_update']));?> </center></td>   
                 <td><center></center><?php echo $user['faucet'];   ?></td>   

                  </table>                    
           <?php } ?>
           
       
       ```

#2

You really want to build a table for each database result?


#3

No i just need the username from users table to go to where the 1 and 2 is so 1 admin 2 user


#4

How about we start with taking a look at your database. Post an SQL dump of your DB with a few sample records.


#5

ok
https://imgur.com/a/jbvJuTG

theres 300 in my database so i cant do an SQL dump sorry


#6

Pictures are for hanging on the wall and they dont import to a DB structure and I have no idea what table that is. Just post the table Sql. I can add some fake data.

EDIT: You just need a basic JOIN on the two tables joining on the user id column of the users table and the username column of the other table.


#7

what one? the list or the users?


#8

Both, or you could just read my edit in the previous post.


#9

but how can i do it as i read up on google and i cant understand it


#10

Then give me what I asked for. The easier you make it to help you the better help you will get.

EDIT:
Something like this…

SELECT u.username, l.sumcolumn
FROM users u
INNER JOIN list l ON l.username=u.user_id;

#11

i cant do it heres the zip file off sql anyway http://wecodedaily.com/sql.zip


#12

Modify to suit your needs

SELECT
	u.username,
	l.faucet_name,
	l.last_update 
FROM
	users u
	INNER JOIN list l ON l.username = u.id

#13

shows me blank

26%20am

 <?php
         $sql = "SELECT
	u.username,
	l.faucet_name,
	l.id,count(username) as faucet 
	FROM
	users u
	INNER JOIN list l ON l.username = u.id  GROUP BY l.username";
         $result = mysqli_query($link, $sql);
         if( $result )    
         while($user = mysqli_fetch_assoc($result)) {
        ?> ```

#14

Never mind now i got it to work thank you @benanamen


closed #15

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.