Merge/Join Fields and Rows of Tables in MySQL

I was asked the other day by someone about some software that could Join/Merge two tables (fields and rows). I thought it’d be pretty easy with some MySQL query but couldn’t find any solution. Then I turned to google in search of any software or any other source that could help me out… but still couldn’t find one. The closest I could get was the case of MySQL Merge Table tool (that merges the records of two identical tables) or the UNION which also required for the number of columns and types to be identical. This wasn’t even remotely what I had desired. So I thought of writing my own script…. Here’s my meager effort.

mysql_connect("localhost", "root", "") ;
mysql_select_db("aaatest") ;

$newTable = "tbltest_12" ;
$newTablePK = "id" ;
$joinTable['left'] = "tbltest1" ; // this table will be on the left side of the join. As a rule, the table with most records whould be set here otherwise some records will be left out.
$joinField['left'] = "id" ; // field on the basis of which this table will be joined to the other table
$joinTable['right'] = "tbltest2" ;
$joinField['right'] = "id" ; // join field for the table on the right side.
$includePrevPK = true ; // if set to true, primary keys of previous tables will also be included in new table
$prefixTableName = true ; // if set to true, previous tablename will be prefixed to every fieldname

$strCreate = "CREATE TABLE `$newTable` (
`$newTablePK` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ," ; // a new primary key for the new table

foreach($joinTable as $side=>$table){
	$str = "SHOW COLUMNS FROM ".$table ;
	$rs = mysql_query($str) or die(mysql_error()) ;
	while($row = mysql_fetch_assoc($rs)){
		if($row['Key'] == "PRI"){ // incase of primary key
			if($includePrevPK){
				$arrFieldsSELECT[] = "`".$joinTable[$side]."`.`".$row['Field']."` AS `".$joinTable[$side]."_".$row['Field']."`" ;
				// Primary Key will be prefixed with tablename in any case
				$arrFieldsINSERT[] = "`".$joinTable[$side]."_".$row['Field']."`" ;
				$strCreate .= "`".$joinTable[$side]."_".$row['Field']."` " ;
			}else{
				// incase Primary Key is not to be included in the new table, skip the rest of the loop iteration
				continue;
			}
		}else{
			// prefix tablename to fieldfiend
			$arrFieldsSELECT[] = "`".$joinTable[$side]."`.`".$row['Field']."` AS `".($prefixTableName ? $joinTable[$side]."_" : "").$row['Field']."`" ;
			$arrFieldsINSERT[] = "`".($prefixTableName ? $joinTable[$side]."_" : "").$row['Field']."`" ;
			$strCreate .= "`".($prefixTableName ? $joinTable[$side]."_" : "").$row['Field']."` " ;
		}
		$strCreate .= $row['Type']." " ;
		$strCreate .= ($row['Null']!="No" ? "NULL" : "")." " ;
		$strCreate .= ($row['Default']!="" ? "DEFAULT '".$row['Default']."'" : "") ;
		$strCreate .= " ," ;
	}
}
$strCreate = substr($strCreate, 0, strlen($strCreate)-1) ; // to remove a (,) comma from the end of string
$strCreate .= ")TYPE=MyISAM ;" ;
// a new table is created here
mysql_query($strCreate) or die(mysql_error());

// records from the two tables will be fetched here and a new INSERT query will be made for insertion in new table
$strFieldsSELECT = implode(",", $arrFieldsSELECT) ;
$strSelect = "
SELECT $strFieldsSELECT FROM `".$joinTable['left']."`
LEFT JOIN `".$joinTable['right']."` ON `".$joinTable['left']."`.`".$joinField['left']."` = `".$joinTable['right']."`.`".$joinField['left']."`" ;

$rs = mysql_unbuffered_query($strSelect) or die(mysql_error()) ;
while($row = mysql_fetch_assoc($rs)){
	$strVal = implode("','", $row) ;
	$strValue .= "('".$strVal."')," ;
}
$strInsert = implode(",", $arrFieldsINSERT) ;
$strInsert = "INSERT INTO `$newTable` (".$strInsert.") VALUES" ;
$strValue = substr($strValue, 0, strlen($strValue)-1) ; // to remove a (,) comma from the end of string
echo $strInsert .= $strValue ;
mysql_query($strInsert) or die(mysql_error()) ;

I’ve used mysql_unbuffered_query() in this code, because, due to JOIN, the resultset could get to enormous size. mysql_unbuffered_query() doesn’t keep the whole resultset of the query in the buffer. It fetches one row at a time, thus reducing the memory consumption. This function is very effective for large resultsets.

