LDAP Data Model ERD

The Requirements have been determined by a brief review of the Wikipedia entry for LDAP (Lightweight Directory Access Protocol).

Continue Reading...

University Management System ERD

University Management System promotes a culture of paperless University. The day to day administration and most of the University procedures are carried out online. The University Management System (UMS), an online portal of University, is one such innovative step of the University to provide vital information regarding academics and other University logistics for assisting the University students, their parents and the staff.

Continue Reading...

Point of Sale ERD (POS)

Continue Reading...

School Management System ERD Diagram

The User Requirements have been defined as follows :-

A Generic School Management System

A Database is required to support an Online school system which should be so generic that any number of schools in an area, (eg Town/country/world) can register to use the System.

The Database should include Teachers,Student,Parents and so on.

• A Teacher could submit reports,datasheets of student with their remarks.
• Parents, (after entering their Password) could view the progress,dues,notices etc  of their children plus any query or discussion regarding their child etc..
• Student can view their progress,notices,dues,with their passwords.

This would be like a general plateform to provide School-Specific faciltiies.

Continue Reading...

MySQL Trim Function Example

In this tutorial we will have a look at the Trim function of MySQL. MySQL has a very strong library we must use it, usually we format input our data using some dynamic languages like PHP, but what if there is a scenario we have to use MySQL database for it.

let the code talk...

MySQL Trim Method Signature

// First Type
String TRIM([{BOTH | LEADING | TRAILING} [STRING_TO_BE_REMOVED] FROM] str)

// Second Type
String TRIM([STRING_TO_BE_REMOVED FROM] str)

Returns the string string with all STRING_TO_BE_REMOVED prefixes or suffixes removed. If none of the specifiers BOTH, LEADING, or TRAILING is given, BOTH is assumed. STRING_TO_BE_REMOVED is optional and, if not specified, spaces are removed.

lets understand it with examples.

Example

SELECT TRIM('  Tutorial   ') 
AS FormatedString;

Outputs: Tutorial

SELECT TRIM(LEADING 'jinni' FROM 'jinniTutorialjinni') 
AS FormatedString;

Outputs: Tutorialjinni

SELECT TRIM(BOTH 'jinni' FROM 'jinniTutorialjinni') 
AS FormatedString;

Outputs: Tutorial

SELECT TRIM(TRAILING 'jinni' FROM 'jinniTutorialjinni') 
AS FormatedString;

Outputs: jinniTutorial

Continue Reading...

SQL Injection & Prevention

In this tutorial we will take a look atSQL Injection, how to attack using sql injection and how we can prevent ourselves form it. Firstly we see what is it after all, SQL Injection is subset of the an unverified/unsanitized user input vulnerability ("buffer overflows" are a different subset), and the idea is to convince the application to run SQL code that was not intended. If the application is creating SQL strings naively on the fly and then running them, it's straightforward to create some real surprises. In this tutorial we will discuss only one and most common type to attack that is done usually on the login.

Typically the code we used for loging a user is some what similar to the following.

// ...
    $username=$_REQUEST["username"];
    $password=$_REQUEST["password"];

    $query="select * from user where username='$username' AND password='$password'";

    $result=mysql_query($query);
    $count=$mysql_num_rows($result);

    if($count==1){
        // after login process goes here
    }
    if($coun!=1){
        // Declined user process goes here
    }
    // ...

this work perfectly fine until an invader comes and tried to run credentials like

User Name : admin
Password  : FAKE_PASSWORD' OR 'x'='x

if the above credentials are passed the query rendered will be

select * from user where 
username='admin' 
AND password='FAKE_PASSWORD' 
OR 'x'='x' 

which is a legal SQL query it also satisfy our login criteria and hence an ilegimate user will be granted access.

Prevention

if we want to defend ourselves from this type of attack we have many solution available like

$query = sprintf("SELECT * FROM `user` WHERE username='%s' AND password='%s'",
                  mysql_real_escape_string($username),
                  mysql_real_escape_string($password));
mysql_query($query);

