PHP basic query

Heathen

Hi there,

I am a noob to php and mysql, i have been working on this small database just to get my head around working with php and mysql...

My DB is setup and working, i can add info to the table through phpmyadmin, however when i try to add from a form it will not add and does not give me any error to help... SELECT works fine and i can see my data on the page, but POST does not seem to be adding data to the DB...

I will include the index.php, create.php and includes/connection.php scripts below.. i will also attach the database, this is only a small project i am doing to try learn so there is no private data or anything.

if anyone could shed any light i would be most grateful

Thanks

index.php

<?php
    include 'includes/connection.php';
    
    $query = "SELECT * FROM people";
	
	$result = mysql_query($query);
	
	while($person = mysql_fetch_array($result)) {
		echo "<h3>" . $person['Name'] . "</h3>";
		echo "<p>" . $person['Description'] . "</p>";
	}
?>


<h1>Create a User</h1>
<form action="create.php" method="post">
	Name: <input type="text" name="inputName" value="" /><br>
	Description: <input type="text" name="inputDesc" value="" />
	<br>
	<br>
	<input type="submit" value="Submit" />
</form>

create.php

<?php
	include 'includes/connection.php';
	
	$name = $_POST['inputName'];
	$desc = $_POST['inputDesc'];
	
	if(!$_POST['submit']) {
		echo "Please fill out the form";
		header('Location: index.php');
	} else {
		mysql_query("INSERT INTO people (`ID`,`Name`,`Description`)
					VALUES(NULL,'$name','$desc')") or die(mysql_error());
		echo "User has been added!";
		header('Location: index.php');
		
		}
 ?>

includes/connection.php

<?php
	$dbhost = 'localhost';
	$dbuser = 'root';
	$dbpass = '';
	$db = 'userdb';
	
	$conn = mysql_connect($dbhost,$dbuser,$dbpass);
	mysql_select_db($db);
?>

SQL Database

--
-- Database: `userdb`
--

-- --------------------------------------------------------

--
-- Table structure for table `people`
--

CREATE TABLE IF NOT EXISTS `people` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(25) NOT NULL,
  `Description` varchar(150) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `people`
--

INSERT INTO `people` (`ID`, `Name`, `Description`) VALUES
(1, 'Bob', 'bob blah bob blah description'),
(2, 'Jeff', 'Jeff, description yo!'),
(3, 'tester', 'story horse');

Thanks for reading

H

BrokenArrows

You are inserting null but your create table does not allow null for that column

Nulty

finish your query strings with semi colons

Edit: actually your not supposed to with that function - sorry

Heathen

BrokenArrows wrote: »

You are inserting null but your create table does not allow null for that column

thanks for the reply

should i be allowing NULL for the ID row in the table, in the database?

sorry if im asking stupid questions, i am really new to this..

H

Nulty

Heathen wrote: »

thanks for the reply

should i be allowing NULL for the ID row in the table, in the database?

sorry if im asking stupid questions, i am really new to this..

H

You don't need to insert anything into ID, it's automatically incremented.

mysql_query("INSERT INTO people (`Name`,`Description`)
VALUES('$name','$desc')") or die(mysql_error());

Heathen

Ok, that makes sense...

I have changed

mysql_query("INSERT INTO people (`ID`,`Name`,`Description`)
					VALUES(NULL,'$name','$desc')") or die(mysql_error());

To

mysql_query("INSERT INTO people (`Name`,`Description`)
VALUES('$name','$desc')") or die(mysql_error());

in the create.php file, but still get the same problem, it just refreshes back to the index file with no new data added..

i bet its something really simple i messed up... im going blind looking at it since yesterday haha

H

Nulty

I don't use PHP so bear with my efforts to help!

Are those supposed to be backticks around the column names?

`Name` -v- 'Name'

Heathen

Nulty wrote: »

I don't use PHP so bear with my efforts to help!

Are those supposed to be backticks around the column names?

`Name` -v- 'Name'

your help is much appreciated, as well as BrokenArrows, thanks...

apparently i am to use backticks, but let me try with single quotes and see if it makes a difference

cheers

Nulty

http://www.w3schools.com/php/php_mysql_insert.asp

Shows here that you just put the column name in unquoted.

[php]mysql_query("INSERT INTO Persons (FirstName, LastName, Age)
VALUES ('Peter', 'Griffin',35)");[/php]

Creamy Goodness

the back ticks aren't the problem, and in fact good practice as it allows you to have mysql keywords in column names e.g. column name `index` wouldn't throw an error but column name index would.

quickest way of finding what's wrong here is to change your mysql_query to this:

$query = "INSERT INTO Persons (`Name`, `Age`) VALUES ('$name','$desc'))";
echo $query;
mysql_query($query) or die(mysql_error());

when the query prints to the screen copy and paste it into your mysql command line, tell us what error comes back

Heathen

Ok i have tried with and without backticks, and also the following

$query = "INSERT INTO Persons (`Name`, `Description`) VALUES ('$name','$desc'))";
echo $query;
mysql_query($query) or die(mysql_error());

still have no luck, i am using wampserver, with PHP ver 5.4.3, Mysql ver 5.5.24 and apache ver 2.2.22 surely though this is a basic enough task i am trying to achieve and these versions should not be the issue?

should i try maybe to create a new db user instead of using root? i figured root has full permissions so should be able to write to the database?

cheers for all the help so far i am very grateful

H

Dave!

As the poster above suggested, print the query to the browser (echo $query), then copy what is printed and paste it into the mysql command line or a client program. What error is being returned, if any?

Heathen

This may be of assistance....


I uploaded to one or my sites' servers and got the following when submitting...

Please fill out the form
Warning: Cannot modify header information - headers already sent by (output started at /home/***/public_html/userDB/create.php:8) in /home/***/public_html/userDB/create.php on line 9

is this of any help?

H

Dave!

Heathen wrote: »

This may be of assistance....


I uploaded to one or my sites' servers and got the following when submitting...



is this of any help?

H

Try remove the "User has been added" before redirecting. You can't do both of those. Use JavaScript to redirect if you want to show a message first.

Creamy Goodness

if(!$_POST['submit']) {
	echo "Please fill out the form";
	header('Location: index.php');
} else {
	mysql_query("INSERT INTO people (`ID`,`Name`,`Description`) VALUES(NULL,'$name','$desc')") or die(mysql_error());
	echo "User has been added!";
	header('Location: index.php');
		
}

this code is failing based on your error above, the error you get is showing me that it's going into the if and not going into the else as your error log shows "Please fill out the form" Also it's good practice to do a call to exit; after header(), php will still execute the rest of your script when that may not be the desired outcome.

Heathen

I have removed

echo "Please fill out the form";

and no longer get the error, but the data still does not get added to the DB.

H

cython

Heathen wrote: »

I have removed

echo "Please fill out the form";

and no longer get the error, but the data still does not get added to the DB.

H

Try adding an or die(mysql_error()) clause to your connect and DB selection statements, in case there is an issue arising there that is somehow not being exhibited when executing the query.

Heathen

cython wrote: »

Try adding an or die(mysql_error()) clause to your connect and DB selection statements, in case there is an issue arising there that is somehow not being exhibited when executing the query.

ok, ill try, however can you explain where exactly i should put that? really sorry, im a complete green horn ... :-/


H

Creamy Goodness

i think you misunderstood.

if (something is true) { 'please fill out the form' } else { //insert into the db; }

because it was saying 'please fill out the form', it doesn't execute the mysql_query() function that's in the else at all.

your proble here is with the !isset($_POST) this will fail because your input submit button doesn't have a name.

try this:

<input type="submit" name="submit" value="Submit" /> and see does it get into the else.

Heathen

Creamy Goodness wrote: »

i think you misunderstood.

if (something is true) { 'please fill out the form' } else { //insert into the db; }

because it was saying 'please fill out the form', it doesn't execute the mysql_query() function that's in the else at all.

your proble here is with the !isset($_POST) this will fail because your input submit button doesn't have a name.

try this:

<input type="submit" name="submit" value="Submit" /> and see does it get into the else.

YOU.... sir.... have earned a Pint :-)
that was it.. i never put in "name="submit"" such an easy slip of the mind

Thanks to all who have commented and helped, i am still learning and all of you have given me valuable input


Thanks guys

H

Creamy Goodness

excellent, it's a long haul slog debugging especially when you're new to programming but it's worth it.

another point...

don't rely on your inputs to be clean. for example if i enter a description like the following

"''); DROP TABLE people;

have a look at mysql_real_escape_string() http://php.net/manual/en/function.mysql-real-escape-string.php

Heathen

Creamy Goodness wrote: »

excellent, it's a long haul slog debugging especially when you're new to programming but it's worth it.

another point...

don't rely on your inputs to be clean. for example if i enter a description like the following

"''); DROP TABLE people;

have a look at mysql_real_escape_string() http://php.net/manual/en/function.mysql-real-escape-string.php

Thanks for the heads up on this, i am just trying to add modify and delete features tonight and then i am going to begin learning about how to sanitize inputs to prevent sql injection and xss and the likes... this is interesting stuff so i want to make sure i start right so my code doesn't become riddled with holes.

cheers for the help tonight guys!

H