I have the status and city ID, and now what?

Question

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 is passed the id and it sends to the file getcity.php that makes the select and returns the respective city, thus entering the ID’s in the bank.

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 id the state 26 and the city 4773 as display the name of the state SP and the city Bauru?

NOTE* I already make one INNER JOIN with the user id to display the city/state that the so-and-so registered, but is in the ad.

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 in: 23/08 Location: 4773/26

How can I get the names on the table?

Answer 1

Considering these tables created according, apparently, with their structure we would have it:

Ad 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 ;

Table City

--
-- 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 ;

Table State

--
-- 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 ;

Then the select with the join so much for the tb_cidade how much for the tb_estado would look this way:

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

Upshot

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

then you add whatever you want.