Hope it helps someone. Also please leave a comment as your feedback, so that I can further improve this code. Its improved version will be online as soon as I come up with any brilliant ideas.

Powered by ScribeFire.

Change Filenames to Upper or Lower Case (in bulk) in PHP

Atlast I overcame my laziness and wrote a long overdue script to change filenames. Before writing this code, I couldn’t find a reasonable solution in PHP despite a major search operation. The need, infact, arose when I copied my PHP code from windows to linux machine, and it refused to show alot of images, which never gave any problem in windows as windows isn’t case sensitive. Here’s the script to change filenames from lower case to upper case and vice versa.

//$DIRNAME = "/srv/www/htdocs/kbs/pollimages" ; // for linux
$DIRNAME = "C:\\gohar\\gohar data\\ngc\\ngckbs\\pollimages" ;
$TO = "LOW" ; // UP or LOW
$SPECIAL_CHAR_REPLACE = "_" ; // To replace the special characters with
$REMOVE_SPECIAL_CHAR = true ; // will replace special characters with the value given in $SPECIAL_CHAR_REPLACE, if set to true
$REMOVE_REDUNDENCY = true ; // will remove repetitive $SPECIAL_CHAR_REPLACE, if set to true
$CONCAT = "__x_x__" ; // for temporarily replace 

$dir = dir($DIRNAME);
while (false !== ($old_filename = $dir->read())) {
	if(filetype($DIRNAME."/".$old_filename) == "file"){
		// change case
		$new_filename = ($TO == "UP" ? strtoupper($old_filename) : strtolower($old_filename)) ;
		// replace special characters
		if($SPECIAL_CHAR_REPLACE){
			$new_filename = ereg_replace("[^a-z0-9.]" , $SPECIAL_CHAR_REPLACE , $new_filename);
		}
		// remove repetition after replacement
		if($REMOVE_REDUNDENCY){
			$new_filename = ereg_replace("[$SPECIAL_CHAR_REPLACE]{1,}" , $SPECIAL_CHAR_REPLACE , $new_filename);
		}
		// remove replacement from end
		if(substr($str , -1) == $SPECIAL_CHAR_REPLACE){
			$new_filename = substr($new_filename , 0 , -1);
		}
		// first rename
		@rename($DIRNAME."/".$old_filename, $DIRNAME."/".$CONCAT.$new_filename) ;
		// second rename
		@rename($DIRNAME."/".$CONCAT.$new_filename, $DIRNAME."/".$new_filename) ;
		echo $DIRNAME."/".$new_filename."<br>" ;
	}
}
$dir-&gt;close();

$CONCAT is required because in windows, atleast in my case, it doesn’t change the case of file if the filename is to be kept the same, as it doesn’t consider it a different filename. So I had to rename it using $CONCAT and then remove this concatenation. This gave me a filename with changed case, thus changing the filename to lower case or upper case. This script also removes special characters and replaces those with any mentioned replacement in $SPECIAL_CHAR_REPLACE and then removes redundency of replacement character.

Hope it serves someone’s purpose.

Fetch Fields (of one row) from a MySQL Table

I was sick and tired of writing small queries where I had to retrieve one or few or even all records of one row against an id. E.g. when I had a userid and I had to fetch firstname, lastname and/or email against that id, I had to write a query each time and then repeat that mysql_query() & mysql_fetch_assoc() over and over again.

I then decided to write a function that helped me alot and reduced my coding time by huge amount. Here’s the function.

function getFieldsFromTable($fields, $table, $where_val, $where_field = "id"){
	$q = "SELECT $fields FROM $table WHERE $where_field = '$where_val'" ;
	if($row = mysql_fetch_assoc(mysql_query($q))){
		if(strpos($fields,",") || $fields == "*"){ // has more than one fields
			return $row ;
		}else{
			return $row[key($row)] ;
		}
	}
}

And here’s a list of ways I used it in.

$email = getFieldsFromTable(“email”, “users_table”, 8 ) ;
When I knew that id is the field name I’ve to search against.

list($firstname, $lastname) = getFieldsFromTable(“firstname,lastname”, “users_table”, 8, “id”) ;
Here I knew that “WHERE id=8″ is the clause I’m using it against, but still I mentioned “id”. The function returns an array which I’m receiving using list().

$arr_userfields = getFieldsFromTable(“*”, “users_table”, “me@mydomain.com”, “email”) ;
This will return an array of all the fields against “WHERE email = ‘me@mydomain.com’”.

$counter = getFieldsFromTable(“count(*) as counter”, “users_table”, “Gohar”, “firstname”) ;
This will return the count of all the records “WHERE firstname =
‘Gohar’”.

