database update question: capturing data from a form

hconnor · #1 (**permalink**) 06-14-07, 22:59

My apologies since this is not a strictly mysql question . . . more php.

I'm trying to update a database by building a form that links a city with surrounding towns. I want to enter the main town and the surrounding towns and then it updates all records that contain the main town. the code runs without errors, but the data i'm entering is not finding its way into the variables and doesn't update the database . . . all help appreciated.

<form id="form1" name="form1" method="get" action="demograph_updatep2.php">
<label><strong class="innerpageheading">Main town</strong>
<input name="maintown" type="text" id="maintown" />
</label>
<p><label><span class="results_13px">Nearby Town1</span>
<input name="nearbytown1" type="text" id="nearbytown1" />
</label>
</p>
<p>
<label><span class="results_13px">NearbyTown2</span>
<input name="nearbytown2" type="text" id="nearbytown2" />
</label>
</p>
<p>
<label><span class="results_13px">NearbyTown3</span>
<input name="nearbytown3" type="text" id="nearbytown3" />
</label>
</p>
<p>
<label><span class="results_13px">NearbyTown4</span>
<input name="nearbytown4" type="text" id="nearbytown4" />
</label>
</p>
<p>
<label>
<input type="submit" name="Submit" value="Update" />
</label>
</p>
</form>

<?php

// setting variables

$maincity = $_GET['maintown'];
$nearbycity1 = $_GET['nearbytown1'];
$nearbycity2 = $_GET['nearbytown2'];
$nearbycity3 = $_GET['nearbytown3'];
$nearbycity4 = $_GET['nearbytown4'];