or you can use Object Relation Mapping for may be Prepared statement... but i use the following code to prevent myself from it.

// ...
    $username=$_REQUEST["username"];
    $password=md5(md5($_REQUEST["password"]));
    // Double MD5 are hard to find

    $query="select * from user where username='$username'";

    $result=mysql_query($query);
    $obj=mysql_fetch_object($result);

    $dbPass=$obj->Password;
    // password stored with Double MD5

    if($dbPass==$password){
        // after login process goes here
    }
    else{
        // Declined user process goes here
    }
    // ...

in my case i don't send password to the database just fetch the password and compare it.

i find it easy to implement... do you?

Continue Reading...

MySQL Soundex Example

Soundex is a phonetic algorithm for indexing names by sound, as pronounced in English. The goal is for homophones to be encoded to the same representation so that they can be matched despite minor differences in spelling. The algorithm mainly encodes consonants; a vowel will not be encoded unless it is the first letter. Soundex is the most widely known of all phonetic algorithms, as it is a standard feature of MS SQL and Oracle, and is often used (incorrectly) as a synonym for "phonetic algorithm". Improvements to Soundex are the basis for many modern phonetic algorithms.

Soundexis a phonetic normalization function that was invented for the 1880 U.S. Censusto get around the problem of sorting information by last names with different spellings but similar or identical sounds, such as Smith and Smythe. Since then, it's become one of the more popular ways of searching for similar sounding names in genealogy and government applications.

In this tutorial we will use the MySQL SOUNDEX() function, it will very help for searching purpose i.e. if a user search for something and he/she spell wrong then we can use SOUNDEX() to understand the search term.

Method Signature for SOUNDEX

String SOUNDEX(String)

Example 1

SELECT SOUNDEX('tutorialjinni');

it will yeild T6425

comparing two words that sound same

SELECT STRCMP(SOUNDEX('sun'), SOUNDEX('son')) AS Result;

Output is 0 because both words sound same and for that reason there SOUNDEX() codes are same, for reference SOUNDEX code for both sun and son is S500

Limitations

• This function, as currently implemented, is intended to work well with strings that are in the English language only. Strings in other languages may not produce reliable results.
• This function is not guaranteed to provide consistent results with strings that use multi-byte character sets, including utf-8.

Continue Reading...

MySQL indexOf Function

In this tutorial we will learn how to find occurrence of a string in a string, to do this in MySQL database it provide us with a function INSTR(), it take two arguments first one is haystack or the string from which you wish to find the occurrence of other string, second string is the whom location we are interested in, Please note that the positioned returned will be the starting position of the needle.

Method Signature

int INSTR(haystack,needle);

Example 1

SELECT INSTR('foobarbar', 'bar');

Output : 4

Example 2

SELECT INSTR('xbar', 'foobar');

Output : 0


INSTR() is multi-byte safe, and is case sensitive only if at least one argument is a binary string.

Continue Reading...

ROWNUM in Oracle

ROWNUM is a pseudo column(pseduo = not real) in oracle. It count the number of records returned in result set. The first record that comes in the result set increments the ROWNUM counter to 1, second record make ROWNUM = 2 , so on so forth.

ROWNUM is a magic column in Oracle Database that gets many people into trouble. When you learn what it is and how it works, however, it can be very useful. I use it for two main things:

1. To perform top- N processing. This is similar to using the LIMIT clause, available in some other databases.
2. To paginate through a query, typically in a stateless environment such as the Web.

How ROWNUM Works

ROWNUM is a pseudocolumn (not a real column) that is available in a query. ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N , where N is the number of rows in the set ROWNUM is used with. A ROWNUM value is not assigned permanently to a row (this is a common misconception). A row in a table does not have a number; you cannot ask for row 5 from a table—there is no such thing.

Also confusing to many people is when a ROWNUM value is actually assigned. A ROWNUM value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation. Also, a ROWNUM value is incremented only after it is assigned, which is why the following query will never return a row:

select * 
  from t 
 where ROWNUM > 1;