Another PHP function, in the conjunction of which I used this function of mine, and got excellent results, is

extract(getFieldsFromTable(“firstname,lastname”, “users_table”, 8), EXTR_PREFIX_ALL, “user”) ;
This is a very powerful function of PHP that creates variables of all the elements of an array using their indexes and as an option (e.g. EXTR_PREFIX_ALL), concats the variable names with a string that you mention, here “users”… and returns variables (e.g. $user_firstname & $user_lastname).

This function can further be modified to accept multiple tables and multiple fields for WHERE CLAUSE.

Powered by ScribeFire.

Sunni vs. Shi’ites & Stretegic Superiority of Israel??? What is this Bush administration upto?

http://www.haaretz.com/hasen/spages/894644.html

U.S. officials have said the package – which was announced last month and must still be approved by Congress – is designed to reassure Israel and Sunni Muslim Gulf countries of Washington’s commitment to the Middle East despite its problems in Iraq. It would also strengthen the Gulf nations in the face of the growing clout of Shi’ite Iran and its nuclear program.

What’s this Bush Administration upto? Are they trying to create peace in the world or start a global Sunni-Shi’ite conflict? Have they not had enough with Palestine vs. Israel? Pakistan vs. India? Muslims vs. Hindus? Muslims vs. Jews? Muslims vs. Christians? Was there any need to spread the fire of this another sectarian conflict?

If Bush is trying to play the policeman of the world, atleast he should act like one. A policeman’s job is to keep the conflicts down, not to heat those up.

They are certainly showing creativity atleast. Earlier, it used to be “Axis of Evil – Iran, Syria and South Korea”…. and now they’ve come up with another terminology… “an Axis of Cooperation between Iran, Syria, Hezbollah, Islamic Jihad and Hamas”.

Olmert has said he understands the U.S. need to bolster Saudi Arabia in facing Iran. “The increase in military aid to Israel would guarantee its strategic superiority,” Olmert has said, despite upgrades to other Arab countries in the region.

Militarily superior Israel??? Because it waged a war on Arabs back in sixties? Because it bombed Lebanon? Because it’s been killing innocent Palestinians for decades? Because it bombed Iraq’s nuclear plants while itself possessing nuclear technology? Because its threatening to bomb Iran’s nuclear facilities which has signed NPT agreement? Because it is keeping thousands of innocent Palestinians in the desert, not letting them through Gaza to their homes, stranded between Egypt and Israel, withing letting them have food and medical facilities??? …. well, you’ve got a point here Mr. Olmert. Bravo! Bravo! Bravo!

Powered by ScribeFire.

Calculating Age from Date of Birth in MySQL

I was doing some experiments the other day with MySQL and wrote some interesting queries to calculate age using MySQL. Here you go.

SELECT DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(),”1978-03-28″)), ‘%Y’)+0 AS age

1:- DATEDIFF(NOW(),”1978-03-28″)
      This function DATEDIFF() returns difference of two dates in days, e.g. DATEDIFF(“1978-04-28″, “1978-03-28″) will return 31 days. So by using NOW() i.e. current date, in the above query, we get, say, 10744 days.

2:- FROM_DAYS(10744)
      retuns the date starting from 0000-00-00 i.e. since year 0… so this function outputs “0029-06-01″, i.e. the difference between two dates “1978-03-28″ & “2008-08-27″ is precisely 29 years, 6 months & 1 day. We need just years, so we use

3:- DATE_FORMAT(“0029-06-01″, %Y) +0
      and it returns us 29 as an integer value.

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(“1978-03-28″)), ‘%Y’)+0 AS age

1:- TO_DAYS(“1978-03-28″)
      The only new function in this query is TO_DAYS(date), which converts our date to number of days starting from year 0 i.e. the opposite of function FROM_DAYS(days). So this gives us 722536 days. The other function TO_DAYS(NOW()) returns us 733280 days (for 2007-08-27). Subtracting the two, we get 733280 – 722536 = 10744 days. From here on, we move on to step 2 in the above scenario.


SELECT EXTRACT(YEAR FROM (FROM_DAYS(DATEDIFF(NOW(),”1978-03-28″))))+0 AS age

1:- EXTRACT(YEAR FROM “0029-06-01″) +0
     Taking the queue from step 2 in the first scenario, FROM_DAYS(10744) we apply another function. EXTRACT(date) which extracts a part from the given date as per the format, here YEAR, and it returns us 29 as an integer value.

This last query using EXTRACT() method is a bit slower than the one using DATE_FORMAT() – (.0001 sec). All these queries return the age in years (as a numeric value).

Powered by ScribeFire.

Simple User Search in PHP

