Easy way to select numerical data from MySQL table

I’ll explain this using a example,


Lets thing we have table called EMP and column called SALARY.
Now suppose there are numerical data and string data in the SALARY column. (ex: 12000, 25000, not available, null…)
For this kind of columns we can use following method to select only numerical data;


SELECT SALARY FROM EMP WHERE (SALARY+0)!=0;

Simple PHP,AJAX application for get updates without doing any action at the client side using prototype JavaScript framework

This sample program contains 5 php files as follows;

1) config.php – This file use for manage the database access configuration.
2) add_name.php – This file use for send data to insert_name.php using AJAX.
3) insert_name.php – This file use for insert new record (which are receive from the add_name.php file) to the database.
4) view_names.php – This file use for view the database and database changes.
5) select_names.php – This file use for get all database data and print.

I have user a javascript framework called “PROTOTYPE” to develop this example (don’t worry, its not gona be a hard job for you). It is a single javascript file. So you have to download the latest version of it from http://prototypejs.org/ (the file called prototype.js)

config.php is required for manage your database connection information easily.
config.php
————-
<?php
// change the parameters to connect to your DB
// CHANGE ONLY $db_host, $db_name, $username, $password
$db_host=”localhost”;
$db_name=”test”;
$username=”root”;
$password=”password”;

$db_con=mysql_connect($db_host,$username,$password);
$connection_string=mysql_select_db($db_name);
mysql_connect($db_host,$username,$password);
mysql_select_db($db_name);
?>

add_name.php is provide a text field for enter the data (eg: names) into the database. By clicking the insert button the text form data send to the insert_name.php through ajax functions of prototype javascript framework.
add_name.php

——————









function insertName(){
new Ajax.Request(‘insert.php’, {
parameters: $(‘name’).serialize(true),
});
}





insert_name.php is insert post data to the database.
insert_name.php
——————–
<?php
if(isset($_POST[‘name’])){
// Connection to Database
include(‘config.php’);
// Remove HTML tag to prevent query injection
$name = strip_tags($_POST[‘name’]);

$sql = ‘INSERT INTO USER (name) VALUES(
“‘.$name.'”)’;
mysql_query($sql);
echo $name;
} else { echo ‘0’; }
?>

view_names.php is print the data of the database. It call the select_names.php file and get the data.
view_names.php
——————–

DB names

new Ajax.PeriodicalUpdater(‘names’, ‘select_names.php’,
{
method: ‘get’,
insertion: Element.update,
frequency: 1,
decay: 1
});

selest_names.php is get the data from the database and print.
select_names.php
———————
“;
while ($row = mysql_fetch_array($result)){
echo “”.$row[0].””;
}
echo “”;

?>


To get the the first N records in a SQL query differs acording to database platforms.

Here’s some samples:

Microsoft SQL Server

SELECT TOP 10 column FROM table

PostgreSQL and MySQL

SELECT column FROM table
LIMIT 10

Oracle

SELECT column FROM table
WHERE ROWNUM <= 10

Sybase

SET rowcount 10
SELECT column FROM table

Firebird

SELECT FIRST 10 column 
FROM table
%d bloggers like this: