I have a couple of questions regarding getting/inserting/editing data into a mysql database via php form and I'm really stuck....but I forget who on here knows their shit...and if you do....plz to let me know. You'll be my hero.

3/4/2008 5:07:09 PM

ask the question

3/4/2008 5:08:32 PM

ok. I have (or am attempting to have) an event registration database. My first problem is that I have a field "REGION" in my database set as an ENUM with values (ie, africa, asia, pacific rim...etc) and I want people to be able to login to their registration page which will bring up a form that will have their data in it if it exists, OR just the blank field. Now I can get the values into any of the plain old text-field types in the form with th echo statement, but I'm at a total loss as to how to do this with the ENUM/select fields. Mind you that I was fine with just having the field be set as a text field in the database and then having the form itself have only the values (africa, asia, etc) but I don't know how to a)get that inserted into the database or b)get it to show up on the form if they are updating it.

3/4/2008 5:17:58 PM

Something along these lines? Someone correct me if I've mistyped something ...

$events = mysql_query("SELECT event_id FROM EVENTS");
echo "<select name=\"client_region\">";
    //Note: $client_region is our value we want to have preselected on the list. 
while( $row = mysql_fetch_row($events) )
{
    foreach ($row as $REGION)
    {
        echo "<option value=\"$REGION\"";
        if( $client_region== $REGION){ echo " selected=\"selected\""; $found_region= "Y"; } 
        echo ">$REGION</option>";
    }
}

3/4/2008 5:41:38 PM

do it as text, not enum, and validate it in code. GS7 has the right idea.


I'd highly HIGHLY recommend using a mysql library in PHP to do query management

PHPLib or Pear both have good scripts

3/4/2008 5:49:21 PM

ok....so I changed the field type to a text field.... and I tried using that code but something's not quite right:



and so I don't know where what would go ??

[Edited on March 4, 2008 at 6:17 PM. Reason : Please see the ghost edit, for the life of me I don't know how to use the stupid fucking 'code' tag]

3/4/2008 6:13:09 PM

<!--
$regions = mysql_query("SELECT region FROM REGISTRATION where username=$username");

3/4/2008 6:45:31 PM

why do you have html comment tags surrounding all the code

<!-- and -->
should be

<?php and ?>

3/4/2008 6:47:21 PM

I put the comment tags in there because I apparently have no idea how to use the code tag so instead of fucking up the page I just put it in a ghost edit that could be seen if you look at my 'edit post'

3/4/2008 6:50:06 PM

ghost edit?

[Edited on March 4, 2008 at 6:50 PM. Reason : ^^... but nvm, too slow]

3/4/2008 6:50:21 PM

Using the code tag is easy ...

[ code ] put your code here [ /code ]

But no spaces between the square brackets. There's also a # button at the top that will do it for you, click once, type your code, click again.


Edit: Oh also, if you'd like us to be more specific so you can understand what the code means within the context of your code, then you're best off posting your stuff and we make suggestions on it. Otherwise you'll get example code that can be hard to decipher if you're not familiar with it.

[Edited on March 4, 2008 at 7:35 PM. Reason : .]

3/4/2008 7:19:11 PM

     $options = array
      (
 "Africa" => "Africa"
  "Middle_East"  => 'Asia-Middle East',
   "Oceania"   => 'Asia-Oceania',
      "Europe"    => 'Europe',
         "North America"    => 'North America',
            "Caribbean"    => 'North America-Caribbean',
                "Latin_America" => 'Latin America'
  );



      $q = mysql_query ("SELECT region FROM registration WHERE username=$username");
      $r = mysql_result($q, 0);

      echo ''."\n";
      foreach ( $options as $region_name => $region_value ){

      $selected = ($region_name == $r ) ? ' selected="selected"' : '';
      echo ''.

         $region_value .''."\n";
        }

      echo ''; 

3/4/2008 7:55:47 PM

ok...once again, that stupid fucking code tag ain't working for me Look at the edit post for the entire thing, but how about this....I basically need code that says in 'code':

I need some sort of function that says 'Use these options for the select list (options are
Africa
Asia-Middle East
Asia-Oceania
Europe
North America
North America-Caribbean
Latin America)

if there is nothing already selected in the database....if there IS already something in the database then make THAT option the selected one'

3/4/2008 7:58:33 PM

