2. PHPRunner General questions
  3. syntax for mysql querys
This topic is locked

syntax for mysql querys

 		2/9/2008 3:02:38 PM
PHPRunner General questions
Allenh author

OK this is going to be a complicated question: - I could do it php so it must be easy in php runner I just don't know the right sintaxt.
I am making a data base where there is a number of employees in different departments. Each department has a number of things they need to do at regular intervals(get there site tested, take training)
What I need to is every time a new employee is added I need to look up what different things someone in thier department may need to do and add those as rows to another table.
This is really long and does not work but should give you an idea of what I am trying to do
global $conn;

$query = "SELECT * FROM `mng_cal_items` WHERE `department`='".$values["depart"]."'";

echo $query;

$result=mysql_query($query);
while($record=mysql_fetch_assoc($result)){

foreach($record as $key=>$file){

if ($key="category"){

$category=$file

}

else if ($key="item"){

$item=$file

}

else if ($key="interval_months"){

$interval_months=$file

}

}

global $conn,$strTableName;

$strSQLSave = "INSERT INTO `airhypen_cats`.user_cal_items

(`user_access_id`, `status`, `department`,

`employee_no`, `username_lu`, `category_lu`,

`item_lu`, `interval_months_lu` )

SELECT `id`, `status`, `depart`,

`employee_no`, `user_name`, `".$category."`,

`".$item."`, `".$interval."`

FROM ".$strTableName." WHERE `id`=".$keys["id"]."";

db_exec($strSQLSave,$conn);

}
return true;
J
 		Jane 2/11/2008

Hi,
it's difficult to tell you what's happening without seeing actual files.
Try to use this code:

...

$strSQLSave = "INSERT INTO `airhypen_cats`.user_cal_items (`user_access_id`, `status`, `department`,`employee_no`, `username_lu`, `category_lu`,`item_lu`, `interval_months_lu` ) values (".$keys["id"].",'".$values["status"]."','".$values["depart"]."','".$values["employee_no"]."','".$values["user_name"]."','".$category."','".$item."','".$interval."')";
db_exec($strSQLSave,$conn);

...
Allenh author 2/11/2008

actually that part was working (I think I cant test it until I get this working)

'I can't even get the

mysql_query($query); to work

or

mysql_fetch_assoc($result)

or

foreach()
global $conn;

$query = "SELECT * FROM `mng_cal_items` WHERE `department`='".$values["depart"]."'";
$result=mysql_query($query);
while($record=mysql_fetch_assoc($result)){

foreach($record as $key=>$file){

}

}
thanks
J
 		Jane 2/12/2008

Hi,
you can publish your project on Demo Account and send to support@xlinesoft.com a URL to your pages.

I'll find what's wrong with your project inspecting it at Demo account site.
Allenh author 2/12/2008

I figured out how to do it. Here is the code:
global $conn;

//looking for all the calendar events items for the department

$query = "SELECT `category`, `item`, `interval_months` FROM `mng_cal_items` WHERE `department`='".$values["depart"]."'";

$result=mysql_query($query);

while($record=mysql_fetch_assoc($result)){//I need to add a record for every calendar event

global $conn,$strTableName;

$strSQLSave = "INSERT INTO `airhypen_cats`.`user_cal_items` (

`id` ,`user_access_id` ,

`status` ,`employee_no` ,

`department` ,`username_lu` ,

`category_lu` ,`item_lu` ,

`date_start` ,`interval_months_lu` ,

`date_due_cal`)

VALUES (

NULL , ".$keys["id"].",

".$values["status"].", ".$values["employee_no"].",

'".$values["depart"]."', '".$values["user_name"]."',

'".$record["category"]."', '".$record["item"]."',

'0000-00-00', '".$record["interval_months"]."',

'0000-00-00'

);";

db_exec($strSQLSave,$conn);
} end of the while statement