This topic is locked
[SOLVED]

 Using the $strSQLInsert function

10/8/2011 4:28:39 PM
PHPRunner General questions
S
swinma author

Dear reader,
I’m having a problem in writing some code for my database.

I hope anyone can give me a clue because I’m trying this for some time now but with no results.

Maybe it’s because I,m not a programmer but the issue is as follows:

I’m trying to insert a record in a table after a record was added in another table.

In the first table I’m adding a date field with the picker. In the events of this table on the “after record added” I’m using this date to make a start date and an end date.

For example: date added in the first table08-10-2011. I’m using the month and year to make the following start date and end date  fact_start = 01-10-2011 and fact_end = 30-10-2011. But what happens I get only 0-0-0000 in my date fields in the second table instead of for example: 01-10-2011 for Fact start and 30-10-2011 for fact Eind?

Please can someone help me on this?
See events code:

// Place event code here.

// Use "Add Action" button to add code snippets.
global $dal;

global $conn;
$dal->Table("Facturen");

$dal->Table("Vestigingen");

$dal->Table("fact_code");
// bepalen factuurcode nieuwe factuur (AK, AZ, GEZ)

$tmp1 = $dal->Vestigingen->Query("Vestigingen_id=".$values["Vestigingen_id"],"");

$datatmp1 = db_fetch_array($tmp1);
$fact_tmp1 = $datatmp1["vestiging_code"];
if ($datatmp1["vestiging_code"]=="GEZ" && $values["behandelaar_id"]=="AK") $fact_tmp1 = "AK";

if ($datatmp1["vestiging_code"]=="GEZ" && $values["behandelaar_id"]=="AZ") $fact_tmp1 = "AZ";
$tmp2 = $dal->fact_code->Query("fact_code='".$fact_tmp1."'","");

$datatmp2 = db_fetch_array($tmp2);
$fact_tmp2 = $datatmp2["fact_code_id"];
echo $fact_tmp2 . "<br />";
// bepalen begin en einddatum nieuwe factuur
$fact_tmp_y = substr($values["behandel_datum"],0,4);

$fact_tmp_m = substr($values["behandel_datum"],5,2);
$fact_tmp_start = mktime(0,0,0,$fact_tmp_m,1,$fact_tmp_y);

$fact_tmp_eind = mktime(0,0,0,$fact_tmp_m,30,$fact_tmp_y);
echo $fact_tmp_start . "<br />";

echo $fact_tmp_eind . "<br />";
echo date('d-m-Y',$fact_tmp_start) . "<br />";

echo date('Y-m-d',$fact_tmp_eind) . "<br />";
//Zoek of voor de client factuurnummer binnen start en einddatum aanwezig is, zoja gebruik factuurnummer zo niet maak factuurnummer aan

$strSQLExists = "select * from Facturen where fact_client_id=".$values["behandel_client_id"]." AND fact_start>=".$values["behandel_datum"]." AND fact_eind<=".$values["behandel_datum"];

$rsExists = db_query($strSQLExists,$conn);

$data=db_fetch_array($rsExists);
if($data)

{

echo"Factuur bestaat";

// if record exists do something
}
else
{

$strSQLInsert = "insert into Facturen (fact_client_id, fact_code_id, fact_start, fact_eind) values (".$values["behandel_client_id"].", ".$fact_tmp2.", ".date('d-m-Y',$fact_tmp_start).", ".date('Y-m-d',$fact_tmp_eind).")";
db_exec($strSQLInsert,$conn);
// if dont exist do something else
S
stiven 10/8/2011

For what i understood you want to get the month and the year from table 1 and then insert on table 2 the start date with the first day of that month and the end date with the last day of that month?? this is what i would do... I'M ASSUMING THAT YOU HAVE A DATE FIELD ON THE DATABASE IN THIS FORMAT 'YYYY-MM-DD' if you have it in different format you might have to adjust the code.. also notice not every month has 30 days..
I'm not sure where your getting $fact_tmp2 from.. but if the problems were with the date maybe you can use this code..
hope this helps...



$cdate = $values["behandel_datum"];//get the date value

$arr = explode("-",$cdate);
$fact_tmp_y = $arr[0];//get the year

$fact_tmp_m = $arr[1];//get the month
$fact_tmp_start = $fact_tmp_y."-".$fact_tmp_m."-01";// this is the YYYY-MM-01 for start time

$fact_tmp_eind = $fact_tmp_y."-".$fact_tmp_m."-30";// this is the YYYY-MM-30 for end time
global $conn;
$strSQLInsert = "insert into Facturen (fact_client_id, fact_code_id, fact_start, fact_eind) values ('".$values["behandel_client_id"]."','".$fact_tmp2."','".$fact_tmp_start."','".$fact_tmp_eind."')";

db_exec($strSQLInsert,$conn);
S
swinma author 10/9/2011

Thanks <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=61338&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' /> for the reply,
I replaced my code for the code you wrote.

I made some adjustments to ensure i had the same date format.
// Place event code here.

// Use "Add Action" button to add code snippets.
global $dal;

global $conn;
$dal->Table("Facturen");

$dal->Table("Vestigingen");

$dal->Table("fact_code");
// bepalen factuurcode nieuwe factuur (AK, AZ, GEZ)

$tmp1 = $dal->Vestigingen->Query("Vestigingen_id=".$values["Vestigingen_id"],"");

$datatmp1 = db_fetch_array($tmp1);

$fact_tmp1 = $datatmp1["vestiging_code"];
if ($datatmp1["vestiging_code"]=="GEZ" && $values["behandelaar_id"]=="AK") $fact_tmp1 = "AK";

if ($datatmp1["vestiging_code"]=="GEZ" && $values["behandelaar_id"]=="AZ") $fact_tmp1 = "AZ";
$tmp2 = $dal->fact_code->Query("fact_code='".$fact_tmp1."'","");

$datatmp2 = db_fetch_array($tmp2);
$fact_tmp2 = $datatmp2["fact_code_id"];
echo $fact_tmp2 . "<br />";
// bepalen begin en einddatum nieuwe factuur

$cdate = $values["behandel_datum"];//get the date value

$arr = explode("-",$cdate);

$fact_tmp_y = $arr[0];//get the year

$fact_tmp_m = $arr[1];//get the month

$fact_tmp_start = "01-".$fact_tmp_m."-".$fact_tmp_y;// this is the 01+MM-YYYY for start time

$fact_tmp_eind = "30-".$fact_tmp_m."-".$fact_tmp_y;// this is the 30-MM-YYYY for end timeglobal
echo $fact_tmp_start . "<br />";

echo $fact_tmp_eind . "<br />";
//Zoek of voor de client factuurnummer binnen start en einddatum aanwezig is, zoja gebruik factuurnummer zo niet maak factuurnummer aan

$strSQLExists = "select
from Facturen where fact_client_id=".$values["behandel_client_id"]." AND fact_start>=".$values["behandel_datum"]." AND fact_eind<=".$values["behandel_datum"];

$rsExists = db_query($strSQLExists,$conn);

$data=db_fetch_array($rsExists);
if($data)

{

echo"Factuur bestaat";

// if record exists do something
}
else
{

$strSQLInsert = "insert into Facturen (fact_client_id, fact_code_id, fact_start, fact_eind) values (".$values["behandel_client_id"].", ".$fact_tmp2.", ".$fact_tmp_start.", ".$fact_tmp_eind.")";
db_exec($strSQLInsert,$conn);
// if dont exist do something else
}

*[size="2"][/size]
I added an echo to make sure if the date is in the same format which is DD-MM-YYYY (Nederlands(Nederland)).

the result from the echo: 01-10-2011 and 30-10-2011

So far so good but when i use the strSQLInsert to fill the date field in the other table i get the same result <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=61338&image=2&table=forumreplies' class='bbc_emoticon' alt=':(' /> for fact_start 0-0-0000 and for fact_eind the same 0-0-0000.

Could it be that i'm trying to insert a string in a date field. Must i first convert the string back to type date?

I'm hoping for new ideas?

thanks

C
cgphp 10/9/2011

Single quotes around the two date values are missing:

$strSQLInsert = "insert into Facturen (fact_client_id, fact_code_id, fact_start, fact_eind) values (".$values["behandel_client_id"].", ".$fact_tmp2.", '".$fact_tmp_start."', '".$fact_tmp_eind."')";
db_exec($strSQLInsert,$conn);
S
stiven 10/9/2011

$cdate = $values["behandel_datum"];//get the date value

$arr = explode("-",$cdate);

$fact_tmp_y = $arr[0];//get the year

$fact_tmp_m = $arr[1];//get the month

$fact_tmp_start = "01-".$fact_tmp_m."-".$fact_tmp_y;// this is the 01+MM-YYYY for start time

$fact_tmp_eind = "30-".$fact_tmp_m."-".$fact_tmp_y;// this is the 30-MM-YYYY for end timeglobal
//if you echo this result $fact_tmp_start and the result was DD-MM-YYYY that means that in your DB the date format is YYYY-MM-DD and you should convert it for your DB like i did in the first code
$cdate = $values["behandel_datum"];//get the date value

$arr = explode("-",$cdate);
$fact_tmp_y = $arr[0];//get the year

$fact_tmp_m = $arr[1];//get the month
$fact_tmp_start = $fact_tmp_y."-".$fact_tmp_m."-01";// this is the YYYY-MM-01 for start time

$fact_tmp_eind = $fact_tmp_y."-".$fact_tmp_m."-30";// this is the YYYY-MM-30 for end time
global $conn;
$strSQLInsert = "insert into Facturen (fact_client_id, fact_code_id, fact_start, fact_eind) values ('".$values["behandel_client_id"]."','".$fact_tmp2."','".$fact_tmp_start."','".$fact_tmp_eind."')";

db_exec($strSQLInsert,$conn);

// don't forget to use single quotes around the values..
S
swinma author 10/9/2011



Single quotes around the two date values are missing:

$strSQLInsert = "insert into Facturen (fact_client_id, fact_code_id, fact_start, fact_eind) values (".$values["behandel_client_id"].", ".$fact_tmp2.", '".$fact_tmp_start."', '".$fact_tmp_eind."')";
db_exec($strSQLInsert,$conn);



Thanks for the reply <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=61342&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' /> .

Added the quotes, but still no effect on the result.

The both date fiels are still 0-0-0000.

fact_start and fact_eind from the table facturen are both of type shortdate with Dropdown boxes with datepicker.

Should i choose another one?

S
stiven 10/10/2011

try this one.... don't change the date format it looks like the date format on the DB is yyyy-mm-dd
$cdate = $values["behandel_datum"];//get the date value

$arr = explode("-",$cdate);
$fact_tmp_y = $arr[0];//get the year

$fact_tmp_m = $arr[1];//get the month
$fact_tmp_start = $fact_tmp_y."-".$fact_tmp_m."-01";// this is the YYYY-MM-01 for start time

$fact_tmp_eind = $fact_tmp_y."-".$fact_tmp_m."-30";// this is the YYYY-MM-30 for end time
$strSQLInsert = "insert into Facturen (fact_client_id, fact_code_id, fact_start, fact_eind) values ('".$values["behandel_client_id"]."','".$fact_tmp2."','".$fact_tmp_start."','".$fact_tmp_eind."')";

db_exec($strSQLInsert,$conn);

S
swinma author 10/10/2011

Many thanks Stivens and Cristian, this time it works <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=61366&image=1&table=forumreplies' class='bbc_emoticon' alt=':D' />

I really appreciate the support.

I'm trying to understand why and when all these single and double quotes are being used.

In the help files and on the net much is being explained, but not to this extend.

Thanks!