PHP from Novice to Ninja

JQuery: Ajax Example of Select Values Filled Dynamically

noreply@blogger.com (Shad Saleem) — Tue, 12 Feb 2013 05:23:00 +0000

Make Dynamic Select Box using Ajax, Jquery, Php, MySql.

First Choose country from Select Box of country after that pull data from database using Ajax, Jquery and show data other Select box of city without page refresh.

Requirements:

1)index.php

2)city.php

3)tables in a database.

country
city

Two tables are required

index.php

________________________________________________________________________________

java script code

<script type=”text/javascript” src=”http://ajax.googleapis.com/ajax/libs/jquery/1.7.1/jquery.min.js”></script>
<script type=”text/javascript”>
$(document).ready(function(){

$(“.country”).change(function(){

$id = $(this).val();

$.ajax({
type:”Post”,
url:”city.php”,
data:”id=”+$id,
cache:”false”,
success:function(html){
$(“#city”).html(html);
}
});
});
});
</script>

Php code

<div class=”label”>Country</div>
<select class=”country”>
<option selected=”selected”>Select Country</option>
<?php
include(“config.php”);
$sql = “select * from country” or die(“query error”);
$result=mysql_query($sql);
while($rows=mysql_fetch_array($result)){

$id = $rows['id'];
$country = $rows['country'];
echo ‘<option value=”‘.$id.’”>’.$country.’</option>’;
}
?>
</select>

Html code

<div class=”label”>City</div>
<select id=”city”>
<option value=”"<Select Country first</option>
</select>

________________________________________________________________________________

City.php

_________________________________________________________________________________

<?php
include(“config.php”);
$sql = “select id,city from city where country_id=”.$_POST['id'] or die(“query error”);
$result = mysql_query($sql);
while($rows=mysql_fetch_array($result)){

$cityid= $rows['id'];
$cityname = $rows['city'];

echo ‘<option id=”‘.$cityid.’”>’.$cityname.’</option>’;

}
?>

__________________________________________________________________________________________

Designing A Navigation using Jquery

noreply@blogger.com (Shad Saleem) — Thu, 03 Jan 2013 06:13:00 +0000

Step 1)
Create a html page and name it as "test1.html"
here goes our code

<html>
<head>
<title>Example 1</title>
</head>
<body>
<ul id="navigation">
<li id="home"><a href="#">Home</a></li>
<li class="active"><a href="#">about us</a></li>
<li><a href="#">Our Vision</a></li>
<li><a href="#">Blogs</a></li>
</ul>
<div id="placeHolder">

</div>
<script src=js/jquery.min.js></"></script>
<script>
$(document).ready(function(){
$('#navigation li a').each(function(){
var $item = $(this);
$item.bind('click',function(event){
event.preventDefault();
var title = $item.html();
var html = title + ' was selected.';
$('#placeHolder').html(html);
});
});
});

Step 2) you need to get a Jquery file i.e., jquery.min.js you can download it from http://code.jquery.com/jquery-1.7.1.min.js and put it in "js" folder
<script src=js/jquery.min.js></"></script>

Demo Here

Create Agile Web application using PHP Ajax and Jquery

noreply@blogger.com (Shad Saleem) — Thu, 03 Jan 2013 05:57:00 +0000

I will publish a series of blog posts for creating responsive Web applications using PHP AJAX and JQuery
and with this series of posts you will be able understand how we can use these Technologies to create Responsive web application moreover you will be able use it in Development.

PHP isset() function

noreply@blogger.com (Shad Saleem) — Mon, 06 Aug 2012 05:24:00 +0000

isset — Determine if a variable is set and is not NULL

isset() determines whether a certain variable has already been declared by PHP. It returns a boolean value true if the variable has already been set, and false otherwise, or if the variable is set to the value NULL.

Example #1 isset() Examples

<?php

$var = '';// This will evaluate to TRUE so the text will be printed.if (isset($var)) {
    echo "This var is set so I will print.";
}// In the next examples we'll use var_dump to output
// the return value of isset().$a = "test";$b = "anothertest";var_dump(isset($a));      // TRUE

var_dump(isset($a, $b)); // TRUEunset ($a);var_dump(isset($a)); // FALSE
var_dump(isset($a, $b)); // FALSE$foo = NULL;var_dump(isset($foo)); // FALSE?>

php Event Calendar

