MySQL select statement not working

[lasithc]

Hi,

I have 2 tables in my db such as city and location. I want to get the particular city from the city table and find the corresponding locations names for that city. Please check the following code,

---------------------------------------------------------------
$id = $_GET['city'];

$con1 = mysql_connect("localhost","lasith","lasith123");
if (!$con1){
die('Could not connect: ' . mysql_error());
}
mysql_select_db("test", $con1);
$cities = mysql_query("SELECT * FROM cities where name = '" .$id. "'") or die(mysql_error());
while($city = mysql_fetch_array($cities)){
$city_id = $city['id'];
}

$result1 = mysql_query("SELECT * FROM locations where city = '" .$city_id. "'") or die(mysql_error());

$names = "";
while($row1 = mysql_fetch_array($result1)){
if (empty($names)){
$names = $row1['name'];
}else {
$names = $names . "," . $row1['name'];
}
}

---------------------------------------------------------------

The first select statement is working. Even I can see value for $city_id when debugging. Second select statement always return empty results without any errors. I feel something wrong with the type of $city_id. Can you guys help me to resolve this.

Note:
The city field of locations table has the type of varchar(10) and the ID field of cities table has type int(6)

[minimihi]

Code seems to be fine.
If you have PhpMyAdmin or anything similar, could you make screen-shots of table structures?

[freshnet]

First rule of troubleshooting queries.. echo back the query to your browser and run it on your MYSQL install, see if the query runs.

[faust]

I think that your queries are fine but the last IF is the problem.

Code: Select all

if (empty($names)){
$names = $row1['name'];
}else {
$names = $names . "," . $row1['name'];
}   


You are checking IF the name is empty and then you add it to the array. I think it should be

Code: Select all

if (!empty($names)){...


[Nullsig]

First rule of troubleshooting queries.. echo back the query to your browser and run it on your MYSQL install, see if the query runs.

This is correct. Since you aren't seeing a MySQL error it means the query is syntactically sound. More than likely the value you are passing in empty or there is not data in the second table for your selected $city_id.

[lasithc]

First rule of troubleshooting queries.. echo back the query to your browser and run it on your MYSQL install, see if the query runs.

This is correct. Since you aren't seeing a MySQL error it means the query is syntactically sound. More than likely the value you are passing in empty or there is not data in the second table for your selected $city_id.

Hi,

I verified it by echo and executing it with phpmyadmin and solved issue,

Thanks

[Acesystech]

Not sure..plz try this..

$id = $_GET['city'];

$con1 = mysql_connect("localhost","lasith","lasith123");
if (!$con1){
die('Could not connect: ' . mysql_error());
}
mysql_select_db("test", $con1);
$cities = mysql_query("SELECT * FROM cities where name = '$id'") or die(mysql_error());
while($city = mysql_fetch_array($cities)){
$city_id = $city['id'];
}

$result1 = mysql_query("SELECT * FROM locations where city = '$city_id'") or die(mysql_error());

$names = "";
while($row1 = mysql_fetch_array($result1)){
if (empty($names)){
$names = $row1['name'];
}else {
$names = $names . "," . $row1['name'];
}
}