Jump to content
Please Use CODE Tags

php database insertion by a form

prolemur
By prolemur
in Programming
 Share
Posted

Hey i'm having trouble inserting data into my database using my little php script. It should take the data from the form and add it to the database houses but nothing happens. It would have probably helped if I did some error checking in my code, but i'm just trying to get my feet wet in databases and stuff.

 

Here's my code and a screen shot of mysql database:

 

index.php

<html>	<head>		<title>House Listings</title>		<link rel = "stylesheet" href = "style.css">	</head>	<body>		<h2>Recorded House Listings</h2>		<table>			<tr>				<th>Id</th>				<th>Street</th>				<th>Address</th>				<th>Bed</th>				<th>Bath</th>				<th>Price</th>				<th>Date</th>			</tr><?php	$link = mysqli_connect("localhost", "root", "password", "home");	$sql = "SELECT * FROM houses";	$result = mysqli_query($link, $sql);	while($row = mysqli_fetch_array($result)) {		echo "\t\t\t<tr>\n\t\t\t\t<td>" , $row["id"] , "</td>\n\t\t\t\t<td>" , $row["street"] , "</td>\n\t\t\t\t<td>" , $row["address"] , "</td>\n\t\t\t\t<td>" , $row["bed"] , "</td>\n\t\t\t\t<td>" , $row["bath"] , "</td>\n\t\t\t\t<td>$" , $row["price"] , "</td>\n\t\t\t\t<td>" , $row["date"] , "</td>\n\t\t\t</tr>\n";	}?>		</table>		<form action = "add.php" method = "post">			<p>Street</p><input type = "text" name = "street"/>			<p>Address</p><input type = "text" name = "address"/>			<p>Bed</p><input type = "text" name = "bed"/>			<p>Bath</p><input type = "text" name = "bath"/>			<p>Price</p><input type = "text" name = "price"/>			<p>Date</p><input type = "text" name = "date"/>			<input type = "submit" name = "submit" value = "Add"/>		</form>	</body></html>

add.php

<?php	$link = mysqli_connect("localhost", "root", "password", "home");	$sql = "INSERT INTO houses(id, street, address, bed, bath, price, date) VALUES (NULL, ?, ?, ?, ?, ?, ?)";	$stmt = mysqli_prepare($link, $sql);	mysqli_stmt_bind_param($stmt, "isiidis", $_POST["street"], $_POST["address"], $_POST["bed"], $_POST["bath"], $_POST["price"], $_POST["date"]);	mysqli_stmt_execute($stmt);	mysqli_stmt_close($stmt);	mysqli_close($link);	header("Location: index.php");?>

http://puu.sh/j8ihx/9ca3aef1fd.png

Link to comment
https://linustechtips.com/topic/412757-php-database-insertion-by-a-form/
Share on other sites
Link to post
Share on other sites
Posted

This might not be the exact reason your code is failing but you don't need to pass null as the ID. Just leave the ID out of the column list 
Also words like "date" and for example "description" are sometimes keywords, or reserved words. I wrap them in [brackets] just to be on the safe side.

 

So 

$sql = "INSERT INTO houses(id, street, address, bed, bath, price, date) VALUES (NULL, ?, ?, ?, ?, ?, ?)";

Becomes

$sql = "INSERT INTO [houses] ([street], [address], [bed], [bath], [price], [date]) VALUES (?, ?, ?, ?, ?, ?)";

Also wrap your insert code in a try .. catch like so

 

try {$link = mysqli_connect("localhost", "root", "password", "home");	$sql = "INSERT INTO houses([street], [address], [bed], [bath], [price], [date]) VALUES (NULL, ?, ?, ?, ?, ?, ?)";	$stmt = mysqli_prepare($link, $sql);	mysqli_stmt_bind_param($stmt, "isiidis", $_POST["street"], $_POST["address"], $_POST["bed"], $_POST["bath"], $_POST["price"], $_POST["date"]);	mysqli_stmt_execute($stmt);	mysqli_stmt_close($stmt);	mysqli_close($link);	header("Location: index.php");}catch (Exception $e) {echo 'Caught exception: ',  $e->getMessage(), "\n";}