test

     $options = array
      (
 "Africa" => "Africa"
  "Middle_East"  => 'Asia-Middle East',
   "Oceania"   => 'Asia-Oceania',
      "Europe"    => 'Europe',
         "North America"    => 'North America',
            "Caribbean"    => 'North America-Caribbean',
                "Latin_America" => 'Latin America'
  );



      $q = mysql_query ("SELECT region FROM registration WHERE username=$username");
      $r = mysql_result($q, 0);

      echo '<select name="regions">'."\n";
      foreach ( $options as $region_name => $region_value ){

      $selected = ($region_name == $r ) ? ' selected="selected"' : '';
      echo '<option value="'. $region_name .'"'. $selected .'>'.

         $region_value .'</option>'."\n";
        }

      echo '</select>'; 

3/4/2008 8:02:29 PM

     $options = array
      (
 "Africa" => "Africa"
  "Middle_East"  => 'Asia-Middle East',
   "Oceania"   => 'Asia-Oceania',
      "Europe"    => 'Europe',
         "North America"    => 'North America',
            "Caribbean"    => 'North America-Caribbean',
                "Latin_America" => 'Latin America'
  );



      $q = mysql_query ("SELECT region FROM registration WHERE username=$username LIMIT 1");
      $item = mysql_fetch_object($q);

      echo '<select name="regions">'."\n";
      foreach ( $options as $region_name => $region_value ){

      $selected = ($region_name == $item->region ) ? ' selected="selected"' : '';
      echo '<option value="'. $region_name .'"'. $selected .'>'.

         $region_value .'</option>'."\n";
        }

      echo '</select>'; 

3/4/2008 8:02:30 PM

BigMan. I owe you HUGE TIME. Seriously. I can't tell you how helpful you've been. I was near tears a couple days ago and now I'm pretty much done making this form and it's pages. I swear I'll send you $texas via paypal. You have no idea how helpful you've been.

3/6/2008 3:11:18 PM

Quote :

"do it as text, not enum, and validate it in code."

Noen, what benefits does it have to do this?

http://en.wikipedia.org/wiki/Second_normal_form

3/6/2008 3:35:40 PM

1) Because you should be checking your database input regardless.
2) Because there's no point in the overhead of hitting MySQL with information you can easily detect is invalid.
3) Databases are meant for holding data, not validating data.

3/6/2008 3:58:19 PM

Points 1 & 2: I'll agree with that, but that doesn't justify changing an enum to a text field.

Point 3: Doing so allows the database to also hold invalid data.

3/6/2008 4:23:59 PM

^^^ You can use text and keep it in second normal, you'd just need an extra table (or two). Using text would also make it a little easier to add new options and change existing ones. But if he is just changing his enum to a text field then that is bad.

I would have stuck to the enum field unless they're going to be changing the options a lot. It probably doesn't matter for this case, but using enum would be faster for complex queries w/ lots of data and it does provide that extra level of validation. Like ^^ said though, relying solely on the enum field to validate your data is bad form.

3/6/2008 4:29:24 PM

OK...new question. I was using a script that allows users to sign up by entering their email address and then the script creates a random password using MD5 encryption and sends it to the email address. Now I know very little about encryption and passwords etc, but the script works. The password will be something like e13p87. OK, so fine, it works....but I want to be able to let people change their password...and seeing as how I couldn't figure that out I thought, 'well fuck it' I'll just let them pick their own password when they signup and cut out the encryption part. Well everything worked, it updated in the database etc, but it's saying the login and username is incorrect. I don't understand why because looking in the database itself the password is in there and everything....I just don't see where I'm wrong. So either I need to have a page that lets the user change their password OR a page that let's them pick their own password and ideally, I need a page that sends lost passwords, but first things first. I'll post my code here but chances are the stupid code tag won't work for me in which case I'll post it in another post as a ghost edit. I tried taking out as much filler and html but meh...

[Edited on March 7, 2008 at 3:26 AM. Reason : nevermind...stupid paranthesis....I'll need help with something else I'm sure]

3/7/2008 3:05:54 AM

OK....told you I'd be back...so now I want a page that allows a user to have their password mailed to them if they've forgotten it.

1. Have a form with one field, 'username'

2. Have the script check to see if that username exists in the database, if NOT, then print an error message

3. If YES, then print results to an automatic email and send it to them.


I'm trying to go about this by reusing/cutting & pasting other code but clearly I'm missing something. How do you say, 'If the results of that query are 0, then print error message otherwise get that password and send it to them?

3/7/2008 12:45:58 PM

$query = 'SELECT * FROM `users` WHERE `name` == $name LIMIT 0, 30';
$result = mysql_query($query) or die ("error: " . mysql_error());
$num = mysql_num_rows($result);

