Script Help required

[keyvanrahmadi]

Hiya guys,

I have created a simple form which the script is as follow:

[HTML]
<form name="Untitled-11A" method="post">

MODEL TYPE:<select name=“model_id”>
<option value=“model1”/>Clio
<option value=“model2”/>Espace
<option value=“model3”/>TT
<option value=“model4”/>306
<option value=“model5”/>Vectra
</select>

Choose Colour:<select name=“colour_id”>
<option value=“colour1”/>Blue
<option value=“colour2”/>Green
<option value=“colour3”/>Red
<option value=“colour4”/>White
<option value=“colour5”/>Black
<option value=“colour6”/>Yellow
<option value=“colour7”/>Silver
</select>

Transmission TYPE:<select name=“transmission_id”>
<option value=“trans1”/>Automatic
<option value=“trans2”/>Manual
</select>

Location:<select name=“location_id”>
<option value=“location1”/>London
<option value=“location2”/>Liverpool
</select>

Car Reg:<input type="text" name="reg" size="20">

<input type="submit" value="Submit" name="submit">
</form>
</select>
[/HTML]

Then i have the following script which is suppoe to insert the chosen values into a mysql databse, but as you most probebly have guessed by now, i am not sure why it dosent. can you guys look over the code and enlighten me where my mistake is please.

[PHP]

<?php

if (isset($_POST['reg']))
$reg = $_POST['reg'];

if (isset($_POST['model_id']))
$mode_id = $_POST['model_id'];

if (isset($_POST['colour_id']))
$colour_id = $_POST['colour_id'];

if (isset($_POST['transmisson_id']))
$transmisson_id = $_POST['transmisson_id'];

if (isset($_POST['location_id']))
$location_id = $_POST['location_id'];

//make a connection to database

$DB = mysql_connect("host", "usr", "pass") or die("omg it didnt work!");
mysql_select_db("db", $DB) or die("aaah! so close!");

$insert = "INSERT INTO car_o2 (reg, model_id, colour_id, transmission_id, location_id ) VALUES ('$reg', '$model_id', '$colour_id', '$transmission_id', $location_id)";

if (mysql_query($insert, $DB) === FALSE)
{
echo 'Car was not inserted into database.<br />';
}
else
{
echo 'Car was successfully inserted into database.<br />';
echo 'CLICK HERE to return to admin page';
}
?>

[/PHP]

The mysql table has the follwoing colums: reg, model_id, colous_id, transmission_id and location_id.

Any help would be appriciated.

keyvan

[vssp]

<?php $result = mysql_query($insert)
or die("Invalid query: " . mysql_error());
?>

[ronverdonk]

What vssp means is that you should echo the MySQL error that the insert statement returns. As follows:

[php] if (!mysql_query($insert, $DB))
{
die ('Car was not inserted into database, error is: '.mysql_error());
}
[/php]

Ronald :cool:

[keyvanrahmadi]

What vssp means is that you should echo the MySQL error that the insert statement returns. As follows:

[php] if (!mysql_query($insert, $DB))
{
die ('Car was not inserted into database, error is: '.mysql_error());
}
[/php]

Ronald :cool:

Ronald can you please explain the difference to me, i am new to php and not sure i understand it very well. I like to apologise if i sound too stupid.

keyvan

[ronverdonk]

The following statement adds the MySQL error message mysql_error() to your own error message:

die ('Car was not inserted into database, error is: '.mysql_error());

So when the MySQL statement returns an error you can see what went wrong and where.

Ronald :cool:

[keyvanrahmadi]

It returns the following error massage:

Car was not inserted into database, error is: 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 ')' at line 1.

I have checked all the script and i cant find the problem?

[ronverdonk]

Are you sure that the $location_id variable holds numeric data? I mean: could it be a character field that includes funny characters, like a blank. Because then the last field in this insert statement would be erroneous.

"INSERT INTO car_o2 
(reg, model_id, colour_id, transmission_id, location_id ) VALUES 
('$reg', '$model_id', '$colour_id', '$transmission_id', $location_id)";

I found another error:

    $mode_id = $_POST['model_id'];

shoulbe

    $model_id = $_POST['model_id'];

Ronald :cool:

[keyvanrahmadi]

Are you sure that the $location_id variable holds numeric data? I mean: could it be a character field that includes funny characters, like a blank. Because then the last field in this insert statement would be erroneous.

"INSERT INTO car_o2 
(reg, model_id, colour_id, transmission_id, location_id ) VALUES 
('$reg', '$model_id', '$colour_id', '$transmission_id', $location_id)";

I found another error:

    $mode_id = $_POST['model_id'];

shoulbe

    $model_id = $_POST['model_id'];

Ronald :cool:

Regarding $model typo, not sure how i managed that, but its corrected now and i am still getting the same error massage. $location_id is a mediumint, auto increment and i have changed the insert value into numeric and still getting the same error.

Now i really dont have a clue whats the problem.