A friend of mine asked me to give him a sample code of some sort for search. He’s a newbie to PHP. I wrote him a piece of code for convenient search and he was like ‘AWSOME!!!’. I thought it was such a simple script that it took me less than 5 minutes to type.. and he was still very happy…. so that gave me an idea to publish it here… maybe it could help some newbies looking for simple search scripts.

/* ======== PHP PART ========== */
if($_POST['Search']){
	$searchclause = "" ;
	if($_POST['user_name']){
		$searchClause .= " AND name LIKE '%".$_POST['user_name']."%'" ;
	}
	if($_POST['email']){
		$searchClause .= " AND email LIKE '%".$_POST['email']."%'" ;
	}
	if($_REQUEST['country']){
		$searchClause .= " AND country = '".$_POST['country']."'" ;
	}
	$searchClause = substr($searchClause, 5) ; // remove first instance of " AND"
	$searchClause = " WHERE " . $searchClause ; // prefix with " WHERE "
	$qry = "SELECT * FROM users" . $searchClause ; // suffex the $qry (query) with $searchclause } // end if Search
}
/* ======== PHP PART ========== */

<!– ======== HTML PART ========== –>

<h3>User Search</h3>
<form name=”testForm” method=”post” action=”somepage.php”>
User Name: <input type=”text” name=”user_name” id=”user_name” size=“20″ />
Email: <input type=”text” name=”email” id=”email” size=”35″ />
Country: <select name=”country” id=”country”>
<option value=””>Select a Country </option>
<option value=”GER”>Germany </option>
<option value=”KSA”>Saudi Arabia <option>
<option value=”PAK” selected>Pakistan </option>
<option value=”UK”>United Kingdom </option>
<option value=”US”>United States </option>
<option value=”OTHER”>Other </option>
</select>
<input type=”submit” name=”Search” id=”Search” value=”Search” />
</form>

<!– ======== HTML PART ======== –>

And that’s about it. This form is supposed to submit itself to the same page.

Powered by ScribeFire.

Who were they? Where have they gone?

http://daily.urdupoint.com/todayColumn.php?column_id=3874&page=6&page1=6&writer_id=110&date1=2007-07-16

I literally cried after reading this article. I thank to Almighty Allah that He gave me sufficient enough wisdom to oppose this military action and atleast I’ll not hold myself culprit that I sided with the govt. And I can not imagine how those ppl would be feeling who had sent messages and calls to GEO and other networks to ASK govt to attach Lal Masjid and kill all those in there. I couldn’t believe whether those all were Muslims who were asking to kill those bearded muslims who offered 5 times prayers.. and those muslim sisters whom noone had ever seen.. who were so pios…

Please Allah forgive everyone.. and bless those who died for you…

In the end, I wish those soldiers of Pakistan Army who took part in this operation, had resigned instead of attacking these ppl who were fighting for Allah’s rule in this country instead of US and Dajjal and Jews and Shaitan.

Allah please forgive me as well as I don’t have enough courage to stand against the oppressive govt like this pios ppl. Ameen.

powered by performancing firefox

Another Day At Flying

It had been almost a month since we last went for flying, so I was getting kind of desperate. Along came Sunday and I got ready early morning, collected the flying gear and opened to main door to load the gear in my car….. and uuuuhhhhhhhhh!!!

It was dense fog outside. Visibility = 10 meters approx. It was so much demoralizing that the whole day was gloomy for me (apart from the apparent effect of fog). Next morning, I was a bit apprehensive, however, the weather was still the same. The night was even more foggy as we made the mistake of dining out. On our way back, Haroon had his head stuck outside the window, was telling me if I was driving on the road or off it. We traveled at around 30 km/hr on a virtually empty road. We had our cell-phones stuck in our socks… anything could happen at anytime at this speed on a lonely track at almost midnight … Anyways, getting back to flying, Monday also passed by… same gloomy day.

Tuesday was a pleasant and surprisingly bright day. I wasted no time in thinking of going or not to go, and was on the road in no time at all. Before reaching the landing strip, I’d thought I was late… but there were still quite a few flyers out there… a noteworthy presence was of Mr. Adeem (remember my first post? The Hobby-Lobby guy we bought our plane from). He, unexpectedly, recognized me immediately and was kind enough to ask about my flying-level. When I took the plane off, I immediately realized that I had forgot to trim the controls of RC. The plane started doing nasty things.. was flying sideways and nose-down. Adeem bhai came rushing thinking something bad was going on, took the RC from me and trimmed it personally. Although it was easy for me to do it myself, but his gesture was very encouraging and a pleasant one.

