mysql insert question

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
pearjam
Forum Commoner
Posts: 29
Joined: Sun May 31, 2009 5:05 pm

mysql insert question

Post by pearjam »

The code below isn't inserting into the db.

I've tried variations of it - sometimes getting a vague error, sometimes getting no output... but never actually working and inserting into the table.

I've tried combining the pages (form and processing), using php_self and script_name. Tried using mysql_query and $sql - etc...

The db can be written to inside phpmyadmin, so it is probably the code. Does anyone see anything that would give an error or cause it to not write to the table?

Code: Select all

<form action="d-dbconnect.php" method="post">
who: <input type="text" name="name" />
site: <input type="text" name="site" />
what: <input type="text" name="comment" />
<input type="submit" value="post" />
</form>
The d-dbconnect.php

Code: Select all

<?php
mysql_connect("localhost","root","xxxxxxxx") or die(error connecting);
mysql_select_db("feedback") or die(error selecting database);
$sql = "INSERT INTO index (site, name, comment) VALUES('$_POST[site]','$_POST[name]','$_POST[comment]')";
mysql_query($sql) or die(updating);
echo "Data Inserted";
?>
When it errors out, it grabs the values from the form, but says something like sql syntax error near 'index' ('site', 'name', 'comment') VALUES('sitefromform','namefromform','commentfromform')
Last edited by Benjamin on Thu Jun 11, 2009 9:19 pm, edited 1 time in total.
Reason: Changed code type from text to php.
mischievous
Forum Commoner
Posts: 71
Joined: Sun Apr 19, 2009 8:59 pm

Re: mysql insert question

Post by mischievous »

Code: Select all

<?php
mysql_connect("localhost","root","xxxxxxxx") or die(error connecting);
mysql_select_db("feedback") or die(error selecting database);
$sql = "INSERT INTO index (site, name, comment) VALUES($_POST[site], $_POST[name], $_POST[comment])";
$queryresult = mysql_query($sql);
echo "Data Inserted";
if($queryresult)
{
     echo "Data Inserted";
} else {
     echo "Problem Inserting Data";
}
?>
pearjam
Forum Commoner
Posts: 29
Joined: Sun May 31, 2009 5:05 pm

Re: mysql insert question

Post by pearjam »

Just tried - didn't work :(

No error, but nothing went into the table.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: mysql insert question

Post by Benjamin »

Code: Select all

# if you aren't escaping your data, or magic_quotes_gpc is disabled, use the following line
$_POST = array_map('mysql_real_escape_string', $_POST);
 
# index is a reserved MySQL keyword.  Also, correct syntax helps.
$sql = "INSERT INTO `index` (site, name, comment) VALUES('{$_POST['site']}', '{$_POST['name']}', '{$_POST['comment']}')";
pearjam
Forum Commoner
Posts: 29
Joined: Sun May 31, 2009 5:05 pm

Re: mysql insert question

Post by pearjam »

AWESOME! That worked! It's inserting into the table now!!

Here are a few things I'm trying now that aren't working.

When the form is submitted, it's supposed to add a time stamp (it's one of the fields in the table). When I add the php, the output is an error, or a weird character. Here's the code:

Code: Select all

<?php
$connect = mysql_connect("localhost","root","xxxxxxxx") or die(mysql_error());
mysql_select_db("feedback") or die(mysql_error());
$_POST = array_map('mysql_real_escape_string', $_POST);
$time = date("Md 'y");
$sql = "INSERT INTO `main` (time, site, name, comment) VALUES('{$time['time']}', '{$_POST['site']}', '{$_POST['name']}', '{$_POST['comment']}')";
mysql_query($sql) or die(not updated);
$ref = $_SERVER['HTTP_REFERER'];
header( 'refresh: 02; url='.$ref);
mysql_close($connect);
?>
I think the syntax is wrong where the variable ($time) is inserted into the table here:

Code: Select all

