Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

MySQL select statement not working

Codes here !

Moderators: macek, egami, gesf

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)
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 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
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 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.
User avatar
freshnet
php-forum Active User
php-forum Active User
 
Posts: 252
Joined: Tue Feb 22, 2011 8:19 am
Location: Canada

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.
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 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.
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 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
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 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'];
}
}
Acesystech
New php-forum User
New php-forum User
 
Posts: 35
Joined: Mon Aug 20, 2012 10:27 pm
Location: Ernakulam


Return to mySQL & php coding

Who is online

Users browsing this forum: No registered users and 6 guests

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.

cron