Monday, July 9, 2012

MySQL COUNT,MAX,MIN,AVG & SUM Function

MySQL COUNT,MAX,MIN,AVG & SUM Functions

We have discussed some of the mathematical functions in previous tutorials but above functions are very common in MySQL.Here also I have taken progress table.Codes for above functions are in left column and results are in right column.
COUNT

<?php
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_database", $con);

$result = mysql_query("SELECT COUNT(*) AS total FROM progress ");
echo "<table border='1'>
<tr>
<th>Month</th>
<th>Nuber of Products</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
$num_rows = $row['total'];

  echo "<tr>";
  echo "<td>" . $row['Month'] . "</td>";
  echo "<td>" . $row['Production'] . "</td>";
echo "</tr>";

  }
echo "</table>";
echo $num_rows."rows found";

mysql_close($con);
?>
Month Nuber of Products


12rows found
MAX

<?php
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_database", $con);

$result = mysql_query("SELECT MAX(Production) FROM progress ");


while($row = mysql_fetch_array($result))
  {


echo "maximum production is:-".$row['MAX(Production)'];

  }
echo "</table>";



mysql_close($con);
?> 		maximum production is:-32
MIN


<?php
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_database", $con);

$result = mysql_query("SELECT MIN(Production) FROM progress ");


while($row = mysql_fetch_array($result))
  {


echo "minimum production is:-".$row['MIN(Production)'];

  }
echo "</table>";



mysql_close($con);
?> 		minimum production is:-10
AVG


<?php
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_database", $con);

$result = mysql_query("SELECT AVG(Production) FROM progress ");


while($row = mysql_fetch_array($result))
  {


echo "Average production is:-".$row['AVG(Production)'];

  }
echo "</table>";



mysql_close($con);
?> 		Average production is:-21
SUM


<?php
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_database", $con);

$result = mysql_query("SELECT SUM(Production) FROM progress ");


while($row = mysql_fetch_array($result))
  {


echo "Sum of production is:-".$row['SUM(Production)'];

  }
echo "</table>";



mysql_close($con);
?> 		Sum of production is:-252
Posted by at No comments:

MySQL Total Number of rows

MySQL Total Number of rows

This tutorial, I will show you how to display total number of rows in table and rows found due to the MySQL query you executed.I have used the same table progress use in a previous tutorial.
create the php file and find the result as bellow.

<?php
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_database", $con);
$q=mysql_query("SELECT *FROM progress");
$result = mysql_query("SELECT *FROM progress WHERE Month IN ( 'January', 'February', 'March' )");
$rows = mysql_num_rows($q);

echo "<table border='1'>
<tr>
<th>Month</th>
<th>Nuber of Products</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['Month'] . "</td>";
  echo "<td>" . $row['Production'] . "</td>";
echo "</tr>";

  }
echo "</table>";
echo "There are ".mysql_num_rows($result)." Result(s) found.";
echo "Total of " . $rows . " rows in  table.";
mysql_close($con);
?>
Month Nuber of Products
January10
February20
March12
There are 3 Result(s) found.Total of 12 rows in table.
Posted by at No comments:

MySQL ORDER BY MONTH

MySQL ORDER BY MONTH


Month Names in year are not in alphabetically ordered.So you will be faced problem of ordering month name.But when you insert a number instead of month name in to MySQL table  could resolve the problem.ex:- January=1,February=2, March=3 etc.Create following table called progress1

