Generate DateTime Value in PHP for Using in MySQL Command

When we work with date and/or time values in MySQL, it’s always easy to use built-in MySQL functions such as NOW(), DATE_ADD(), TIMESTAMP() etc… Now imagine you have to pass date/time values to MySQL from PHP variables. First time I tried that, it didn’t work out so easy for me.

Yes, if you simply take one column value and use it again, you have no problem. But here’s an example that you take date and time from MySQL column, add 1 seconds to that value and use it in query again:

//Here's just stuff in order to get a datetime value from MySQL DB.
$query = "SELECT TOP 1 `date` FROM `table`";
$result = mysql_query($query, $db_link);
$date_time_val = "";
if ($result) {
    while($row = mysql_fetch_assoc($result)) {
        //Here we get the value.
        $date_time_val = $row["date"];
    }
}

//Now we add one second to this value and use it again.
//First, use it for generating a DateTime variable from PHP library.
$time = new DateTime($date_time_val);
//If you need current time in PHP, you can use this:
//$time = new DateTime(date("Y-m-d H:i:s"));

//Add one second,
$time->modify("+1 seconds");

//You can not use it as it is
//You need to convert to a string in MySQL datetime format.
$time_string = $time->format("Y-m-d H:i:s");

//Now you're free to use in your query
$query_new = "SELECT * FROM `table` WHERE `date` = '".$time_string."' ";
Post a comment

T-SQL: Concatenate Multiple Rows of a Column into One Cell

Simple: You have a column and you want to take all rows as one cell. So that you can do searching, ordering, whatever you like.

Solution: Declare a variable, in this case VARCHAR or NVARCHAR, then SELECT the variable by equaling [itself] plus [column name] plus [your seperation char].

Here’s the code snippet:

DECLARE @concatenated NVARCHAR(1000)
SET @concatenated = ''
SELECT @concatenated = @concatenated + [NAME_COLUMN] + ',' FROM [USERS_TABLE] WHERE [ID_COLUMN] < 100
SELECT @concatenated
Post a comment

Generate Random Number and String in MS SQL Server

Sometimes you might wanna generate random number or string in MS SQL. For example, I do it for temporary user passwords.

Here’s how you generate a random number. X is the max value, the code is generating between 0 and (X-1).

SELECT CONVERT(INT, X*RAND())

Be aware that max. generated number is X-1 because it’s generating X numbers starting from 0(zero).

If you generate 5 numbers, your range will be: {0, 1, 2, 3, 4}.

 

Another code is generating random string(VARCHAR). Here it is:

SELECT SUBSTRING(CONVERT(varchar(255), NEWID()), 0, 9)

Attention to 0, 9. It’s substring range, here I’m generating a string value whose length is 10 characters.

1 Comment

JDK 7 With Netbeans: FTP Problem With Windows Firewall

Yesterday I installed Netbeans to my Windows PC and tried to add a remote PHP project. I want to say that 1 day before, I installed same version of Netbeans to my Mac and added the remote project, there were no issues.

But yesterday, I was unable to add remote project, no matter what I tried, it was unable to add, giving the error that there is no file on the remote folder. The connection was tested and working too.

So I tried to disable Windows Firewall and ta-dah! It worked. What confused me is that first time I opened Netbeans, it warned me that there might be issues with Windows Firewall and prompted the classic Firewall pop-up to me to either allow or block the application, and I allowed. But it still was unable to get through.

Some searches guided me that that was a problem with JDK 7 and some people downgraded to JDK 6 were able to connect FTP folder.

But I didn’t want to downgrade because it was loooking too unnecessary to me. I mean, this is JDK and Netbeans, seriously, no solutions?

Anyway, I was too sleepy to fight with firewalls or libraries at that moment, I slept. This morning, with a fresh brain, I found out that “The only difference between JDK7 and older releases is that the JDK is using IPv6 sockets when IPv6 is enabled and so IPv4-mapped IPv6 addresses are used. it may be that Windows or the firewall is not configured to allow IPv6 sockets.”(1)

And there was a command that worked for some people(2). I tried, it worked for me, too.

What you have to do is open MS-DOS with administrator rights and paste this, hit enter. Probably, it will work for you, too. Hope so.

netsh advfirewall set global StatefulFTP disable

(1) Source Link
(2) Source Link

27 Comments

Add a Link Button to Navigate Previous Page

In my Asp.Net web page, I had a trouble with navigating to the previous URL. I guess possibly because of postback, my linkbutton was navigating to the current page when I use this method:

Response.Redirect(Request["HTTP_REFERER"]);

I was unable to give previous page’s URL as a link simply, because there were a lot of data so it always was taking a while when I “navigated to the last page” with this method.

Then I saw this great JavaScript method and it works perfectly so far. All you have to do is to insert a Asp:LinkButton then add this to your Page_Load event:

lnkBtnPreviousPage.Attributes.Add("onClick", "javascript:history.back(); return false;");
Post a comment