VALUES('{$time['time']}',

The second thing is that because of the way it's laid out, I'd like to have the 'insert into table name' section be on the page with the form. So it looks like this:

Code: Select all

<form action="process.php" method="post">
who: <input type="text" name="name" />
site: <input type="text" name="site" />
what: <input type="text" name="comment" />
<input type="submit" value="post" />
</form>
<?php
$time = date("Md 'y");
$sql = "INSERT INTO `main` (time, site, name, comment) VALUES('{$time['time']}', '{$_POST['site']}', '{$_POST['name']}', '{$_POST['comment']}')";
?>
which should pass it to the process.php page which looks like this:

Code: Select all

<?php
$connect = mysql_connect("localhost","root","xxxxxxxx") or die(mysql_error());
mysql_select_db("feedback") or die(mysql_error());
$_POST = array_map('mysql_real_escape_string', $_POST);
mysql_query($sql) or die(not updated);
$ref = $_SERVER['HTTP_REFERER'];
header( 'refresh: 02; url='.$ref);
mysql_close($connect);
?>
When I set it up this way, it doesn't work. No error is given. It's not passing the values of the variables to the process.php page? Where the form php is, should I use a "require once" to send the data to the process page - or is the forms action doing that?


The third thing I would like to try is when the site field is filled out, it'll be a url - I'd like to have it wrap the 'name' entry as a link, so when the feedback is displayed, a visitor can click on the name in a post and it'll open the link in a new window. (To combine the "name" and "site" fields when displayed, but adding a href="$site" target="_blank">$name</a .)

I don't have any idea how that's done php wise. I searched a ton of key words and couldn't find anything - does anyone know how to do that?
Last edited by Benjamin on Fri Jun 12, 2009 5:35 pm, edited 2 times in total.
Reason: Changed code type from text to php.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: mysql insert question

Post by Benjamin »

Code: Select all

 
$time = date("Y-m-d");
$sql = "INSERT INTO `main` (time, site, name, comment) VALUES('$time', '{$_POST['site']}', '{$_POST['name']}', '{$_POST['comment']}')";
 
pearjam
Forum Commoner
Posts: 29
Joined: Sun May 31, 2009 5:05 pm

Re: mysql insert question

Post by pearjam »

astions It works...!

Any ideas on how to keep the insert statement on the form page? The "insert into main" statement will be different with each page (the page name is the table name in the db).

I think it would basically look like this...

Code: Select all

<form action="process.php" method="post">
who: <input type="text" name="name" />
site: <input type="text" name="site" />
what: <input type="text" name="comment" />
<input type="submit" value="post" />
</form>
<?php
$sql = "INSERT INTO `main` (time, site, name, comment) VALUES('$time', '{$_POST['site']}', '{$_POST['name']}', '{$_POST['comment']}')";
?>
...and the process.php page would look like:

Code: Select all

<?php
$connect = mysql_connect("localhost","root","xxxxxxxxx") or die(mysql_error());
mysql_select_db("feedback") or die(mysql_error());
$_POST = array_map('mysql_real_escape_string', $_POST);
$time = date("Y-m-d");
mysql_query($sql) or die(not updated);
$ref = $_SERVER['HTTP_REFERER'];
header( 'refresh: 02; url='.$ref);
mysql_close($connect);
?>
<div id="comment">
<br /><br /><br />
Your comment was sucesfully added <?php print $_POST[name] ?>, thank you!
<br /><br />
<img src="../loading.gif" alt="" />
</div><!--end comment-->
</body></html>
Of course I tried it this way - and it doesn't work... lol I've tried using require once (process.php) up w/ the php code for the form and that doesn't work either.
pearjam
Forum Commoner
Posts: 29
Joined: Sun May 31, 2009 5:05 pm

Re: mysql insert question

Post by pearjam »

So the rest of the code is really starting to work out...

There are two pages, one 'form page' and one 'process page'.

I'm trying to set it up where the form page handles the form and specifies the mysql insert table and passes those to the process page.

The process page handles opening the mysql connection, inserting the info from the from to the table specified on the form page. It then closes the mysql connection, gives a "thank you" then redirects back to the previous page.

Does anyone know how I can specify what table mysql will insert into with the php code on the form page?
User avatar
mikemike
Forum Contributor
Posts: 355
Joined: Sun May 24, 2009 5:37 pm
Location: Chester, UK

Re: mysql insert question

Post by mikemike »

Just put a hidden field in the form.

I HIGHLY recommend you don't though, massive security flaw.
pearjam
Forum Commoner
Posts: 29
Joined: Sun May 31, 2009 5:05 pm

Re: mysql insert question

Post by pearjam »

There's got to be a way to specify what table to insert into on the form page, that isn't a risk?
User avatar
mikemike
Forum Contributor
Posts: 355
Joined: Sun May 24, 2009 5:37 pm
Location: Chester, UK

Re: mysql insert question

Post by mikemike »

why would you want to?
pearjam
Forum Commoner
Posts: 29
Joined: Sun May 31, 2009 5:05 pm

Re: mysql insert question

Post by pearjam »

The table name reflects the page name.

index page:
form
table insert statement specifying 'index' table
displays 'index' table content (feedback for the index page)
form points to the one 'thank you' page

gallery page:
form
table insert statement specifying 'gallery' table
displays 'gallery' table content (feedback for the gallery page)
form points to the one 'thank you' page

howto page:
form
table insert statement specifying 'howto' table
displays 'howto' table content (feedback for the howto page)
form points to the one 'thank you' page

etc....

thankyou page:
opens db connection
inserts content into whatever table from the page's form was just filled out
closes db connection
says thank you
redirects to previous page

Thats why. One thank you page, and an easy template I can use to make new pages with.
pearjam
Forum Commoner
Posts: 29
Joined: Sun May 31, 2009 5:05 pm

Re: mysql insert question

Post by pearjam »

Let's say instead - I remove the db connect from the Thank You page and move it to the Form Page.

Now the Thank You page only thanks, and redirects.

So on the page with the form, there would be one db connect that would serve both the "display * from 'tablename'", and the "insert into 'tablename'".

(then closes the connection?)

How can this be done?

I need to run an errand but I'll work with the idea as soon as I get back.
User avatar
mikemike
Forum Contributor
Posts: 355
Joined: Sun May 24, 2009 5:37 pm
Location: Chester, UK

Re: mysql insert question

Post by mikemike »

You're doing this wrong. You don't need seperate tables for each page, thats daft. Have one centralised table with a field specifying the page name, then when calling the comments just use WHERE page='gallery', or whatever.
Post Reply