Can update queries really be so troublesome?

bizchris2001

Okay, so my post is actually about 1/3 PHP, but I've once again frittered away my whole evening Searching and RTFM so I'm asking anyway. TIA for help 🙂

Here's my form, after I've connected:

<form action="post">
<select>
<option>
//Query the Type field, which has Chrysanthemum, etc.
<?
mysql_select_db('MyDB');
$user_query = mysql_query("select
Type from tbl_flowers");
while ($row = mysql_fetch_array($user_query)) {
echo $row['Type'] . '<option>';
}
?>
</option>
</select>

//In theory, this is gathering what I can change the type to (not add new ones). For example, change Chrysanthemum to Lilac.
<input name="NewType"></input>
<input type="submit" name="submit" value="submit"></input>
</form>
<?
$update = mysql_query(
"update tbl_flowers set Type = \"Lilac\"")
or die('insert failed: ' . mysql_error() );
?>

Now granted, this does work. However, two things I can't seem to get to work:

1) $update = mysql_query("update tbl_flowers set Type = " . $_POST['Type'])

2) Okay, that was the PHP question. Related questions I've forgotten and can't seem to find the answer to on the Net:

When I do ~set Type = \"Lilac\" where RowID=1~ which is what I would want the user to be able to do instead of all values, it does change one value, but it's the last in the list of RowID's 1 through 4. Why?
When I click the Submit button, it takes me to this url: /.../post?NewType=mytext&submit=submit --> but it's a PageCBDisplayed. It does update the db in the meantime, however. What's up with that?

I think this post may be a better fit in the n00b category, but thanks for your help. 🙂

Chris

drawmack

Originally posted by bizchris2001
<form action="post">
<select>
<option>
//Query the Type field, which has Chrysanthemum, etc.
<?
mysql_select_db('MyDB');
$user_query = mysql_query("select
Type from tbl_flowers");
while ($row = mysql_fetch_array($user_query)) {
echo $row['Type'] . '<option>';
}
?>
</option>
</select>

problem 1, I don't know what you're doing here, but you'r not printing well formed html which it probably the problem.

//post is not an action post is a method try this
<?php
echo "<form action=\"$PHP_SELF\" method=\"post\">\n";
//you didn't name the select field
echo "  <select name=\"Type\">\n";
//Query the Type field, which has Chrysanthemum, etc.
mysql_select_db('MyDB');
$user_query = mysql_query("select 
Type from tbl_flowers"); 
while ($row = mysql_fetch_array($user_query)) { 
    echo "<option>" . $row['Type'] . "</option>\n";
} //end while
echo "</select>\n";

In theory, this is gathering what I can change the type to (not add new ones). For example, change Chrysanthemum to Lilac.

Now it is actually doing that.

1) $update = mysql_query("update tbl_flowers set Type = " . $_POST['Type'])

This would not work before because you did not name the select Type and your html was all messed up it should work now.

2) Okay, that was the PHP question. Related questions I've forgotten and can't seem to find the answer to on the Net:

When I do ~set Type = \"Lilac\" where RowID=1~ which is what I would want the user to be able to do instead of all values, it does change one value, but it's the last in the list of RowID's 1 through 4. Why?

Shouldn't be, try posting the entire sql statement that you're acutally executing.

- When I click the Submit button, it takes me to this url: /.../post?NewType=mytext&submit=submit --> but it's a PageCBDisplayed. It does update the db in the meantime, however. What's up with that?

See the comments in my example.

bizchris2001

Believe it or not, that is my whole SQL so far. :p
Here's my code so far, but whatever text I put in the input to update the Db, it says "insert failed: Unknown column 'alkjds' in 'field list'"

Thoughts? Thanks!

<?php
echo "<form action=\"$PHP_SELF\" method=\"post\">\n";
echo " <select name=\"Type\">\n";
//Query the Type field, which has Chrysanthemum, etc.
mysql_select_db('MyDB');
$user_query = mysql_query("select Type from tbl_flowers");

while ($row = mysql_fetch_array($user_query)) {

echo "<option>" . $row['Type'] . "</option>\n";
} //end while
echo "</select>\n";
?>

ultraslacker

'alkjds' is being mistaken as a column name, it needs quotes - put quotes around the $_POST array

bizchris2001

Yeah, I had tried that last night too:

$update = mysql_query("update tbl_flowers set
Type = " . \"$_POST['NewType']\")

but it gave me these two errors:

Warning: Unexpected character in input: '\' (ASCII=92) state=1 in /.../cd/DBtest.php on line 33

Parse error: parse error, expecting T_STRING' orT_VARIABLE' or `T_NUM_STRING' in /.../cd/DBtest.php on line 33

Also, after I get this resolved I'll still need to add something like "WHERE Type = NewType" so it only updates the one record (i.e., Chrysanthemum to Rose), right? Thanks!

bizchris2001

This seems like something that's pretty common (getting db values from a dropdown, and updating the field those values are coming from with text entered in an input field). Can anyone help, please? Thanks!

Chris

Pig

Your syntax is screwey. You stepped out of your string, and tried to escape your quotes OUTSIDE of your string. You only escape quotes inside your string, NOT outside them. The format is like this:

$update = mysql_query("update tbl_flowers set Type = '" . $_POST['NewType'] . "'");

bizchris2001

Thanks - I didn't realize the \ only applied inside strings. I've now got the following:

//Query the Type field, which has Chrysanthemum, etc.
mysql_select_db('MyDB');
$user_query = mysql_query("select Type from tbl_flowers");

while ($row = mysql_fetch_array($user_query)) {

echo "<option>" . $row['Type'] . "</option>\n";
} //end while
echo "</select>\n";
?>

I'm trying to get it so I can replace the Type. I.e., the user chooses "Rose" from the dropdown, and can type in "Tulip" and that will change the value in the db like this:

Rose Different flower _____
Carnation

When they choose Rose and type in Tulip in the Different flower and Submit, it should change to this:

Tulip Different flower _____
Carnation

My PHP code above doesn't update anything (note I added the Where statement, but I'm not sure how to call the specific Type that's selected, like Rose in the example above). Any help is appreciated - thanks!

Chris

bizchris2001

I can't seem to get this in PHP - here's the code if it were in MS Access:

DoCmd.RunSQL "Update tbl_flowers " &
"Set Type = '" & Me.NewType & "' " &
"Where Type = '" & Me.Combo0.Column(1) & "';"

Question is, what's not working in the PHP in my previous post? Thanks! Chris

Pig

OK, couple things.

First, you don't have an opening tag for select. Maybe you didn't post that.

Second, you didn't give the <option>'s a value. You need to get the id's for what you want, and put that as the value.
<option value=$row[whatever]>$row[blah]</option>

bizchris2001

You're right, I wasn't posting the complete code - it would actually start as

<?
//Query the ....
Is that what you meant in the first part of your reply?

Second, I thought I did give the options values, by making them equal to ['Type']. The dropdown works fine, I just can't get it to update with values I type in the NewType input (for example, Tiger Lily)... If this isn't what you were talking about, could you pop in the code where you're talking about?

bizchris2001

All,
I've been stuck on this for a week and a half, and can't find an answer anywhere. I'm trying to figure out what's wrong with my PHP that's making it so I'm not able to update the current dropdown values by typing in new text (though I was able to do it in MS Access, see my code above). Can anyone help, please? Thanks much!

Chris

Pig

You haven't printed your opening select in the code you show. You need to have a tag that says <select name=something>

also, you have assigned visual value to your options, but not the actual value that will be passed by the form. Reread my last post. Fix those 2 things and repost your code.

Pig

Make sure you post EVERYTHING so we can see what else might be wrong. You don't have an opening <form> tag displayed which could also be where the problem lies.

bizchris2001

Okay, here's the whole enchilada:

<?
$db_addr = "xxx.yyy.com";
$db_user = "me";
$db_pass = "guess";
$db_name = "MyDB";
$db_table = "tbl_flowers";

$connect = mysql_connect($db_addr,$db_user,$db_pass);

if($connect){
echo " connection made";
}
?>
<br>

Pig

You need to give an actual value to the options.
this: echo "<option>" . $row['Type'] . "</option>\n"; does not have a value. You have nothing to indicate what record you want. Simply displaying the type does not give a value, nor does it give you a distinct record.

Change your query to inlcude the table id.
$user_query = mysql_query("select Type, ID from tbl_flowers");

Then your while statement should look like:
while( $row = mysql_fetch_array($user_query) )
{
echo("<option value=\"$row[ID]\">$row[Type]</option>\n");
}

Also, your query is weird. It looks like you are trying to update the record without submitting since you are using $row in it. You have:
update tbl_flowers set Type = '" . $POST['NewType'] . "' where Type = '" . $row['Type'] . "'
change that last var from $row to $POST
$update = mysql_query("update tbl_flowers set Type = '" . $POST['NewType'] . "' where Type = '" . $POST['Type'] . "'");

bizchris2001

Thanks for the reply - fyi, I updated the ID field to be RowID because I think that's what you meant. Here's the code now, but it still doesn't update (no error, it just doesn't update):

<?
$db_addr = "xxx.yyy.com";
$db_user = "me";
$db_pass = "guess";
$db_name = "MyDB";
$db_table = "tbl_flowers";

$connect = mysql_connect($db_addr,$db_user,$db_pass);

if($connect){
echo " connection made";
}
?>
<br>

<?php
echo "<form action=\"$PHP_SELF\" method=\"post\">\n";
echo " <select name=\"Type\">\n";
//Query the Type field, which has Chrysanthemum, etc.
mysql_select_db('MyDB');
$user_query = mysql_query("select Type, RowID from tbl_flowers");

while( $row = mysql_fetch_array($user_query) )
{
echo("<option value=\"$row[RowID]\">$row[Type]</option>\n");
}
?>

bizchris2001

bump Help is greatly appreciated!

Pig

Sorry, I must be losing it. 2 things.

The action of your form is $PHP_SELF. If you have register globals on, then you are fine. Otherwise you need to use $_SERVER['PHP_SELF']
The query looks for the wrong qualifier. I realized the form was wrong, but didn't think to change the query. It needs to update based on the ID, not the type

$update = mysql_query("update tbl_flowers set Type = '" . $POST['NewType'] . "' where RowID = '" . $POST['Type'] . "'");

bizchris2001

Excellent - it's almost there!

1) I tried replacing this:

"The action of your form is $PHP_SELF. If you have register globals on, then you are fine. Otherwise you need to use $_SERVER['PHP_SELF']" ... so I updated it to:

echo "<form action=\"$_SERVER['PHP_SELF']\" method=\"post\">\n";

but it gave me a parse error - seems to be okay the other way (though I thought register_globals was set to off, maybe my host updated it)

2) I try to replace Tiger Lily (from the dropdown) with "Orchid", and when I click Submit it doesn't seem to do anything - until I do a hard refresh, at which point I see Orchid is now in the dropdown instead. Is there any way I can show the updated dropdown automatically after clicking Submit? Thanks!

Chris