Error in Php Mysql Insert Script

[Mostly Ghostly]

Hello everyone,

 

I am creating a giglist for my bands website, and I'm running into errors. Originally, the gig list date was put into the MySQL database as three different columns (day, month, year), and then I realised that this would be pretty stupid as it couldn't be sorted correctly, so now I'm trying to enter it as DATETIME.

 

I can't, for the life of me, see what I'm doing wrong, but I don't know MySQL and PHP that well, so hopefully one of you guys can help.

 

The error that appears is:

"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 'Resource id #2' at line 1"

 

The code for the insert.php script is:

 

<meta http-equiv="refresh" content="10; URL=addgig.php">

<?php

$con = mysql_connect("localhost","user","password");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("hopelesssons", $con);

if (($_POST['day']) and ($_POST['month']) and ($_POST['year'])) { 
$date = "$_POST[year]-$_POST[month]-$_POST[day]"; 
} 
else { die("Invalid date values supplied."); } 

$select = "insert into giglist (venue, town, postcode, date, time, age, noflier, flier, flierul, notes) 
values 
('{$_POST['venue']}','{$_POST['town']}','{$_POST['postcode']}','[$date]','{$_POST['time']}','{$_POST['age']}','{$_POST['noflier']}','{$_POST['flier']}','{$_FILES['image']['name']}','{$_POST['notes']}')";


$target_path = "fliers/";

$target_path = $target_path . basename( $_FILES['image']['name']);

if(move_uploaded_file($_FILES['image']['tmp_name'], $target_path)) {
echo "<p>The image has been uploaded successfully.</p>";
} else{
echo "<p>There was an error uploading the image, please try again!</p>";
} 

//{

if (!mysql_query($con))

  {
  die('Error: ' . mysql_error());
  }
echo "Record added successfully. You will shortly be redirected back to the previous page.";

mysql_close($con)
  ?>

[MadTechie]

Okay a few problems

$select = "insert into giglist (venue, town, postcode, date, time, age, noflier, flier, flierul, notes) 
values 
('{$_POST['venue']}','{$_POST['town']}','{$_POST['postcode']}','[$date]','{$_POST['time']}','{$_POST['age']}','{$_POST['noflier']}','{$_POST['flier']}','{$_FILES['image']['name']}','{$_POST['notes']}')";

first off, '[$date]' should be '$date',

but the main problem is your not using that query..

 

it should be here

if (!mysql_query($con))

so update it to this

if (!mysql_query($select))

 

also i would of called it $insert instead of $select as its an insert 

 

in addition you are vulnerable to SQL Injection,  I have made the changes and updated the whole script see below

(it may not work as its untested)

 

<meta http-equiv="refresh" content="10; URL=addgig.php">
<?php
$con = mysql_connect("localhost","user","password");
if (!$con){
die('Could not connect: ' . mysql_error());
}
mysql_select_db("hopelesssons", $con);

if (!empty($_POST['day']) && !empty($_POST['month']) && !empty($_POST['year'])) {
$date = $_POST['year']."-".$_POST['month']."-".$_POST['day'];
}else{
die("Invalid date values supplied.");
}
$file = (!empty($_FILES['image']['name']))?$_FILES['image']['name']:"";
$insert = sprinf("INSERT INTO giglist (venue, town, postcode, date, time, age, noflier, flier, flierul, notes)
VALUES 	('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')",
mysql_real_escape_string($_POST['venue']),
mysql_real_escape_string($_POST['town']),
mysql_real_escape_string($_POST['postcode']),
$date,
mysql_real_escape_string($_POST['time']),
mysql_real_escape_string($_POST['age']),
mysql_real_escape_string($_POST['noflier']),
mysql_real_escape_string($_POST['flier']),
mysql_real_escape_string($file),
mysql_real_escape_string($_POST['notes'])
);


$target_path = "fliers/";
$target_path = $target_path . basename( $_FILES['image']['name']);

if(move_uploaded_file($_FILES['image']['tmp_name'], $target_path)) {
echo "<p>The image has been uploaded successfully.</p>";
} else{
echo "<p>There was an error uploading the image, please try again!</p>";
}

if (!mysql_query($insert)){
die('Error: ' . mysql_error());
}else{
echo "Record added successfully. You will shortly be redirected back to the previous page.";
}

mysql_close($con)
?>

 

[Mostly Ghostly]

Thank you so much for your quick and useful response!

 

I have put the amended script into place, but it's now generating an error (only one though!) which says "PHP Fatal error:  Call to undefined function sprinf() in /insert.php on line 12".

 

 

The only occurance of "sprinf" is in the following line:

 

$insert = sprinf("INSERT INTO giglist (venue, town, postcode, date, time, age, noflier, flier, flierul, notes)

[MadTechie]

oops typo it should be sprintf

 

$insert = sprintf("INSERT INTO giglist (venue, town, postcode, date, time, age, noflier, flier, flierul, notes)

[Mostly Ghostly]

I literally spotted it just as you posted that!

 

Unfortunately, the date still isn't going into the database correctly, although the rest of the information finally is.

 

Thanks again, for your time on this!

[MadTechie]

can you add this line to the end of the code

echo $insert;

and also check the date field is a DATETIME type

 

it should display something like this

...snip... VALUES ('venue', 'town', 'postcode', '2010-01-11', ..snip...

 

[MadTechie]

infact.. lets do this correctly

 

change

$date = $_POST['year']."-".$_POST['month']."-".$_POST['day'];

to

//Create unix timestamp
$phptime = mktime(0,0,0,$_POST['month'],$_POST['day'],$_POST['year']);
//convert to SQL datetime
$date = date ("Y-m-d", $phptime); 

 

EDIT: added comments

[Mostly Ghostly]

Okay, I've made the alterations, but when you say the "date field" do you mean the column on the database? That is set at DATETIME.

 

There is still no date or time being added into the database, but the echo $insert displays:

 

The image has been uploaded successfully.

 

INSERT INTO giglist (venue, town, postcode, date, time, age, noflier, flier, flierul, notes) VALUES ('TheVenue','TheTown','ThePostcode','1970-01-01','18:30','Over 18\\\'s Only','£3.00','£2.00','flier.jpg','No further notes!')Record added successfully. You will shortly be redirected back to the previous page.

 

Thanks.

 

[Mostly Ghostly]

Aha!

 

It IS working! I just need to update my the php for the page it displays on, and that should be great!

 

Would this script be very different to one which ALTERS the details? As that's my next step!

 

Thank you so much for your help!

[Mostly Ghostly]

I've got the date to display on the gig page now, but not exactly how it's required.

 

I entered a date for next month, and it's displaying "1970-01-01 00:00:00".

 

What should I do here?

 

Also, the pages now don't seem to be able to handle apostrophes, as it puts a "\" in front of each one...

 

Any ideas?

 

Thanks alot!

[MadTechie]

I've got the date to display on the gig page now, but not exactly how it's required.

 

I entered a date for next month, and it's displaying "1970-01-01 00:00:00".

 

What should I do here?

It would seam the data is not being passed,

add a

echo "<pre>";var_dump($_POST);

to the end of you code and post the results,

 

 

Also, the pages now don't seem to be able to handle apostrophes, as it puts a "\" in front of each one...

 

Any ideas?

 

Okay the apostrophes is due to magic quotes, if possible turn them off,

a workaround is to add this code to the start

if (get_magic_quotes_gpc()) {
    function magicQuotes_awStripslashes(&$value, $key) {$value = stripslashes($value);}
    $gpc = array(&$_GET, &$_POST, &$_COOKIE, &$_REQUEST);
    array_walk_recursive($gpc, 'magicQuotes_awStripslashes');
}

 

[Mostly Ghostly]

Thanks.

 

This is the output:

 

 

array(12) {

  ["venue"]=>

  string(14) "TheVenue"

  ["town"]=>

  string( "TheTown"

  ["postcode"]=>

  string(7) "ThePostcode"

  ["day"]=>

  string(2) "09"

  ["month"]=>

  string(3) "May"

  ["year"]=>

  string(4) "2011"

  ["time"]=>

  string(5) "21:00"

  ["age"]=>

  string(14) "Over 18's Only"

  ["noflier"]=>

  string(4) "Free"

  ["flier"]=>

  string(4) "Free"

  ["notes"]=>

  string(17) "No further notes!"

  ["upload"]=>

  string(7) "Add Gig"

}

Record added successfully. You will shortly be redirected back to the previous page.

 

 

As you can see, the date is displaying fine there...

 

Also, thanks for the other bit, that's got rid of the slashes.

[MadTechie]

Ahhhhhh!

["day"]=>

  string(2) "09"

  ["month"]=>

  string(3) "May"

  ["year"]=>

  string(4) "2011"

 

Is May selected from a drop down box ? if so change the value to the month number ie May = 5

or replace

$phptime = mktime(0,0,0,$_POST['month'],$_POST['day'],$_POST['year']);

with

$phptime = strtotime($_POST['day']."-".$_POST['month']."-".$_POST['year']);

 

personally i would change the values (if possible)

[MadTechie]

in addition, you could also add the time into the field ie

 

$phptime = strtotime($_POST['day']."-".$_POST['month']."-".$_POST['year']." ".$_POST['time'] );
$date = date ("Y-m-d H:i:s", $phptime); 

[Mostly Ghostly]

If I may be so bold, I want to have your PHP babies!!

 

 

Thank you! I changed the values and it's worked... Now all I need it to do is display as day, month and year in letters...

 

With regards to the time, is it possible to lose the seconds, and rather than have a 24 hour clock have it display as, for example: 9:30PM.

 

Any ideas? :-)