Category Archives: MySQL

perform inner join on same table in mysql

Posted on by
Reply

During application development, i was stuck in one mysql – query problem.  i don’t know whether this post title gives meaning or not. but I had used this title to search on google for my problem’s solution.  so i kept same here.

let me describe problem

+-------------------------+---------+
| switch                  | port_no |
+-------------------------+---------+
| 00:00:00:00:00:00:00:02 | 3       |
| 00:00:00:00:00:00:00:01 | 2       |
| 00:00:00:00:00:00:00:01 | 1       |
| 00:00:00:00:00:00:00:02 | 1       |
| 00:00:00:00:00:00:00:04 | 2       |

so i have such type of data in sla table.  and i want bellow output

Expected output

+-------------------------+---------+
| switch                  | port_no |
+-------------------------+---------+
| 00:00:00:00:00:00:00:02 | 3,1     |
| 00:00:00:00:00:00:00:01 | 2,1     |
| 00:00:00:00:00:00:00:04 | 2       |

so here is magic function which saved my life 🙂

Function :

Look at GROUP_CONCAT()

Query which return expected output

select switch,GROUP_CONCAT(port_no) from sla group by switch;

i know lots of newbie developers got such problem so i’m sharing here..
if anyone have best title for it .. plz suggest it in comment.

🙂

Ruby Mysql Connectivity

Posted on by
Reply

This post shows you how you can connect to MySQL in Ruby.   I’m assuming that you already aware with basic SQL Operation like how to create database, table and insert, delete, update statement. So I’m directly dive in coding part, as I believe that more theory can confuse us. so I gonna explain you straight to the point.

okay so first we try to connect to the MySQL server and fetch all rows from table “test”.

require 'mysql'
con = Mysql.new('localhost', 'root', 'root', 'test')  
rs = con.query('select * from demo')
rs.each_hash do |row|
    puts row['uid'] + " " + row['name']
end
con.close

to run this basic example you need to use following command

ruby fetchallrow.rb

for insert statement you can use following code.

con.query("INSERT INTO demo values('"+id+"','"+name+"','"+mno+"')")

now you can able to perform basic insert,update and delete statement in same way.

That’s It.!

Enjoy d code.. 🙂

Get Latest Updated Data using SSE(Server-Sent-Events) Automatically

Posted on by
6

hii guys,

This is very interesting topic which i gonna share with you people.  So many times i think about how facebook/Twitter updates, stock price updates, news feeds, sport results are working?

finally i found easy and simple way to do same thing. so i just developed simple application which demonstrate usage of SSE (Sever-Sent-Events).

so i developed Latest Job Post Application, which give you latest job posted by any employee on webpage automatically.

let’s go for coding

index.html

index.php_SSE

so here i’m using SSE and in EventSource i’m passing viewpost.php file, which fetch latest record from table.

viewpost.php

viewpost.php_SSE

As a output in {topjob} div, always latest job will be printed without refreshing page.

About SSE:

A server-sent event is when a web page automatically gets updates from a server.

This was also possible before, but the web page would have to ask if any updates were available. With server-sent events, the updates come automatically.

Examples: Facebook/Twitter updates, stock price updates, news feeds, sport results, etc.

Disadvantage:

IE does not support it .. 😦

Done!

Hope you gonna like Dis. .. 🙂

Fill select box dynamically using jquery

Posted on by
3

hello ,

you may noticed in so many website while filling registration form that depend on which state you select , the next select box (city) automatically filled with city name.

This thing achieved by jquery.  let’s create it.

HTML CODE

<select id="state" name="state" onchange="getCity(this.value)">
<option>Gujarat</option>
<option>Karnataka</option>
</select>
City
<select  id="city" name="city" >
<option>Select</option>
</select>

oky now write this jquery function


function getCity(s)
{
var url="getCity.php?s="+s;
$.ajax({
url: url,
method: "post",
success: function(response){
document.getElementById("city").innerHTML = response;
}

});
}

now, create getCity.php file and write bellow code inside that. (first create voluntaryregistration table inside phpmyadmin and write connection code on top of this file)


include "inc/connect.php";
$s = $_GET['s'];
$qry = "select city from voluntaryregistration where state = '$s'";
$res = mysql_query($qry) or die(mysql_error());
while($data = mysql_fetch_row($res))
{
echo "<option value=".$data[0].">" .$data[0]. "</option>"; 

}

NOTE:

  • write jquery function inside <head> <script > … </script> </head> tag.
  • download jquery from http://code.jquery.com/jquery-1.8.3.min.js
  • add jquery file by <script src=”jquery-1.8.3.min.js > </script> on top of jquery function.

Problem:

if you have city name like “surendra nagar” then only surendra will stored in side option value ,
like <option value=”surendra” nagar> surendra nagar </option>

so if you are fetching this record by _POST or _GET variable you will get only surendra.