This way you'll have a better idea of exactly where the code is failing.
Be sure to have error reporting turned on in your php.ini file too (while you're in development)

// Gigabyte 990FXA-UD3 // AMD FX-8320 CPU @ 4.3 Ghz (7-21.5 Multiplier) 200.90mhz FSB CPU-Z Validated // Kraken X40 AIO - 2x140mm Push-Pull // 4GB Corsair Vengeance LP - 8GB Avexir Core Series Red 1760Mhz // Sapphire R9 Fury Nitro 1130mhz/4GB 1025mhz (Effective) GPU-Z Validation // Corsair SP2500 2.1 & Microlab Solo 9C Speakers // Corsair K90 Silver - Cherry MX Red & Blue LEDs // EVGA SuperNova 850w G2

Link to post
Share on other sites
Posted
  • Author

Thanks, removing the null value for the id fixed it. yay!

 

I'm going to get addicted to php... :D

 

edit: this is what I made btw. I plan on adding little x's at the end of each row of the table so i can delete them and playing around with a bunch of other things.

 

2bfc598d99.png

Link to post
Share on other sites
Posted

PDO feels much nicer to use than mysqli, this is how i would do it I know the problem has been solved but still might be worth a look ?

 

have a database file

<?phpclass db {    private static $handle;    public static function connect($host, $username, $password, $db){        self::$handle = new PDO("mysql:host={$host};dbname={$db}", $username, $password);        self::$handle->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING );    }    public static function query($sql, $args=null){        $sth = self::$handle->prepare($sql);        $sth->execute($args);        return $sth;    }}db::connect("location", "username", "password", "database");?>

then to insert

