Page 2 of 3

Posted: Tue Nov 28, 2006 2:19 pm
by RobertGonzalez
CURDATE() is a MySQL function.

Code: Select all

INSERT INTO `mytable` (`my_date`) VALUES (NOW());

Posted: Tue Nov 28, 2006 2:58 pm
by Obadiah
when i used it worked

Code: Select all

$sql="INSERT INTO $table_name (create_date) VALUES (NOW())";
but it ignored everything else it needed to post to the database....this is the way i set it up

Code: Select all

"INSERT INTO $table_name values('$_POST[merchant_num]', 
                '$_POST[date_recieved]', 
                '$_POST[merchant_name]', 
                '$_POST[purchase_type]', 
                '$_POST[lease_score]', 
                '$_POST[amex]', 
                '$_POST[app_id]', 
                '$_POST[discover]', 
                '$_POST[user_name]', 
                '$_POST[check_conversion]', 
                '$_POST[gift_loyalty]', 
                '$_POST[app_type]', 
                '$_POST[terminal]', 
                '$_POST[serial_num]', 
                '$_POST[nms]', 
                '$_POST[ckmerchant_num]', 
                '$_POST[giftmerchant_num]', 
                '$_POST[comments]',)";

$sql="INSERT INTO $table_name (create_date) VALUES (NOW())";
in the database it stored like this

"null", "null","null","null","null","null","null",
"null","null","null","null","null","null","null"
"null","null","null","null","null","2006-11-28"

is there a way to make it so all entries enter?

Posted: Tue Nov 28, 2006 3:41 pm
by RobertGonzalez
You need to wrap each $_POST array var in curly braces. Better yet, you should not be taking data sent from a form and putting it directly into the database. You should be filtering it first. But, just as an example, do this with what you have...

Code: Select all

$sql = "INSERT INTO $table_name values ('{$_POST[merchant_num]}',
                '{$_POST[date_recieved]}',
                '{$_POST[merchant_name]}',
                '{$_POST[purchase_type]}',
                '{$_POST[lease_score]}',
                '{$_POST[amex]}',
                '{$_POST[app_id]}',
                '{$_POST[discover]}',
                '{$_POST[user_name]}',
                '{$_POST[check_conversion]}',
                '{$_POST[gift_loyalty]}',
                '{$_POST[app_type]}',
                '{$_POST[terminal]}',
                '{$_POST[serial_num]}',
                '{$_POST[nms]}',
                '{$_POST[ckmerchant_num]}',
                '{$_POST[giftmerchant_num]}',
                '{$_POST[comments]}',)"; 

Posted: Tue Nov 28, 2006 4:05 pm
by Obadiah
i know i should have sanitized the fields but i was testing to see whether i could get this to work....but i did try what you asked and it gave me this as an error for each variable
Notice: Use of undefined constant comments

Posted: Tue Nov 28, 2006 4:32 pm
by RobertGonzalez
Try wrapping the array indeces in single quotes also. Of course, you could start small and just try a single value insert to see if it is taking.

Posted: Tue Nov 28, 2006 4:50 pm
by Obadiah

Code: Select all

$sql = "INSERT INTO $table_name values('{'$_POST[merchant_num]'}',
		'{'$_POST[date_recieved]'}',
		'{'$_POST[merchant_name]'}',
		'{'$_POST[purchase_type]'}',
		'{'$_POST[lease_score]'}',
		'{'$_POST[amex]'}',
		'{'$_POST[app_id]'}',
		'{'$_POST[discover]'}',
		'{'$_POST[user_name]'}',
		'{'$_POST[check_conversion]'}',
		'{'$_POST[gift_loyalty]'}',
		'{'$_POST[app_type]'}',
		'{'$_POST[terminal]'}',
		'{'$_POST[serial_num]'}',
		'{'$_POST[nms]'}',
		'{'$_POST[ckmerchant_num]'}',
		'{'$_POST[giftmerchant_num]'}',
		'{'$_POST[comments]'}')";
$sql="INSERT INTO $table_name (create_date) VALUES (NOW())";
it gives me what the result of the original...it ignores the first insert command and enteres the correct date....now say if i did something like this

Code: Select all

$sql = "INSERT INTO $table_name values('{'$_POST[merchant_num]'}',
		'{'$_POST[date_recieved]'}',
		'{'$_POST[merchant_name]'}',
		'{'$_POST[purchase_type]'}',
		'{'$_POST[lease_score]'}',
		'{'$_POST[amex]'}',
		'{'$_POST[app_id]'}',
		'{'$_POST[discover]'}',
		'{'$_POST[user_name]'}',
		'{'$_POST[check_conversion]'}',
		'{'$_POST[gift_loyalty]'}',
		'{'$_POST[app_type]'}',
		'{'$_POST[terminal]'}',
		'{'$_POST[serial_num]'}',
		'{'$_POST[nms]'}',
		'{'$_POST[ckmerchant_num]'}',
		'{'$_POST[giftmerchant_num]'}',
		'{'$_POST[comments]'}',
		'{'$_POST[NOW()]'}')";
it will enter 00-00-00 in the create_date spot but enter in everything else :?

Posted: Tue Nov 28, 2006 4:52 pm
by feyd
NOW() is inside of the $_POST variables?

