Page 1 of 1

mysql insert question

Posted: Thu Jun 11, 2009 8:30 pm
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')

Re: mysql insert question

Posted: Thu Jun 11, 2009 9:09 pm
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";
}
?>

Re: mysql insert question

Posted: Thu Jun 11, 2009 9:23 pm
by pearjam
Just tried - didn't work :(

No error, but nothing went into the table.

Re: mysql insert question

Posted: Thu Jun 11, 2009 11:52 pm
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']}')";

Re: mysql insert question

Posted: Fri Jun 12, 2009 1:44 pm
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?

Re: mysql insert question

Posted: Fri Jun 12, 2009 5:36 pm
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']}')";
 

Re: mysql insert question

Posted: Fri Jun 12, 2009 6:09 pm
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.

Re: mysql insert question

Posted: Sat Jun 13, 2009 9:01 am
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?

Re: mysql insert question

Posted: Sat Jun 13, 2009 10:07 am
by mikemike
Just put a hidden field in the form.

I HIGHLY recommend you don't though, massive security flaw.

Re: mysql insert question

Posted: Sat Jun 13, 2009 10:53 am
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?

Re: mysql insert question

Posted: Sat Jun 13, 2009 11:15 am
by mikemike
why would you want to?

Re: mysql insert question

Posted: Sat Jun 13, 2009 11:28 am
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.

Re: mysql insert question

Posted: Sat Jun 13, 2009 11:48 am
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.

Re: mysql insert question

Posted: Sat Jun 13, 2009 8:56 pm
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.