Jump to content

PHP to insert into table

anevins

By anevins
in PHP Coding Help

 Share

Recommended Posts

anevins Regular Member

anevins

Posted
Posted

Hi there,

I have a form which I want to submit data into my tables.

There are going to be 4 tables involved with this form, and these 4 tables should relate to one another in some sort of way.

 

My problem is either PHP or MySQL, but I keep getting a warning which I can't figure out.

I remember this warning appearing even if the code before it is wrong, therefore I am not relying on it.

 

This is what the error says:

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in G:\xampp\htdocs\xampp\dsa\wp3.php on line 40

 

Here's my code:

<html>
<head>
<title>WP3</title>
</head>
<body>
<form id="search" name="search" id="search" method="get" action="searchresults.php" />
<input type="text" name="terms" value="Search..." />
<input class="button" type="submit" name="Search" value="Search" />
</form>

<?php

include_once('connectvars.php');

$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);


if(isset($_POST['report'])){

$firstname = mysqli_real_escape_string($dbc, trim($_POST['firstName']));
$middlename = mysqli_real_escape_string($dbc, trim($_POST['middleName']));
$lastname = mysqli_real_escape_string($dbc, trim($_POST['lastName']));
$image = mysqli_real_escape_string($dbc, trim($_POST['image']));
$phone = mysqli_real_escape_string($dbc, trim($_POST['phone']));
$organisation = mysqli_real_escape_string($dbc, trim($_POST['organisation']));
$street = mysqli_real_escape_string($dbc, trim($_POST['street']));
$town = mysqli_real_escape_string($dbc, trim($_POST['town']));
$city = mysqli_real_escape_string($dbc, trim($_POST['city']));


if (!empty($firstname) && !empty($middlename) && !empty($lastname) && !empty($image) && !empty($phone) && !empty($organisation) && !empty($city)) {

	$query = "INSERT INTO report (organisation, phoneNo) VALUES ('$organisation', '$phone');
			  INSERT INTO person (firstName, middleName, lastName) VALUES ('$firstname', '$middlename', '$lastname');
			  INSERT INTO identification (image) VALUES ('$image');
			  INSERT INTO location (street, town, city) VALUES ('$street', '$town', '$city')";

	$data = mysqli_query($dbc, $query);

		if (mysqli_num_rows($data) == 0) {

		mysqli_query($dbc, $query);
		echo "Thank you, your report has been received.";

		}

	else {
        // An account already exists for this username, so display an error message
        echo '<p>This report already exists.</p>';
        $username = "";
	}
}	
else echo "Please enter all of the fields";	
}

?>

<form id="report_sighting" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<h2>Report a sighting</h2>
<table>
	<tr>
		<td>
			<label>First name:</label>
		</td>
		<td>
			<input type="text" id="firstname" name="firstName" value="<?php if (!empty($firstname)) echo $firstname; ?>" />
		</td>
	</tr>
	<tr>
		<td>
			<label>Middle name:</label>
		</td>
		<td>
			<input type="text" id="middlename" name="middleName" value="<?php if (!empty($middlename)) echo $middlename; ?>" />
		</td>
	</tr>
	<tr>
		<td>
			<label>Last name:</label>
		</td>
		<td>
			<input type="text" id="lastname" name="lastName" value="<?php if (!empty($lastname)) echo $lastname; ?>" />
		</td>
	<tr>
		<td>
			<label>Upload Identification:</label>
		</td>
		<td>
			<input type="file" id="image" name="image" />
		</td>
	<tr>
	<tr>
		<td>
			<label>Contact phone number: </label>
		</td>
		<td>
			<input type="text" id="phone" name="phone" />
		</td>
	<tr>
	<tr>
		<td>
			<label>Organisation: </label>
		</td>
		<td>
			<input type="text" id="organisation" name="organisation" />
		</td>
	</tr>
	<tr>
		<td>
			<label>Street seen: </label>
		</td>
		<td>
			<input type="text" id="street" name="street" />
		</td>
	</tr>
	<tr>
		<td>
			<label>Town seen: </label>
		</td>
		<td>
			<input type="text" id="town" name="town" />
		</td>
	</tr>
	<tr>
		<td>
			<label>City seen: </label>
		</td>
		<td>
			<input type="text" id="city" name="city" />
		</td>
	</tr>
	<tr>
		<td>
			 
		</td>
		<td>
			<input type="submit" value="Report" name="report" />
		</td>
	</tr>
</table>
</form>

</body>
</html>

 

I've checked out the SQL statement and it's alright, so that leaves me with the PHP.

 

I would very much appreciate if anyone could help me out here,

thanks.

Link to comment
Share on other sites
Psycho Prolific Member