noreply@blogger.com (Shad Saleem) — Thu, 05 Jul 2012 07:17:00 +0000

phpEventCalendar is a MySQL backed application that allows users to post and display events or notes on a month-at-a-glance calendar. A user administration panel allows authorized users (Administrators) to control who can add, delete, and edit events (Editors).

Features:

Script renders a month-at-a-glance calendar that displays events that have been added by a calendar administator or editor.
User administration panel.
There are two user levels. Administrators can add/edit users, and add/edit any and all events. Editors can add events, edit their own events, and change their password.
Adjustable weekstart. The calendar can be set to display any day of the week as the first column in the calendar.
Displays in two time formats, a 12 hour format that displays 12:00am to 12:00pm, and a 24 hour format that displays 00:00-23:59.
Calendar colors and size customizable through css file
Simple template file makes it easy for people with little php knowledge to integrate the calendar in their site.
Ability to limit the maximum number of posts, and the maximum number of characters per event, that are displayed on the month-at-a-glance calendar.

Demo :http://www.phpcodeworks.com/scripts/pec/demo
website : http://www.phpcodeworks.com

Vector Maps With jQuery, Vector Graphic Maps for Modern Browsers :JQVMAP

noreply@blogger.com (Shad Saleem) — Mon, 02 Jul 2012 11:11:00 +0000

JQVMap is a jQuery plugin for render vector graphic maps by using SVG for modern browsers and VML for the rest.

It is a heavily modified version of another plugin, jVectorMap, and comes withready-to-use maps of “world, USA, Europe and Germany”.

There are several customization options for beautifying the maps including colors, borders or their opacity.

Maps can have zooming enabled or not, show tooltips of data when hovered and there is callback for clicks.

Also, it is possible to select any regions on initial load or after any custom event.

Requirements: jQuery
Compatibility: All Modern Browsers
Website: http://jqvmap.com/
Download: https://github.com/manifestinteractive/jqvmap

PHP Image Gallery

noreply@blogger.com (Shad Saleem) — Mon, 02 Jul 2012 10:41:00 +0000

PhotoShow is a free, open-source, PHP application and very easy to use web image gallery installable on any web server.

The application works without a database and uses the folder structure for creating the hierarchy of the galleries (supports unlimited sub-galleries).

Images can be added either from the admin panel via a drag ‘n’ drop uploader or directly from the filesystem. Organizing the folders/images, editing and deleting them are also accomplished with the same methods.

There is support for multiple users and groups where each can have different permissions.

PhotoShow can auto-generate thumbnails of images, display the EXIF data and allows others to comment them.

Requirements: PHP 5+
Website: http://www.photoshow-gallery.com/
Demo: http://www.photoshow-gallery.com/demo
Download: https://github.com/thibaud-rohmer/PhotoShow

jQuery File Upload Plugin

noreply@blogger.com (Shad Saleem) — Mon, 02 Jul 2012 10:18:00 +0000

Free Open Source jQuery File Upload Plugin

File Upload widget with multiple file selection, drag&drop support, progress bars and preview images for jQuery.
Supports cross-domain, chunked and resumable file uploads and client-side image resizing.
Works with any server-side platform (PHP, Python, Ruby on Rails, Java, Node.js, Go etc.) that supports standard HTML form file uploads.

Download from here: https://github.com/blueimp
website: https://blueimp.net/

JQuery image fade menu for Interactive websites

noreply@blogger.com (Shad Saleem) — Sat, 30 Jun 2012 07:08:00 +0000

Files required

HTML
menu.html
CSS
style.css
SCRIPTS
jquery1.2.6.min.js download from here
IMAGE
http://www.narga.net/wp-content/uploads/2010/04/Dragon-Interactive.jpg download and rename as dragon-sprite.jpg

DEMO HERE

style.css

body { font-family: helvetica;}

#navigation { list-style: none;}

#navigation li { float: left;}

#navigation a * { display: none;}

#navigation a,#navigation a .hover {
height: 70px;

position: relative;

display: block;

background: url(dragon-sprite.jpg) 0 0 no-repeat;

}

/* individual navigation items */

#navigation a.home {

background-position: 0 0;

width: 102px;

}

#navigation .highlight a.home:hover,#navigation a.home .hover {

background-position: 0 -280px;

width: 102px;

}

#navigation a.services {

background-position: -102px -140px;

width: 115px;

}

#navigation .highlight a.services:hover,

