[RESOLVED] Is there an easier way?

[tommy107]

Is there an easier way of do this?
Maybe using arrays?

if (isset($_POST['TripNumber']) && isset($_POST['TruckNumber'])) {
	$sql_insert = "INSERT INTO trip_info SET trip_num='".$_POST['TripNumber']."', trip_truck='".$_POST['TruckNumber']."'";
	if (isset($_POST['TrailerNumber'])) {
		$sql_insert.=", trip_trlr='".$_POST['TrailerNumber']."'";
	}
	if (isset($_POST['LoadDate'])) {
		$sql_insert.=", trip_date='".date("Y-m-d", strtotime($_POST['LoadDate']));."'";
	}
	if (isset($_POST['LoadNumber'])) {
		$sql_insert.=", trip_loadnum='".$_POST['LoadNumber']."'";
	}
	if (isset($_POST['PuNumber'])) {
		$sql_insert.=", trip_punum='".$_POST['PuNumber']."'";
	}
	if (isset($_POST['BOLNumber'])) {
		$sql_insert.=", trip_bolnum='".$_POST['BOLNumber']."'";
	}
	if (isset($_POST['ContactNumber1'])) {
		$sql_insert.=", trip_contactnum1='".$_POST['ContactNumber1']."'";
	}
	if (isset($_POST['ContactNumber2'])) {
		$sql_insert.=", trip_contactnum2='".$_POST['ContactNumber2']."'";
	}
	if (isset($_POST['ContactName'])) {
		$sql_insert.=", trip_contactname='".$_POST['ContactName']."'";
	}
	if (isset($_POST['EmptyMiles'])) {
		$sql_insert.=", trip_emptymiles='".$_POST['EmptyMiles']."'";
	}
	if (isset($_POST['LoadedMiles'])) {
		$sql_insert.=", trip_loadedmiles='".$_POST['LoadedMiles']."'";
	}
	if (isset($_POST['TotalMiles'])) {
		$sql_insert.=", trip_totalmiles='".$_POST['TotalMiles']."'";
	}
	if (isset($_POST['TripNotes'])) {
		$sql_insert.=", trip_notes='".$_POST['TripNotes']."'";
	}
	if (isset($_POST['time_stamp'])) {
		$sql_insert.=", time_stamp='".$_POST['time_stamp']."'";
	}
	if (isset($_POST['Renbursment'])) {
		$sql_insert.=", trip_remb='".$_POST['Renbursment']."'";
	}
	if (isset($_POST['ReeferTemp'])) {
		$sql_insert.=", trip_reefertemp='".$_POST['ReeferTemp']."'";
	}

Thanks,
Tommy

[bradgrafelman]

Don't know if this is easier, per sé, but it's a bit shorter and the end result is the same:

$fields = array (
  "PuNumber"  =>  "trip_punum",
  "BOLNumber" =>  "trip_bolnum",
  // etc. for all fields
);

foreach($fields as $formIndex => $sqlIndex)
    if(isset($_POST[$formIndex]))
      $sql_insert .= ", $sqlIndex='" . $_POST[$formIndex] . "'";

Note, however, that your code is vulnerable to SQL injection attacks (and/or just plain SQL errors). User-supplied data should never be placed directly into a SQL query. Instead, it must first be sanitized with a function such as [man]mysql_real_escape_string/man.

EDIT: Also.. welcome to PHPBuilder! 🙂

[djjjozsi]

INSERT INTO trip_info SET

Insert doesn't have SET clause!

In this you have a typo error, because of the " ;. "

       $sql_insert.=", trip_date='".date("Y-m-d", strtotime($_POST['LoadDate']));."'"; 

You don't need to use this version because its not a validation:

    if (isset($_POST['TripNotes'])) 

Create function and build array to define table structure instead.

[tommy107]

Insert doesn't have SET clause!

Sorry to disagree but it does, I am using it.

In this you have a typo error, because of the " ;. "

Yes I know I just found it, but thanks anyway.

And sorry each check should have been;

if (!empty($_POST['TrailerNumber'])) {
	$sql_insert.=", trip_trlr='".$_POST['TrailerNumber']."'";
}

Thanks,
Tommy

[bradgrafelman]

A quick note about [man]empty/man... it will return true for 0 and '0'. Thus, if you expect '0' to be a valid input for any of the fields, you'll have to use [man]isset/man and [man]strlen/man (or compare it to the empty string) to see if a field is truly "empty" (versus "non-zero", which is what empty() will actually test for numeric data/strings).

[tommy107]

Ok,
Then how would these be done;

if (!empty($_POST['EmptyMiles'])) {
		$sql_insert.=", trip_emptymiles='".$_POST['EmptyMiles']."'";
	}
	if (!empty($_POST['LoadedMiles'])) {
		$sql_insert.=", trip_loadedmiles='".$_POST['LoadedMiles']."'";
	}
	if (!empty($_POST['TotalMiles'])) {
		$sql_insert.=", trip_totalmiles='".$_POST['TotalMiles']."'";
	}
        if (!empty($_POST['ReeferTemp'])) {
		$sql_insert.=", trip_reefertemp='".$_POST['ReeferTemp']."'";
	}

Those could have a value of 0

Thanks,
Tommy

[tommy107]

What I am trying to do is insert a NULL into the database if nothing was entered in the form.
I have tried different things but I always get an empty string, or 0 for INT, in the database.

If you know a better way please let me know.

Thanks,
Tommy

[bradgrafelman]

As I said above:

bradgrafelman wrote:

you'll have to use isset() and strlen() (or compare it to the empty string) to see if a field is truly "empty" (versus "non-zero", which is what empty() will actually test for numeric data/strings).

As an example:

if(isset($_POST['foo']) && $_POST['foo'] != '') {

[Weedpacket]

tommy107 wrote:

Sorry to disagree but it does, I am using it.

Only according to MySQL. Everyone else who uses SQL has a different opinion.

[tommy107]

Only according to MySQL. Everyone else who uses SQL has a different opinion.

Well I am using MYSQL.

Plus using SET serves my purpose.

Is it not wises to use SET?

Thanks,
Tommy

[tommy107]

Ok, I think I got it now.
This is what I have;

$fields = array (
	  				"TrailerNumber"  =>  "trip_trlr",
					"LoadNumber"  =>  "trip_loadnum",
	  				"PuNumber" =>  "trip_punum",
					"BOLNumber"  =>  "trip_bolnum",
	  				"ContactNumber1" =>  "trip_contactnum1",
					"ContactNumber2"  =>  "trip_contactnum2",
	  				"ContactName" =>  "trip_contactname",
					"EmptyMiles"  =>  "trip_emptymiles",
	  				"LoadedMiles" =>  "trip_loadedmiles",
					"TotalMiles"  =>  "trip_totalmiles",
	  				"TripNotes" =>  "trip_notes",
					"time_stamp"  =>  "time_stamp",
	  				"Renbursment" =>  "trip_remb",
					"ReeferTemp"  =>  "trip_reefertemp",
	);

foreach($fields as $formIndex => $sqlIndex) {
	if(isset($_POST[$formIndex]) && $_POST[$formIndex] != '') {
		$sql_insert .= ", $sqlIndex='" . $_POST[$formIndex] . "'";
	}

Thanks,
Tommy

[Weedpacket]

wrote:

Is it not wises to use SET?

Well, when you move on to something else you'd have to rewrite the statements and how you put them together. That's on top of changing all the function names, if you're not using any sort of database abstraction (which you're not or you'd be using PDO as well so that you don't have to change the interface or quoting string values or escaping them).

I just thought I'd see what using a standard SQL insert statement would allow:

function quote_and_escape($string)
{
    return "'" . mysql_real_escape_string($string) . "'";
}

$fields = array(/*...*/);

$fields= array_intersect_key($fields, $_POST);
$values = array_intersect_key($_POST, $fields);
$values = array_map('quote_and_escape', $values);
ksort($fields);
ksort($values);
$insert_statement = "INSERT INTO some_table
    (" . join(',',$fields) . ")
    VALUES
    (" . join(',',$values) . ")";