Psycho

Posted
Posted

You have absolutely no error checking on your current MySQL functions. So, if there is a problem you won't know what it is.

 

Change this

$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

 

To This (replace the DB name as necessary)

$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) or die ("Cant connect to server " . mysql_error());
if (mysqli_connect_errno()) {
    echo "Connect failed: " . mysqli_connect_error();
    exit();
}

 

Then Change this

$data = mysqli_query($dbc, $query);

 

To This

$data = mysqli_query($dbc, $query);
if ($data===false) {
    echo "Query failed: $query<br />Error: " . mysqli_error($dbc);
    exit();
}

Link to comment
Share on other sites
anevins Regular Member

anevins

Posted
  • Author
Posted

Thank you Mjdamato, I have used that code and now I am receiving feedback:

 

Query failed: INSERT INTO report (organisation, phoneNo) VALUES ('a', 'a'); INSERT INTO person (firstName, middleName, lastName) VALUES ('a', 'a', 'a'); INSERT INTO identification (image) VALUES ('entry2_pid.doc'); INSERT INTO location (street, town, city) VALUES ('a', 'a', 'a')

Error: 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 'INSERT INTO person (firstName, middleName, lastName) VALUES ('a', 'a', 'a'); ' at line 2

 

I'll spend 10 minutes checking over the syntax for now.

Link to comment
Share on other sites
anevins Regular Member

anevins

Posted
  • Author
Posted

I've looked over the code of the line:

				  INSERT INTO person (firstName, middleName, lastName) VALUES ('$firstname', '$middlename', '$lastname');

 

But I cannot find the syntax error.

 

Could you give me a hint to what is wrong please?

Link to comment
Share on other sites
Psycho Prolific Member

Psycho

Posted
Posted

You are apparently trying to combine multiple INSERT queries into a single string (separated by semicolons) and attempting to run them all at one. MySQL is NOT PHP - it doesn't work that way. I don't know if you can combine all the queries into a single string. In fact, I highly doubt it, but if you can you don't separate by semi-colons.

 

You should create and run each query separately.

 

The reason why I think it is not possible is when you run an insert query there are certain parameters (such as last insert ID) that are set. If you ran multiple inserts in one then how would you get the insert ID for each insert?

Link to comment
Share on other sites
anevins Regular Member

anevins

Posted
  • Author
Posted

Thank you for your feedback, I have now split up the multiple queries one bye one.

I'm now getting a warning with one of my else statements used to prevent duplication data in the database, but my form entered data has not been used in the database:

 

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given

This report already exists.

 

<html>
<head>
<title>WP3</title>
</head>
<body>
<form id="search" name="search" id="search" method="get" action="searchresults.php" />
<input type="text" name="terms" value="Search..." />
<input class="button" type="submit" name="Search" value="Search" />
</form>

<?php

include_once('connectvars.php');

$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) or die ("Cant connect to server " . mysql_error());
if (mysqli_connect_errno()) {
    echo "Connect failed: " . mysqli_connect_error();
    exit();
}


if(isset($_POST['report'])){

$firstname = mysqli_real_escape_string($dbc, trim($_POST['firstName']));
$middlename = mysqli_real_escape_string($dbc, trim($_POST['middleName']));
$lastname = mysqli_real_escape_string($dbc, trim($_POST['lastName']));
$image = mysqli_real_escape_string($dbc, trim($_POST['image']));
$phone = mysqli_real_escape_string($dbc, trim($_POST['phone']));
$organisation = mysqli_real_escape_string($dbc, trim($_POST['organisation']));
$street = mysqli_real_escape_string($dbc, trim($_POST['street']));
$town = mysqli_real_escape_string($dbc, trim($_POST['town']));
$city = mysqli_real_escape_string($dbc, trim($_POST['city']));
$size = mysqli_real_escape_string($dbc, trim($_POST['size']));
$model = mysqli_real_escape_string($dbc, trim($_POST['model']));
$colour = mysqli_real_escape_string($dbc, trim($_POST['colour']));


if (!empty($firstname) && !empty($middlename) && !empty($lastname) && !empty($image) && !empty($phone) && !empty($organisation) && !empty($city)) {

	$query = "INSERT INTO report (organisation, phoneNo) VALUES ('$organisation', '$phone')";
	$query = "INSERT INTO person (firstName, middleName, lastName) VALUES ('$firstname', '$middlename', '$lastname')";
	$query = "INSERT INTO identification (image) VALUES ('$image')";
	$query = "INSERT INTO location (street, town, city) VALUES ('$street', '$town', '$city')";
	$query = "INSERT INTO stick (size, colour, make) VALUES ('$size', '$colour', '$model')";

	$data = mysqli_query($dbc, $query);
		if ($data===false) {
			echo "Query failed: $query<br />Error: " . mysqli_error($dbc);
			exit();
		}

		if (mysqli_num_rows($data) === 0) {

		mysqli_query($dbc, $query);
		echo "Thank you, your report has been received.";

		}

	else {
        // An account already exists for this username, so display an error message
        echo '<p>This report already exists.</p>';
        $username = "";
	}
}	
else echo "Please enter all of the fields";	
}