I was pretty comfortable with the plane then on. Slow breeze was yet strong enough for the plane. It was always going up into the wind. Did some aerobatics and there came the moment every flyer and batsman is scared of. The moment of in-decisiveness. I was in the mood of a low fly-past infront of me, but then at the very last moment, I decided to do a touch-n-go. But the angle wasn’t the best one. It was angling across the landing strip. But by the time I had realized that it wasn’t the best of the approaches, the plane was very close to a side-pole along the strip – too close for the comfort. The speed was dead zero. I immediately upped the throttle and the plane. It narrowly flew above the pole, just 5 ft above ground level. The speed wasn’t very fast so the next thing the plane did was, gradually come down sideways. I employed some serenity and leveled the plane and took it off again and there it was… safe. The point to consider is that all this was happening just 5-6 feet above ground-level, which is very very low for a plane. Everyone around me was numb… I realized it when I had made an almost perfect landing of the plane in my very next approach. Everyone around me cheered and clapped (to my surprise) and the biggest surprise was of the Grand Master, Mr. Adeem. He cheered as well and complemented the narrow recovery. This was certainly very encouraging.

It was my nastiest stuff with the plane so far . Sab was almost stunned when I told him about it the next day but was relieved to find out of the well-being of the plane.

The Title should’ve been: Another [Almost Desasterous] Day At Flying

powered by performancing firefox

Improved Flying Skills

I seem to be getting bored with the trainer plane now. Its become so easy now that I feel like playing a video game. 360s… rollovers… mid-air upwards stall… no big deal now… atleast with this plane. Although its not made to do a mid-air upwards stall, but still I managed it… for like 5-6 seconds. I tried to do a few more aerobatics like side-ways flying… but its really asking too much from this plane.

In short, the day was pretty boring but for one accident though. There was another flier with his trainer plane, just a couple of days old. The guy had his instructor with him. His first attempt of takeoff was near perfect, flying was also good but the instructor took control for the landing. After refueling, his next attempt of takeoff was terrible? Honestly, terrible. He took off the plane after just 10 feets from start. Literally 10 ft. I was like… huh??? And whats more, it was 90 degrees from the surface. What happened next was bound to happen. Momentum was never enough, so it turned sideways and then downwards after a climb of just 40-50 ft and rushed downwards. Instructor tried to take control but it was already late enough. It hit the ground with a bang and I felt something shatter inside me. Now imagine the look on the face of the flier. I felt so sad that couldn’t bear a look at his face.

Other than this incident, this weekend was a pretty boring day for flying.

However, next day, I went to see another Aero Modelling shop. This guy, Rehan, gave me his visiting card when he first met me at flying zone. He was there to test fly a few of his planes. His shop wasn’t a very fancy one… but it was stuffed with planes. Hardly any place to stand. Rehan is a very friendly person. Gave me full time while I was there. Also gave quite a few handy tips on the selection of next plane. Sab and I are going to get our now model hopefully from him. Its just a matter of WHEN now.

powered by performancing firefox

Technical Specifications of Fire Wall – The RC Plane

Structure Info

Model Name	:	World Star – 40F
Wing Span	:	67.0 in / 1700 mm
Wing Area	:	733 sq in / 47.3 sq dm
Flying Weight	:	5.5 – 6 lbs / 2500 – 2800 g
Fuselage Length	:	53.0 in / 1350 mm
Engine Required	:	2-stroke 0.40
		4-stroke 0.52
Radio Required	:	4-channel radio w/ 5 servos
Semi-symmetrical airfoil Detachable stabilizer Pre-hinged flaps for smoother landing Top quality balsa and plywood construction

Engine Info

Engine Name	:	ASP XL .46A
Engine Type	:	2-Stroke
Displacement	:	0.46 cu. in.
Cylinders	:	1
General Specs
Bore: 0.880 (in.) Stroke In: 0.760 (in.) Shaft: 1/4 x 28 thread Low RPM: 2000 High RPM: 12800 1.430 bhp @ 12000 RPM Weight (no muffler): 14.70 oz Ball bearings

RC Info

Frequency: FM 40.665 MHz
Model: Hitec Laser 4

• 4 Channel FM Transmitter
• All Channel Servo Reversing
• ATV on Ch. 1 and 2
• Ch. 1 and 2 “Elevon” Mixing Option
• Ch. 2 and 4 “V-tail” Mixing Option
• 5 LED Power Meter
• Trainer Switch
• Ergonomic Case
• Low Batter Alarm
• Supreme 8 Ch. Receiver
• 4 HS-322 Servos (Hitec)
• Rechargeable Transmitter and Receiver Batteries (with AC Charger)

Technorati Tags: ASP .46, RC Plane Engine