[ SQL/recordset ] Show NAME from other table instead of ID

Report · May 08, 2009

I have one problem.

Some tables have only ID that refer from other table, and, in that table, exist what each numer means. When I create one table do display for example 10 itens of my main table, this table just show the ID instead of the NAME, or, will just who the fist NAME of all rows that that have revefenre on both tables.

So what i need Know is:

How to display NAME instead of ID, from one table that have for each ID, one NAME?

In my case, for example, I have the folowing table name and cel name:

CITY
id_city
nameofcity

1 - Rio de Janeiro

2 - São Paulo

3 - Belo Horizonte

4 - Curitiba

USER
id_user
city

1 - 1 : user 1 from city Rio de Janeiro

2 - 1 : user 2 from city Rio de Janeiro

3 - 2 : user 3 from city Rio de Janeiro ( error, must be São Paulo )

4 - 2 : user 4 from city Rio de Janeiro ( error, must be São Paulo )

5 - 4 : user 5 from city Rio de Janeiro ( error, must be Curitiba )

Really thanks for all that help me with this. Also, some link with tutorial or something useful will be really useful.

Report · May 08, 2009

SELECT id_user, nameofcity
FROM user, city
WHERE user.city = city.id_city

Report · May 08, 2009

This code do not work. I tryed someting like it before.

On "test' of dreamweaver, just show one list with results that both tables have in comon ( see image attached at this post, especifci about my case ), and on live site, just show the fist name of NAME list. Do not take only the NAME that must take, and take all and show just the fist and wrong data.

As the result on live site, all repeat cells, with diferent cities show only the fist city that are comon on both tables.

I guess that what I'm trying to do is not something so rare of someone use it

Report · May 08, 2009

No, it's not rare. If it's not working, it suggests that your database setup is wrong.

This is a simple example.

cities

city_id	city
1	London
2	Paris
3	Rome

users

user_id	city_id	username
1	1	David
2	2	Jeanne
3	3	Massimo
4	1	John
5	3	Giovanni

David and John are in London

Jeanne is in Paris

Massimo and Giovanni are in Rome

Report · May 08, 2009

I try it with this code:

SELECT users.user_id, cities.city
FROM users, cities
WHERE users.city_id = cities.city

But do not return any information now.

I make excatly the sabe tables and informations that you put on last post.

Report · May 09, 2009

SELECT users.user_id, cities.city
 FROM users, cities
 WHERE users.city_id = cities.city_id

You need to match the two city_id columns.

Report · May 09, 2009

Didint work on the live page.

This just show the fist citie of the list for all members.

See on image that the page shows the right ID of citie, but after the 'Name' part, just show the fist item of the cities that exist.

The table code:

<table border="1">
<tr>
    <td>user_id</td>
    <td>city_id</td>
    <td>username</td>
</tr>
<?php do { ?>
    <tr>
      <td><?php echo $row_rs_users['user_id']; ?></td>
      <td><?php echo $row_rs_users['city_id']; ?>- Name: <?php echo $row_rs_cities['city']; ?></td>
      <td><?php echo $row_rs_users['username']; ?></td>
    </tr>
    <?php } while ($row_rs_users = mysql_fetch_assoc($rs_users)); ?>
</table>

and the recodset part before header of each page:

mysql_select_db($database_coneccao_shanera_db, $coneccao_shanera_db);
$query_rs_cities = "SELECT users.user_id, cities.city FROM users, cities WHERE users.city_id = cities.city_id";
$rs_cities = mysql_query($query_rs_cities, $coneccao_shanera_db) or die(mysql_error());
$row_rs_cities = mysql_fetch_assoc($rs_cities);
$totalRows_rs_cities = mysql_num_rows($rs_cities);

I guess that the problems is that the recordset is executed only once a time instead of for each new item of the table, and the And I read someting about the add new variables on the SQL comand, but i didint know how to do it, and also on fist try makes the same error, and the '$row_rs_cities['city']' return only the fist item.

Maybe my problem is not because the while ($row_rs_users = mysql_fetch_assoc($rs_users));cite only rs_users and no rs_cities? Maybe I must put 2 whiles instead of just one. If is this, how can I do it?

PS: How can I style with php or SQL this code here? I didint know the tags on this forum for apears like code.

Report · May 09, 2009

SOLVED!

The problem was not with the SQL, but with something more simple.

After read code, i just see that has one one 'while' for 2 recordsets. So, for who have the same problem and want know how i solve it, I just added the folowing code:

<table border="1">
<tr>
    <td>user_id</td>
    <td>city_id</td>
    <td>username</td>
</tr>
<?php do { ?>
    <tr>
      <td><?php echo $row_rs_users['user_id']; ?></td>
      <td><?php echo $row_rs_users['city_id']; ?>- Name: <?php echo $row_rs_cities['city']; ?></td>
      <td><?php echo $row_rs_users['username']; ?></td>
    </tr>
    <?php } while ( ($row_rs_users = mysql_fetch_assoc($rs_users)) and ($row_rs_cities = mysql_fetch_assoc($rs_cities))); ?>
</table>

And now this example and my code is working:

Thanks for all help, David Powers =D

Report · May 09, 2009

Ops, I found one bug.

If I have less cities than how much informations are displayed at same time, only the max numbers of cities are displayed at same time.

Example: I have 3 cities for 10 users, but, with this proposal that i make before, only 3 informations will be displayer at same time.

Can someone help me with this ( new ) problem ?