1

I'm just learning PHP and am trying the most basic thing: capturing info from a form and sticking it into a table in a mySQL database. I'm embarrassed to ask such a stupid newbie question, but after reviewing two books, several Stack Overflow posts, and 7 different tutorials, I still can't get my pathetic code to write a few lousy metrics to my database.

Here's the latest version of the code. Could someone please tell me what I am doing wrong?

* Basic HTML Form *

<form method="post" action="post_metrics_stack.php" >
<p>Date<br />
<input name="date" type="text" /></p>

<p>Metric1<br />
<input name="metric1" type="text" /></p>

<p>Metric2<br />
<input name="metric2" type="text" /></p>

<input type="submit" name="submit" value="Submit" />
</form>

* Processor File *

<?php 

$date=$_POST['date'];
$metric1=$_POST['metric1'];
$metric2=$_POST['metric2'];

$con = mysql_connect("localhost", "root", "mypassword");
if (!$con)
  {die('Could not connect to mysql: ' . mysql_error());} 

$mydb = mysql_select_db("mydatabasename");
if (!$mydb)
  {die('Could not connect to database: ' . mysql_error());} 

mysql_query("INSERT INTO my_metrics VALUES ('$date', '$metric1', '$metric2')"); 
 Print "Your metrics have been successfully added to the database."; 

mysql_close($con);
?>
Improve this question
11
  • 1
    What you show looks fine so far. What goes wrong, what errors do you get? To debug, try print_r($_POST); in the processor file. What does it show? Also, you should throw a mysql_error() after the query as well (if it fails) so you can catch if something goes wrong there. Commented May 16, 2011 at 18:57
  • By the way, your code is vulnerable to SQL injection Commented May 16, 2011 at 18:58
  • 1
    what error are you getting? what is your database schema? You might want to list your fields in the insert query like so: INSERT INTO my_metrics (date, metric1, metric2) VALUES ('$date', '$metric1', '$metric2') Commented May 16, 2011 at 18:58
  • can you show us some of your output/error ? Commented May 16, 2011 at 18:59
  • Also, if you're not getting any errors, put this code at the top: ini_set('display_errors', 1); error_reporting(E_ALL); Commented May 16, 2011 at 18:59

5 Answers 5

Reset to default
2

Your mysql-syntax is wrong.

Try

INSERT INTO my_metrics
SET
date = '$date',
metric1 = '$metric1',
metric2 = '$metric2'
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Your syntax is for update, not insert. As long as the only fields she has are date, metric1, and metric2, it should work.
This answer shows valid alternate INSERT syntax for mysql but OP is also using correct syntax.
Note also that she's only using valid syntax IF they are the only three fields. If she's got an ID field or something at the start, it's wrong.
@captainclam: I don't want to get into a semantics debate but her statement is syntactically correct regardless if it has the incorrect number of fields ;)
Thanks so much for the feedback. This is a simplified version of the form and processor - the original has about 10 metrics. Neither one works though.
|
2

Depending on what the table looks like, your code may or may not work,

"INSERT INTO my_metrics VALUES ('$date', '$metric1', '$metric2')"

assumes that the fields are in that order, and that there are no fields before this one.

"INSERT INTO my_metrics (date, metric1, metric2) VALUES ('$date', '$metric1', '$metric2')"

would be more future proof, and may also solve your problem as they are going to insert into the correct fields.

It is also possible that you are getting some bad data for the field definitions, try doing the insert in phpmyadmin or at the command line instead of in php, then work backwards from there.

Improve this answer

1 Comment

I tried this format in a few earlier versions. Will try it again. Thanks!
2

As far as the vulnerability to SQL injection, you should feed your input strings to mysql_real_escape_string();. This will escape any unwanted characters.

When connecting to the database, you write

$con = mysql_connect("localhost", "root", "mypassword");
if (!$con)
  {die('Could not connect to mysql: ' . mysql_error());}

You can simplify this, and making this more readable by writing

mysql_connect('localhost','root','mypassword') or die('Could not connect to mysql:<hr>'.mysql_error());

For solving your problem, see if specifieng column names helps. If you don't, mysql will assume you enter values in the order of the columns, you might get some trouble with an ID field, or something like that. Your query could look like this:

"INSERT INTO my metrics (date,metric1,metric2) VALUES ('$data','$metric1','$metric2'))"

And finally, here's a speed concideration.

There are two ways to write strings: using single quotes ('string'), and using double quotes ("string"). in the case of 'string' and "string", they will work exactly the same, but there is a difference. Look at the following code

$age=3
echo 'the cat is $age years old.';
//prints out 'the cat is $age years old.'

echo "the cat is $age years old.";
//prints out 'the cat is 3 years old'

echo 'the cat is '.$age.' years old';
//prints out 'the cat is 3 years old'.

As you can see from this example, when you use single quotes, PHP doesn't check the string for variables and other things to parse inside the string. Doing that takes PHP longer than concatinating the variable to the string. so although

echo "the cat is $age years old"

is shorter to type than

echo 'the cat is '.$age.' years old';

it will boost your page loading when you write larger applications.

Improve this answer

5 Comments

'date','metric1','metric2' Shouldn't those be backticks? I usually leave them out when I am hand writing sql, but I don't think single quotes will work there.
Apparently I cannot edit... ; or die should be: ` or die` no semicolon or it breaks the line and probably throws a parser error
+1 for well put, good advice, but -1 for perpetuating the myth of single quotes having any effect on performance. The benefit is microseconds - millionths of a second! Every database call will take hundreds or thousands of times longer.
-1 for forgetting about echo 'the cat is ',$age,' years old'; when fussing over micro-optimizations :P
Thanks for the clarifications about the apostrophes and quotation marks. This nuance finally makes sense now! Awesome!
1

Hooray! Hooray! Hooray!

Thank you all for such helpful advice! It finally works! Here's the updated code in case any other newbies have the same issue. (Hope I didn't screw anything else up.)

Form

<form method="post" action="post_metrics_stack.php" >
  <p>Date<br />
  <input name="date" type="text" /></p>

  <p>Metric1<br />
  <input name="metric1" type="text" /></p>

  <p>Metric2<br />
  <input name="metric2" type="text" /></p>

<input type="submit" name="submit" value="Submit" />
</form>

Processor

<?php 

  ini_set('display_errors', 1); error_reporting(E_ALL); 

  // 1. Create connection to database

  mysql_connect('localhost','root','mypassword') or die('Could not connect to mysql: <hr>'.mysql_error());

  // 2. Select database

  mysql_select_db("my_metrics") or die('Could not connect to database:<hr>'.mysql_error());


  // 3. Assign variables (after connection as required by escape string)

    $date=mysql_real_escape_string($_POST['date']);
    $metric1=mysql_real_escape_string($_POST['metric1']);
    $metric2=mysql_real_escape_string($_POST['metric2']);


  // 4. Insert data into table

  mysql_query("INSERT INTO my_metrics (date, metric1, metric2) VALUES ('$date', '$metric1', '$metric2')"); 

  Echo 'Your information has been successfully added to the database.';  

  print_r($_POST);

  mysql_close()

?>
Improve this answer

1 Comment

Cool, make sure you come back and mark this one as the correct answer in a couple of days.
0

Here you go love :) try W3c it a good place for new pepps

 <?php
    $con = mysql_connect("localhost","peter","abc123");
    if (!$con)
      {
      die('Could not connect: ' . mysql_error());
      }

    mysql_select_db("my_db", $con);

    $sql="INSERT INTO my_metrics (date, metric1, metric2)
    VALUES
    ('$_POST[date]','$_POST[mertric1]','$_POST[metric2]')";

    if (!mysql_query($sql,$con))
      {
      die('Error: ' . mysql_error());
      }
    echo "Your metrics have been successfully added to the database.";

    mysql_close($con)
    ?>
Improve this answer

4 Comments

This is vulnerable to SQL injection.
Yes, it is, but the question is "why doesn't this work" not "why is my database gonzo?", it is good to point out the failing, but it is possible that it was left out because mysql_real_escape_string is a really long thing to type out 3 times. It does point out the failing of the W3fools website though if this is copied straight from there.
@SeanJA No matter what was asked, code that contains vulnerabilities shouldn't be posted on SO. It will be copy & pasted by people. Related discussion: What to do with questions with "harmful" content?
Thanks so much - will note the vulnerability and address it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.