Posted: Tue Nov 28, 2006 4:56 pm
by Obadiah
right....because im trying to post the current date into create_date(create_date is the last field in the table) i know its an improper way of doing it but i dont understand how to get it to work without ignoring the above INSERT command

Posted: Tue Nov 28, 2006 5:00 pm
by feyd
I'll try again, "NOW()" is a field name in your form?

Posted: Tue Nov 28, 2006 5:06 pm
by Obadiah
ok...i get what your saying feyd...no NOW() is not a field im my DB...instead its a function that spits out the current date....so in saying that i have a question for ya...say if i placed the name create_date there instead...how do i inject the NOW() function into that value because supposedly its supposed to be a field but its not...thats where the trouble is comming from i know...but how do i put the value of NOW() into the database with all of the other values without actually having a feild and posting it in there with all of the other fields

Posted: Tue Nov 28, 2006 5:15 pm
by feyd
You don't need any PHP junk around it, nor do you it need with single quotes around it. It would look a lot like the way your former snippet's latter query is.

Posted: Tue Nov 28, 2006 5:35 pm
by RobertGonzalez
$_POST is an array of information that comes from a form, not what you'd use to 'post' things to the database. NOW() is a MySQL function, not a PHP one. Try this and see what comes of it.

Code: Select all

$sql = "INSERT INTO $table_name values ('{$_POST['merchant_num']}',
                '{$_POST['date_recieved']}',
                '{$_POST['merchant_name']}',
                '{$_POST['purchase_type']}',
                '{$_POST['lease_score']}',
                '{$_POST['amex']}',
                '{$_POST['app_id']}',
                '{$_POST['discover']}',
                '{$_POST['user_name']}',
                '{$_POST['check_conversion']}',
                '{$_POST['gift_loyalty']}',
                '{$_POST['app_type']}',
                '{$_POST['terminal']}',
                '{$_POST['serial_num']}',
                '{$_POST['nms']}',
                '{$_POST['ckmerchant_num']}',
                '{$_POST['giftmerchant_num']}',
                '{$_POST['comments']}',
                NOW())";
EDIT | Was missing a closing paren at the end.

Posted: Wed Nov 29, 2006 1:03 am
by dibyendrah
Dear all,

I inserted the data in the table with the following loop like follows :

Code: Select all

$yy = 2006;
for($mm=9; $mm<=12; $mm++){
	for($dd=1; $dd<=30; $dd++){
		$dt = $yy."-".$mm."-".$dd;
		print $dt."\n";
		$sql = "INSERT INTO tbl_dates(date_val)  VALUES('$dt')";
		mysql_query($sql, $cn) or die(mysql_error());
	}
}


Previous query which I posted used to fetch all the records which are less than 30 days. It means all the records less between 30 days

Code: Select all

SELECT date_val FROM tbl_dates WHERE date_val<=DATE_SUB(CURDATE(), INTERVAL 30 DAY)
The output gave all the dates which are less than 30 days from current date.

So, to delete exactly records which are 30 days old you have to do like follows :

Code: Select all

SELECT date_val FROM tbl_dates WHERE date_val=DATE_SUB(CURDATE(), INTERVAL 30 DAY)

Code: Select all

Output :
2006-10-30

Hope this will help you.

With Regards,
Dibyendra

Posted: Wed Nov 29, 2006 8:12 am
by Obadiah
Everah wrote:$_POST is an array of information that comes from a form, not what you'd use to 'post' things to the database. NOW() is a MySQL function, not a PHP one. Try this and see what comes of it.

Code: Select all

$sql = "INSERT INTO $table_name values ('{$_POST['merchant_num']}',
                '{$_POST['date_recieved']}',
                '{$_POST['merchant_name']}',
                '{$_POST['purchase_type']}',
                '{$_POST['lease_score']}',
                '{$_POST['amex']}',
                '{$_POST['app_id']}',
                '{$_POST['discover']}',
                '{$_POST['user_name']}',
                '{$_POST['check_conversion']}',
                '{$_POST['gift_loyalty']}',
                '{$_POST['app_type']}',
                '{$_POST['terminal']}',
                '{$_POST['serial_num']}',
                '{$_POST['nms']}',
                '{$_POST['ckmerchant_num']}',
                '{$_POST['giftmerchant_num']}',
                '{$_POST['comments']}',
                NOW())";
EDIT | Was missing a closing paren at the end.
thanks everah..that worked like a charm....also thanks for the clarity on the post...i knew it gathered the information from the form via the array but was uncertain of how it actually stored the info into the db....i just figured it played a double role...when i tried to do the now before like the way you have it there it was giving me whitespace errors...im guessing because of the closed paren also

Posted: Wed Nov 29, 2006 9:19 am
by Obadiah
ok peeps one more question...much thanks to dibyendrah for the inspiration....now, im already using a where clause to pull up records based on username right

if i wanted to use this query statement

Code: Select all

SELECT create_date FROM merchant WHERE create_date<=DATE_SUB(CURDATE(), INTERVAL 30 DAY)
and combine it with the one i already have

Code: Select all

Select * FROM merchant WHERE user_name = '{$_SESSION['logname']}'
would i need to use a and(is that even allowed)?

sort of like

Code: Select all

Select * FROM merchant WHERE user_name = '{$_SESSION['logname']}' AND WHERE create_date<=DATE_SUB(CURDATE(), INTERVAL 30 DAY)
or do i actually write out AND

[edited]

edited multiple times for testing color error indicators :)