<?phprequire_once('database.php');$sql = db::query("insert into houses street, address, bed, bath, price, date) VALUES (:street, :address, :bed, :bath, :price, :date", array (":street" => $_POST['street'],":address" => $_POST['address'],":bed" => $_POST['address'],":bath" => $_POST['bath'],":price" => $_POST['price'],":date" => $_POST['date']));if(!$sql){echo "error";}else{//redirect here / message here}?>

this means you don't have to remember the order of your query, :street rather than a ? which does make it much easier if you have a long query.

                     ¸„»°'´¸„»°'´ Vorticalbox `'°«„¸`'°«„¸
`'°«„¸¸„»°'´¸„»°'´`'°«„¸Scientia Potentia est  ¸„»°'´`'°«„¸`'°«„¸¸„»°'´

Link to post
Share on other sites
Posted

I'm going to get addicted to php... :D

 

THIS

 

since I learned to program in PHP I never want to program in anything else

it's such a flexible language and you constantly learn new ways to make stuff easier and more elegant

Link to post
Share on other sites
Posted

THIS

 

since I learned to program in PHP I never want to program in anything else

it's such a flexible language and you constantly learn new ways to make stuff easier and more elegant

may i ask what other programming languages you know? i think you might be mistaking PHP with your personal progress. you're getting better, PHP is possibly just restricting your horizons imho

 

try doing stuff in languages

Link to post
Share on other sites
Posted
  • Author

may i ask what other programming languages you know? i think you might be mistaking PHP with your personal progress. you're getting better, PHP is possibly just restricting your horizons imho

 

try doing stuff in languages

I know turing, java and some c++. I will have to learn javascript to have the majority of web developing covered. Would I be able to do this in other languages, with mysql or using something in it's place?

Link to post
Share on other sites
Posted

I know turing, java and some c++. I will have to learn javascript to have the majority of web developing covered. Would I be able to do this in other languages, with mysql or using something in it's place?

you can do it in any language, but there are well-established combos and environments that suit it best

the ones i'm aware of are:

php / apache

javascript / node.js

python / django

C# / IIS

 

the first is the one you know.

 

the second is the one i'm very new to but i'm very much liking and i recommend.

 

the third is the one i want to learn when i'm at a good point with my current project in node. it should be another different story from both php and js, so i look forward to see how things are done the python way.

python/django vs php/apache and the others might be comparing apples with melons, but well i'm talking about combos, you get it.

 

the fourth i'm not really excited about but it's there for the sake of some sort of completeness.

Link to post
Share on other sites
Posted

Since we're discussing web frameworks and languages, I'd highly recommend Ruby on Rails. Honestly, php is very restrictive, not to mention overly verbose. For example, the code you posted would probably just be a few lines for the form, and one line to save to the database. The fact that you have to actually write any sql at all is somewhat jarring for me, working with rails for so long. Rails can be a little confusing, but it really is flexible and incredibly elegant. As Ciccioo mentioned, doing everything in javascript with node.js is also looking like the future. Web technologies are moving incredibly fast and honestly, learning with something ancient like php is unnecessarily keeping you back and in the past.

Workstation: i7-4930k | Asus Rampage IV Gene | Reference GTX 780 | 32GB Crucial Ballistix | 500GB Samsung 840 EVO | Corsair RM650 | MidNight Black BitFenix Prodigy M

 

Old Rig: i5-2500k @ 4.7 GHz | Asus P8P67 Deluxe | EVGA GTX 560 | 16GB Corsair Vengeance | 240 GB Samsung 830 Pro | 1TB Hitachi | CoolerMaster Storm Scout (1)

Link to post
Share on other sites
Posted

IMO oop with mysqli is a lot easier to remember.

<form action = "add.php" method = "post">	<p>Street</p><input type = "text" name = "street"/>	<p>Address</p><input type = "text" name = "address"/>	<p>Bed</p><input type = "text" name = "bed"/>	<p>Bath</p><input type = "text" name = "bath"/>	<p>Price</p><input type = "text" name = "price"/>	<p>Date</p><input type = "text" name = "date"/>	<input type = "submit" name = "submit" value = "Add"/></form><?php		if(isset($_POST['street'], $_POST['address'], $_POST['bed'], $_POST['bath'], $_POST['price'], $_POST['date']))	{		$mysqli = new mysqli(dbhost, dbuser, dbpassword, dbname);		$mysqli->prepare("INSERT INTO `table` (row1, row2, row3, row4) VALUES (?, ?, ? ,?)");		$mysqli->bind_param("ssss", $var1, $var2, $var3, $var4); // change s to i if int etc		$mysqli->exectue();		$mysqli->close();		header("Location: /index.php");	}

i want to die

Link to post
Share on other sites
Posted
  • Author

 

IMO oop with mysqli is a lot easier to remember.

<form action = "add.php" method = "post">	<p>Street</p><input type = "text" name = "street"/>	<p>Address</p><input type = "text" name = "address"/>	<p>Bed</p><input type = "text" name = "bed"/>	<p>Bath</p><input type = "text" name = "bath"/>	<p>Price</p><input type = "text" name = "price"/>	<p>Date</p><input type = "text" name = "date"/>	<input type = "submit" name = "submit" value = "Add"/></form><?php		if(isset($_POST['street'], $_POST['address'], $_POST['bed'], $_POST['bath'], $_POST['price'], $_POST['date']))	{		$mysqli = new mysqli(dbhost, dbuser, dbpassword, dbname);		$mysqli->prepare("INSERT INTO `table` (row1, row2, row3, row4) VALUES (?, ?, ? ,?)");		$mysqli->bind_param("ssss", $var1, $var2, $var3, $var4); // change s to i if int etc		$mysqli->exectue();		$mysqli->close();		header("Location: /index.php");	}

That does look a lot better than passing in the same variable for each command.

Link to post
Share on other sites
Posted

That does look a lot better than passing in the same variable for each command.

Just realized it's improper syntax, woops flew right over my head. let me give u the right stuffs.

	if(isset($_POST['street'], $_POST['address'], $_POST['bed'], $_POST['bath'], $_POST['price'], $_POST['date']))	{		$mysqli = new mysqli(dbhost, dbuser, dbpassword, dbname);		$stmt = $mysqli->prepare("INSERT INTO `table` (row1, row2, row3, row4) VALUES (?, ?, ? ,?)");		$stmt->bind_param("ssss", $var1, $var2, $var3, $var4); // change s to i if int etc		$stmt->exectue();		$stmt->close();		header("Location: /index.php");	}

i want to die

Link to post
Share on other sites
Posted

 

Just realized it's improper syntax, woops flew right over my head. let me give u the right stuffs.

	if(isset($_POST['street'], $_POST['address'], $_POST['bed'], $_POST['bath'], $_POST['price'], $_POST['date']))	{		$mysqli = new mysqli(dbhost, dbuser, dbpassword, dbname);		$stmt = $mysqli->prepare("INSERT INTO `table` (row1, row2, row3, row4) VALUES (?, ?, ? ,?)");		$stmt->bind_param("ssss", $var1, $var2, $var3, $var4); // change s to i if int etc		$stmt->exectue();		$stmt->close();		header("Location: /index.php");	}
$stmt->exectue();

I'm sure that should be execute(); ?

Speedtests

WiFi - 7ms, 22Mb down, 10Mb up

Ethernet - 6ms, 47.5Mb down, 9.7Mb up

 

Rigs

Spoiler

 Type            Desktop

 OS              Windows 10 Pro

 CPU             i5-4430S

 RAM             8GB CORSAIR XMS3 (2x4gb)

 Cooler          LC Power LC-CC-97 65W

 Motherboard     ASUS H81M-PLUS

 GPU             GeForce GTX 1060

 Storage         120GB Sandisk SSD (boot), 750GB Seagate 2.5" (storage), 500GB Seagate 2.5" SSHD (cache)

 

Spoiler

Type            Server

OS              Ubuntu 14.04 LTS

CPU             Core 2 Duo E6320

RAM             2GB Non-ECC

Motherboard     ASUS P5VD2-MX SE

Storage         RAID 1: 250GB WD Blue and Seagate Barracuda

Uses            Webserver, NAS, Mediaserver, Database Server

 

Quotes of Fame

On 8/27/2015 at 10:09 AM, Drixen said:

Linus is light years ahead a lot of other YouTubers, he isn't just an average YouTuber.. he's legitimately, legit.

On 10/11/2015 at 11:36 AM, Geralt said:

When something is worth doing, it's worth overdoing.

On 6/22/2016 at 10:05 AM, trag1c said:

It's completely blown out of proportion. Also if you're the least bit worried about data gathering then you should go live in a cave a 1000Km from the nearest establishment simply because every device and every entity gathers information these days. In the current era privacy is just fallacy and nothing more.

 

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing Programming

×