Because ROWNUM > 1 is not true for the first row, ROWNUM does not advance to 2. Hence, no ROWNUM value ever gets to be greater than 1. Consider a query with this structure:

select ..., ROWNUM
  from t
 where where clause="">
 group by <columns>
having <having clause="">
 order by <columns>;

Think of it as being processed in this order:

1. The FROM/WHERE clause goes first.
2. ROWNUM is assigned and incremented to each output row from the FROM/WHERE clause.
3. SELECT is applied.
4. GROUP BY is applied.
5. HAVING is applied.
6. ORDER BY is applied.

That is why a query in the following form is almost certainly an error:

select * 
  from emp 
 where ROWNUM <= 5 
 order by sal desc;

The intention was most likely to get the five highest-paid people—a top- N query. What the query will return is five random records (the first five the query happens to hit), sorted by salary. The procedural pseudocode for this query is as follows:

ROWNUM = 1
for x in 
( select * from emp )
loop
    exit when NOT(ROWNUM <= 5)
    OUTPUT record to temp
    ROWNUM = ROWNUM+1
end loop
SORT TEMP

It gets the first five records and then sorts them. A query with WHERE ROWNUM = 5 or WHERE ROWNUM > 5 doesn't make sense. This is because a ROWNUM value is assigned to a row during the predicate evaluation and gets incremented only after a row passes the WHERE clause. Here is the correct version of this query:

select *
  from  
( select * 
    from emp 
   order by sal desc ) 
 where ROWNUM <= 5;

This version will sort EMP by salary descending and then return the first five records it encounters (the top-five records). As you'll see in the top- N discussion coming up shortly, Oracle Database doesn't really sort the entire result set—it is smarter than that—but conceptually that is what takes place.

Continue Reading...

Difference Between Dates in MySQL

Find difference between two dates is very easy to find out using simple SQL queries, suppose we want to check,the duration a user logged in.

SELECT 
TIME_TO_SEC( TIMEDIFF( COL_LOGOUT_TIME , COL_LOGIN_TIME ) ) 
AS INTERVAL_IN_SECONDS
FROM Users

Above query will return number of seconds between last logout and login time you can format it using PHP strtotime() and date() functions , COL_LOGOUT_TIME,COL_LOGIN_TIME are column names.

There is another scenario where one may wish to see the number of days since user last logged in.

SELECT 
DATEDIFF(CURDATE(), COL_LOGOUT_TIME) 
AS INTERVAL_IN_DAYS 
FROM Users 

above query will return number of days between current date and COL_LOGOUT_TIME.

Continue Reading...

Hospital Management System Software

This Free Hospital Management System Software is developed in Microsoft Office's Access Database you just need to have Microsoft Office installed.

ERD

Download Hospital Management System Software

Continue Reading...

Library Management System Software

This Free Library Management System is developed in Microsoft Office's Access Database you just need to have Microsoft Office installed.

The scope of the current project is to:

• Centralization of the Data
• Accuracy of the Data
• Storage of the Data for a long Time
• Easy access to Data every time
• To reduce the Data-Redundancy

The Entity Relationship Diagram (ERD) of the project is as follows.

Download it and use it ...

Download Free Library Management System Software

Continue Reading...

Select Random Rows SQL

Sometimes ago i have a requirement to select random result from MySQL for my website dirjinni. I want to display Random articles at the bottom, as usual i search on google and find a solution which was some thing like...

-- NOT RECOMMENDED --

SELECT id,articles FROM articletable 
ORDER BY RAND() LIMIT 5

i was very happy to get the job done in very short period of time, but i forget there is no short cut to success :).

After 2 or 3 days i realized sometimes the results did not appeared, then i investigate the problem and found out that it is because of that MySQL "RAND()" function, the query become so expensive some times that the query failed and nothing is displayed (as i have shred hosting so resources are limited).

To Solve the problem i first get the MAX id from the table and then use PHP's rand() function to get 5 values from it and then put those values in the "IN" clause of SQL, my PHP code is ...