CREATE TABLE IF NOT EXISTS `progress1` (
  `id` int(30) NOT NULL AUTO_INCREMENT,
  `Month` varchar(60) NOT NULL,
  `Production` varchar(60) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=25 ;

--
-- Dumping data for table `progress1`
--

INSERT INTO `progress1` (`id`, `Month`, `Production`) VALUES
(13, '1', '20'),
(14, '2', '21'),
(15, '3', '45'),
(16, '4', '54'),
(17, '5', '56'),
(18, '6', '67'),
(19, '7', '34'),
(20, '8', '98'),
(21, '9', '12'),
(22, '10', '33'),
(23, '11', '45'),
(24, '12', '53');


Then create following php file and ypu will get the results in month name in order

<?php
echo "<center><table bgcolor=#0b2d04>
<tr bgcolor=#327423>
<th><font color=#ffffff>Month</font></th>
<th><font color=#ffffff>Number of Products</font></th>
</tr>";
$username="root";
$password="";
$database="my_database";
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query ="(SELECT * FROM progress1 ORDER BY ABS('Month') ASC)";
$result = mysql_query($query);
mysql_close();
while ($row = mysql_fetch_assoc($result)){
$month=$row['Month'];
if ($month == "1") {
$month_name = "January";
} else if ($month == "2"){
$month_name = "February";
} else if ($month == "3"){
$month_name = "March";
} else if ($month == "4"){
$month_name = "April";
}else if ($month == "5"){
$month_name = "May";
} else if ($month == "6"){
$month_name = "June";
} else if ($month == "7"){
$month_name = "July";
}else if ($month == "8"){
$month_name = "August";
} else if ($month == "9"){
$month_name = "September";
} else if ($month == "10"){
$month_name = "October";
}else if ($month == "11"){
$month_name = "November";
} else if ($month == "12"){
$month_name = "December";
}
echo "<tr  bgcolor=#d7eecb>

<td>" .$month_name."</td>

<td>" .$row['Production']."</td>
</tr>";
}
echo "</table></center>";
?>
Month Number of Products
January 20
February 21
March 45
April 54
May 56
June 67
July 34
August 98
September 12
October 33
November 45
December 53
Posted by at No comments:

Sunday, July 8, 2012

MySQL IN Clause

MySQL IN Clause


When you need to have quarterly production in a year you have to break down the months into block like (January, February, March),(April,May,June) etc.Also you need to retrieve these block of data from the database. For this tutorial,I have created following table called "progress" in my-database.

CREATE TABLE IF NOT EXISTS `progress` (
  `id` int(30) NOT NULL AUTO_INCREMENT,
  `Month` varchar(60) NOT NULL,
  `Production` varchar(60) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;

--
-- Dumping data for table `progress`
--

INSERT INTO `progress` (`id`, `Month`, `Production`) VALUES
(1, 'January', '10'),
(2, 'February', '20'),
(3, 'March', '12'),
(4, 'April', '21'),
(5, 'May', '23'),
(6, 'June', '21'),
(7, 'July', '23'),
(8, 'August', '27'),
(9, 'September', '32'),
(10, 'October', '14'),
(11, 'November', '25'),
(12, 'December', '24');

Create the following php file to display the data for first quarter(January, February, march)


<?php
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("my_database", $con);

$result = mysql_query("SELECT * FROM progress WHERE Month IN ( 'January', 'February', 'March' )");

echo "<table border='1'>
<tr>
<th>Month</th>
<th>Nuber of Products</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['Month'] . "</td>";
  echo "<td>" . $row['Production'] . "</td>";
echo "</tr>";
  }
echo "</table>";

mysql_close($con);
?>
Month Nuber of Products
January10
February20
March12
Posted by at No comments:

Get mysql table colum information php

Get mysql table colum information php  

 You coud see the MySQL colomn information by using following information

Codes
<?php
$conn = mysql_connect('localhost', 'root', '');
if (!$conn) {
    die('Could not connect: ' . mysql_error());
}
mysql_select_db('my_database');
$result = mysql_query('select * from namelist');
if (!$result) {
    die('Query failed: ' . mysql_error());
}
/* get column metadata */
$i = 0;
while ($i < mysql_num_fields($result)) {
    echo "Information for column $i:<br />\n";
    $meta = mysql_fetch_field($result, $i);
    if (!$meta) {
        echo "No information available<br />\n";
    }
    echo "<pre>
blob:         $meta->blob
max_length:   $meta->max_length
multiple_key: $meta->multiple_key
name:         $meta->name
not_null:     $meta->not_null
numeric:      $meta->numeric
primary_key:  $meta->primary_key
table:        $meta->table
type:         $meta->type
unique_key:   $meta->unique_key
unsigned:     $meta->unsigned
zerofill:     $meta->zerofill
</pre>";
    $i++;
}
mysql_free_result($result);
?>
Results
Information for column 0:
blob:         0
max_length:   2
multiple_key: 0
name:         id
not_null:     1
numeric:      1
primary_key:  1
table:        namelist
type:         int
unique_key:   0
unsigned:     0
zerofill:     0
Information for column 1:
blob:         0
max_length:   10
multiple_key: 0
name:         FirstName
not_null:     0
numeric:      0
primary_key:  0
table:        namelist
type:         string
unique_key:   0
unsigned:     0
zerofill:     0
Information for column 2:
blob:         0
max_length:   9
multiple_key: 0
name:         LastName
not_null:     0
numeric:      0
primary_key:  0
table:        namelist
type:         string
unique_key:   0
unsigned:     0
zerofill:     0
Posted by at 1 comment:

E-Mail Extractor php

E-Mail Extractor php

This post,I am going to extract email addresses in a web page and display then in a php file.I have taken a web page which have number of email addresses and saved it in my root directory.

Then created following php file to display the e mail addresses.

<?php
function extract_emails_from($string){
  preg_match_all("/[\._a-zA-Z0-9-]+@[\._a-zA-Z0-9-]+/i", $string, $matches);
  return $matches[0];
}
$url =file_get_contents("index.html");
$emails = extract_emails_from($url);
print(implode($emails,"</br>"));
?>		abera@soka.edu
abera@soka.edu
mahn@soka.edu
mahn@soka.edu
rallinson@soka.edu
rallinson@soka.edu
asawa@soka.edu
asawa@soka.edu
sbarter@soka.edu
sbarter@soka.edu
ebayazian@soka.edu
ebayazian@soka.edu
mbennink@soka.edu
mbennink@soka.edu
hbhandari@soka.edu
hbhandari@soka.edu
rbhatt@soka.edu
rbhatt@soka.edu
Posted by at 1 comment:

Saturday, July 7, 2012

My sql affected rows

Mysql affected rows


when you update,delete mysql records number of rows could be affected,but you do not know how many rows deleted or updated.By using mysql_affected_rows() function you could be noticed how many rows affected with your query.
For this post,I used namelist table to delete 9 records by using the row id.

<?php
$link = mysql_connect('localhost', 'root', '');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
mysql_select_db('my_database');


mysql_query('DELETE FROM namelist WHERE id < 10');
printf("Records deleted: %d\n", mysql_affected_rows());


?>
Result
Records deleted: 9
Posted by at No comments:
Subscribe to: Posts (Atom)