cannot insert into table

[flemingmike]

hello, anybody able to see what im doing wrong?

 

	 $id2=mysql_insert_id();
 $year=$_POST['y'];
 $yr=substr($year,-2);
 $mth=$_POST['m'];
 $init=$_POST['initial'];	 
 $jobnumber=$yr.$mth.'-'.$id2.$init;

 $query = "INSERT INTO jobs VALUES (
 '',
 '$id2',
 '$_POST[initial]',
 '$_POST[y]-$_POST[m]-$_POST[d]',date
 '$_POST[contact]',
 '$_POST[contactphone]',
 '$_POST[customer]',
 '$_POST[address]',
 '$_POST[city]',
 '$_POST[postal]',
 '$_POST[province]',
 '$_POST[description]'
 )";
 mysql_query($query) or die('Error, adding new job failed.  Check you fields and try again.');

 echo "You have successfully entered a new job.  The job number is $jobnumber";

[Miss_Rebelx]

Two questions (for which I'm not sure of the answers without testing / researching more:

 

1) Can you use an associative array without quotes for the index?

 

//Is this valid?
$_POST[initial];
//Or must it be like this:
$_POST['initial'];
//Or even like this:
$_POST["initial"];

 

2) I don't think that you did this line properly:

'$_POST[y]-$_POST[m]-$_POST[d]',date

 

Remove the "date" part.

[Pikachu2000]

In a double-qouted string, you'll need to enclose your array elements in curly braces, or use concatenation.

<?php
$string = "this is a $variable followed by an {$array['element']} in a string";

[flemingmike]

ok, so ive changed it to this:

 

	 $id2=mysql_insert_id();
 $year=$_POST['y'];
 $yr=substr($year,-2);
 $mth=$_POST['m'];
 $init=$_POST['initial'];	 
 $jobnumber=$yr.$mth.'-'.$id2.$init;

 $sql = mysql_query("INSERT INTO members VALUES (
 NULL, 
 '$yr',
 '$init',
 '$_POST['y']-$_POST['m']-$_POST['d']',
 '$_POST['contact']',
 '$_POST['contactphone']',
 '$_POST['customer']',
 '$_POST['address']',
 '$_POST['city']',
 '$_POST['postal']',
 '$_POST['province']',
 '$_POST['description']'
 NULL, 
 NULL, 
 '1', 
 CURRENT_TIMESTAMP)");
 mysql_query($sql) or die('Error, adding new job failed.  Check you fields and try again.');

 

this still doesnt work

[Miss_Rebelx]

Yes but you didn't put in curly braces as Pikachu2000 suggested. Go through the code and do that - because right now your code keeps breaking at every single quote you use.

 

Think of your quotes as levels. If you are in level 1 ("s) and use a single quote (') then you are in level 2. If you use another single quote, you break back into level 1.

(If that makes any sense to you)

So in the code you provided, you basically kept changing levels at the wrong places when in fact you wanted to go to a third level, of sorts, by using the curly braces.

[flemingmike]

makes sence.  i didnt see his post.  ill give that a try now.  thx

[flemingmike]

i cant seem to figure this out.  can you give me an example on one of the lines?

 

$sql = mysql_query("INSERT INTO members VALUES (	 
NULL, 	 
'$yr',	 
'$init',	 
'$_POST['y']-$_POST['m']-$_POST['d']',	
'$_POST['contact']'

[Miss_Rebelx]

$sql = mysql_query("INSERT INTO members VALUES (   
NULL,    
'$yr',   
'$init',   
'{$_POST['y']}-{$_POST['m']}-{$_POST['d']}',   
'{$_POST['contact']}'
//...

[flemingmike]

ok, i did all that, still no good.  here is my whole page.  maybe i have another screwup.

 

<?php
include 'config.php';

// After validating that form has been submitted . . .
array_map('trim', $_POST); // trim leading and trailing whitespace from all elements of $_POST array.

echo "<center>";
if($_POST['setupby'] == "Select...") {
     // validation fails, do something
 echo "You need to select your name from the Setup By field.";
 echo "<br /><br /><br /><br /><a href='#' onclick='javascript:history.go(-1);'>Go Back</a>";
 } elseif( empty($_POST['contact']) ) {
     // validation fails, do something
 echo "You need to enter a Contact Name.";
 echo "<br /><br /><br /><br /><a href='#' onclick='javascript:history.go(-1);'>Go Back</a>";
 } elseif($_POST['customer'] == "Select...") {
     // validation fails, do something
 echo "You need to select a Customer name.";
 echo "<br />If the customer didn't exist in the drop down list, they can be added <a href='newcompany.php'>Here</a>";
 echo "<br /><br /><br /><br /><a href='#' onclick='javascript:history.go(-1);'>Go Back</a>";
 } elseif($_POST['address'] == "Address") {
     // validation fails, do something
 echo "You need to enter an Address.";
 echo "<br /><br /><br /><br /><a href='#' onclick='javascript:history.go(-1);'>Go Back</a>";
 } elseif( empty($_POST['address']) ) {
     // validation fails, do something
 echo "You need to enter an Address.";
 echo "<br /><br /><br /><br /><a href='#' onclick='javascript:history.go(-1);'>Go Back</a>";
 } elseif($_POST['city'] == "City") {
     // validation fails, do something
 echo "You need to enter a City.";
 echo "<br /><br /><br /><br /><a href='#' onclick='javascript:history.go(-1);'>Go Back</a>";
 } elseif( empty($_POST['city']) ) {
     // validation fails, do something
 echo "You need to enter a City.";
 echo "<br /><br /><br /><br /><a href='#' onclick='javascript:history.go(-1);'>Go Back</a>";
 } elseif( empty($_POST['description']) ) {
     // validation fails, do something
 echo "You need to enter a Description.";
 echo "<br /><br /><br /><br /><a href='#' onclick='javascript:history.go(-1);'>Go Back</a>";

 } else {
     // validation passes, so do something completely different

 $id2=mysql_insert_id();
 $year={$_POST['y']};
 $yr=substr($year,-2);
 $mth=$_POST['m'];
 $init={$_POST['initial']};	 
 $jobnumber=$yr.$mth.'-'.$id2.$init;

 $sql = INSERT INTO jobs VALUES (
 NULL, 
 '$yr',
 '$init',
 '{$_POST['y']-$_POST['m']-$_POST['d']}',
 '{$_POST['contact']}',
 '{$_POST['contactphone']}',
 '{$_POST['customer']}',
 '{$_POST['address']}',
 '{$_POST['city']}',
 '{$_POST['postal']}',
 '{$_POST['province']}',
 '{$_POST['description']}'
 NULL, 
 NULL, 
 '1', 
 CURRENT_TIMESTAMP
 )";
 mysql_query($sql) or die('Error, adding new job failed.  Check you fields and try again.');

 echo "You have successfully entered a new job.  The job number is $jobnumber and can be viewed <a href='http://here.com/'>here</a>";
 }


include 'close.php';
?>

 

also, how do i turn on error reporting?  might help me.

[Miss_Rebelx]

You're missing a double quote:

 

$sql = INSERT INTO jobs VALUES

 

Should be:

 

$sql = "INSERT INTO jobs VALUES

[rwwd]

Syntax for inserting is like:-

 

INSERT INTO `TableName` (`column1`,`column2`,`column3`) VALUES ('Value1','Value2','Value3');

 

Error reporting:-

 

<?php

//first declaration

error_reporting(E_ALL);

 

Rw

[flemingmike]

still getting no page now.

[flemingmike]

driving me crazy!  did i do the {  }  right?

[Pikachu2000]

No, not really. Close, but no cigar, as they say. This line is incorrect. Each element needs to be in braces, not the whole line . . .

 

'{$_POST['y']-$_POST['m']-$_POST['d']}',

 

And you really, really, really need to sanitize all form data before allowing it into a database query string.

 

[flemingmike]

i tried how you said and still no cigar.

 

else {
     // validation passes, so do something completely different

 $id2=mysql_insert_id();
 $year=$_POST['y'];
 $yr=substr($year,-2);
 $mth=$_POST['m'];
 $init=$_POST['initial'];	 
 $jobnumber=$yr.$mth.'-'.$id2.$init;

 $sql = "INSERT INTO jobs VALUES (
 NULL, 
 '$yr',
 '{$init}',
 '{$_POST['y']}-{$_POST['m']}-{$_POST['d']}',
 '{$_POST['contact']}',
 '{$_POST['contactphone']}',
 '{$_POST['customer']}',
 '{$_POST['address']}',
 '{$_POST['city']}',
 '{$_POST['postal']}',
 '{$_POST['province']}',
 '{$_POST['description']}'
 NULL, 
 NULL, 
 '1', 
 CURRENT_TIMESTAMP
 )";
 mysql_query($sql) or die('Error, adding new job failed.  Check you fields and try again.');

 echo "You have successfully entered a new job.  The job number is $jobnumber and can be viewed <a href='http://here.com/'>here</a>";
 }

[PFMaBiSmAd]

Are you developing and debugging your code on a system with error_reporting set to E_ALL and display_errors set to on so that all the errors that php detects will be reported and displayed? You will save a ton of time.

 

Also, are you using mysql_insert_id() AFTER you have executed an INSERT query? That's the only time it holds a valid value.

[flemingmike]

ok, so i changed it to :

 

else {
     // validation passes, so do something completely different

 $year=$_POST['y'];
 $yr=substr($year,-2);

 $sql = "INSERT INTO jobs VALUES (
 NULL, 
 '$yr',
 '{$_POST['initial']}',
 '{$_POST['y']}-{$_POST['m']}-{$_POST['d']}',
 '{$_POST['contact']}',
 '{$_POST['contactphone']}',
 '{$_POST['customer']}',
 '{$_POST['address']}',
 '{$_POST['city']}',
 '{$_POST['postal']}',
 '{$_POST['province']}',
 '{$_POST['description']}'
 NULL, 
 NULL, 
 '1', 
 CURRENT_TIMESTAMP
 )";
 mysql_query($sql) or die('Error, adding new job failed.  Check you fields and try again.');

 $mth=$_POST['m'];
 $init=$_POST['initial'];	
 $id2=mysql_insert_id();
 $jobnumber=$yr.$mth.'-'.$id2.$init;

 echo "You have successfully entered a new job.  The job number is $jobnumber and can be viewed <a href='http://here.com/'>here</a>";
 }

 

i have tried to turn on error reporting, i only get this though:

 

'Error, adding new job failed.  Check you fields and try again'

[Pikachu2000]

OK. Temporarily change the die() to die( mysql_error() ); and see what error is produced.

[flemingmike]

here is the 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 'NULL, NULL, '1', CURRENT_TIMESTAMP )' at line 14

[Pikachu2000]

Missing a comma here:

 

  '{$_POST['description']}',

    NULL,

 

[flemingmike]

that fixed it.