if ($num < 1)
{
ERROR
}

else {
DO WORK SON
}

3/7/2008 1:23:21 PM

^ what he said, except that limit is not really necessary - your username field should be either unique or the primary key, so you expect either 1 or 0 results.

also, it should be

...WHERE `username` = "$name"

3/7/2008 1:52:07 PM

as usual, the code tag ain't working. In the edit post is what I have...and it's not working. Keep in mind that their email address IS their username....


[Edited on March 7, 2008 at 2:37 PM. Reason : EDIT POST]

3/7/2008 2:36:20 PM

There are a couple of errors. First of all, $username is never set. You check for $_POST['username'] and then have $username in your query. Second, you should only use one '=' in an sql statement. And third, which is the MOST important, you should escape your variables to avoid sql injection. It's easy, and you can do it when you set $username.

$username = mysql_real_escape_string($_POST['username']);
$query = "SELECT * FROM registration WHERE username = '$username'";

3/7/2008 3:07:29 PM

true. oops.

3/7/2008 3:12:10 PM

Honestly, I think it's time for you to buy a book or find a solid web tutorial.

Not that people shouldn't help you, just that these are all topics where solid walkthroughs and explanations exist and in the long run that may help you more.

3/7/2008 3:12:14 PM

^I've never taken a computer class in my life (exception of a gen id MIS class where we learned about where QWERTY came from and what RAM stood for) and I have a book...and I've read many a tutorials...but the truth is that I use such a little bit of php (in the grand scheme of things) that it would take me too long and too much time to learn php from the beginning...and I've managed to get around this somewhat decently...usually. And I follow tutorials but the minute I need something a little different, I find myself in a mess of code that doesn't make sense...so it's really helpful to me that there are so many of you guys on here who are smart enough to actually know the code rather than 'how to make something work'.

That being said, I've replaced and changed what you guys said to change and I'm getting an error in the last line of my code....on the page. I can only assume I have a bracket where I should or shouldn't have....or I didn't close something...or?

3/7/2008 3:37:11 PM

well what is the error?

p.s. i'm going to the beach right now, so i apologize in advance for not answering.

3/7/2008 3:44:31 PM

OK....well this is I'm sure a novice question but..In an effort to work through this problem myself I've shortened the scripts and elimimated certain parts in order to find the problem...I've gotten it to the point where I can query the database by username and then display the password....my problem is that I apparently am not getting the right way to define the variable 'password'

 $sql = "SELECT password FROM registration WHERE username = '$_POST[username]'";
   $password=$sql('$password');
   $message = "Your password for $username is $password

3/8/2008 2:35:07 PM

is the password encrypted in the database? if so you'd need to decrypt it first

3/8/2008 3:09:21 PM

^no....it was initially, but not anymore....it's just a plain old text password...

3/8/2008 3:23:33 PM

 $sql = "SELECT password FROM registration WHERE username = '{$_POST[username]}'";
   $password=mysql_fetch_object(mysql_query($sql))->password;
   $message = "Your password for $username is $password";

3/8/2008 3:40:11 PM

^I'm getting an error and I'm assuming it's because I'm not using PHP5....how else can I do it?

3/8/2008 3:58:31 PM

You know....actually posting the error its giving you could help a little...

Quote :

" $sql = "SELECT password FROM registration WHERE username = '{$_POST[username]}'"; $password=mysql_fetch_object(mysql_query($sql))->password; $message = "Your password for $username is $password"; "

I think there may be something wrong with doing this, at least in PHP4. I don't think it likes you to call a method function and then use -> to reference a variable of the returned object all on the same line. I don't have my development computer with me so I can't test it, but try splitting it out to be ...

$return = mysql_query($sql);
$row = mysql_fetch_object($return);
$password = $row->password;

and see if it still gives an error.

[Edited on March 8, 2008 at 4:35 PM. Reason : .]

[Edited on March 8, 2008 at 4:53 PM. Reason : method != function]

3/8/2008 4:33:28 PM

^IT WORKED!!!!!!!!!!!!!!!!!!


OMG I <3 YOU!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!


BigMan, you know I <3 you x 1000

3/8/2008 4:46:12 PM

I'd suggest doing something like this:

$return = mysql_query($sql);
if ($row = mysql_fetch_object($return)){
$password = $row->password;
} else {
echo 'Record not found.';
}

That will give you some error checking in case the record isn't found.

3/8/2008 10:35:41 PM