//...

$maxSQL="select max(id) as MAXIMUM form articletable";

$resultSet=mysql_query($maxSQL);
$arrayAsObject=mysql_fetch_object($resultSet);
$max=$arrayAsObject->MAXIMUM;

$v1=rand(1,$max);
$v2=rand(1,$max);
$v3=rand(1,$max);
$v4=rand(1,$max);
$v5=rand(1,$max);

$randomSQL="SELECT id,article FROM articletable 
            WHERE id 
            IN ($v1,$v2,$v3,$v4,$v5)";
 
//...

if you want more random number you can use array_rand function in PHP but this solve my problem and i hope it solve yours too.

Continue Reading...

Free Inventory Management Software

This Free Inventory Management Software is developed in Microsoft Office's Access Database you just need to have Microsoft Office installed.

The scope of the current project is to design the an inventory management system for Allied Engineering Limited.the System covers following functionalities

• Display which order was place on which date so, that we can view its details
• To check the stock quantity of any product so that it can be useful before taking orders and ultimately updating stock.
• View all the records of outstation customers. In this we can have a better grip on customers.
• To check the age group of employees
• To check which customer has placed most orders so which one is our regular customer. It will provide us with all the required details of customers and orders.
• Analyze our sales that which product is having more sales and which is having the greater customer demand.
• To check late payments

and many others...

The Entity Relationship Diagram (ERD) of the project is as follows.

Click ERD to enlarge it!

A screen shot of customer form of this inventory management system is attached so that you can get an early glimpse of how it is like :)

you can download free inventory management software from the link below and you can edit and redistribute it.

This Post is a part of our initiative to help students to use these free software as a helping guide for there term project or for there general learning.

Continue Reading...

Free Project Management Software

This free Project Management Software is developed in Microsoft Office's Access Database you just need to have Microsoft Office installed.

The scope of the current project is to design the database of one of the several processes of a software house. The main objectives of designing data base are as follows:

• To keep records of projects.
• To keep records about the employees.
• To keep records about the sections and departments this relates with the Project Management Systems.

The Entity Relationship Diagram (ERD) of the project is as follows.

it has a pretty good interface just attaching a images so got an idea of it.

you can download it from the link below and you can edit and redistribute it.

Download Free Project Management Software

This Post is a part of our initiative to help students to use these free software as a helping guide for there term project or for there general learning.

Continue Reading...

Store Arabic or Urdu in MySQL Using PHP

Often there are times when we have to store / retrieve a language other than the English language, so in order to do this and in a very easy manner i have found a way to solve this problem.

What i do , i simple map non English characters to numeric HTML equivalents and save them to MySQL and on retrieving form MySQL i map them back.

<?php
public function convertUrduForMySQL($urdu){

    $map = array(0x0, 0x2FFFF, 0, 0xFFFF);

    return  mb_decode_numericentity($urdu, $map, 'UTF-8');
    
    //Return encoded data that can be stored in MySQL
}

public function convertBackToUrduFromMySQL($encodedDataFromMySQL){

    $map = array(0x0, 0x2FFFF, 0, 0xFFFF);

    return mb_encode_numericentity($encodedDataFromMySQL, $map, 'UTF-8');

    // Convert back to Urdu
}

?>

and a simple usage example would be like it

public function usage(){

    $urdu="میں نے روبوٹ اور روبالہ پر آپ کا تجزیہ دیکھا ہے";
    
    $encodedDataFromMySQL=$this->convertUrduForMySQL($urdu);

    echo $encodedDataFromMySQL."<hr>";

    $urdu=$this->convertBackToUrduFromMySQL($encodedDataFromMySQL);

    echo $urdu;
}

output of the above code will be

میں نے روبوٹ اور روبالہ پر آپ کا تجزیہ دیکھا ہے


میں نے روبوٹ اور روبالہ پر آپ کا تجزیہ دیکھا ہے

although there are other ways to do this but to me it is very simple and quick and hope same for you :)

Continue Reading...