Insert query

[vasu]

I have created two tables

 

1. state

 

having fields state_id and state_name

 

2. city

 

having fields city_id, state_id and city_name

 

city id and state id is primary keys....

 

Now I want to insert city_id, state_id and city_name using three fields

 

but i m not getting state_id

 

i use ...

 

$city_id = $_REQUEST['city_id'];

$state_id = $_REQUEST['state_id'];

$city_name = $_REQUEST['city_name'];

 

$sql = " INSERT INTO city SET

city_id='".$city_id."',

state_id='".$state_id."',

city_name='".$city_name."'" ;

 

//$objDB ->dbclass();

$a = mysql_query($sql) or die(mysql_error());

mng_location.php

indexlocation.php

location.php

[BananaAcid]

use $_POST instead of $_REQUEST .... if thought _Request should hold posts and gets, it seems to be troublesome to use.

 

cant tell you why, but it has always worked to use the specific _GET (for a link or form method=get)

and _POST (for form method=post)

where as _REQUEST (merges _GET and _POST) is sometimes just blank, or does not contain all keys.

 

so to your error:

$_REQUEST['state_id']; might just return "" or null => evaluates as empty()

 

try inserting a <?php print "<pre>";var_dump($_REQUEST); ?> at top to test if the form supplied it all.

 

 

btw u should have mysql statements as one line to avoid mysql (sometimes) and readability confusions.

 

$sql = "INSERT IGNORE INTO city SET city_id='$city_id', state_id='$state_id', city_name='$city_name'";

 

just php variables in double-qoutes " will be parsed to its value. so just use them without ending and starting the string again. how do you handle duplicate keys (use ignore for example)? if "state_id" is a unique field too (like your city one), no other city may be in the same state. i would recomend you make ONLY the city id unique.

 

 

well, i hope i have targeted the most common problems.

[vasu]

I have done it......

 

but then also it is not working....

 

 

I want it should enter the state_id from the state table....

 

I have done some modifications....

 

<?

//ob_start();

session_start();

//include("../conn/config.php");

//include("../conn/dbclass.php");

$conn = mysql_connect('localhost','root','root') or die(mysql_error());

mysql_select_db('searchindia');

//$objDB=new dbclass();

 

$city_id = $_POST['city_id'];

$state_id = $_POST['state_id'];

$city_name = $_POST['city_name'];

$statename = $_POST['state']; ///state is the dropdown box name///

$state = "SELECT state_id FROM state WHERE state_name = $state_id";

$state1 = mysql_query($state);

 

$sql = " INSERT INTO city SET

city_id='".$city_id."',

state_id='".$state1."',

city_name='".$city_name."'" ;

 

//$objDB ->dbclass();

$a = mysql_query($sql) or die(mysql_error());

?>

<?php

header('Location: indexlocation.php');

 

?>

 

<script>

//window.location='index.php?p=location';

</script>

<form action="indexlocation.php"></form>

 

 

 

 

use $_POST instead of $_REQUEST .... if thought _Request should hold posts and gets, it seems to be troublesome to use.

 

cant tell you why, but it has always worked to use the specific _GET (for a link or form method=get)

and _POST (for form method=post)

where as _REQUEST (merges _GET and _POST) is sometimes just blank, or does not contain all keys.

 

so to your error:

$_REQUEST['state_id']; might just return "" or null => evaluates as empty()

 

try inserting a <?php print "<pre>";var_dump($_REQUEST); ?> at top to test if the form supplied it all.

btw u should have mysql statements as one line to avoid mysql (sometimes) and readability confusions.

 

$sql = "INSERT IGNORE INTO city SET city_id='$city_id', state_id='$state_id', city_name='$city_name'";

 

just php variables in double-qoutes " will be parsed to its value. so just use them without ending and starting the string again. how do you handle duplicate keys (use ignore for example)? if "state_id" is a unique field too (like your city one), no other city may be in the same state. i would recomend you make ONLY the city id unique.

well, i hope i have targeted the most common problems.

[BananaAcid]

open your admin panel.

 

navigate to PhpMyAdmin.

 

within there is a SQL console you can test your code.

 

it will tell you, that you have to enter all values within quotes.

 

$state = "SELECT state_id FROM state WHERE state_name = $state_id";

=> state_name = '$state_id'";

 

$state1 = mysql_query(...)

 

$state1 is a mysql-result. not just a string. but you are using it line a single string. you need to $row = mysql_fetch_array($result, MYSQL_ASSOC); $my_string_state = $row['state_name']; (that should make it clear..)

 

will do it. you should really see www.PHP.net for mysql handling. just visit http://php.net/mysql