| Re: Adding a record to a database [message #175145 is a reply to message #175124] |
Tue, 16 August 2011 21:21   |
Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma:
|
Senior Member |
|
|
On 8/16/2011 9:11 AM, A.Reader wrote:
> 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?
>
Well, for one thing, if he gets on a MySQL server which enforces STRICT
SQL standards.
And it's always a good idea to get in the habit of using
standards-compliant code. You never know when someone may want you to
port it to another database - or even if you want to start learning
another database.
There is no problem with using standards-compliant code here. It is
very easy to keep things straight if you format your code properly.
>>
>>> 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.
>
Yes, we are.
>>
>>> 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 when you have to go back and change the code to get rid of all the
die() statements, you add another possibility for errors being
introduced - or even forget to do it.
Better to not do it in the first place.
> 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.
>
Please show me where in the PHP manual it documents SQL errors - or even
the syntax of a SQL statement.
The final solution may be in PHP code - but the ERROR is SQL - and
should be followed up that way.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]