MySQL select statement not working

Codes here !

Moderators: macek, egami, gesf

lasithc
New php-forum User
New php-forum User
Posts: 2
Joined: Mon May 07, 2012 11:42 am

MySQL select statement not working

Postby lasithc » Mon May 07, 2012 11:56 am

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)

User avatar
minimihi
New php-forum User
New php-forum User
Posts: 238
Joined: Sat Apr 14, 2012 11:57 am
Location: Vilnius, Lithuania

Re: MySQL select statement not working

Postby minimihi » Mon May 07, 2012 12:21 pm

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

User avatar
freshnet
php-forum Active User
php-forum Active User
Posts: 285
Joined: Tue Feb 22, 2011 8:19 am
Location: Canada

Re: MySQL select statement not working

Postby freshnet » Mon May 07, 2012 3:18 pm

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
New php-forum User
New php-forum User
Posts: 109
Joined: Thu May 03, 2012 7:22 am

Re: MySQL select statement not working

Postby faust » Tue May 08, 2012 3:56 am

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)){...
Last edited by faust on Tue May 08, 2012 3:57 am, edited 1 time in total.

User avatar
Nullsig
php-forum Fan User
php-forum Fan User
Posts: 981
Joined: Thu Feb 17, 2011 6:52 am
Location: Racine, WI

Re: MySQL select statement not working

Postby Nullsig » Tue May 08, 2012 4:33 am

freshnet wrote: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
New php-forum User
New php-forum User
Posts: 2
Joined: Mon May 07, 2012 11:42 am

Re: MySQL select statement not working

Postby lasithc » Tue May 08, 2012 9:24 am

Nullsig wrote:
freshnet wrote: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
New php-forum User
New php-forum User
Posts: 35
Joined: Mon Aug 20, 2012 10:27 pm
Location: Ernakulam
Contact:

Re: MySQL select statement not working

Postby Acesystech » Wed Aug 22, 2012 4:39 am

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'];
}
}


Return to “mySQL & php coding”

Who is online

Users browsing this forum: No registered users and 0 guests