Solution

use this code inside php script.

while($data = mysql_fetch_row($res))
{
?>
<option value = “<?php echo $data[0]; ?>” ><?php echo $data[0]; ?></option>
<?php
}
?>

oky done!!

hope you like it!!

Fetching images from database

Posted on by
2

hii everyone!

Fetching the images from database is quite different from fetching other data from the database.
so here is code for fetching the images from table.

view.php 

<?php
mysql_connect(“localhost”,”root”,””);
mysql_select_db(“test”);
$qry = “select id from image”;
$res = mysql_query($qry) or die(mysql_error());
while($row = mysql_fetch_array($res))
{
if(!empty($row[0]))
{
echo “<img src=image.php?id=’$row[0]‘ >”;
}
}
?>

image.php

<?php
mysql_connect(“localhost”,”root”,””);
mysql_select_db(“test”);
header(“Content-type:image/jpeg”);
$query = mysql_query(“SELECT img FROM image WHERE id = “.$_GET[‘id’]);
$row = mysql_fetch_array($query);
echo $row[‘img’];
?>

DB Structure of image table

db

Create HTML Element by just Dragging Element Name

Posted on by
2

hello guys,

today i visited Wufoo website, actually this website provides facility to create online forms, it’s very good website.

but i found one drawback there that we can’t download that form.

there i seen drag and drop textbox , radio button n all html elements , we just need to drag and drop on the form area. so no need to remember element’s syntax.

so i just thought let’s write such type of code which gives you facility to just drag element-name and drop it on form area , so that element will generate on form.

code here!

first create one database and store the element syntax inside on table.

for ex:
table name -> widget

elementsyntax
<input type=”text” />

so this is our widget table.

oky now create index.php file for bellow code


<!doctype html>
<html lang="en">
<head>
<link rel="stylesheet" href="http://code.jquery.com/ui/1.9.1/themes/base/jquery-ui.css" />
<script src="http://code.jquery.com/jquery-1.8.2.js"></script>
<script src="http://code.jquery.com/ui/1.9.1/jquery-ui.js"></script>
<style>
#draggable { width: 50px; height: 50px; padding: 0.5em; float: left; margin: 10px 10px 10px 0; }
#droppable { width: 150px; height: 150px; padding: 0.5em; float: left; margin: 10px; }
</style>
<script>
var res;
$(function()
{
$( "#draggable" ).draggable();
$( "#droppable" ).droppable(
{
drop: function( event, ui ) {
var url = "widget.php";
$.ajax(
{
type: "post",
url: url,
success: function(response)
{
document.getElementById('result').innerHTML = response;
$('pre').hide();
}
});
}
});
});
</script>
</head>
<body>
<div id="draggable">
<pre>TextBox</pre>
</div>
<div id="droppable">
<p id = "result">Drop here</p>
</div>
</body>
</html>

so here, inside droppable event i used $.ajax() for sending request to widget.php file, widget.php file fetch the data from our widget table (using select query) and i just add response to result div. so there , textbox will generate and appear.!

so simple…

 

widget.php

<?php
mysql_connect("localhost","root","");
mysql_select_db("test");
$qry = "select *from widget";
$res = mysql_query($qry);
$data = mysql_fetch_row($res);
echo $data[0];
?>

 

(Note: this code will work only if internet connection is available because online jquery files are included)

okY donE!!

aaahh… Beauty of JquerY!

Njoy D codE!!

hope you Like it!

Checking availability for username in Signup form

Posted on by
2

hii guys,

you may noticed that in signup form username have check availability option. in so many website signup form when you type username and move your focus to another textfield at that time if that username is not available then one notification will come nearby username field.

so let’s code for it..

first create index.html file and write bellow code in that

index.html

<html>
<head>
<title>Signup</title>
</head>
<body>
<input type=”username” id=”username” name=”username” value=”” onBlur=”checkuname()”><span id=”username_status”> </span></td>
</body>
</html>

now, write bellow javascript code inside <script> tag.

$(document).ready(function()
{
$('#username').keyup(function()
{
var username = $(this).val();
$('#username_status').text('searching..');
if(username != "")
{
$.post('check availability.php',{username:username},function(data){
$('#username_status').text(data);
});
}
});
});

check availability.php

<?php
require ‘inc/connect.php’;
if(isset($_POST[‘username’]))
{
$u = $_POST[‘username’];
if(!empty($u))
{
$res = mysql_query(“select count(‘uname’) from signup where uname=’$u’”) or                                              die(mysql_error());
$ures = mysql_result($res,0);
if($ures == 0)
{
echo “Available”;
}
else
{
echo “Not Available”;
}
}
}
?>

so here, i used select query to search that username in my database table. if it is there in my table i will display “Not Available” message. but if it is not there in my table i will display “Available” message.

so simple.!

hope you like this.!!