#navigation a.services .hover {

background-position: -102px -280px;

}

#navigation a.portfolio {

background-position: -217px 0;

width: 120px;

}

#navigation .highlight a.portfolio:hover,#navigation a.portfolio .hover {

background-position: -218px -280px;

}

#navigation a.about {

background-position: -337px 0;

width: 100px;

}

#navigation .highlight a.about:hover,#navigation a.about .hover {

background-position: -339px -280px;

}

#navigation a.contact {

background-position: -437px 0;

width: 115px;

}

#navigation .highlight a.contact:hover,#navigation a.contact .hover {

background-position: -440px -280px;

}

<!--[if IE]>

#navigation a .hover {

cursor: pointer;

}

menu.html

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"

"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<head>

<title>Image Fade Revisited</title>

$(function () {

if ($.browser.msie && $.browser.version < 7) return;

$('#navigation li')

.removeClass('highlight')

.find('a')

.append('<span class="hover" />').each(function () {

var $span = $('> span.hover', this).css('opacity', 0);

$(this).hover(function () {

// on hover

$span.stop().fadeTo(500, 1);

}, function () {

// off hover

$span.stop().fadeTo(500, 0);

});

</script>

</head>

<body>

<h1>Image Fade - Revisited</h1>

<p>This technique revisits image fade transitions, following the techniques shown on the Dragon Interative web site. <br /></p>

</div>

<li class="highlight"><a href="#" class="services"><span>Services</span></a></li>

<li class="highlight"><a href="#" class="portfolio"><span>Portfolio</span></a></li>

<li class="highlight"><a href="#" class="about"><span>About</span></a></li>

<li class="highlight"><a href="#" class="contact"><span>Contact</span></a></li>

</ul>

</body>

</html>

Validating form and Display tooltip if any errors

noreply@blogger.com (Shad Saleem) — Fri, 29 Jun 2012 12:20:00 +0000

Copy this code and try it.

how to use?

Answer:
Get these scripts and css files
CSS
1) https://raw.github.com/posabsolute/jQuery-Validation-Engine/master/css/validationEngine.jquery.css

2) https://raw.github.com/posabsolute/jQuery-Validation-Engine/master/css/template.css

JS FILEs

1) https://raw.github.com/posabsolute/jQuery-Validation-Engine/master/js/jquery-1.6.min.js

2) https://raw.github.com/posabsolute/jQuery-Validation-3)Engine/master/js/languages/jquery.validationEngine-en.js

https://raw.github.com/posabsolute/jQuery-Validation-Engine/master/js/jquery.validationEngine.js

For Required field

class="validate[required] text-input" add this in <input> tag

For Email validation

class="validate[required,custom[email]] text-input" add this in <input> tag etc

DEMO HERE

validator.html

<html>

<head>

</script>

</script>

</script>

jQuery(document).ready(function(){

// binds form submission and fields to the validation engine

jQuery("#formID").validationEngine();

});

</script>

</head>

<h1>This is form validator, shows tooltip when inserted unformatted data</h1>

<ul>

<li> first name *

<li>Email*

<li>

<li>

password:

<li>

<span>password is admin</span><br/>

<li>

Confirm password :

<li>

<br/>

<li>

url*

<li><input value="http://" class="validate[required,custom[url]] text-input" type="text" name="url" id="url" />

<li>Favorite sport*:
<li><select name="sport2" id="sport2" multiple class="validate[required]">
<option value="">Choose a sport</option>
<option value="option1">Tennis</option>
<option value="option2">Football</option>
<option value="option3">Golf</option>
</select>
<br/>
<li>Minimum field size*
<li>
<input value="" class="validate[required,minSize[6]] text-input" type="text" name="minsize" id="minsize" />
<br/>
<li>Checks that the value is a date in the past*
<span class="color">
Please enter a date ealier than 2010/01/01
</span></li>
<li>
<input value="2009/06/30" class="validate[custom[date],past[2010/01/01]] text-input" type="text" name="past" id="past" />
<br/>
<li>Checks that the value is a date in the future
<span class="color">Please enter a date older than today's date
</span>
<li>
<input value="year-month-date" class="validate[custom[date],future[NOW]] text-input" type="text" name="future" id="future" />
<br/>
<li>Date Range
<span class="color">
Checks that the start date is before the end date.<br/>
Please enter an end date ealier than the start date</span><br/>
<label for="date1">Start Date : </label>
<input value="9/1/2009" class="validate[dateRange[grp1]]" type="text" id="date1" />
</label>
<label>
<label for="date2">End Date : </label>
<input value="3/18/1985" class="validate[dateRange[grp1]]" type="text" id="date2" />
<br/>