?>

<form id="report_sighting" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
<h2>Report a sighting</h2>
<table>
	<tr>
		<td>
			<label>First name:</label>
		</td>
		<td>
			<input type="text" id="firstname" name="firstName" value="<?php if (!empty($firstname)) echo $firstname; ?>" />
		</td>
	</tr>
	<tr>
		<td>
			<label>Middle name:</label>
		</td>
		<td>
			<input type="text" id="middlename" name="middleName" value="<?php if (!empty($middlename)) echo $middlename; ?>" />
		</td>
	</tr>
	<tr>
		<td>
			<label>Last name:</label>
		</td>
		<td>
			<input type="text" id="lastname" name="lastName" value="<?php if (!empty($lastname)) echo $lastname; ?>" />
		</td>
	<tr>
		<td>
			<label>Upload Identification:</label>
		</td>
		<td>
			<input type="file" id="image" name="image" />
		</td>
	<tr>
	<tr>
		<td>
			<label>Contact phone number: </label>
		</td>
		<td>
			<input type="text" id="phone" name="phone" />
		</td>
	<tr>
	<tr>
		<td>
			<label>Organisation: </label>
		</td>
		<td>
			<input type="text" id="organisation" name="organisation" />
		</td>
	</tr>
	<tr>
		<td>
			<label>Street seen: </label>
		</td>
		<td>
			<input type="text" id="street" name="street" />
		</td>
	</tr>
	<tr>
		<td>
			<label>Town seen: </label>
		</td>
		<td>
			<input type="text" id="town" name="town" />
		</td>
	</tr>
	<tr>
		<td>
			<label>City seen: </label>
		</td>
		<td>
			<input type="text" id="city" name="city" />
		</td>
	</tr>
	<tr>
		<td>
			<label>Size: </label>
		</td>
		<td>
			<input type="text" id="size" name="size" />
		</td>
	</tr>
	<tr>
		<td>
			<label>Colour:</label>
		</td>
		<td>
			<input type="text" id="colour" name="colour" value="<?php if (!empty($colour)) echo $colour; ?>" />
		</td>
	<tr>
		<td>
			<label>Model / make:</label>
		</td>
		<td>
			<input type="text" id="model" name="model" value="<?php if (!empty($model)) echo $model;?>" />
		</td>
	<tr>
	<tr>
		<td>
			 
		</td>
		<td>
			<input type="submit" value="Report" name="report" />
		</td>
	</tr>
</table>
</form>
</body>
</html>

Link to comment
Share on other sites
Pikachu2000 Prolific Member

Pikachu2000

Posted
Posted

That error indicates that the query is failing and returning a boolean FALSE. But I have a question. What are you going to use to tie all of these pieces of information together once they're inserted? I mean what unique identifier will each record have that will identify all of them as belonging to the same record? I can't be certain without knowing how you're using the data, but from the looks of it, a single table might be the way to go with this part of it.

Link to comment
Share on other sites
anevins Regular Member

anevins

Posted
  • Author
Posted

I've been encouraged to use multiple tables, but I see your point as some tables might be useful to be as one, such as the identification with the person.

This is what I've been doing;

I've created EAR models through XML code which have been converted to DDL / MySQL with foreign keys of those relationships between entities through a 'case tool' from my uni.

This case tool has created the MySQL code; solving many to many relationships automatically but I still don't think the tables are communicating with one another. Are tables which have relations with other tables supposed to share data?

 

Here's the DDL code generated from my XML:

CREATE TABLE User ( 

  user_id INTEGER(11) NOT NULL,

  username CHAR(52) NOT NULL,

  password VARCHAR(20) NOT NULL,

  join_date VARCHAR(20) NOT NULL,

  first_name CHAR(52) NOT NULL,

  last_name CHAR(52) NOT NULL,

  PRIMARY KEY ( user_id )

);

   

CREATE TABLE Product ( 

  product_id INTEGER(11) NOT NULL,

  description VARCHAR(20) NOT NULL,

  title CHAR(52) NOT NULL,

  img VARCHAR(20) NOT NULL,

  price DECIMAL(7,2) NOT NULL,

  amount VARCHAR(20) NOT NULL,

  qty_stock INTEGER(5) NOT NULL,

  ProductHasCategory INTEGER(11) REFERENCES Category ( cat_id) ,

  PRIMARY KEY ( product_id )

);

   

