INSERT into mySQL (full string please!)

[blakes01]

Hi guys,

 

I have a very simple add.php to add data to a mySQL db.

 

I have a menu/list drop down as one of my fields on my form and this shows an array of results from another table (ranks of the RAF) within my db.

 

When I click the save button I have it process a INSERT INTO command but all i get inputted into my staff table is the first word... eg if I chose "Pilot Officer" from the list menu and then click save all that would appear in my db is "Pilot".

 

Any clues? I will paste the php below...

 

<?php
include('config.php');
?>
<form action='' method='POST' enctype='multipart/form-data'> 
<p><b>Rank:</b><br />
<select name="rank" id="rank">
<option selected>Please Select</option>
<?php
$query = "SELECT * FROM ranks ORDER BY rank ASC";
$result = mysql_query($query);
while($row = mysql_fetch_array($result))
{
echo "<option value=". $row["rank"] .">". $row["rank"] ."</option>";
}
?>
</select>
  <p><b>Forename:</b><br />
  <input name="forename" type="text" id="forename" value="" size="40"> 
<p><b>Surname:</b><br /><input name='surname' type='text' id="surname" value='' size="40" />
<p><b>Category:</b><br />
<select name="category" id="category">
<option selected>Please Select</option>
<?php
$query = "SELECT * FROM categories";
$result = mysql_query($query);
while($row = mysql_fetch_array($result))
{
echo "<option value=". $row["category"] .">". $row["category"] ."</option>";
}
?>
</select>
  
  <p><b>Email:</b><br /><input name='email' type='text' id="email" value='' size="50" />
  <p><b>Mobile:</b><br />
    <input name='mobile' type='text' id="mobile" value='' size="40" />
  </p>
  <input type='submit' value='Save' />
  <input type='hidden' value='1' name='submitted' />
</form>
    
<?php

if (isset($_POST['submitted'])) {

$rank = mysql_real_escape_string($_POST['rank']);
$forename = mysql_real_escape_string($_POST['forename']);
$surname = mysql_real_escape_string($_POST['surname']);
$category = mysql_real_escape_string($_POST['category']);
$email = mysql_real_escape_string($_POST['email']);
$mobile = mysql_real_escape_string($_POST['mobile']);

$sql = "INSERT INTO `staff` (`rank` ,  `forename` ,  `surname` ,  `category` ,  `email` ,  `mobile` ) VALUES ( '$rank' ,  '$forename' ,  '$surname' ,  '$category' ,  '$email' ,  '$mobile')"; 
mysql_query($sql) or die(mysql_error()); 

echo (mysql_affected_rows()) ? "Staff Added":"Nothing Added";

}
?>

[revraz]

See if you can figure out what is truncating it.  Echo your variables after you sanitize them and before your Insert statement.

[Muddy_Funster]

First Place I would look would be the mysql_real_escape_string().  As this isn't user chosen data you don't in theory need to use the real escape, but if you do want to use it I would suggest applying a quoted str to the variable first and then apply the real escape. 

 

Hope that helps.

[blakes01]

Hi guys,

 

thanks for your replys... i have removed the mysql_real_escape_string() as it isn't really needed yet.

 

i have got the page to echo $_POST['rank']; before the defining it as the variable $rank and INSERT ing it into the db.

 

It appears as first word only eg. "pilot" instead of "pilot officer"... im baffled!

 

Cheers

[blakes01]

Also whats really strange is if I go into phpMyAdmin and insert it manually, the sql query it produces automatically for me is...

 

INSERT INTO `web43-staff`.`staff` (`id`, `rank`, `forename`, `surname`, `category`, `email`, `mobile`) VALUES (NULL, 'Pilot Officer', 'David', 'Moss', 'B Category', 'dave@test.com', '01234567890');

 

Mine is...

 

$sql = "INSERT INTO `staff` (`rank` , `forename` , `surname` , `category` , `email` , `mobile` ) VALUES ( '$rank' ,  '$forename' , '$surname' , '$category' , '$email' , '$mobile')";

 

However the phpMyAdmin query inserts the data into the db perfectly (both words), but my php form one only does the first word...

 

The phpMyAdmin one obviously doesnt understand my relationship with the ranks table therefore i manually type the rank in. The only thing I think it could be is the list/menu is not outputting correctly to the form...

 

Any help guys? Cheers!

[PFMaBiSmAd]

The HTML of your <option value='...' is broken. You don't have any quotes around the value in the HTML and the first space character serves as a stop character when the browser submits the data.

 

Since you are building the string with overall double-quotes in php, it would be easiest to use single-quotes (what I showed in red) around the value in the HTML. Alternatively, you could use escaped double-quotes \"

 

[Pikachu2000]

First Place I would look would be the mysql_real_escape_string().  As this isn't user chosen data you don't in theory need to use the real escape, but if you do want to use it I would suggest applying a quoted str to the variable first and then apply the real escape. 

 

Hope that helps.

 

You need to validate/sanitize all data from any form submission. A malicious user can easily use their own form to submit whatever values they want to the script.