</ul>

</form>

</fieldset>

</html>

Simple JQuery Accordin

noreply@blogger.com (Shad Saleem) — Thu, 21 Jun 2012 06:35:00 +0000

Files needed
Scripts
1) jquery.min.js. can be downloaded from http://code.jquery.com/jquery-1.7.2.min.js
2) jquery-ui.min.js can be downloaded from http://ajax.googleapis.com/ajax/libs/jqueryui/1.7.2/jquery-ui.min.js

Css
1) jquery-ui.css can be downloaded from
http://ajax.googleapis.com/ajax/libs/jqueryui/1.7.2/themes/ui-lightness/jquery-ui.css

HTML
1)index.html

index.html

<html>

<head>

$(document).ready(function() {

$("#accordion").accordion();

});

</script>

</head>

<body>

<h3>Geography</h3>

<div>

<p>

Geography (from Greek γεωγραφία, geographia, lit. "earth describe-write") is the science that studies the lands, the features, the inhabitants, and the phenomena of the Earth. </p>

</div>

<h3>Mathematics</h3>

<div>

<p>

Mathematics (from Greek μάθημα máthēma, "knowledge, study, learning") is the abstract study of subjects encompassing quantity,structure,space, change, and more; it has no generally accepted definition.

</p>

</div>

<h3>History</h3>

<div>

<p>

History (from Greek ἱστορία - historia, meaning "inquiry, knowledge acquired by investigation") is the discovery, collection, organization, and presentation of information about past events. History can also mean the period of time after writing was invented.

</p>

</div>

</body>

</html>

LIVE DEMO

JQuery Tabs

noreply@blogger.com (Shad Saleem) — Thu, 21 Jun 2012 05:55:00 +0000

Files needed
Scripts
1)jquery.min.js can be downloaded from http://code.jquery.com/jquery-1.7.2.min.js
2) http://ajax.googleapis.com/ajax/libs/jqueryui/1.7.2/jquery-ui.min.js

Css
1) http://ajax.googleapis.com/ajax/libs/jqueryui/1.7.2/themes/ui-lightness/jquery-ui.css

HTML
1)index.html

index.html

<html>

<head>

$(document).ready(function() {

$("#tabs").tabs();

});

</script>

</head>

<body>

<ul>

</ul>

<p>

This text is under tab1 div

as you can see the content is coming when clicked tab1

</p>

</div>

<p>

This text is under tab2 div

as you can see the content is coming when clicked tab2

</p>

</div>

<p>

This text is under tab3 div

as you can see the content is coming when clicked tab3

</p>

<p>

This text is under tab3 div

as you can see the content is coming when clicked tab3

</p>

</div>

</body>

</html>

LIVE DEMO

Pick date using JQuery Library from an Html text box

noreply@blogger.com (Shad Saleem) — Wed, 20 Jun 2012 11:05:00 +0000

Arrangement of Files and Folder
create a folder "Datepicker"
and put above two folder and one file in "datepicker" folder.
Again "base" folder conatins "images" folder and css files and "js" folder contains scripts
Download the script and try its implementation.

LIVE DEMO

PHP and JQuery

noreply@blogger.com (Shad Saleem) — Wed, 20 Jun 2012 09:52:00 +0000

Using PHP and JQuery to Reterieve city wise list when selected country-script

we need to Three Files
1)Jquery.min.js which can be found at (http://ajax.googleapis.com/ajax/libs/jquery/1.5/jquery.min.js)
2)index.html
3)get_cities.php

get_cities.php

//get_cities.php

