| Re: Adding a record to a database [message #175124 is a reply to message #175123] |
Tue, 16 August 2011 13:11   |
A.Reader
Messages: 15
Registered: December 2010
Karma:
|
Junior Member |
|
|
On Tue, 16 Aug 2011 06:23:06 -0400,
Jerry Stuckle <jstucklex(at)attglobal(dot)net> wrote:
> On 8/16/2011 5:41 AM, A.Reader wrote:
>> On Tue, 16 Aug 2011 01:30:02 -0700 (PDT),
>> Charles<cchamb2(at)gmail(dot)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).
Why would he need -or want- to eliminate MySQL-specific
extensions, unless he's planning to port the code? What would
the practical payoff be?
>
>> 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.
Aren't we talking about validation at INSERT time, not SELECT
time? I thought we were, but I might be confused.
>
>> 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.
We're talking about the debugging phase here, aren't we? There
shouldn't _be_ any sql errors left by rollout.
And, from the error msg, the error doesn't represent an "SQL
problem" as such but rather a plain syntax error. My suggestion
was aimed at helping him improve his PHP code such that he could
then find the error. That seems to be within the remit of this
group.
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]