PHP form submitting to MySQL db

[danny232]

I'm new to php so i'm not the best with the terms at the minute!

 

I'm trying to create a website where I can display our job vacancies on our main website and edit/add job vacancies in an admin panel in the back end.

 

I've got the database setup correctly and i've got the php page to connect to the database, i've created a php form to submit to the mysql database but i've had no luck (it keeps saying posting error).

 

Can anyone show me an example of how to do this please?

 

Thanks

Danny

[Barand]

Post your code. Use the <> button above.

[danny232]

<center>
<form action="insert.php" method="post">
Title<br />
<input type=text name=title maxlength=30 size=30><br /><br />
Description<br />
<textarea name=title maxlength=30 size=30 rows="8" cols="30"></textarea><br /><br />
Wage<br />
£<input type=text name=wage maxlength=8 size=5><br /><br />
Expiry Date<br />
<input type=expiry name= maxlength=15 size=15><br /><br />
Apply to:<br />
<input type=text name=apply maxlength=30 size=30><br /><br />
<input type="submit" value="Submit Job">
</form>

The insert.php

<?php include 'config.php'; ?>

<?
	if (!$title || !$desc || !$wage || !$expiry || !$apply)
{
	echo "Missing Fields.<br />"
		."Go back.";
	exit;
}

$title	= addslashes($title);
$desc	= addslashes($desc);
$wage	= addslashes($wage);
$expiry	= addslashes($expiry);
$apply	= addslashes($apply);

mysql_select_db("jobs");
$query = "insert into jobs values
		('".$title."', '".$desc."', '".$wage."', '".$expiry."', '".$apply."')";
$result = mysql_query($query);
if ($result)
	echo mysql_affected_rows()." Job entered into database.";
?>

When I submit the form I just get the error message "Missing fields, go back"

Edited July 4, 2013 by danny232

[AbraCadaver]

When I submit the form I just get the error message "Missing fields, go back"

 

No shit... Try:

$_POST['title'] //etc

[Barand]

Error in this line

<input type=expiry name= maxlength=15 size=15><br /><br />

Also values should be quoted eg type="text"

 

But your main problem is that your reference book seems to be 10 years out of date (look up register_globals) and you need to pick up the posted values from the $_POST array

 

 

$title = $_POST['title'];

etc

[danny232]

Thanks for your replies.

 

insert.php

<?php include 'config.php'; ?>

<?
	if (!$title || !$desc || !$wage || !$expiry || !$apply)
{
	echo "Missing Fields.<br />"
		."Go back.";
	exit;
}

$title	= $_POST['title'];
$desc	= $_POST['desc'];
$wage	= $_POST['wage'];
$expiry	= $_POST['expiry'];
$apply	= $_POST['apply'];

mysql_select_db("Jobs");
$_POST = "INSERT into Jobs values
		('".$title."', '".$desc."', '".$wage."', '".$expiry."', '".$apply."')";
$result = mysql_query($query);
if ($result)
	echo mysql_affected_rows()." Job entered into database.";
?>

index.html

<center>
<form action="insert.php" method="post">
Title<br />
<input type="text" name="title" maxlength="30" size="30"><br /><br />
Description<br />
<textarea name="title" maxlength="30" size="30" rows="8" cols="30"></textarea><br /><br />
Wage<br />
£<input type="text" name="wage" maxlength="8" size="5"><br /><br />
Expiry Date<br />
<input type="text" name="expiry" maxlength="15" size="15"><br /><br />
Apply to:<br />
<input type="text" name="apply" maxlength="30" size="30"><br /><br />
<input type="submit" value="Submit Job">
</form>

[Barand]

You are testing for missing fields before you get the values from the POST array

[danny232]

You are testing for missing fields before you get the values from the POST array

<?php include 'config.php'; ?>

<?

$title	= $_POST['title'];
$desc	= $_POST['desc'];
$wage	= $_POST['wage'];
$expiry	= $_POST['expiry'];
$apply	= $_POST['apply'];

	if (!$title || !$desc || !$wage || !$expiry || !$apply)
{
	echo "Missing Fields.<br />"
		."Go back.";
	exit;
}