<?php
switch($_REQUEST['country']){
case 'ie': // { ireland
$cities=array(
'Cork', 'Dublin', 'Galway', 'Limerick',
'Waterford'
);
break;
// }
case 'uk': // { United Kingdom
$cities=array(
'Bath', 'Birmingham', 'Bradford',
'Brighton &amp; Hove', 'Bristol',
'Cambridge', 'Canterbury', 'Carlisle',
'Chester', 'Chichester', 'Coventry',
'Derby', 'Durham', 'Ely', 'Exeter',
'Gloucester', 'Hereford', 'Kingston upon Hull',
/* and on and on! */
'Newport', 'St David\'s', 'Swansea'
);
/**add another case with array of values(cities) to be retrieved*
eg:- for 'us'
step1) make sure that you have added and option in index.html as
<option value="us">united states</option>
now here in get_cities.php
add
case 'us':
$cities=array('texas','abc', 'bef', 'hef', 'etc');
**/
break;
// }
default: // { else
$cities=false;
// }
}
if(!$cities)echo 'please choose a country first';
else echo '<select name="city"><option>',
join('</option><option>',$cities),
'</select>';
print_r($_REQUEST);
?>

index.html

<!-- 1) use this if yu want to include js script from google api

1)ends here (OR)-->

function setup_country_change(){

// If #country is changed, then it calls update_cities() function

$('#country').change(update_cities);

}

function update_cities(){

var country=$('#country').attr('value');

// Calls get_cities.php and when retrieved, call show_cities() with the result.

$.get('get_cities.php?country='+country, show_cities);

}

function show_cities(res){

// Replace contents of #cities with retrieved result

$('#cities').html(res);

}

// Run setup_country_change() when the document is ready

$(document).ready(setup_country_change);

</script>

</head>

<body>

<form>

<table>

<tr>

<th>Country</th>

<td>

<option value=""> -- please choose -- </option>

<option value="ie">Ireland</option>

<option value="uk">Great Britain</option>

</select>

</td>

</tr>

<tr>

<th>cities</th>

<td id="cities">please select a country first</td>

</tr>

</table>

</form>

</body>

</html>

PHP API - using functions built into PHP with MySQL

noreply@blogger.com (Shad Saleem) — Tue, 19 Jun 2012 12:28:00 +0000

mysql_change_user



mysql_affected_rows

mysql_client_encoding

mysql_close

mysql_connect

mysql_create_db

mysql_data_seek

mysql_db_name

mysql_db_query

mysql_drop_db

mysql_errno

mysql_error

mysql_escape_string

mysql_fetch_array

mysql_fetch_assoc

mysql_fetch_field

mysql_fetch_lengths

mysql_fetch_object

mysql_fetch_row

mysql_field_flags

mysql_field_len

mysql_field_name

mysql_field_seek

mysql_field_table

mysql_field_type

mysql_free_result

mysql_get_client_info

mysql_get_host_info

mysql_get_proto_info

mysql_get_server_info

mysql_info

mysql_insert_id

mysql_list_dbs

mysql_list_fields

mysql_list_processes

mysql_list_tables

mysql_num_fields

mysql_num_rows

mysql_pconnect

mysql_ping

mysql_query

mysql_real_escape_string

mysql_result

mysql_select_db

mysql_stat

mysql_tablename

mysql_thread_id

mysql_unbuffered_query

Perl API - using functions and methods built into the Perl DBI with MySQL

noreply@blogger.com (Shad Saleem) — Tue, 19 Jun 2012 12:26:00 +0000

available_drivers

begin_work

bind_col

bind_columns

bind_param

bind_param_array

bind_param_inout

can

clone

column_info

commit

connect

connect_cached

data_sources

disconnect

do

dump_results

err

errstr

execute

execute_array

execute_for_fetch

fetch

fetchall_arrayref

fetchall_hashref

fetchrow_array

fetchrow_arrayref

fetchrow_hashref

finish

foreign_key_info

func

get_info

installed_versions


last_insert_id

looks_like_number

neat

neat_list

parse_dsn

parse_trace_flag

parse_trace_flags

ping

prepare

prepare_cached

primary_key

primary_key_info

quote

quote_identifier

rollback

rows

selectall_arrayref

selectall_hashref

selectcol_arrayref

selectrow_array

selectrow_arrayref

selectrow_hashref

set_err

state

table_info

table_info_all

tables

trace

trace_msg

type_info

type_info_all

Attributes for Handles

Command-Line Utilities

noreply@blogger.com (Shad Saleem) — Tue, 19 Jun 2012 12:25:00 +0000

isamchk

comp_err

make_binary_distribution

msql2mysql

my_print_defaults

myisamchk

myisamlog

myisampack

mysqlaccess

mysqladmin

mysqlbinlog

mysqlbug

mysqlcheck

mysqldump

mysqldumpslow

mysqlhotcopy

