Hi,

i am not a php or sql programmer. I have a simple php form in dreamweaver that submits to another file "insert.php" which inserts the data into a MySQL database. Most of it works but I am having a problem with some fields.

First I want to get the current date YYYY-MM-DD posted into a field in the database. I am not getting anything. Not sure how to do it.

Next I have a drop down box field name "LocationID" which is posting the value which is a number to the database. I want the label value of the dropdown which would produce a cities name not the value of the selected item.

Lastly I have a field "City" which i would like to add "" + "City" + "domainname.com" and take the results and insert it into a field in the database.

I've been searching all day and with all the information online I can't come up with any help.

Thanks in advance.

Bill

I needed something like that, can't remember the exact details but here's part of the query . . .

$sql = sprintf("UPDATE %s SET language=%s, address=%s, postcode=%s, city=%s, country=%s, phone=%s, fax=%s, homepage=%s, notes=%s, %s=%s, %s=%s, %s=%s, %s=%s,<b> last_change=NOW() </b>WHERE id = %s AND users_id = %d",

NOW() picks up the current date, also check out in the mysql documentation exactly what it puts in

IMHO your location city is best done with a separate table so that you store the cities only once then in your main table store the key (a foreign key) of the city, that's like the number you are now getting. Doing it that way you are not storing the same city name over and over again, which uses less space.

The last bit is a buildup before storing, something like . . .

$field=''; /starting point
$field=$field.$my_city_name; / which will have been pre-set to your city name
$field=$field.$_SERVER["SERVER_NAME"];

then insert $field

check out the php documentation for the environment varibles too

Hi Alan,

Thanks for the example though still trying to get it to work. One thing you did not mention was how to get a label value from a dropdown and insert it into a database field.

Example:

&lt;SELECT name=State id="State"&gt;
&lt;OPTION value=-1&gt;&lt;/OPTION&gt; &lt;OPTION
value=0&gt;AK&lt;/OPTION&gt; &lt;OPTION value=1&gt;AS&lt;/OPTION&gt;
&lt;OPTION value=2&gt;AZ&lt;/OPTION&gt; &lt;OPTION
value=3&gt;AR&lt;/OPTION&gt; &lt;OPTION value=4&gt;CA&lt;/OPTION&gt;
&lt;OPTION value=5&gt;CO&lt;/OPTION&gt; &lt;OPTION
value=6&gt;CT&lt;/OPTION&gt; &lt;OPTION value=7&gt;DE&lt;/OPTION&gt;
&lt;OPTION value=8&gt;DC&lt;/OPTION&gt; &lt;OPTION value=""
selected&gt;&lt;/OPTION&gt;&lt;/SELECT&gt;



Above is a dropdown for a user to choose their state. The form submit will pass the field "state" to the insert.php script which inserts it into a database.

Note: the State "AZ" has a value of "2". If i post the field "state" that the user selected (AZ) in the submit form to the mysql database, the value of state is "2". How can i get the label.value ( "AZ" that the user selected) to the "state" field to insert into the database?

Thanks,

Bill

Bill,
You store that number in your first table, what I was suggesting is that you store the states in a separate table in your database. That means you only store the state codes once. You would have a separate table, states, with just two fields, an index - numbered from 1 to n, and the corresponding state names in full or 2 letter codes, or both.

You store the number in your other table, then when you want to show the state you do something like this example, it comes from something similar where there are events at different cities, so that I don't have to keep storing city names I have a separate table cities, the INNER JOIN takes the value from the event_city field (which is a number) in the first table and looks in the cities table for a match, then extracts the city_name from the cities table.

This looks much more complex, BUT you only store the cities once and when you need to add a new city it's simple, so is removing one. I appreciate that's a lot less likely with US states.

SELECT location, title, approved, contact_name, event_city, city_name FROM events INNER JOIN cities ON city_number = event_city ORDER BY city_name ASC

Hope that helps

Alan

Thanks for the suggestion. But as i said i don't know php or sql well. So to do that would simply open up a larger can of worms for me and put me miles from a solution. Like how to post to more than one table at a time. Much more for updating and deleting. The table i have is not very big. Not that much information stored. So really I only need a solution on how to store both the value and label of a dropdown selected field. Does php work like VB? $field = selected.label.state?

Bill


Edited by - gibsongk55 on 17 Dec 2006 01:39:07

Yes, you have a good point there.

What I was trying to avoid was a solution based on fixed data because that means that in the future when you have to update something your updates would have to be to the code rather than the tables.

&lt;select name="arrival_time" id="arrival_time"&gt;
&lt;option value="3 to 5pm"&gt;between 3pm and 5pm&lt;/option&gt;
&lt;option value="5 to 7pm" selected="selected"&gt;between 5pm and 7pm&lt;/option&gt;
&lt;option value="7 to 9pm"&gt;between 7pm and 9pm&lt;/option&gt;
&lt;/select&gt;

the selected="selected" bit says which option is selected by default

works well for me, so you could build yours like . . .
&lt;select name="State id" id="State"&gt;
&lt;option value="AK"&gt;AK&lt;/option&gt;
&lt;option value="AZ"&gt;AK&lt;/option&gt;
etc, etc for the other states/provinces
&lt;/select&gt;

rather than typing out that lot you might be able to find a form that someone has already done and look at the source code. Here's an example that has lots of dropdowns in different formats

uaonline.alaska.edu/banprod/owa/bwskwpro.P_WebProspectMain?prel_code_in=JUWEB&button_text_in=Continue