CREATE TABLE Category ( 

  cat_id INTEGER(11) NOT NULL,

  name CHAR(52) NOT NULL,

  PRIMARY KEY ( cat_id )

);

   

CREATE TABLE Customer ( 

  cust_id INTEGER(11) NOT NULL,

  first_name CHAR(52) NOT NULL,

  last_name CHAR(52) NOT NULL,

  house CHAR(52) NOT NULL,

  postcode VARCHAR(20) NOT NULL,

  PRIMARY KEY ( cust_id )

);

   

CREATE TABLE Orders ( 

  cust_id INTEGER(11) NOT NULL REFERENCES Customer ( cust_id) ,

  product_id INTEGER(11) NOT NULL REFERENCES Product ( product_id) ,

  PRIMARY KEY ( product_id, cust_id )

);

   

CREATE TABLE UserhasProduct ( 

  user_id INTEGER(11) NOT NULL REFERENCES User ( user_id) ,

  product_id INTEGER(11) NOT NULL REFERENCES Product ( product_id) ,

  PRIMARY KEY ( product_id, user_id )

);

   

Should the tables interact with each other through that code?

 

Thanks

Link to comment
Share on other sites
Psycho Prolific Member

Psycho

Posted
Posted

Are tables which have relations with other tables supposed to share data?

 

Tables don't "communicate" or share data with each other. It is up to you, the programmer, to create references between the tables. IF you insert a record into the "Customer" table for a new customer then insert a record into the "Orders" table for that customer the database doesn't "know" that those two records are related. YOU have to create the references and then do JOINS on the tables to get the related data between tables to make it useful.

 

Your tables above have the foreign keys (i.e. references) necessary to do what you need. You just need to make sure you are populating the data correctly AND that you are uaing the proper queries when extracting data. Here is an example.

 

New customer signs up on the site by filling out a form. You would take the POST data and create a new record for that user with a query such as

INSERT INTO Customer
    (first_name, last_name, house, postcode)
VALUES
    ('$fname', '$lname', '$house', '$postcode')

 

I assume that the field cust_id is an auto-increment field. You would then want to "capture" the ID for that customer while they are on the site. At the time you insert the record you could use mysql_insert_id() to get the ID that was just generated. Or, when the customer logs in at a later time you would do a SELECT query to get the ID. You would then, most likely, want to store the ID in a session variable to track with the user while they navigate the site in case you need the ID in a later script.

 

OK, so now you have a customer record created and you have the customer ID stored in a session value. Now we will assume that the user adds some products to their shopping cart and places an order. You now need to create the order record(s). Your order table only has two fields (cust_id & product_id). That doesn't seem sufficient. For one, there is no way to track between mutiple orders for a customer and each order can only have one product. You would want one table to store the order "container" which will include fields such as [(fk) identifies a forein key which is associated to another table] order_id (fk), cust_id (fk), date, status, etc. You would then have an associated table for the products that belong to the order. That way an order can have multiple products. That table (let's call it order_detail) would contain fields such as: order_id (fk), prod_id (fk), qty, price_per_unit.

 

So, you have a shopping cart full of products and you need to create an order for the customer. The process would go something like this:

 

1. Creat the order record using the cust_id you have stored in the user's session and any other one-to-one information for the order. Something like

INSERT INTO Orders
    (cust_id, date, status)
VALUES
    ('{$_SESSION['cust_id]}', NOW(), 'placed')

 

2. Get the order ID of the record you just created so you can add the products to the order

$orderID = mysql_insert_id();

 

3. Insert the records for the products of the order. I will assume the product details are in a session array.

$productInserts = array();
foreach($_SESSION['order'] as $prod)
{
    $productInserts = "('{$orderID}', '{$prod['id']}', '{$prod['qty']}', '{$prod['price']}')\n";
}

$query = "INSERT INTO Order_details
              (order_id, prod_id, qty, price_per_unit)
          VALUES
             " . implode(', ', $productInserts);

 

Now, you have your data in separate tables with proper associations, now comes the real benefit of a relational database.

 

Let's say you wanted to provide the customer a list of all the orders he has placed AND show all the products that were in each order. You could get that info by doing a simple JOIN on the Orders and Order_details tables such as

SELECT *
FROM Orders
JOIN Order_details USING (order_id)
Where Orders.cust_id = $cust_id

 

If none of this makes sense to you, you need to go read up on some tutorials with using databases. Tizag has some really good stuff

Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.