[SOLVED] Can you guys improve this?

[ERuiz]

Hello everyone,

 

I am extremely new to PHP and thus I am in no way, a programmer. I created a custom page which basically receives a data feed from an external program and processes the data and writes it to a MySQL database.

It is extremely basic and I was wondering if it can be improved... The code is below and I have made comments on it, to explain what each section does. I am sure it can be improved and made more efficient. If you think you can shed some tips, please feel free to reply. Thanks in advanced!

 

<?php
//  Retrieve all data submitted
$pilot_id = @$_POST["pilot_id"];
$pilot_name = @$_POST["pilot_name"];
$position = @$_POST["position"];
$position_google = @$_POST["position_google"];
$ac_type = @$_POST["ac_type"];
$origin_icao = @$_POST["origin_icao"];
$origin_name = @$_POST["origin_name"];
$destination_icao = @$_POST["destination_icao"];
$destination_name = @$_POST["destination_name"];
$flight_number = @$_POST["flight_number"];
$altitude = @$_POST["altitude"];
$heading = @$_POST["heading"];
$vs = @$_POST["vs"];
$true_heading = @$_POST["true_heading"];
$ias = @$_POST["ias"];
$mach = @$_POST["mach"];
$tas = @$_POST["tas"];
$gs = @$_POST["gs"];
$zfw = @$_POST["zfw"];
$fuel_used = @$_POST["fuel_used"];
$fuel_available = @$_POST["fuel_available"];
$e1n1 = @$_POST["e1n1"];
$e1n2 = @$_POST["e1n2"];
$e2n1 = @$_POST["e2n1"];
$e2n2 = @$_POST["e2n2"];
$e1ff = @$_POST["e1ff"];
$e2ff = @$_POST["e2ff"];
$autopilot = @$_POST["autopilot"];
$flaps = @$_POST["flaps"];
$transponder = @$_POST["transponder"];
$landing_lights = @$_POST["landing_lights"];
$strobe_lights = @$_POST["strobe_lights"];
$nav_lights = @$_POST["nav_lights"];
$sim_rate = @$_POST["sim_rate"];
$pause_mode = @$_POST["pause_mode"];
$landing_gear = @$_POST["landing_gear"];
$spoilers = @$_POST["spoilers"];
$current_wx = @$_POST["current_wx"];
$status = @$_POST["status"];
$destination_wx = @$_POST["destination_wx"];
$flown_route = @$_POST["flown_route"];
$dist_planned = @$_POST["dist_planned"];
$dist_flown = @$_POST["dist_flown"];
$ete = @$_POST["ete"];
$eta = @$_POST["eta"];
$last_update = date('Y-m-d H:i:s');

//  Connect to the database
$host = 'localhost';
$user = 'user';
$pass = 'password';
$db = 'database';
mysql_connect($host,$user,$pass) or die(mysql_error());
mysql_select_db($db) or die(mysql_error());

//  Run the SQL queries
$query = "SELECT * FROM liveacars WHERE pilot_id = '$pilot_id' ";
$result = mysql_query($query) or die("SQL query failed");

