[SOLVED] Cannot write to database...

[nvee]

As you will see in a moment, I am still very new to PHP, but I am trying my upmost best to get the things going.

 

I wrote a silly little script to see if I have what it takes to insert data into a database. The database has fields for id, name, surname, telephone. There is a simple HTML form which passes values to my script via POST.

 

My script is:

 

<?php
$name = $_POST['name'];
$surname = $_POST['surname'];
$telephone = $_POST['telephone'];
$connect = mysql_connect("localhost","root","pass");
if(!$connect) { die("No SERVER connection");
}
$db = mysql_select_db("users");
if (!$db) { die("No DB connection");
}
$sql = mysql_query("INSERT INTO villagers ('name','surname','telephone') VALUES ('$name','$surname','telephone')");
$results = mysql_query($sql);
$rows = mysql_affected_rows();
if ($rows < 0) {
echo "Unsuccessful!"; 
} else {
echo "It worked!";
}
$sql = mysql_query("SELECT * FROM villagers");
while($rows = mysql_fetch_array($sql)) {
echo "Fullname: " . $rows['name'];
echo "Surname: " . $rows['surname'];
echo "Telephone: " . $rows['telephone'];
}
?>

 

IT just does not want to write to the database, keeps giving me the "unsuccessful" message. I have tested if the values actually passes and it does. The connection also works like a charm.

 

Any suggestions?

[smerny]

$sql = mysql_query("INSERT INTO villagers ('name','surname','telephone') VALUES ('$name','$surname','telephone')");
$results = mysql_query($sql);

 

 

the second line there is basically like $results = mysql_query(mysql_query("INSERT..etc

 

remove the mysql_query from the $sql and put an "or die" to capture errors

 

$sql = "INSERT INTO villagers ('name','surname','telephone') VALUES ('$name','$surname','telephone')";
$results = mysql_query($sql) or die ("MySQL Error: ". mysql_error());

 

 

[Bricktop]

Hi nvee,

 

Your line:

 

VALUES ('$name','$surname','telephone')");

 

Should read:

 

VALUES ('$name','$surname','$telephone')");

 

You just missed the $ from the beginning of telephone.

[nvee]

MySQL 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 ''name','surname','telephone') VALUES ('asd','asd','123')' at line 1

 

This is the error im getting when I do a test.

[nvee]

Thank you bricktop & smerny - I corrected both your answers but without prevail!

 

I am getting the SQL error as listed above

[PFMaBiSmAd]

Only string data is enclosed in single-quotes. Your column names need to have the single-quotes removed from around them.

[Bjom]

'name','surname','telephone')

 

you are using quotes where you should use backticks.

 

Backticks: for names

Quote: for values.

 

Do not use "or die" ever. It is very bad practice. Use trigger_error instead (see below).

 

example:

$sql = "INSERT INTO villagers (`name`, `surname`, `telephone`) VALUES ('$name','$surname','telephone')";
$results = mysql_query($sql) or trigger_error('MySQL error: '. mysql_error(), E_USER_ERROR);

 

 

[PFMaBiSmAd]

trigger_error() is dependent on the error_reporting and log_errors/display_errors settings, so its' use must also include instructions on how to correctly set those values to produce any output. On the other hand, die(), when used for learning or troubleshooting purposes, will always result in output. Most of the all in one LAMP/WAMP packages that are used for learning and development don't have error_reporting or display_errors set so that trigger_error() would directly be of any help.

[nvee]

Thank you sooo much for the assistance, the problem as you probably thought was with the backticks. If you dont mind me asking:

 

* Do you have to use backticks for the table names or can you leave them then rather just without anything (name, surname, telephone)

* What is the difference between trigger_error and die()? Seeing that I am realllly beginning now, I think I must get use to the correct ways from the word go!

 

Thank you for everyones help, I really appreciate it

[PFMaBiSmAd]

Back-ticks are mysql specific syntax so using them will make your sql more difficult to port to a different database engine. They are only necessary when identifiers need special handling, such as when a reserved keyword is used as an identifier or when you use special characters in identifiers. It is usually better to not use reserved words or special characters in identifiers.

 

trigger_error and die are explained in the php.net documentation and repeating that information here would be redundant.

[Bjom]

The remark about the backticks is correct, however, sometimes it simply cannot be avoided to use reserved words or even an alias with a space in it, that's when you do have to use them. The portability argument...well there are different views and lengthy debates about that, just google, then decide whether you are in favor or against using backticks all the time.

 

On die and trigger error: there is a nice blog here on the site about that: or-die-must-die

 

regards

 

Bjom