How can fix a problem populating database from PHP?

Question

1.00/5 (1 vote)

See more:

Hi, I'm facing a problem populating MYSQL database from a form using PHP and I have no idea why is not working.

What I have tried:

MYSQL
<pre>CREATE TABLE `bus_location` (
  `id` int(11) NOT NULL,
  `address` text DEFAULT NULL,
  `city` text DEFAULT NULL,
  `state` text DEFAULT NULL,
  `b_number` text DEFAULT NULL,
  `b_name` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `bus_location`
  ADD PRIMARY KEY (`id`);
COMMIT;

FORM

<form action="sendlocation.php" method="POST">
    ADDRESS: <input name="address" type="text" value="<?php echo $row['address']?>" value="" required/><br />
    CITY: <input name="city" type="text" value="<?php echo $row['city']?>" value="" required/><br />
    STATE: <input name="state" value="<?php echo $row['state']?>" type="text" value="" required/><br />
    PHONE NUMBER: <input name="b_number" value="<?php echo $row['b_number']?>" type="text" value="" required/><br />
    BUSINESS NAME: <input name="b_name" value="<?php echo $row['b_name']?>" type="text" value="" required/><br />
    <input type="submit" name="submit" value="Save Data">
</form>

THE PHP FILE

<?php 
include 'dbc.php';

if(isset($_POST['submit'])){
	//print_r($_POST); exit;
	$address = $_POST['address'];
	$city = $_POST['city'];
	$state = $_POST['state'];
	$b_number = $_POST['b_number'];
	$b_name = $_POST['b_name'];
	mysqli_query($con, "DELETE FROM bus_location");
	$query = "INSERT INTO bus_location (address, city, state, b_number, b_name)  VALUES ($address, $city, $state, $b_number, $b_name)";

	header("location:location.php");
	exit();
	
}

?>

Posted 28-Sep-22 9:04am

Sphere Wallpapers

Updated 29-Sep-22 6:17am

v2

Add a Solution

Comments

Richard MacCutchan 29-Sep-22 3:47am

You never execute the query.

2 solutions

Solution 1

Aside from the fact that the only query you're actually executing is DELETE FROM bus_location, you have a more serious problem.

Assuming you were actually intending to execute the query stored in the $query variable, your code would be vulnerable to SQL Injection[^]. NEVER use string concatenation/interpolation to build a SQL query. ALWAYS use a parameterized query.

PHP: SQL Injection - Manual[^]
PHP: Prepared statements and stored procedures - Manual[^]

Posted 28-Sep-22 21:46pm

Richard Deeming

Comments

Sphere Wallpapers 29-Sep-22 12:15pm

Thanks for your response, I was aware of the vulnerable to SQL Injection and corrected my problem, It was an MYSQL database problem, also the posted PHP codes where from an example found on the web. This is not for a live website but intranet device I control remotely via web app.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sphere Wallpapers · Accepted Answer · 2022-09-29T06:17:00

I fixed my problem by implementing the following codes to PHP and modification to the MYSQL database.
PHP

if(isset($_POST['submit'])){
	$address = $_POST['address'];
	$city = $_POST['city'];
	$state = $_POST['state'];
	$b_number = $_POST['b_number'];
	$b_name = $_POST['b_name'];
	mysqli_query($con, "DELETE FROM bus_location");

	mysqli_query($con, "INSERT INTO bus_location SET
		address='$address',
		city='$city',
		state='$state',
		b_number='$b_number',
		b_name='$b_name'
	");

	header("location:location.php");
	exit();
	
}

MYSQL

CREATE TABLE `bus_location` (
  `id` int(11) NOT NULL,
  `address` text DEFAULT NULL,
  `city` text DEFAULT NULL,
  `state` text DEFAULT NULL,
  `b_number` text DEFAULT NULL,
  `b_name` text DEFAULT NULL,
  `role` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


ALTER TABLE `bus_location`
  ADD PRIMARY KEY (`id`);

--
ALTER TABLE `bus_location`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=50;
COMMIT;