Inserting Values into DB

[Anonymous]

I need to select 2 fields from the db. One field is a key field and the other is a var char field but has dates in it. Unfortunatly the date is being supplied to me as 05/05/2005 so I have to extract it to calculate how many days in stock a vehicle has been. After selecting these 2 fields I run the BookInDate fields through a script to come up with my DaysInStock value. I need to take the values I get for each record and then insert it back into the same table in the DaysInStock field. I then need to run another recordset to extract all vehicles where DaysInstock are greater than 45. I can get the first and last part I'm unclear as to how to take my values calculated after the first recordset and then insert them into the proper record in the DaysInStock field. Any help would be greatly appreciated.

 

[Anonymous]

I need to select 2 fields from the db. One field is a key field and the other is a var char field but has dates in it. Unfortunatly the date is being supplied to me as 05/05/2005 so I have to extract it to calculate how many days in stock a vehicle has been. After selecting these 2 fields I run the BookInDate fields through a script to come up with my DaysInStock value. I need to take the values I get for each record and then insert it back into the same table in the DaysInStock field. I then need to run another recordset to extract all vehicles where DaysInstock are greater than 45. I can get the first and last part I'm unclear as to how to take my values calculated after the first recordset and then insert them into the proper record in the DaysInStock field. Any help would be greatly appreciated.

1 as date use or simple string and put UNIX timestamp or use DB timestamp field type
2 make a request into array one first and second
3 calculate and manage it
4 put back in table
done.

 

[Anonymous]

I post on several forums for feedback and information and I must say this one is the most perplexing. Moderators and admin basically make us newbies feel quite dumn with their vague answers and lack of effort to answer the actuall question. I'm wondering why this forum is even here sometimes. If I'm suppose to make sense of wizards answer to my question then I only have 2 brain cells left. One for breathing and one for sleeping. I mean wizard did nothing to answer my problem. I'm already aware of what I need to do to solve the problem hense I stated it in my original question. What I need is help on figuring out a solution. It would appear this forum is for rhymes and riddles not problem solving help? I think I'll find a new forum to find answers.

 

[gesf]

I feel sad knowing that you think like that macrunning!

Vague answers come from vague questions. And your question is too vague!
What i see from your question is that... you want the code done and you haven't done almost anything.

I know i'm wrong, but what i'm trying to say is:
If you need some specific help, you need to show us your code and tell us where you're getting problems and what you're trying to do.

That way will be easier for anyone to help you.

----

Now for your question. Well... i see it's more a MySQL than a php issue, right !?
Seems that your date is stored in PHP and/or MySQL timestamp. That's what you need to tell us.

Whatever beeing you date field type, using MySQL date/time related functions, you can easily calculate your stock and with a single query.... i bet!

Please see: MySQL Date/Time field types (I think you can find your answer here)

I'll ask you to please describe a little more your date fields, so we can find a better way to do what you need.

 

[Anonymous]

gesf,
You must have missed the part where I said the date function was in a varChar format in the database because it is supplied as '05/05/2005'. Unfortunatley as I already went onto the mysql website and looked up the timestamp function this is in the wrong format hense it is in a varChar field and not a datetime field. So if the first time was to vague maybe you can read what I wrote this time. I've actually wrote out quite a bit of the code already so I'm not looking for someone to just write the code for me. And as for your assumption on and I quote 'you haven't done almost anything' I didn't think signing up for this forum was about mud slinging, I thought people here would be able to answer questions I have. But your correct in that it would seem this is a mysql issue. thanks for the link to the mysql date/time field types but already been there.

 

[gesf]

Ok macrunning... don't get me wrong.... peace ! Just let's forget our previous posts
Your right... i really miss that varchar part

Here's what i ended up.
First part: Let's turn our date from a string to integer values.

<?php

// A string, just like you get on your date result set
// month/day/year
$date_string = '03/01/2005';

// Let's break it in parts, so we can get
// an array with all date values
$date_parts = explode('/', $date_string);

// Just to get rid of... Notice: Undefined variable :P
$new_date_parts = array();

// Now we'll create a new array
// and turn the date values to integer
foreach($date_parts as $k => $v) {
    $new_date_parts[] = sprintf ("%02d", $v);
    
    // We could make $new_date_parts[] = (int) $v;
    // But we would lost the zeros(0) and this way we have the day
    // and month like it is.
}

// This for you to see what we have inside
// print_r($new_date_parts);

?>