mysqlimport

mysqlshow

perror

Flow Control Functions

noreply@blogger.com (Shad Saleem) — Tue, 19 Jun 2012 12:24:00 +0000

MYSQL- Flow Control Functions

CASE

IF

IFNULL

NULLIF

Mathematical and Aggregate Functions

noreply@blogger.com (Shad Saleem) — Tue, 19 Jun 2012 12:23:00 +0000

MYSQL-Mathematical and Aggregate Functions
ABS ACOS ASIN ATAN ATAN2 AVG BIT_AND BIT_OR BIT_XOR CEIL CEILING COS COT COUNT CRC32 DEGREES EXP FLOOR FORMAT GREATEST GROUP_CONCAT LEAST LN LOG LOG2 LOG10 MAX MIN MOD PI POW POWER RADIANS RAND ROUND SIGN SIN SQRT STD STDDEV SUM TAN TRUNCATE VARIANCE

Date and Time Functions

noreply@blogger.com (Shad Saleem) — Tue, 19 Jun 2012 12:23:00 +0000

ADDDATE

ADDTIME

CONVERT_TZ

CURDATE

CURRENT_DATE

CURRENT_TIME

CURRENT_TIMESTAMP

CURTIME

DATE

DATE_ADD

DATE_FORMAT

DATE_SUB

DATEDIFF

DAY

DAYNAME

DAYOFMONTH

DAYOFWEEK

DAYOFYEAR

EXTRACT

FROM_DAYS

FROM_UNIXTIME

GET_FORMAT

HOUR

LAST_DAY

LOCALTIME

LOCALTIMESTAMP

MAKEDATE

MAKETIME

MICROSECOND

MINUTE

MONTH

MONTHNAME

NOW

PERIOD_ADD

PERIOD_DIFF

QUARTER

SEC_TO_TIME

SECOND

STR_TO_DATE

SUBDATE

SUBTIME

SYSDATE

TIME

TIMEDIFF

TIMESTAMP

TIMESTAMPDIFF

TIMESTAMPADD

TIME_FORMAT

TIME_TO_SEC

TO_DAYS

UNIX_TIMESTAMP

UTC_DATE

UTC_TIME

UTC_TIMESTAMP

WEEK

WEEKDAY

WEEKOFYEAR

YEAR

YEARWEEK

String Functions

noreply@blogger.com (Shad Saleem) — Tue, 19 Jun 2012 12:22:00 +0000

AES_DECRYPT AES_ENCRYPT ASCII BIN BINARY BIT_LENGTH CHAR CHAR_LENGTH CHARACTER_LENGTH COMPRESS CONCAT CONCAT_WS CONV DECODE DES_DECRYPT DES_ENCRYPT ELT ENCODE ENCRYPT EXPORT_SET FIELD FIND_IN_SET HEX INET_ATON INET_NTOA INSERT INSTR LCASE LEFT LENGTH LOAD_FILE LOCATE LOWER LPAD LTRIM MAKE_SET MATCH AGAINST MD5 MID OCT OCTET_LENGTH OLD_PASSWORD ORD PASSWORD POSITION QUOTE REPEAT REPLACE REVERSE RIGHT RPAD RTRIM SHA SHA1 SOUNDEX SPACE STRCMP SUBSTRING SUBSTRING_INDEX TRIM UCASE UNCOMPRESS UNCOMPRESSED_LENGTH UNHEX UPPER

MYSQL Statements and clauses

noreply@blogger.com (Shad Saleem) — Tue, 19 Jun 2012 12:21:00 +0000

ALTER DATABASE

ALTER TABLE

ALTER VIEW

ANALYZE TABLE

BACKUP TABLE

CACHE INDEX

CHANGE MASTER TO

CHECK TABLE

CHECKSUM TABLE

COMMIT

CREATE DATABASE

CREATE INDEX

CREATE TABLE

CREATE VIEW

DELETE

DESCRIBE

DO

DROP DATABASE

DROP INDEX

DROP TABLE

DROP USER

DROP VIEW

EXPLAIN

FLUSH

GRANT

HANDLER

INSERT

JOIN

KILL

LOAD DATA FROM MASTER

LOAD DATA INFILE

LOAD INDEX INTO CACHE

LOAD TABLE...FROM MASTER

LOCK TABLES

OPTIMIZE TABLE

PURGE MASTER LOGS

RENAME TABLE

