TIME NOW
World current time now,
CALENDAR
Calendar monthly, yearly
login GOLD PRICE, RATE
login HOROSCOPE
login DICTIONARIES, LISTS
login SCIENCE EDUCATION RELIGION
login FINANCE, BUSINESS
login WORLD COUNTRIES, WIKI

PHP scripts, code, examples, tutorial

Previous articlePage bottomNext article  ALL TOPICS

PHP. Mysql examples: 1-M operations

 
<?php

error_reporting(E_ERROR | E_WARNING | E_PARSE);

$db = "test";


$table1 = "test1";
$table2 = "test2";

$link = mysql_connect('localhost', 'root', '');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';


$db_selected = mysql_select_db($db, $link);
if (!$db_selected) {
die ('Can\'t use foo : ' . mysql_error());
}

$sql = "CREATE TABLE IF NOT EXISTS `$table1` (
`id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
`data` VARCHAR(100),
`created` TIMESTAMP DEFAULT NOW()
);";

$result = mysql_query($sql) or die (mysql_error());

$sql = "CREATE TABLE IF NOT EXISTS `$table2` (
`id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
`data` VARCHAR(100),
`created` TIMESTAMP DEFAULT NOW(),
`id_parent` INT
);";

$result = mysql_query($sql) or die (mysql_error());


menu();

echo "<pre>";

if ($_GET['op'] == 1){

select();

} elseif ($_GET['op'] == 2){ // op1

insert ();     

}elseif ($_GET['op'] == 3){

delete ();


}elseif ($_GET['op'] == 4){

deleteChild ();

}elseif ($_GET['op'] == 5){

deleteChilds ();

}elseif ($_GET['op'] == 6){

insertChilds ();

}elseif ($_GET['op'] == 7){

insertParentAndChilds ();

}elseif ($_GET['op'] == 8){

select();

}elseif ($_GET['op'] == 10){

updateParent();

}else{

stats ();

}



mysql_close($link);

// #################################################
function formDelete(){

global $table1, $link, $db;

$sql = "SELECT * FROM $table1";
$result = mysql_query($sql) or die (mysql_error());


while ($row = mysql_fetch_assoc($result)) {
//print_r($row);

$content .=  "<Option value = \"{$row['id']}\">{$row['id']} - {$row['data']}</Option>\n";


} // while

$content = "&nbsp;<BR>\n 4 Delete child of parent
<FORM action = {$_SERVER['PHP_SELF']} >
<Select name = id_parent >
$content
</Select>
<Input type = hidden name = op value = 5>
<Input type = submit value = OK>
</FORM>
";

echo $content; 

}
// #################################################

function formInsert(){

global $table1, $link, $db;

$sql = "SELECT * FROM $table1";
$result = mysql_query($sql) or die (mysql_error());


while ($row = mysql_fetch_assoc($result)) {
//print_r($row);

$content .=  "<Option value = \"{$row['id']}\">{$row['id']} - {$row['data']}</Option>\n";


} // while

$content = "&nbsp;<BR>\n 7 Insert parent and childs
<FORM action = {$_SERVER['PHP_SELF']} >
Data <Select name = id_parent >$content</Select><Input type = submit value = OK><BR><BR>Childs:<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<Input type = text name = dataChild[] ><BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<Input type = text name = dataChild[] ><BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<Input type = text name = dataChild[] ><BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<Input type = text name = dataChild[] ><BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<Input type = text name = dataChild[] ><BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<Input type = text name = dataChild[] ><BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<Input type = text name = dataChild[] ><BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<Input type = text name = dataChild[] ><BR>
<Input type = hidden name = op value = 6>

</FORM>
";

echo $content; 

}

function formInsertChild(){

global $table1, $link, $db;

$content = "&nbsp;<BR>\n 6 Insert parent and childs
<FORM action = {$_SERVER['PHP_SELF']} >
Data <Input type = text name = data><Input type = submit value = OK><BR><BR>Childs:<BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<Input type = text name = dataChild[] ><BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<Input type = text name = dataChild[] ><BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<Input type = text name = dataChild[] ><BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<Input type = text name = dataChild[] ><BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<Input type = text name = dataChild[] ><BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<Input type = text name = dataChild[] ><BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<Input type = text name = dataChild[] ><BR>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<Input type = text name = dataChild[] ><BR>
<Input type = hidden name = op value = 7>

</FORM>
";

echo $content; 

}

// #################################################

function delete(){

global $table1, $table2, $link, $_GET, $_POST;  

if ($_GET['id']) { $like = $_GET['id'];} else {$like = "%";}

echo " trynimas\n" ;
$sql = "DELETE FROM $table1 where `id` like '$like'";
$result = mysql_query($sql);
echo  "$sql = " . mysql_affected_rows ($link) . "<BR>\n";

$sql = "DELETE FROM $table2 where `id_parent` like '$like'";
$result = mysql_query($sql);
echo  "$sql = " . mysql_affected_rows ($link) . "<BR>\n";

}

// #################################################

function deleteChild(){

global $table2, $link, $_GET, $_POST;  

if ($_GET['id']) { $like = $_GET['id'];} else {$like = "%";}

echo " trynimas\n" ;
$sql = "DELETE FROM $table2 where `id` like '$like'";
$result = mysql_query($sql);
echo  "$sql = " . mysql_affected_rows ($link) . "<BR>\n";

}
// #################################################

function deleteChilds(){

global $table2, $link, $_GET, $_POST;  

if ($_GET['id_parent']) { $like = $_GET['id_parent'];} else {$like = "%";}

echo " trynimas\n" ;
$sql = "DELETE FROM $table2 where `id_parent` like '$like'";
$result = mysql_query($sql);
echo  "$sql = " . mysql_affected_rows ($link) . "<BR>\n";

}
// #################################################
function stats (){

global $table1, $table2, $link, $db; 

echo " statistika\n" ;

$sql = "SHOW TABLES FROM $db";
$result = mysql_query($sql);

while ($row = mysql_fetch_row($result)) {
//print_r ($row);
echo "Table: {$row[0]}\n<BR>";
}

echo "<HR>TABLE 1 \n";
$sql = "SHOW COLUMNS FROM $table1";
$result = mysql_query($sql);

while ($row = mysql_fetch_assoc($result)) {
print_r($row);  echo "<br>" ;
}

echo "<HR>TABLE 2 \n";
$sql = "SHOW COLUMNS FROM $table2";
$result = mysql_query($sql);

while ($row = mysql_fetch_assoc($result)) {
print_r($row);  echo "<br>" ;
}

}

// #################################################

function insert(){

global $table1, $table2, $link, $db;
echo "iterpimas\n";

for ($i = 1; $i < 5 ; $i ++){

$rand = md5 (rand (1, 999));
$sql = "INSERT INTO `$table1` SET `data` = '$rand' ";
echo "$sql\n";
$result = mysql_query($sql) or die (mysql_error());



$id = mysql_insert_id ();
for ($y = 1; $y < 5 ; $y ++){
$sql = "INSERT INTO `$table2` SET `data` = '$rand', `id_parent` = '$id' ";
echo "\t\t $sql \n"  ;
$result = mysql_query($sql) or die (mysql_error());
}

echo "END\n\n"  ;

}


}

// #################################################

function select(){

global $table1, $table2, $link, $db;
echo "atvaizdavimas<BR>\n";

$sql = "SELECT * FROM $table1";
$result = mysql_query($sql) or die (mysql_error());

while ($row = mysql_fetch_assoc($result)) {
//print_r($row);
if ($_GET['op'] ==8 and $_GET['id'] ==$row['id'] )   {

echo "<A name= \"parent{$row['id']}\"></A>
<form action = {$_SERVER['PHP_SELF']}>
<input name = op value = 10 type = hidden >
<input name = id value = \"{$_GET['id']}\" type = hidden >
<input  value = ok type = submit >&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<input name = data value =  \"{$row['data']}\" size= 50><input name = created value =\"{$row['created']}\" > <br>\n";


}else{
echo "<A name= \"parent{$row['id']}\"></A><a href=\"{$_SERVER['PHP_SELF']}?op=3&id={$row['id']}\">Delete</A> &nbsp;&nbsp;<a href=\"{$_SERVER['PHP_SELF']}?op=8&id={$row['id']}#parent{$row['id']}\">Update</A> {$row['id']}, {$row['data']}, {$row['created']} <br>\n";
}
$sql1 = "SELECT * FROM $table2 WHERE `id_parent` = '{$row['id']}'";
$result1 = mysql_query($sql1) or die (mysql_error());
while ($row1 = mysql_fetch_assoc($result1)) {

echo "\t\t<a href=\"{$_SERVER['PHP_SELF']}?op=4&id={$row1['id']}\">Delete</A>{$row1['id']}, {$row1['data']}, {$row1['created']}, {$row1['id_parent']}  <br>\n";

}

} // while
}

// #################################################

function menu(){

global  $_SERVER ;

?>
<a href="/<?php echo $_SERVER['PHP_SELF'] ; ?>?op=0"> 0 info   </A>&nbsp;&nbsp;
<a href="/<?php echo $_SERVER['PHP_SELF'] ; ?>?op=1"> 1 select </A> &nbsp;&nbsp;
<a href="/<?php echo $_SERVER['PHP_SELF'] ; ?>?op=2"> 2 insert </A> &nbsp;&nbsp;
<a href="/<?php echo $_SERVER['PHP_SELF'] ; ?>?op=3"> 3 delete All</A> &nbsp;&nbsp;

<a href="/<?php echo $_SERVER['PHP_SELF'] ; ?>?op=5"> 5 delete All childs</A> &nbsp;&nbsp;

<Table>
<Tr>
<Td valign = top>
</Td>
<Td valign =top>
<?php formDelete (); ?><?php formInsert() ;?>  <?php formInsertChild() ;?>
</Td>
</Tr>
</Table>
<?php

}

function insertParentAndChilds (){

global $table1, $table2, $link, $db, $_GET;
echo "iterpimas\n";

$sql = "INSERT INTO `$table1` SET `data` = '{$_GET['data']}' ";
echo "$sql\n";
$result = mysql_query($sql) or die (mysql_error());

$id = mysql_insert_id ();


foreach ($_GET ['dataChild'] as $v){

if (! trim ($v)) continue ;
$sql = "INSERT INTO `$table2` SET `data` = '$v', `id_parent` = '$id' ";
echo "\t\t $sql \n"  ;
$result = mysql_query($sql) or die (mysql_error());

}

echo "END\n\n"  ;


}

function insertChilds (){

global $table2, $link, $db, $_GET;
echo "iterpimas\n";


$id = $_GET['id_parent'];


foreach ($_GET ['dataChild'] as $v){

if (! trim ($v)) continue ;
$sql = "INSERT INTO `$table2` SET `data` = '$v', `id_parent` = '$id' ";
echo "\t\t $sql \n"  ;
$result = mysql_query($sql) or die (mysql_error());

}

echo "END\n\n"  ;


}


function updateParent(){

global $table1, $table2, $link, $_GET, $_POST;  
$rand = md5 (rand (1, 999));
$sql = "UPDATE `$table1` SET `data` = '{$_GET['data']}', `created` = '{$_GET['created']}' where id = '{$_GET['id']}' ";
echo "$sql\n";
$result = mysql_query($sql) or die (mysql_error());


}
// ###############################################3
?>
 
Previous articlePage topNext article  ALL TOPICS



 Use username: Guest, Anonymous, Programmer






QUOTES:
Let your life lightly dance on the edges of Time like dew on the tip of a leaf.
Rabindranath Tagore
Hidden talent counts for nothing.
Nero
Mankind, when left to themselves, are unfit for their own government.
George Washington