Ok, now you have you date in integer.
Now i don't know how your date calculation will be done.
Here's a little example how you can work with the dates, in case you have both dates in the same format.
Example:

<?php

// mktime ( [int hour [, int minute [, int second [, int month [, int day [, int year [, int is_dst]]]]]]] )


// Creating UNIX TIMESTAMP of the dates

// Specify the first date
// What we have in our array.
$first_date = mktime (0,0,0,$new_date_parts[0], $new_date_parts[1], $new_date_parts[2]);

// Specify the second date
$second_date = mktime (0,0,0,5,15,2005);

// The calculation
// 1 day = 86400 seconds
$difference = ($second_date - $first_date) / 86400;

print 'The Difference: ' . $second_date . ' - ' . $first_date . ' = ' . $difference;
// You'll get The Difference: 1116115200 - 1109635200 = 75
// 75 -> Days

?>

Cheers

 

[Anonymous]

gesf,
Looks good but not quite what I needed. I've actually got this part down. I'm already able to extract the varChar field and calculate how long ago it is. What I'm having trouble with is getting this new value into my database. But your example looks great, a lot less code than what I came up with.

// my new variable for  total number of days

$DIM = ($yr_diff *365) + ($mon_diff * 30) + $day_diff;

// print '<br />Total number of days: '.$DIM;

// My attempt at getting the value back into the db in the correct field

mysql_select_db($database_myConnection, $myConnection);
$RecordsetInsert = "INSERT INTO used_vehicle (daysInStock) VALUES($DIM) ON DUPLICATE KEY UPDATE";

I need to make sure that each value that is inserted into the daysInStock field goes with the appropriate record.
Hope that makes a bit more sense of what I'm having trouble with.
Thanks for the previous code snippet.

 

[gesf]

Let's see if i get it!
Now your having a MySQL issue right ? You have to add it in a specific record.
You must have a field to indentify the record, say an "ID". So what you'll need is a WHERE clause to properly insert the value.

Is that what you need ?

And by the way.... backing to MySQL.
You can easily make the date calculation with MySQL.
Example:

// In thi case day/month/year .... but doesn't matter :p
SELECT FROM_UNIXTIME('05/01/2005') - FROM_UNIXTIME('01/01/2005');
// -> 4 (days)

You can also use MySQL's DATEDIFF() function. I couln't test it cus my MySQL version doesn't support it, but i almost sure it works!

So you are also able to do something like:

INSERT INTO used_vehicle (daysInStock)
VALUES(DATEDIFF(date_field2,date_field1)) 
WHERE vehicle_id = 2 
ON DUPLICATE KEY UPDATE;

// Or with the above FROM_UNIXTIME()

Hope i undertood you point and this helps

 

[Anonymous]

Didn't know about the UNIXTIME() function. Looks like that could shrink the code way way way down. I'll let you know about the insert into the db but it looks like your grasping my issue now. I know I need to use a where clause I think I'm missing something else. I'll keep hacking at it. Thanks again for your help.

 

[Anonymous]

Is it possible to use the FROM_UNIXTIME funtion in my sql clause: So for instance is it possible to write something like the following:

"SELECT * from myTable WHERE (FROM_UNIXTIME('SET TODAYS DATE') - FROM_UNIXTIME('STOCK_IN_DATE_IN_TABLE'))>45"

Basically I am trying to retrieve records older than 45 days. I'm thinking if I can use the FROM_UNIXTIME function then maybe set the first part to todays date and then subtract my value in the stockInDate field and anything greater than 45 will give me my result. Am I in the ballpark?

 

[Anonymous]

This is what I get from the unixtime() function:

Fatal error: Call to undefined function: from_unixtime() in /home/parkciti/public_html/data/test.php on line 8

Not sure whats up???

 

[gesf]

Yes, you're able to do that kind of query.
In FROM_UNIXTIME() you can also set the date format as second parameter:

FROM_UNIXTIME(unix_timestamp,format)

Please note that FROM_UNIXTIME() and UNIX_TIMESTAMP() are two different functions!
The first one returns a representation of the unix_timestamp argument as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context.
The second one, will return a unix_timestamp representation from the server current date and time.
Both functions assumes that its argument is a datetime value in the current time zone!

Fatal error: Call to undefined function: from_unixtime() in /home/parkciti/public_html/data/test.php on line 8

Your error is being return by PHP. That's a MySQL function, so... i believe it's in the wrong place.

Cheers