Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.php > #2830

Re: Adding a record to a database

Show all headers | View raw

On 8/16/2011 5:41 AM, A.Reader wrote:
> On Tue, 16 Aug 2011 01:30:02 -0700 (PDT),
> Charles<cchamb2@gmail.com>  wrote:
>
>> Is this better?
>>
>> I still get one error message - Error: You have an error in your SQL
>> syntax; check the manual that corresponds to your MySQL server version
>> for the right syntax to use near 'Ford'', ''Crown Victoria'',
>> ''Taxicab'', ''SEP'', '2010', ''sadfasdfsadfdsf' at line 21
>>
>> =====================================
>>
>> <?php
>>
>> /***Switch statement that controls processing from
>> value of $_POST(deform)***************/
>>
>> switch ( $_POST['deform'] )
>>
>> {
>>
>> /***Case statement that acts on value of $_POST(deform)******/
>>
>> CASE $_POST['deform'] = "cab_vehicle_data_entry_add_a_vehicle":
>>
>> $con = mysql_connect("localhost","root","edward");
>>
>> if (!$con)
>>
>> {
>>
>> die("Could not connect: " . mysql_error());
>>
>> }
>>
>> function check_input($value)
>> {
>>
>> if (get_magic_quotes_gpc())
>>    {
>>    $value = stripslashes($value);
>>    }
>>
>> if (!is_numeric($value))
>>    {
>>    $value = "'" . mysql_real_escape_string($value) . "'";
>>    }
>> return $value;
>> }
>>
>> $Make = check_input($_POST['Make']);
>> $Model = check_input($_POST['Model']);
>> $Edition = check_input($_POST['Edition']);
>> $Month = check_input($_POST['Month']);
>> $Year = check_input($_POST['Year']);
>> $VIN = check_input($_POST['VIN']);
>> $Registration = check_input($_POST['Registration']);
>> $reg_exp_month = check_input($_POST['reg_exp_month']);
>> $reg_exp_year = check_input($_POST['reg_exp_year']);
>> $pax_capacity = check_input($_POST['pax_capacity']);
>> $cargo_cubic_feet = check_input($_POST['cargo_cubic_feet']);
>> $cargo_weight_lbs = check_input($_POST['cargo_weight_lbs']);
>>
>> mysql_select_db("taxicab", $con);
>>
>> $sql="INSERT INTO
>>
>> cab_vehicle (
>> cab_vehicle_make,
>> cab_vehicle_model,
>> cab_vehicle_edition,
>> cab_vehicle_month,
>> cab_vehicle_year,
>>
>> cab_vehicle_VIN,
>> cab_vehicle_registration_number,
>> cab_vehicle_reg_exp_month,
>> cab_vehicle_reg_exp_year,
>>
>> cab_vehicle_pax_capacity,
>> cab_vehicle_cubic_feet_cargo,
>> cab_vehicle_cargo_weight)
>>
>> VALUES
>>
>> ('$Make',
>> '$Model',
>> '$Edition',
>> '$Month',
>> '$Year',
>> '$VIN',
>> '$Registration',
>> '$reg_exp_month',
>> '$reg_exp_year',
>> '$pax_capacity',
>> '$cargo_cubic_feet',
>> '$cargo_weight_lbs')";
>>
>> if (!mysql_query($sql,$con))
>>
>> {
>>
>> die("Error: " . mysql_error());
>>
>> }
>>
>> echo "1 record added";
>>
>> mysql_close($con);
>>
>> break;
>>
>> }
>>
>> /******End of CASE statement start of next one*************/
>>
>> ?>
>
> Don't use the INSERT var1,var2,var3,var4,var5 VALUES
> val1,val2,val3,val5 style -- it's prone to misalignment errors
> when you're doing more than one or two values.   As a matter of
> good practice, always use the SET var1=val1, var2=val2, var3=val3
> form instead.  That way there's no mistake about which value is
> getting assigned to which var (did you notice the 'error'?)
>

Terrible advice.  He is doing it the correct way, according to the SQL 
standard.  SET in an INSERT statement is non-standard and AFAIK only 
supported by MySQL (and then only when not running in STRICT mode).

> Further, do all your testing for the record in one lump, not on a
> per-field basis.  The reason being that unless your validation
> routine can see everything at once, the person could enter
> something like 'Make="Chevrolet", Model="Crown Vic"' and you
> wouldn't be able to catch it.
>

There is nothing wrong with such a search.  It will just not return any 
rows.  Trying to validate all possible combinations like this will add 
unnecessary complexity to the code.

The purpose of validation at this level is not to ensure that 
combinations are valid - but that the field itself is the correct type 
and possibly a reasonable value.

> To find mysql errors such as the one you're getting, change your
>
> die("Error: " . mysql_error());
>
> to
>
> die('Error:<br>'.$sql.'<br>'.mysql_error() ) ;
>
> That way, when you get a mysql error, you're looking at both the
> text of the error message and the broken mysql statement, which
> you can then examine to see where  the problem is.

Better yet - get rid of the die() all together and handle the error 
gracefully.  Then ask about the SQL problem in the appropriate newsgroup.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Back to comp.lang.php | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Adding a record to a database Charles <cchamb2@gmail.com> - 2011-08-15 05:35 -0700
  Re: Adding a record to a database Jerry Stuckle <jstucklex@attglobal.net> - 2011-08-15 08:46 -0400
  Re: Adding a record to a database JohnT <john@example.com> - 2011-08-15 13:45 +0000
  Re: Adding a record to a database A.Reader <anonymously@example.com> - 2011-08-15 14:11 -0400
    Re: Adding a record to a database sheldonlg <sheldonlg@thevillages.net> - 2011-08-15 21:02 -0400
      Re: Adding a record to a database Jerry Stuckle <jstucklex@attglobal.net> - 2011-08-15 21:50 -0400
        Re: Adding a record to a database Charles <cchamb2@gmail.com> - 2011-08-16 01:30 -0700
          Re: Adding a record to a database A.Reader <anonymously@example.com> - 2011-08-16 05:41 -0400
            Re: Adding a record to a database Jerry Stuckle <jstucklex@attglobal.net> - 2011-08-16 06:23 -0400
              Re: Adding a record to a database A.Reader <anonymously@example.com> - 2011-08-16 09:11 -0400
                Re: Adding a record to a database Tim Streater <timstreater@greenbee.net> - 2011-08-16 14:51 +0100
                Re: Adding a record to a database A.Reader <anonymously@example.com> - 2011-08-16 10:42 -0400
                Re: Adding a record to a database Tim Streater <timstreater@greenbee.net> - 2011-08-16 16:58 +0100
                Re: Adding a record to a database Jerry Stuckle <jstucklex@attglobal.net> - 2011-08-16 17:21 -0400
          Re: Adding a record to a database Jerry Stuckle <jstucklex@attglobal.net> - 2011-08-16 06:17 -0400
        Re: Adding a record to a database sheldonlg <sheldonlg@thevillages.net> - 2011-08-16 16:13 -0400
          Re: Adding a record to a database Jerry Stuckle <jstucklex@attglobal.net> - 2011-08-16 17:24 -0400

csiph-web