I have the id of the state and the city, and now? [closed]

Question

I have the id of the state and the city, and now? [closed]

Navigation

#1 by (2 votes)
#2 by (0 votes)

0

Hello, in a classified site with PHP and MySQL I made through Ajax the insertion of the state and the city. When selecting the state, the id is passed and it sends to the file getcidade.php that makes the select and returns the respective city, thus inserting the ID's in the database.

TABELA tb_estado CAMPOS id|int e uf|varchar(2)
TABELA tb_cidade CAMPOS id|int e id_estado_fk|int e cidade|varchar(100)

But now in the bank is id state 26 and city 4773 how to display the state name SP and the city Bauru?

NOTE * I already do INNER JOIN with the id of the user to display the city / state that the guy has registered, but stays in the advertisement.

TABELA tb_anuncio CAMPOS id|int e id_usuario_fk|int e estado_fk|int(10) e cidade_fk|int(10)

On the page it looks like this: inserted on: 23/08 Location: 4773/26

How can I get the names in the table?

mysql join sql-select

asked by anonymous 24.08.2016 / 03:23

2 answers

Call View on Another ASP.NET MVC Controller Print only selected records in the checkbox

score 2 · Answer 1

Considering these tables created accordingly, apparently with its structure we would have this:

Advertisement Table

--
-- Estrutura da tabela 'tb_anuncio'
--

CREATE TABLE IF NOT EXISTS 'tb_anuncio' (
  'id' int(11) NOT NULL AUTO_INCREMENT,
  'anuncio' varchar(500) NOT NULL,
  'id_cidade' int(11) NOT NULL,
  'id_estado' int(11) NOT NULL,
  PRIMARY KEY ('id')
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

City Table

--
-- Estrutura da tabela 'tb_cidade'
--

CREATE TABLE IF NOT EXISTS 'tb_cidade' (
  'id' int(11) NOT NULL AUTO_INCREMENT,
  'name' varchar(500) NOT NULL,
  PRIMARY KEY ('id')
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

State Table

--
-- Estrutura da tabela 'tb_estado'
--

CREATE TABLE IF NOT EXISTS 'tb_estado' (
  'id' int(11) NOT NULL AUTO_INCREMENT,
  'nome' varchar(500) NOT NULL,
  PRIMARY KEY ('id')
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

So the select with join for both tb_cidade and tb_estado would look like this:

SELECT a.anuncio, c.name cidade, e.nome estado 
FROM tb_anuncio a 
JOIN tb_cidade c ON a.id_cidade = c.id 
JOIN tb_estado e ON a.id_estado = e.id

Result

score 0 · Answer 2

Confusing question, the ideal SELECT for this would be:

"SELECT tb_estado.uf, tb_cidade.cidade from tb_estado, tb_cidade 
 where tb_cidade.id_estado_fk = tb_estado.id";

There you add whatever you want.