mysql_select_db("jobs");
$_POST = "INSERT into Jobs values
		('".$title."', '".$desc."', '".$wage."', '".$expiry."', '".$apply."')";
$result = mysql_query($query);
if ($result)
	echo mysql_affected_rows()." Job entered into database.";
?>

i've changed it to this now, and it doesn't seem to give the 'Missing fields' error. However it's still not posting to the database.

[AbraCadaver]

• If you do not specify a list of column names for INSERT ... VALUES or INSERT ... SELECT, values for every column in the table must be provided by the VALUES list or the SELECT statement. If you do not know the order of the columns in the table, use DESCRIBE tbl_name to find out.

Also:

$result = mysql_query($query) or die(mysql_error());

 

[danny232]

I now get "No database selected".

I've run the config.php and the statement comes back as "Connected to MYSQL".

Would the error mean it's not connecting to the actual database or the table within the database?

<?php include 'config.php'; ?>

<?

$title	= $_POST['title'];
$desc	= $_POST['desc'];
$wage	= $_POST['wage'];
$expiry	= $_POST['expiry'];
$apply	= $_POST['apply'];

	if (!$title || !$desc || !$wage || !$expiry || !$apply)
{
	echo "<center><br /><br /><br /><br /><br />Missing Fields.<br />"
		."<a href='javascript: history.go(-1)'>Go Back</a></center>";
	exit;
}

mysql_select_db("jobs");
$query = "INSERT INTO jobs (title,wage,desc,expiry,apply)";

$result = mysql_query($query) or die(mysql_error());
if ($result)
	echo mysql_affected_rows()." Job entered into database.";
?>

P.s i'm new to PHP if you couldn't tell...

Edited July 5, 2013 by danny232

[Barand]

you have

mysql_select_db("jobs");

you need to select the database that contains the jobs table

[danny232]

It's now recognising the database and the form fields, but now coming back with:

 

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 '; VALUES (title,desc,wage,expiry,applyto)' at line 1

 

Highlighted in bold is what was entered into the form fields.

<?php include 'config.php'; ?>

<?

$title	= $_POST['title'];
$desc	= $_POST['desc'];
$wage	= $_POST['wage'];
$expiry	= $_POST['expiry'];
$apply	= $_POST['apply'];

	if (!$title || !$desc || !$wage || !$expiry || !$apply)
{
	echo "<center><br /><br /><br /><br /><br />Missing Fields.<br />"
		."<a href='javascript: history.go(-1)'>Go Back</a></center>";
	exit;
}