keyvan :(

[ronverdonk]

Then only 'brute force' can be used to determine the error.

You must now also display the select statement itself in the error message. That way you can see where the MySQL error is located (hope).

So replace the query by the following statement and see what the error message including the constructed sql statement is.

[php]if (!mysql_query($insert, $DB)) {
die ("Car was not inserted into database.<br>SQL statement is: $insert<br>Error is: ".mysql_error());
}
[/php]

Ronald :cool:

[keyvanrahmadi]

Then only 'brute force' can be used to determine the error.

You must now also display the select statement itself in the error message. That way you can see where the MySQL error is located (hope).

So replace the query by the following statement and see what the error message including the constructed sql statement is.

[php]if (!mysql_query($insert, $DB)) {
die ("Car was not inserted into database.<br>SQL statement is: $insert<br>Error is: ".mysql_error());
}
[/php]

Ronald :cool:

here is the error massage:

Car was not inserted into database.
SQL statement is: INSERT INTO car_o2 (reg, model_id, colour_id, transmission_id, location_id ) VALUES ('', '', '', '', )
Error is: 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 ')' at line 1

[ronverdonk]

So none of your variables contain any data.

But hold on: looking at your html form (I really haven't looked at that before, just at the POST processing script) I see that you did not specify the <option> correcly. An option statement looks like:

[html]<option value="value">text</option>[/html]

Ronald :cool:

[keyvanrahmadi]

So none of your variables contain any data.

But hold on: looking at your html form (I really haven't looked at that before, just at the POST processing script) I see that you did not specify the <option> correcly. An option statement looks like:

[html]<option value="value">text</option>[/html]

Ronald :cool:

Car was not inserted into database.
SQL statement is: INSERT INTO car_o2 (reg, model_id, colour_id, transmission_id, location_id ) VALUES ('P000 KEY', '', '', '', )
Error is: 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 ')' at line 1.

I also noticed i have started my form name="form" rather than Form Action="form", if that makes any difference.

[keyvanrahmadi]

its very strange i changed the form from drop down menu to text and it works fine..although it woudl have been far better as a drop down menu. I am still not sure what the problem is.

keyvan

[ronverdonk]

You still have problems. As I can see the $location_id cannot be an integer, it is a character field, how could you otherwise store 'London' or 'Liverpool' in it?

Since this is dragging into an endless discussion, I have re-arranged the code you showed an put it into one script. That makes it easier to maintain and see what is ahppening. And it works, so try it and adapt it to your own needs. But do not forget to make your location_id field in the database a VARCHAR data type!

[php]<?php
// -------------------------------------------
// if form is submitted, process POSTed data
// -------------------------------------------
if (isset($_POST['submit'])) {

if (isset($_POST['reg']))
$reg = $_POST['reg'];
if (isset($_POST['model_id']))
$model_id = $_POST['model_id'];
if (isset($_POST['colour_id']))
$colour_id = $_POST['colour_id'];
if (isset($_POST['transmisson_id']))
$transmisson_id = $_POST['transmisson_id'];
if (isset($_POST['location_id']))
$location_id = $_POST['location_id'];

//make a connection to database
$DB = mysql_connect("localhost", "xxxxx", "yyyyy")
or die("Connect to server failed: ".mysql_error());
mysql_select_db("zzzz", $DB)
or die("DB select failed: ".mysql_error());

$insert = "INSERT INTO car_o2 (reg, model_id, colour_id, transmission_id, location_id ) VALUES ('$reg', '$model_id', '$colour_id', '$transmission_id', '$location_id')";

if (mysql_query($insert, $DB) === FALSE) {
die ("Car was not inserted into database.<br>SQL statement is: $insert<br>Error is: ".mysql_error());
}
else {
echo 'Car was successfully inserted into database.<br />';
echo 'CLICK HERE to return to admin page';
exit;
}
} // end isset

// ------------------------------------------------
// form is not submitted, show the data entry form
// ------------------------------------------------
?>

<form name="Untitled-11A" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">

MODEL TYPE:<select name="model_id”>
<option value="model1">Clio</option>
<option value="model2">Espace</option>
<option value="model3">TT</option>
<option value="model4">306</option>
<option value="model5">Vectra</option>
</select>
<br />
Choose Colour:<select name="colour_id">
<option value="colour1">Blue</option>
<option value="colour2">Green</option>
<option value="colour3">Red</option>
<option value="colour4">White</option>
<option value="colour5">Black</option>
<option value="colour6">Yellow</option>
<option value="colour7">Silver</option>
</select>
<br />
Transmission TYPE:<select name="transmission_id">
<option value="trans1">Automatic</option>
<option value="trans2">Manual</option>
</select>
<br />
Location:<select name="location_id">
<option value="location1">London</option>
<option value="location2">Liverpool</option>
</select>
<br />
Car Reg:<input type="text" name="reg" size="20">
<br />
<input type="submit" value="Submit" name="submit">
</form>
</select>[/php]

Ronald :cool:

[keyvanrahmadi]

Thank you, as usuall you have been more than helpfull.

keyvan

[ronverdonk]

You are welcome. I hope the code works for you. Please let me know if it isn't.

Ronald :cool: