Jump to content

Archived

This topic is now archived and is closed to further replies.

vicky57t

help regarding PHP & My SQL

Recommended Posts

[!--coloro:#FF6666--][span style=\"color:#FF6666\"][!--/coloro--]I have the following code :[!--colorc--][/span][!--/colorc--]

-----------------------------------------------------------------------------------------------------------------------
<html>
<?php

//////// Link up with back end

$link = mysql_connect('10.34.36.18:3306', 'gforge', 'gforge123');
if (!$link)
{
die('Could not connect: ' . mysql_error());
}
//////// Selecting database

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


/////////// Query 0 - getting DB table (DT) entries

$query0 = mysql_query('select count,year,year_order from DT',$link);

if (!$query0)
{
die('Invalid query: ' . mysql_error());
}

if (mysql_num_rows($query0) == 0)

{
echo "No rows found, nothing to print so am exiting";
exit;
}

while ($row = mysql_fetch_assoc($query0))

{
echo "Table Entries - ";
echo $row["count"];
echo $row["year"];
echo $row["year_order"];
}

?>

<br>

<?php
/////////// Query 1 - getting Max year from DB table (DT) entries


$query1 = mysql_query('SELECT MAX(year) FROM DT',$link);

if (!$query1)
{
die('Invalid query: ' . mysql_error());
}

$max_year_db=mysql_result($query1,0);

echo "Max year - $max_year_db";

?>

<br>

<?php

////////// Query 2 - getting Max count corresponding to Max year from Query 1

$sql="SELECT MAX(count) FROM DT WHERE Year='".$max_year_db."'";

$query2 = mysql_query($sql,$link);

if (!$query2)
{
die('Invalid query: ' . mysql_error());
}

$max_count=mysql_result($query2,0);

echo "Max Counter corresponding to max year - $max_count";

?>

<br>

<?php

//////// Query 3 - Selecting current Date from System

$query3 = mysql_query('SELECT CURRENT_DATE',$link);

if (!$query3)
{
die('Invalid query: ' . mysql_error());
}

$current_date=mysql_result($query3,0);

echo "Current date - $current_date";

?>

<br>

<?php
////// Query 4 - Getting Current year from System date

$sql="SELECT EXTRACT(YEAR FROM ('$current_date'))";

$query4 = mysql_query($sql,$link);

if (!$query4)
{
die('Invalid query: ' . mysql_error());
}

$current_year=mysql_result($query4,0);

echo "Current year - $current_year";


?>

<br>

<?php

////////// Check for Max year

if (($max_year_db)<($current_year))
{
echo ("$max_year_db<$current_year");

$query5 = mysql_query("insert into DT values (1,'".$current_year."','".$current_year."-1')",$link);

die("Opt 1 executed");

}

else

{
echo ("$max_year_db>=$current_year");

$max_count = ($max_count)+1;

echo ("Count is: $max_count") ;

$query6 = mysql_query("insert into DT values '".$max_count."','".$max_year_db."','".$max_year_db."-".$max_count."')",$link);

die ("Opt 2 executed");

}

?>

</html>

--------------------------------------------------------------------------------------------------------------------

[!--coloro:#FF6666--][span style=\"color:#FF6666\"][!--/coloro--]Before executing this file the table in operation(DT) has following entries :[!--colorc--][/span][!--/colorc--]

[!--coloro:#9999FF--][span style=\"color:#9999FF\"][!--/coloro--] Count Year Year_Order
-------- ------- -------------
1 2005 2005-1 [!--colorc--][/span][!--/colorc--]

[!--coloro:#FF6666--][span style=\"color:#FF6666\"][!--/coloro--]when I run the above PHP code I get the output as :[!--colorc--][/span][!--/colorc--]

[!--coloro:#9999FF--][span style=\"color:#9999FF\"][!--/coloro--]Table Entries - 120062006-1Table Entries - 120052005-1
Max year - 2006
Max Counter corresponding to max year - 1
Current date - 2006-04-13
Current year - 2006
2006>=2006Count is: 2Opt 2 executed[!--colorc--][/span][!--/colorc--]

[!--coloro:#FF6666--][span style=\"color:#FF6666\"][!--/coloro--]
The table output is:[!--colorc--][/span][!--/colorc--]

[!--coloro:#9999FF--][span style=\"color:#9999FF\"][!--/coloro--]Count Year Year_Order
-------- ------- -------------
2 2006 2006-2
1 2006 2006-1
1 2005 2005-1 [!--colorc--][/span][!--/colorc--]

[!--coloro:#FF6666--][span style=\"color:#FF6666\"][!--/coloro--]i.e. two entries are made to the table,in addition to existing one, whereas only one should be done

I want the output to be :[!--colorc--][/span][!--/colorc--]


[!--coloro:#9999FF--][span style=\"color:#9999FF\"][!--/coloro--] Count Year Year_Order
-------- ------- -------------
1 2006 2006-1
1 2005 2005-1 [!--colorc--][/span][!--/colorc--]

[!--coloro:#FF6666--][span style=\"color:#FF6666\"][!--/coloro--]and on next execution it should be :[!--colorc--][/span][!--/colorc--]

[!--coloro:#9999FF--][span style=\"color:#9999FF\"][!--/coloro--] Count Year Year_Order
-------- ------- -------------
2 2006 2006-2
1 2006 2006-1
1 2005 2005-1 [!--colorc--][/span][!--/colorc--]

[!--coloro:#FF6666--][span style=\"color:#FF6666\"][!--/coloro--]The DT table has thre fileds namely Count, Year, Year_Order as int,int and varchar(25) reapectively

Can somebody help me out with this problem. Thanks[!--colorc--][/span][!--/colorc--]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.