mysql_select_db("jobs");
$query = "INSERT INTO Persons (title, desc, wage, expiry, apply)
	VALUES ('$_POST[title]','$_POST[desc]','$_POST[wage]','$_POST[expiry]','$_POST[apply]'";

$result = mysql_query($query) or die(mysql_error());
if ($result)
	echo mysql_affected_rows()." Job entered into database.";
?>

[Barand]

"desc" is a reserved MySql word. Either change it (recommended) or enclose it in backticks in your queries eg `desc`

[danny232]

I've changed the column name now to "body".

<?php include 'config.php'; ?>

<?

$title	= $_POST['title'];
$body	= $_POST['body'];
$wage	= $_POST['wage'];
$expiry	= $_POST['expiry'];
$apply	= $_POST['apply'];

	if (!$title || !$body || !$wage || !$expiry || !$apply)
{
	echo "<center><br /><br /><br /><br /><br />Missing Fields.<br />"
		."<a href='javascript: history.go(-1)'>Go Back</a></center>";
	exit;
}

mysql_select_db("db477825879");
$query = "INSERT INTO jobs (title, body, wage, expiry, apply)
	VALUES ('$_POST[title]','$_POST[body]','$_POST[wage]','$_POST[expiry]','$_POST[apply]'";

$result = mysql_query($query) or die(mysql_error());
if ($result)
	echo mysql_affected_rows()." Job entered into database.";
?>

index.html

<center>
<form action="insert.php" method="post">
Title<br />
<input type="text" name="title" maxlength="30" size="30"><br /><br />
Description<br />
<textarea name="body" maxlength="30" size="30" rows="8" cols="30"></textarea><br /><br />
Wage<br />
£<input type="text" name="wage" maxlength="8" size="5"><br /><br />
Expiry Date<br />
<input type="text" name="expiry" maxlength="15" size="15"><br /><br />
Apply to:<br />
<input type="text" name="apply" maxlength="30" size="30"><br /><br />
<input type="submit" value="Submit Job">
</form>

and here's a screenshot of the database in phpmyadmin

 

[mac_gyver]

you seem to be randomly changing your code without any reason. when you do make a change to your code, you must know why you are changing it. up until post #10, the values ( ... ) part of your query had the correct syntax. in post #10, you removed that part of your query and in all the posts since then, you have left off the closing ).

 

you must actually know the meaning of each line of code you are writing. the most commonly used syntax for an insert query is -

INSERT INTO your_table_name (your_column1, your_column2, your_column3, ...) VALUES ('your_string_value1','your_string_value2','your_string_value3',...)

it's your job as a programmer to make sure that your php code is producing a database query that has the correct syntax.

 

also, since you are not using prepared query statements to protect against sql injection, you must use your database library's escape function on each piece of string data (the values enclosed by single-quotes in the query) in the query. for the mysql_ database library, this would be the mysql_real_escape_string() function. for numerical values (int, decimal, float,...) that would not be used as a piece of string data in the query (these would not enclosed by single-quotes), you need to validate/cast them as the appropriate numerical data type.

 

lastly, the mysql_ database library is depreciated starting in php 5.5 and any new code should be written using either the mysqli_ or PDO database libraries.

[danny232]

<?php include 'config.php'; ?>

<?

$title	= $_POST['title'];
$body	= $_POST['body'];
$wage	= $_POST['wage'];
$expiry	= $_POST['expiry'];
$apply	= $_POST['apply'];

	if (!$title || !$body || !$wage || !$expiry || !$apply)
{
	echo "<center><br /><br /><br /><br /><br />Missing Fields.<br />"
		."<a href='javascript: history.go(-1)'>Go Back</a></center>";
	exit;
}

mysqli_select_db("db477825879");
$query = "INSERT INTO jobs (title, body, wages, expiry, apply) VALUES ('title','body','wages','expiry','apply')"

$result = mysql_query($query) or die(mysql_error());
if ($result)
	echo mysql_affected_rows()." Job entered into database.";

?>

I'm now getting a new error on line 21 about the syntax: Parse error: syntax error, unexpected '$query' (T_VARIABLE) in /homepages/23/d477349413/htdocs/beta/admin/insert.php on line 21

 

I'm hoping to get the script running first then do all the security mesaures afterwards.

 

Thanks for your help

[AbraCadaver]

Get an editor that does PHP syntax highlighting and you will see. Notepad++ unless you find another that you like.

[boompa]

Look to the line before line 21. What's missing from that line?

[jcbones]

You are selecting your database with a mysqli function, and using mysql on the rest of it.  You cannot mix and match the database functions like that.  It is either 100% mysqli, or 100%mysql. At the end of your $query string, you're missing the closing semi-colon (.

[danny232]

Thanks for all your help, it finally works!

 

In regards to security, the script will be in an admin area which will be in a protected directory, is it still worth creating a php function to block sql injections?

[danny232]

I've managed to get a new php page to retrieve data from the database but i'm struggling to repeat the same command so it displays all the tables. Can anyone help please?

<?php include 'admin/config.php'; ?>

<?php 
 Echo "<strong>";
 ?>

<?php
// Make a MySQL Connection
$query = "SELECT * FROM jobs ORDER BY id ASC"; 
	 
$result = mysql_query($query) or die(mysql_error());


$row = mysql_fetch_array($result) or die(mysql_error());
echo $row['title']. " <br /> </strong>". $row['body'] . " <br /><br />Salary: ". $row['wage'] . " <br />Closing Date: ". $row['expiry'] . " <br /><br />To apply for this position, please email your CV to ". $row['apply'];


?>

[Barand]

Use a while() loop

$result = mysql_query($query) or die(mysql_error());

while ($row = mysql_fetch_array($result) ) {
    echo ...
}