//  Check to see if there is a record already in the database
//  If there are no records, then it will insert a new record
if (mysql_num_rows($result) == 0) {

$add = "INSERT INTO liveacars 
(id, pilot_id, pilot_name, position, position_google, ac_type, origin_icao, 
origin_name, destination_icao, destination_name, flight_number, altitude, 
heading, vs, true_heading, ias, mach, tas, gs, zfw, fuel_used, fuel_available, 
e1n1, e1n2, e2n1, e2n2, e1ff, e2ff, autopilot, flaps, transponder, landing_lights, 
strobe_lights, nav_lights, sim_rate, pause_mode, landing_gear, spoilers, 
current_wx, status, destination_wx, flown_route, dist_planned, dist_flown, ete, 
eta, last_update)
VALUES 
('','$pilot_id','$pilot_name','$position','$position_google','$ac_type',
'$origin_icao','$origin_name','$destination_icao','$destination_name',
'$flight_number','$altitude','$heading','$vs','$true_heading','$ias','$mach',
'$tas','$gs','$zfw','$fuel_used','$fuel_available','$e1n1','$e1n2','$e2n1',
'$e2n2','$e1ff','$e2ff','$autopilot','$flaps','$transponder','$landing_lights',
'$strobe_lights','$nav_lights','$sim_rate','$pause_mode','$landing_gear',
'$spoilers','$current_wx','$status','$destination_wx','$flown_route',
'$dist_planned','$dist_flown','$ete','$eta','$last_update')";
mysql_query($add) or die(mysql_error());

//  If there is a record, then update that record
} else {

$update = "UPDATE liveacars 
SET pilot_id = '$pilot_id', 
pilot_name = '$pilot_name',
position = '$position',
position_google = '$position_google',
ac_type = '$ac_type',
origin_icao = '$origin_icao',
origin_name = '$origin_name',
destination_icao = '$destination_icao',
destination_name = '$destination_name',
flight_number = '$flight_number',
altitude  = '$altitude',
heading = '$heading',
vs = '$vs',
true_heading = '$true_heading',
ias = '$ias',
mach = '$mach',
tas = '$tas',
gs = '$gs',
zfw = '$zfw',
fuel_used = '$fuel_used',
fuel_available = '$fuel_available',
e1n1 = '$e1n1',
e1n2 = '$e1n2',
e2n1 = '$e2n1',
e2n2 = '$e2n2',
e1ff = '$e1ff',
e2ff = '$e2ff',
autopilot = '$autopilot',
flaps = '$flaps',
transponder = '$transponder',
landing_lights = '$landing_lights',
strobe_lights = '$strobe_lights',
nav_lights = '$nav_lights',
sim_rate = '$sim_rate',
pause_mode = '$pause_mode',
landing_gear = '$landing_gear',
spoilers = '$spoilers',
current_wx = '$current_wx',
status = '$status',
destination_wx = '$destination_wx',
flown_route = '$flown_route',
dist_planned = '$dist_planned',
dist_flown = '$dist_flown',
ete = '$ete',
eta = '$eta'
WHERE pilot_id = '$pilot_id' ";
mysql_query($update) or die(mysql_error());

}
?>

[kenrbnsn]

I would simplify your code in the following manner:

<?php
//  Retrieve all data submitted
$qtmp = array();
foreach($_POST as $key => $value) // use a foreach loop to get all the $_POST variables, skipping the submit button (change the key's name to whatever is in your form)
if ($key != 'submit')
	$qtmp[] = $key . " = '" . mysql_real_escape_string(trim(stripslashes($val))) . "'"; // You should always use mysql_real_escape_string to guard against SQL injection attempts

$last_update = date('Y-m-d H:i:s');

//  Connect to the database
$host = 'localhost';
$user = 'user';
$pass = 'password';
$db = 'database';
mysql_connect($host,$user,$pass) or die(mysql_error());
mysql_select_db($db) or die(mysql_error());

//  Run the SQL queries
$query = "SELECT * FROM liveacars WHERE pilot_id = '" . mysql_real_escape_string($_POST['pilot_id']) . "'";
$result = mysql_query($query) or die("SQL query failed");

//  Check to see if there is a record already in the database
//  If there are no records, then it will insert a new record
if (mysql_num_rows($result) == 0) {
$qtmp[] = "last_update = '" . $last_update . "'";
$add = "INSERT INTO liveacars set " . implode(', ',$qtmp); // use the alternative syntax of the INSERT query
        echo $add . '<br>'; // debug line -- so you can see what the query looks like
mysql_query($add) or die("Problem with ADD query <pre>$add</pre><br>" . mysql_error());

//  If there is a record, then update that record
} else {
$update = "UPDATE liveacars set " . implode(', ',$qtmp) . "WHERE pilot_id = '" . mysql_real_escape_string($_POST['pilot_id']) . "'";
        echo $update . '<br>'; // debug line -- so you can see what the query looks like
mysql_query($update) or die("Problem with UPDATE query <pre>$update</pre><br>" . mysql_error());
}
?>

 

Ken

[HeyRay2]

kenrbnsn, you beat me to the punch...

 

Nice work!

 

[ERuiz]

Hello kenrbnsn!!!

 

Thanks for this, I really appreciate it! But before I use your version, I need to let you know that the data is NOT retrieved from a form which a person submits. It's submitted by a program and the program sends the data directly via _POST to the php file.

 

For this reason, there won't be a "submit" value being sent by the program, which you would get as if it was being sent by a form.

[kenrbnsn]

Then just remove this line

<?php
if ($key != 'submit')
?>

 

Ken

[ERuiz]

Works like a charm, buddy!  Thanks a million! So, the way you wrote the script, it processes my data in a faster and more efficient way, than the way I had it before?