mysql_connect (localhost,"");
mysql_select_db("") or die( "Unable to select database");
mysql_query("UPDATE physician
SET

physician_City2 = $nearbycity1
WHERE physician_City1 IN ($maincity)

mysql_close();
?>

as you can see, i've left out some of the db details. i've tried this with GET and POST but no success. Suggestions please . . .

aschk · #2 (**permalink**) 06-15-07, 06:07

Is the whole script called demograph_updatep2.php, or is it split up into different script names?
Can you echo out the $_GET/$_POST variables and post what you're getting
e.g. insert the following into your code somewhere...

Code:

<?php
print_r($_GET);
?>

hconnor · #3 (**permalink**) 06-15-07, 07:12

Hi,
its split into the first page demo_update.php, which contains the code you saw. and a second page, which just displays results. i went ahead and inserted your code at the end of the page along with

echo $nearbytown1;
echo $maintown

and got this:

Array ( )

aschk · #4 (**permalink**) 06-15-07, 07:23

When you submit the form you're going to demograph_updatep2.php
So basically anything at the bottom of demo_update.php is not getting executed properly. Your update SQL and PHP code needs to be put into the demograph_updatep2.php becuase that's where all your data you put into the boxes is being sent...

hconnor · #5 (**permalink**) 06-15-07, 07:40

yes, i tried that but it still doesn't find its way into the database . . .

aschk · #6 (**permalink**) 06-15-07, 10:18

Are you connecting to the Db properly? are you getting an error?
Put the

<?php
print_r($_GET);
?>

code onto the page you're submitting the form to.

All the following code needs to be on the page the form is submitting to :

<?php

// setting variables

$maincity = $_GET['maintown'];
$nearbycity1 = $_GET['nearbytown1'];
$nearbycity2 = $_GET['nearbytown2'];
$nearbycity3 = $_GET['nearbytown3'];
$nearbycity4 = $_GET['nearbytown4'];

mysql_connect (localhost,"");
mysql_select_db("") or die( "Unable to select database");
mysql_query("UPDATE physician
SET

physician_City2 = $nearbycity1
WHERE physician_City1 IN ($maincity)

mysql_close();
?>

How about outputting the contents of
mysql_error() ....

hconnor · #7 (**permalink**) 06-15-07, 16:14

Its definitely doing the GET to the second page. With the echo I can see the entered variable values across the top of the page.

mysql_error();

does not display anything.

Think I'm missing something basic here . . .

hconnor · #8 (**permalink**) 06-15-07, 16:15

also, i moved all that code to the page the GET is outputting to.

aschk · #9 (**permalink**) 06-18-07, 05:05

Ok, let's do a nice simple example :

page1.php :

Code:

<html>
<body>
  <form name="myform" method="POST" action="page2.php">
    Main Town : <input name="maintown" type="text" />
    Town 1 : <input name="nearbytown1" type="text" />
    Town 2 : <input name="nearbytown2" type="text" />
    Town 3 : <input name="nearbytown3" type="text" />
    Town 4 : <input name="nearbytown4" type="text" />
    <input type="submit" name="Submit" value="Onward!" />
  </form>
</body>
</html>

page2.php

Code:

<html>
  <body>
    I have reached this page and the output is
    <pre>
    <?php
      print_r($_POST);
    ?>
    <pre>
  </body>
</html>

What does that give you?

hconnor · #10 (**permalink**) 06-18-07, 08:35

I have reached this page and the output is

Array
(
[maintown] => chicago
[nearbytown1] => naper
[nearbytown2] =>
[nearbytown3] =>
[nearbytown4] =>
[Submit] => Onward!
)

aschk · #11 (**permalink**) 06-18-07, 09:56

Ok, that's lovely.
So you can now see that the form you filled in is passing those fields through from page1.php to page2.php and it's writing out the results so you can see them.

Now, here's a test :
Can you tell me where you should be putting your SQL statement (+ db connection + query)?
i.e. on which page?

hconnor · #12 (**permalink**) 06-18-07, 15:25

the database connection I placed on p2, after the information is posted.

aschk · #13 (**permalink**) 06-19-07, 05:50

Ok so you're saying that this code :

Code:

<?php

// setting variables

$maincity = $_GET['maintown'];
$nearbycity1 = $_GET['nearbytown1'];
$nearbycity2 = $_GET['nearbytown2'];
$nearbycity3 = $_GET['nearbytown3'];
$nearbycity4 = $_GET['nearbytown4'];


mysql_connect (localhost,"");
mysql_select_db("") or die( "Unable to select database");
mysql_query("UPDATE physician
SET
physician_City2 = $nearbycity1
WHERE physician_City1 IN ($maincity)

mysql_close();
?>

goes on page2.php ?

Have you see the SQL you have put there? What happens if the value for $maincity doesn't exist in the physician_City1 column?

hconnor · #14 (**permalink**) 06-19-07, 07:41

the sql doesn't work, at least it doesn't update the db. but doesn't throw an error message as written. it looks like this:

mysql_connect (localhost,"","");
mysql_select_db("") or die( "Unable to select database");
mysql_query("UPDATE physician

SET
Physician_City2=$maintown
WHERE Physician_City1 = $nearbytown1

");
mysql_error();
mysql_close();
?>

for some reason the variable is not being passed into the sql. could it have to do with the way the sql statement is written?

aschk · #15 (**permalink**) 06-19-07, 08:52

I think you need to echo mysql_error();
Nevertheless, the SQL is not necessarily failing. Think it through, you are updating some columns where one of those columns equals a particular value,
e.g. update <tablename> SET column2='somenewvalue',column3='somenewvalue' WHERE column1 IN ('somevalue')

(as a side note there is nothing wrong with using IN but I would use = , because what is inside the bracket is constant)

So what does this do? Well, it updates column2 & 3 where column1 = 'somevalue'.

[I'm MySQL]
Ok, i'm MySQL, i'm searching through all the rows in my table, and I find three rows that have column1='somevalue'. Ok great, i'll update column2 and 3 for those rows then. Done.

Different scenario
[I'm MySQL]
I'm searching through all the rows in my table, and i find zero (0) rows that have column1='somevalue'. What do I have to update? Nothing...! So I don't update anything. Done.

Can you see something here?
There is no error. MySQL has searched through our results and hasn't found any to update so it doesn't update any. That's a perfectly valid result.

Your problem is not that your SQL is wrong, it's that you don't have anything in the table to update...