REPAIR TABLE

REPLACE

RESET

RESET MASTER

RESET SLAVE

RESTORE TABLE

REVOKE

ROLLBACK

ROLLBACK TO SAVEPOINT

SAVEPOINT

SELECT

SET

SET PASSWORD

SET SQL_LOG_BIN

SET TRANSACTION

SHOW BINLOG EVENTS

SHOW CHARACTER SET

SHOW COLLATION

SHOW COLUMNS

SHOW CREATE DATABASE

SHOW CREATE TABLE

SHOW CREATE VIEW

SHOW DATABASES

SHOW ENGINES

SHOW ERRORS

SHOW GRANTS

SHOW INDEX

SHOW INNODB STATUS

SHOW LOGS

SHOW MASTER LOGS

SHOW MASTER STATUS

SHOW PRIVILEGES

SHOW PROCESSLIST

SHOW SLAVE HOSTS

SHOW SLAVE STATUS

SHOW STATUS

SHOW TABLE STATUS

SHOW TABLES

SHOW VARIABLES

SHOW WARNINGS

START SLAVE

START TRANSACTION

STOP SLAVE

TRUNCATE TABLE

UNION

UNLOCK TABLES

USE

MYSQL COMMANDS

noreply@blogger.com (Shad Saleem) — Tue, 19 Jun 2012 12:16:00 +0000

This is a list of handy MySQL commands . At the bottom are statements, clauses, and functions you can use in MySQL. Below that are PHP and Perl API functions you can use to interface with MySQL. To use those you will need to build PHP with MySQL functionality. To use MySQL with Perl you will need to use the Perl modules DBI and DBD::mysql.

Below when you see # it means from the unix shell. When you see mysql> it means from a MySQL prompt after logging into MySQL.

To login (from unix shell) use -h only if needed.

# [mysql dir]/bin/mysql -h hostname -u root -p

Create a database on the sql server.

mysql> create database [databasename];

List all databases on the sql server.

mysql> show databases;

Switch to a database.

mysql> use [db name];

To see all the tables in the db.

mysql> show tables;

To see database's field formats.

mysql> describe [table name];

To delete a db.

mysql> drop database [database name];

To delete a table.

mysql> drop table [table name];

Show all data in a table.

mysql> SELECT * FROM [table name];

Returns the columns and column information pertaining to the designated table.

mysql> show columns from [table name];

Show certain selected rows with the value "whatever".

mysql> SELECT * FROM [table name] WHERE [field name] = "whatever";

Show all records containing the name "Bob" AND the phone number '3444444'.

mysql> SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444';

Show all records not containing the name "Bob" AND the phone number '3444444' order by the phone_number field.

mysql> SELECT * FROM [table name] WHERE name != "Bob" AND phone_number = '3444444' order by phone_number;

Show all records starting with the letters 'bob' AND the phone number '3444444'.

mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444';

Show all records starting with the letters 'bob' AND the phone number '3444444' limit to records 1 through 5.

mysql> SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444' limit 1,5;

Use a regular expression to find records. Use "REGEXP BINARY" to force case-sensitivity. This finds any record beginning with a.

mysql> SELECT * FROM [table name] WHERE rec RLIKE "^a";

Show unique records.

mysql> SELECT DISTINCT [column name] FROM [table name];

Show selected records sorted in an ascending (asc) or descending (desc).

mysql> SELECT [col1],[col2] FROM [table name] ORDER BY [col2] DESC;

Return number of rows.

mysql> SELECT COUNT(*) FROM [table name];

Sum column.

mysql> SELECT SUM(*) FROM [table name];

Join tables on common columns.

mysql> select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;

Creating a new user. Login as root. Switch to the MySQL db. Make the user. Update privs.

# mysql -u root -p

mysql> use mysql;

mysql> INSERT INTO user (Host,User,Password) VALUES('%','username',PASSWORD('password'));

mysql> flush privileges;

Change a users password from unix shell.

# [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password 'new-password'

Change a users password from MySQL prompt. Login as root. Set the password. Update privs.

# mysql -u root -p

mysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');

mysql> flush privileges;

Recover a MySQL root password. Stop the MySQL server process. Start again with no grant tables. Login to MySQL as root. Set new password. Exit MySQL and restart MySQL server.

# /etc/init.d/mysql stop

# mysqld_safe --skip-grant-tables &

# mysql -u root

