homepage Welcome to WebmasterWorld Guest from 54.226.213.228
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
mysql insert
ricksum




msg:4595458
 7:30 pm on Jul 22, 2013 (gmt 0)

I've searched here and the web for answer but none found.
I have a mysql table that has 15 columns but want to control how many appear in insert statement. Sample Code:

$query = "INSERT INTO 2013attend
VALUES ('Lastname','Firstname','testcode')";

Only these these three fields are required in the form and the remaing 12 are optional. How do I do this.

 

bhukkel




msg:4595462
 7:38 pm on Jul 22, 2013 (gmt 0)

its like this:

insert into table (col_name1, col_name2) values (val1,val2)

LifeinAsia




msg:4595467
 7:54 pm on Jul 22, 2013 (gmt 0)

What about the 12 optional fields- are you just going to ignore them even if the user fills them out? Or do you still want to add them to the table if the user does fill them in?

ricksum




msg:4595524
 10:33 pm on Jul 22, 2013 (gmt 0)

Yes, I want to add to the table if user fills them.

JD_Toims




msg:4595541
 11:18 pm on Jul 22, 2013 (gmt 0)

$var_1='NA';$var_2='NA';$var_3='NA';$var_4='NA';$var_5='NA';
// I'm sure you get the point there should be 15.

### DB Connection Goes Here ###

// Yes, I delimit my pregs with #s since I got tired of having to escape /s one day.

if(preg_match('#^PATTERN_HERE$#',$_POST['field_1'])) {
$var_1=mysql_real_escape_string($_POST['field_1']);
}

if(preg_match('#^PATTERN_HERE$#',$_POST['field_2'])) {
$var_2=mysql_real_escape_string($_POST['field_2']);
}

if(preg_match('#^PATTERN_HERE$#',$_POST['field_3'])) {
$var_3=mysql_real_escape_string($_POST['field_3']);
}

if(preg_match('#^PATTERN_HERE$#',$_POST['field_4'])) {
$var_4=mysql_real_escape_string($_POST['field_4']);
}

if(preg_match('#^PATTERN_HERE$#',$_POST['field_5'])) {
$var_5=mysql_real_escape_string($_POST['field_5']);
}

$query="INSERT INTO 2013attend ('Col_1','Col_2','Col_3','Col_4','Col_5') VALUES ($var_1,$var_2,$var_3,$var_4,$var_5)";
// Yes there should be 15 of each above too, but I didn't feel like typing that much lol

### Run the Query Here ###

JD_Toims




msg:4595583
 2:40 am on Jul 23, 2013 (gmt 0)

I guess I should note there are a bunch of different ways it could be coded, and for my use I'd probably use a for loop and check/set that way, but I tried to post for readability and efficiency/refinements can always be made later.

LifeinAsia




msg:4595773
 3:06 pm on Jul 23, 2013 (gmt 0)

$var_1='NA';$var_2='NA';$var_3='NA';$var_4='NA';$var_5='NA';

Personally, I wouldn't add 'NA' to the table if the user didn't input anything for that field. I would either leave it as NULL (a little more coding involved) or use JD_Toims' method with an empty string (i.e., '') instead of 'NA' in the field.

ricksum




msg:4595796
 4:27 pm on Jul 23, 2013 (gmt 0)

I'm very new at both php and mysql and don't understand the preg_match code. Does the following check for no data entered in field_1 and put '' in the column and if so do I just repeat the code for each column?

if(preg_match('#^PATTERN_HERE$#',$_POST['field_1'])) {
$var_1=mysql_real_escape_string($_POST['field_1']);

JD_Toims




msg:4595827
 6:14 pm on Jul 23, 2013 (gmt 0)

I would either leave it as NULL (a little more coding involved) or use JD_Toims' method with an empty string (i.e., '') instead of 'NA' in the field.

Anything "short" is cool in my opinion.

The reason I opted for NA is rather than leaving it NULL is if it's "something" we know "got the info and processed the script, but the user gave No Answer or incorrect info to [whatever] values." ... If it's NULL and NULL is the default "don't know" then it makes "did the user opt to not give us the info, enter something incorrectly or did we have a 'glitch' somewhere?" a bit tougher to identify.

If there's a "known value" that should be the "default" in all cols after the script runs and we find a NULL somewhere, we know there was a glitch in our system, but if NULL is the default and we leave "Not Answered" as NULL, then it can make trouble-shooting a bit more difficult in some cases.

Anyway, that's why I enter some type of "short not NULL something", but it's probably not a big deal either way in most situations.

I'm very new at both php and mysql and don't understand the preg_match code.

The regex doesn't "check for anything" currently since I don't know what info should be present for each field, but if there was an actual pattern rather than PATTERN_HERE and the posted information matched that pattern, then the posted information would be entered in the col rather than the NA I set the variables to initially.

There's some info on pattern here: [webmasterworld.com...]

ricksum




msg:4595836
 7:23 pm on Jul 23, 2013 (gmt 0)

I guess I need to learn to use preg_match and regex so will start trying to learn today ... thanks for everything.

JD_Toims




msg:4595969
 5:01 am on Jul 24, 2013 (gmt 0)

Sure thing ricksum... Regex isn't much fun when you're learning it, but it's a powerful tool to have in your arsenal, so now that you have good reason, imo, it's a good time to add a tool to the proverbial toolbox.

Sorry I don't have time to "just code" everything for everyone, but at the same time learning how to use "new tools" is definitely a long-term benefit. So in some ways it's actually better if I don't "just code" for other people who are trying/need to learn.

If you have some ideas of what you want to do and some regex patterns you think should work but don't, please, post in the PHP Forum [webmasterworld.com] and I'll try to help you out if I see one of your posts. But, if I can't, or miss it, there are plenty of people here who post regularly and are well qualified to give you a hand and help you work through any difficulties you're having in getting things going the way you want/need them to with regex pattern matching.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved