Inserting and Viewing SQL Table in PHP

Question

config.php is the global file which has the connection codes.

my UPDATED Insert.php code is:

<?php

include("config.php");

if ($submit) {
    if (isset($_POST[firstname]) && isset($_POST[lastname]) && isset($_POST[age])) {
        $sql = "INSERT INTO Persons (FirstName, LastName, Age) VALUES ('$_POST[firstname]','$_POST[lastname]','$_POST[age]')";
        mysql_query($sql, $connect); 
        echo "1 record added";
    }
    else {
        // do nothing now
    }
}
else {
?>
<form action="<?php echo $PHP_SELF ?>" method="post">
Firstname: <input type="text" name="firstname">
Lastname: <input type="text" name="lastname">
Age: <input type="text" name="age">
<input type="submit">
</form>

<?php
}

//if (!mysql_query($sql,$connect))
 //{
  //die('Error: ' . mysql_error());
  //}

mysql_close($connect);
?>

my View.php code is:

<?php
include("config.php");

$sql = mysql_query("SELECT * FROM Persons");
while($results = mysql_fetch_array($sql)) {
    echo $results['FirstName'] . ', ' . $results['LastName'] . ', ' . $results['Age'] . '<br/>';
}

if (!mysql_query($sql,$connect))
{
  die('Error: ' . mysql_error());
}

mysql_close($connect);
?>

my Config.php code is:

<?php

$dbhost="MYWEBHOST";
$dbusername="MYUSERNAME";
$dbpassword="MYPASSWORD";
$dbname="MYDATABASE";

$connect = mysql_connect($dbhost, $dbusername, $dbpassword);
mysql_select_db($dbname,$connect) or die ("Could not connect to database");

?>

After the Insert.php is executed and I receive the "1 Record Added message" and when I go to View.php, I get the following error: "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 #4' at line 1"

What did I do wrong?

Also How can I prevent user from refreshing the Insert.php page which keeps adding null values to the table?

this is weird, are you sure your connection works? Did you try to actually insert anything from insert.php? I'm saying this because I see you commented the query execution in this file. It might be that your connection is invalid and when you pass $connect, it fails — Sebas
– Sebas, Commented Feb 21, 2013 at 18:11
in Insert.php, i get the message "1 record added" which mean it's connected successfully or i am to assume otherwise? I should get "no record added" if it wasn't able to connect, right? I commented out CREATE table because it ran once and the table is already created. — Si8
– Si8, Commented Feb 21, 2013 at 18:14
well as you can see, echo "1 record added"; happens after you setup the string $sql, but you don't actually execute it... — Sebas
– Sebas, Commented Feb 21, 2013 at 18:15
Sebas i added the following code: "mysql_query($sql, $connect);" before echo "1 record added" which should take care of that. but when I goto view, i get the following: my first name, my last name, my age and then next line, the following error: "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 #4' at line 1" — Si8
– Si8, Commented Feb 21, 2013 at 18:20

Sebas · Accepted Answer · 2013-02-21 18:26:34Z

1

try this

<?php
include("config.php");

$sql = mysql_query("SELECT * FROM Persons");
if ($sql) {
    while($results = mysql_fetch_array($sql)) {
        echo $results['FirstName'] . ', ' . $results['LastName'] . ', ' . $results['Age'] .     '<br/>';
    }
} else {
    die('Error: ' . mysql_error());
}


mysql_close($connect);
?>

answered Feb 21, 2013 at 18:26

Sebas

21.6k9 gold badges60 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Si8 Over a year ago

Now what can i enter in Insert.php to prevent any value added if the user refreshes the page? Should i include the form inside the Insert.php so i can use if($submit){} ?

Sebas Over a year ago

This is an arquitectural problem and I think it would be too long to proceed in comments. Basically the idea is to not rely on html forms processed manually anymore, but better doing it through ajax calls.

Si8 Over a year ago

I updated the Insert.php. Can you please take a look and should it be sufficient enough to do what I am requesting? Because after I execute and goto View.php, it doesn't show me the new entry in the table.

Sebas Over a year ago

I really think you should open a new subject in stack overflow (or read the existing documentation online, which is prolific on the subject)

Si8 Over a year ago

i opened a new topic. Thanks for the help on this topic Sebas!

Dirk Olbertz · Accepted Answer · 2013-02-21 18:16:17Z

1

You only assign the SQL statement to a variable, but never execute it. Try this:

$sql = "INSERT INTO Persons (FirstName, LastName, Age) VALUES ('$_POST[firstname]','$_POST[lastname]','$_POST[age]')";

mysql_query($sql);

echo "1 record added";

There are different approachs to prevent people from reloadig your insert.php. In general your insert.php would need to decide wether the user is currently allowed to call it, or not. In modern php development we don't any longer work with single .php files for given tasks, but rather with routes and frameworks.

answered Feb 21, 2013 at 18:16

Dirk Olbertz

974 bronze badges

1 Comment

Si8 Over a year ago

Thank you Dirk, I was able to resolve the issue but the view.php is still giving me an issue. I am new to PHP/SQL scripting, what is routes and frameworks?

Collectives™ on Stack Overflow

Inserting and Viewing SQL Table in PHP

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related