I am creating a Insert Query using PHP.. By fetching the data from a Audit table and iterating the values of it in loops.. table from which I am fetching the value has the snapshot below:
The Code I am using
to create is given below:
mysql_select_db('information_schema');
$select = mysql_query("SELECT TABLE_NAME FROM TABLES WHERE TABLE_SCHEMA = 'pranav_test'");
$selectclumn = mysql_query("SELECT * FROM COLUMNS WHERE TABLE_SCHEMA = 'pranav_test'");
mysql_select_db('pranav_test');
$seletaudit = mysql_query("SELECT * FROM jos_audittrail WHERE live = 0");
$tables = array();
$i = 0;
while($row = mysql_fetch_array($select)) {
$tables[$i++] =$row['TABLE_NAME'];
}
while($row2 = mysql_fetch_array($seletaudit))
{
$audit[] =$row2;
}
foreach($audit as $val)
{
if($val['operation'] == "INSERT")
{
if(in_array($val['table_name'],$tables))
{
$insert = "INSERT INTO '".$val['table_name']."' (";
$selfld = mysql_query("SELECT field FROM jos_audittrail WHERE table_name = '".$val['table_name']."' AND operation = 'INSERT' AND trackid = '".$val['trackid']."'");
while($row3 = mysql_fetch_array($selfld))
{
$values[] = $row3;
}
foreach($values as $field)
{
$insert .= "'".$field['field']."', ";
}
$insert .= "]";
$insert = str_replace(", ]",")",$insert);
$insert .= " values (";
$selval = mysql_query("SELECT newvalue FROM jos_audittrail WHERE table_name = '".$val['table_name']."' AND operation = 'INSERT' AND trackid = '".$val['trackid']."' AND live = 0");
while($row4 = mysql_fetch_array($selval))
{
$value[] = $row4;
}
/*echo "<pre>";
print_r($value);exit;*/
foreach($value as $data)
{
$insert .= "'".$data['newvalue']."', ";
}
$insert .= "[";
$insert = str_replace(", [",")",$insert);
}
}
}
When I Echo the $insert out of the most outer for loop (for auditrail) The values get printed as many times as the records are found for the outer for loop..i.e 'orderby= show_noauth= show_title= link_titles= show_intro= show_section= link_section= show_category= link_category= show_author= show_create_date= show_modify_date= show_item_navigation= show_readmore= show_vote= show_icons= show_pdf_icon= show_print_icon= show_email_icon= show_hits= feed_summary= page_title= show_page_title=1 pageclass_sfx= menu_image=-1 secure=0 ', '0000-00-00 00:00:00', '13', '20', '1', '152', 'accmenu', 'IPL', 'ipl', 'index.php?option=com_content&view=archive', 'component'
gets repeated ,
i.e.
INSERT INTO 'jos_menu' ('params', 'checked_out_time', 'ordering', 'componentid', 'published', 'id', 'menutype', 'name', 'alias', 'link', 'type', 'params', 'checked_out_time', 'ordering', 'componentid', 'published', 'id', 'menutype', 'name', 'alias', 'link', 'type', 'params', 'checked_out_time', 'ordering', 'componentid', 'published', 'id', 'menutype', 'name', 'alias', 'link', 'type', 'params', 'checked_out_time', 'ordering', 'componentid', 'published', 'id', 'menutype', 'name', 'alias', 'link', 'type', 'params', 'checked_out_time', 'ordering', 'componentid', 'published', 'id', 'menutype', 'name', 'alias', 'link', 'type', 'params', 'checked_out_time', 'ordering', 'componentid', 'published', 'id', 'menutype', 'name', 'alias', 'link', 'type', 'params', 'checked_out_time', 'ordering', 'componentid', 'published', 'id', 'menutype', 'name', 'alias', 'link', 'type', 'params', 'checked_out_time', 'ordering', 'componentid', 'published', 'id', 'menutype', 'name', 'alias', 'link', 'type', 'params', 'checked_out_time', 'ordering', 'componentid', 'published', 'id', 'menutype', 'name', 'alias', 'link', 'type', 'params', 'checked_out_time', 'ordering', 'componentid', 'published', 'id', 'menutype', 'name', 'alias', 'link', 'type', 'params', 'checked_out_time', 'ordering', 'componentid', 'published', 'id', 'menutype', 'name', 'alias', 'link', 'type') values ('orderby=
show_noauth=
show_title=
link_titles=
show_intro=
show_section=
link_section=
show_category=
link_category=
show_author=
show_create_date=
show_modify_date=
show_item_navigation=
show_readmore=
show_vote=
show_icons=
show_pdf_icon=
show_print_icon=
show_email_icon=
show_hits=
feed_summary=
page_title=
show_page_title=1
pageclass_sfx=
menu_image=-1
secure=0
', '0000-00-00 00:00:00', '13', '20', '1', '152', 'accmenu', 'IPL', 'ipl', 'index.php?option=com_content&view=archive', 'component', 'orderby=
show_noauth=
show_title=
link_titles=
show_intro=
show_section=
link_section=
show_category=
link_category=
show_author=
show_create_date=
show_modify_date=
show_item_navigation=
show_readmore=
show_vote=
show_icons=
show_pdf_icon=
show_print_icon=
show_email_icon=
show_hits=
feed_summary=
page_title=
show_page_title=1
pageclass_sfx=
menu_image=-1
secure=0
', '0000-00-00 00:00:00', '13', '20', '1', '152', 'accmenu', 'IPL', 'ipl', 'index.php?option=com_content&view=archive', 'component', 'orderby=
show_noauth=
..
..
..
.. and so on
What I want is I should get these Values for once, I know there is mistake using the outer Forloop, but I m not getting the idea of rectifying it.. Please help... please poke me for more clarification...