mysql> use mysql;

mysql> update user set password=PASSWORD("newrootpassword") where User='root';

mysql> flush privileges;

mysql> quit

# /etc/init.d/mysql stop

# /etc/init.d/mysql start

Set a root password if there is on root password.

# mysqladmin -u root password newpassword

Update a root password.

# mysqladmin -u root -p oldpassword newpassword

Allow the user "bob" to connect to the server from localhost using the password "passwd". Login as root. Switch to the MySQL db. Give privs. Update privs.

# mysql -u root -p

mysql> use mysql;

mysql> grant usage on *.* to bob@localhost identified by 'passwd';

mysql> flush privileges;

Give user privilages for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs.

# mysql -u root -p

mysql> use mysql;

mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N');

mysql> flush privileges;

or

mysql> grant all privileges on databasename.* to username@localhost;

mysql> flush privileges;

To update info already in a table.

mysql> UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user';

Delete a row(s) from a table.

mysql> DELETE from [table name] where [field name] = 'whatever';

Update database permissions/privilages.

mysql> flush privileges;

Delete a column.

mysql> alter table [table name] drop column [column name];

Add a new column to db.

mysql> alter table [table name] add column [new column name] varchar (20);

Change column name.

mysql> alter table [table name] change [old column name] [new column name] varchar (50);

Make a unique column so you get no dupes.

mysql> alter table [table name] add unique ([column name]);

Make a column bigger.

mysql> alter table [table name] modify [column name] VARCHAR(3);

Delete unique from table.

mysql> alter table [table name] drop index [colmn name];

Load a CSV file into a table.

mysql> LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);

Dump all databases for backup. Backup file is sql commands to recreate all db's.

# [mysql dir]/bin/mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql

Dump one database for backup.

# [mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql

Dump a table from a database.

# [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

Restore database (or database table) from backup.

# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql

Create Table Example 1.

mysql> CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));

Create Table Example 2.

mysql> create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastnamevarchar(50) default 'bato');

Deprecated: Function split() is deprecated

noreply@blogger.com (Shad Saleem) — Tue, 19 Jun 2012 11:03:00 +0000

ERROR:
Deprecated: Function split() is deprecated

SOLUTION:

Replace split() function with explode.

for example if you have this code

/*******code*****/

<?php

$s=123;

$a=split('.', $s);

/*******code*****/

Change the above code to

/*******code*****/
<?php
$s=123;
$a=explode('.', $s); // notice split has been replaced with explode
?>
/*******code*****/

PHP Language Chooser/Translate

noreply@blogger.com (Shad Saleem) — Tue, 19 Jun 2012 10:54:00 +0000

Files needed
lanuage files such as
english.php,
turkish.php,
french.php,
indonesian.php and include the file of your desired language and
index.php

index.php

<?php

/******index.php*******/

$lang=$_POST['lang'];

<html>

<body>

Language:

<option value="english" <?php if($lang=="english"){echo "SELECTED";}?>>English</option>

<option value="indonesian" <?php if($lang=="indonesian"){echo "SELECTED";}?>>indonesian</option>

<option value="french" <?php if($lang=="french"){echo "SELECTED";}?>>french</option>

<option value="turkish" <?php if($lang=="turkishsh"){echo "SELECTED";}?>>Turkish</option>

</select>

</body>

</html>

<?php

switch($lang)

{

case "english":

include('english.php');

break;

case "indonesian":

include('indonesian.php');

break;

case "french":

include('french.php');

break;

case "turkish":

include('turkish.php');

break;

default:

include('english.php');

break;

}

echo "<br/>".$first."<br/>";

echo $second;

/****comment****

echo $lang;

*****comment*****/

english.php

<?php

/******english.php*********/
$bodyText="hello";
$bodyText="This is php programming language. welcome to our php website";
?>

turkish.php

<?php

/*************turkish.php********/
$bodyText="merhaba";
$bodyText="Bu php programlama dilidir. Bizim php web sitesine hoş geldiniz";
?>

french.php

/*******french.php********/

<?php
$bodyText="bonjour";
$bodyText="C'est un langage de programmation php. bienvenue sur notre site php";
?>

indonesian.php

<?php
/*********indonesian.php*********/
$subjectText="halo";
$bodyText="Ini adalah bahasa pemrograman php. selamat datang di website php kita";
?>

LIVE DEMO HERE