PHP PROBLEM ADDING VALUES INTO DATABASE

[mrbobey]

Good day guyz... I dont know why this code does not work...

the connection is fine, the database is also fine it works with the localhost, when i upload it on free hosting site like 000webhost and infinityfree this is not working.. the database on free hosting site are also working also the connection is code, just only this code i cant figure it out..

else{

$sql = "INSERT INTO .$name (TrxID, ProductName, Price, ProductImage, PurchasedDate, PurchasedStatus) VALUES (?, ?, ?, ?, ?, ?)";

$stmt = mysqli_stmt_init($conn2);

if(!mysqli_stmt_prepare($stmt, $sql)){

echo 'connection error';

}

mysqli_stmt_bind_param($stmt, "isssss", $val, $PName, $Price, $PImage, $PDate, $PStatus);

mysqli_stmt_execute($stmt);

mysqli_stmt_close($stmt);

header("location: ../customer/cart.html");

exit();

}

}

 

[requinix]

Could be the syntax error in your SQL. Could be the table doesn't exist. Could be the table exists but doesn't have the right columns or types. Could be something related to earlier code which obviously exists but you decided not to show us.

[mrbobey]

there is no error in syntax i used that code in localhost through xamp and it is fine... the table im using is auto create table when there is a user register he has a table for his own.. i connect into that database, creating table to that database is fine also when there is someone regiter. but when im going to put a value to the columns on that table there is no event that happened

[mrbobey]

i will post the full code for this...

the function that is creating the table

function createcart($conn2, $Regname){
   $sql =  "CREATE TABLE .$Regname (
            TrxID INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            ProductName VARCHAR(50) NOT NULL,
            Price DECIMAL(4.2) NOT NULL,
            Quantity INT(6) NOT NULL,
            ProductImage VARCHAR (255) NOT NULL,
            PurchasedDate VARCHAR(10) NOT NULL,
            PurchasedStatus VARCHAR(10) NOT NULL
            )";
    if ($conn2->query($sql) === TRUE){
        header("location: ../index.html");
        exit();
    }
    else{
        header("location: ../account.html?error=CannotCreateAccount");
        exit();
    }

my productdetail.html

<?php
		$uri =  $_SERVER["REQUEST_URI"];
		$uriArray = explode('/', $uri);
		$value = $uriArray[4];
		$_SESSION["PID"] = $value;
		require_once '../connection/connection.php';
		$sql = "SELECT * FROM featuredlist WHERE ProductID = ?";
		$stmt = mysqli_stmt_init($conn1);
		if (!mysqli_stmt_prepare($stmt, $sql)) {
		 echo 'connection error';
		}
		mysqli_stmt_bind_param($stmt, "i", $value);
		mysqli_stmt_execute($stmt);
		$resultdata = mysqli_stmt_get_result($stmt);
		if($row = mysqli_fetch_assoc($resultdata)){
			$DetailName = $row["ProductName"];
			$DetailPrice = $row["Price"];
			$DetailImage = $row["ProductImage"];
			$DetailCategory = $row["Category"];
			$DetailDetail = $row["ProductDetail"];
			$_SESSION["ProdName"] = $DetailName;
			$_SESSION["ProdPrice"] = $DetailPrice;
			$_SESSION["ProdImage"] = $DetailImage;
			$_SESSION["ProdDate"] = date("y.m.d");
			$_SESSION["ProdStatus"] = 'Not Paid';
		echo '<div class="col-2">
				<img src="'.$DetailImage.'" width="100%">
				</div>
				<div class="col-2">
				<h3>Category</h3>
				<p>'.$DetailCategory.'</p>
				<h1>'.$DetailName.'</h1>
				<h4>'.$DetailPrice.'php</h4>
				<form class="buy" action = "" method = "POST">
				<a href="../connection/buy.php" name = "buybtn" class="buy-btn">Add to Cart</a>
				</form>
				<h3>Product Information</h3><br>
				<p>'.$DetailDetail.'</p>
				</div>';
			}
		?>
my buy.php
<?php
session_start();
if(isset($_SESSION["Usrname"])){
require_once 'connection.php';
$name = $_SESSION["Usrname"];
$PName = $_SESSION["ProdName"];
$Price = $_SESSION["ProdPrice"];
$PImage = $_SESSION["ProdImage"];
$PDate = $_SESSION["ProdDate"];
$PStatus = $_SESSION["ProdStatus"];
$val = $_SESSION["PID"];
$sql = "SELECT * FROM .$name WHERE TrxID = ?";
    $stmt = mysqli_stmt_init($conn2);
    if(!mysqli_stmt_prepare($stmt, $sql)){
       echo "cannot buy this product please try again";
    }
    mysqli_stmt_bind_param($stmt, "s", $val);
    mysqli_stmt_execute($stmt);
    $resultdata = mysqli_stmt_get_result($stmt);
    if($row = mysqli_fetch_assoc($resultdata)){

        header("location: ../customer/cart.html");
        exit();
    }
    else{
    $sql = "INSERT INTO .$name (TrxID, ProductName, Price, ProductImage, PurchasedDate, PurchasedStatus) VALUES (?, ?, ?, ?, ?, ?)";
    $stmt = mysqli_stmt_init($conn2);
    if(!mysqli_stmt_prepare($stmt, $sql)){
    echo 'connection error';
}   
    mysqli_stmt_bind_param($stmt, "isssss", $val, $PName, $Price, $PImage, $PDate, $PStatus);
    mysqli_stmt_execute($stmt);
    mysqli_stmt_close($stmt);
    header("location: ../customer/cart.html");
    exit();
    }
}
else{
    header("location: ../account.html");
    exit();
}

 

Edited August 2, 2021 by mrbobey

[requinix]

3 minutes ago, mrbobey said:

there is no error in syntax i used that code in localhost through xamp and it is fine...

So your working code really has ".$name" in it?

3 minutes ago, mrbobey said:

the table im using is auto create table when there is a user register he has a table for his own..

That is bad. Stop doing that. Use one table for the records from all users.

The change should be simple: create one table with a name, add a column for the user ID (or if you don't have one, the name), and add appropriate WHERE conditions in the places you need that.

1 hour ago, mrbobey said:

not working

What does "not working" mean? What does it do? What does it not do? What do you expect to see happen?

[mrbobey]

16 minutes ago, requinix said:

So your working code really has ".$name" in it?

Yes because everytime when someone register that variable to be called. and that variable has something to do with my session so that when there name is being called it point to their specific table values

That is bad. Stop doing that. Use one table for the records from all users.

okay, i'm just a beginner thats what it comes to my mind. thank you for the suggestion i have to change it later but definitely change the whole back.end

The change should be simple: create one table with a name, add a column for the user ID (or if you don't have one, the name), and add appropriate WHERE conditions in the places you need that.

What does "not working" mean? What does it do? What does it not do? What do you expect to see happen?

my code works in my localhost(xampp) no errors. The code does when the customer buy the product that he see in the product detail page it add on his specific cart and he immediately landed on his cart page. but when i upload the website on free hosting site 000webhost and infinityfree it does not work now... I checked already the connection it is okay, i querry the product from my database into my product page, it does. but when the customer buy the product, it land only his cart page and does not saving his product into the database.. when i replace my database from my backup with values to figure it out.. it really does see it in his cart page.. but when i buy another product using the hosting site its not working anymore... but you can register into my account page and you can log.in also with your account. that is my problem.

 

[requinix]

3 minutes ago, mrbobey said:

Yes because everytime when someone register that variable to be called. and that variable has something to do with my session so that when there name is being called it point to their specific table values

I was pointing out the period, not the variable.

4 minutes ago, mrbobey said:

okay, i'm just a beginner thats what it comes to my mind. thank you for the suggestion i have to change it later but definitely change the whole back.end

Imagine how much harder it will be to fix later.

There's a really, really obvious problem here that you have to solve: being extremely careful about what user names you allow. Not only must you require letters and perhaps numbers, you can't allow certain words because it might conflict with other real tables.

7 minutes ago, mrbobey said:

when the customer buy the product, it land only his cart page and does not saving his product into the database..

That suggests this line

if($row = mysqli_fetch_assoc($resultdata)){

is happening. Possibly the INSERT is not inserting, which you can check if you paid attention to return values from the mysqli functions, but that tends to be less likely.

[mrbobey]

4 minutes ago, requinix said:

I was pointing out the period, not the variable.

Imagine how much harder it will be to fix later.

There's a really, really obvious problem here that you have to solve: being extremely careful about what user names you allow. Not only must you require letters and perhaps numbers, you can't allow certain words because it might conflict with other real tables.

That suggests this line

if($row = mysqli_fetch_assoc($resultdata)){

is happening. Possibly the INSERT is not inserting, which you can check if you paid attention to return values from the mysqli functions, but that tends to be less likely.

if there is wrong with the code why it is working in my localhost? I am sending you the video so that you will see exactly what is happening

[mrbobey]

Just now, mrbobey said:

if there is wrong with the code why it is working in my localhost? I am sending you the video so that you will see exactly what is happening

i cant upload a video, only picture

[mac_gyver]

there are a fairly large number of things that can cause your code to work on one system but not another and if your code is lacking validation and error handling logic, your code won't tell you why it is failing. under perfect conditions, on your development system, your code may work, but if anything goes wrong, you won't get any help from your code.

firstly, you need to temporarily set php's error_reporting to E_ALL and set display_errors to ON, so that you will get immediate feedback if any php errors are occurring (the cheep/free web hosts may not allow you to do this.)

you also need consistent and useful error handling for all the database statements that can fail - connection, query, prepare, and execute (which you don't currently have any error handling for.) the simplest way of adding error handling for all the database statements is to use exceptions for errors and in most cases let php catch and handle the exception, where php will use its error related settings (see the above paragraph) to control what happens with the actual error information (database statement errors will 'automatically' get displayed/logged the same as php errors.) this will let you remove the existing error handling logic and skip adding it where there is none. the exception to letting php catch the exceptions is when inserting/updating duplicate or out of range user submitted values, which is what you should be doing to detect if a product is already in the cart. in this case, your code should catch the exception, detect if the error number is for something that your code is designed to handle, then setup and display a message for the user telling them what was wrong with the data that they submitted. for all other error numbers, simply re-throw the exception and let php handle it.

it doesn't appear that you have any validation logic? you should always trim, then validate all input data before using it. if there are no validation errors, use the submitted data. if there are validation errors, display them when you re-display the add product form(s). the only product information you insert into the cart is the product id and the quantity. all the other product data exists in the featuredlist table and should not be duplicated in the cart. 

you have a lot of unnecessary code that isn't helping. by putting the form processing code on a different page from the add product form(s), you have more code, a bunch of redirects, and are producing a bad user experience. when you put these on the same page, there will be less code, you can eliminate all but one redirect (upon successful completion), and you can display any validation/user error messages when you re-display the add product form(s). the only redirect you should have in your post method form processing code is upon successful completion of the processing code and it should be to the exact same url of the current page. this will cause a get request for that page. if you want the user to be able to navigate to other pages, provide navigation links. if you want to display a one-time success message, store it in a session variable, then test, display, and clear the session variable at the appropriate location in the html document.

as to another reason why your code may not work, on one system, but not another, and be giving the symptom of going back to the cart.html page - what does using a phpinfo() statement in a php script show for the output_buffering setting? because you are redirecting all over the place (which hopefully you will remove, see the above paragraph), any debugging output from your script and any non-fatal php errors will get discarded if the ouput_buffering setting is ON (any non-zero value.) you should set this to OFF.

lastly, you should not attempt to SELECT data in order to decide if it already exists. you should set up the table with a unique composite index for the column(s) that identify the user id and product id combination, just attempt to insert the data, and detect if a duplicate index error occurred (see the above paragraph concerning catching an exception for a duplicate error.) 

[mrbobey]

16 minutes ago, mac_gyver said:

there are a fairly large number of things that can cause your code to work on one system but not another and if your code is lacking validation and error handling logic, your code won't tell you why it is failing. under perfect conditions, on your development system, your code may work, but if anything goes wrong, you won't get any help from your code.

firstly, you need to temporarily set php's error_reporting to E_ALL and set display_errors to ON, so that you will get immediate feedback if any php errors are occurring (the cheep/free web hosts may not allow you to do this.)

you also need consistent and useful error handling for all the database statements that can fail - connection, query, prepare, and execute (which you don't currently have any error handling for.) the simplest way of adding error handling for all the database statements is to use exceptions for errors and in most cases let php catch and handle the exception, where php will use its error related settings (see the above paragraph) to control what happens with the actual error information (database statement errors will 'automatically' get displayed/logged the same as php errors.) this will let you remove the existing error handling logic and skip adding it where there is none. the exception to letting php catch the exceptions is when inserting/updating duplicate or out of range user submitted values, which is what you should be doing to detect if a product is already in the cart. in this case, your code should catch the exception, detect if the error number is for something that your code is designed to handle, then setup and display a message for the user telling them what was wrong with the data that they submitted. for all other error numbers, simply re-throw the exception and let php handle it.

it doesn't appear that you have any validation logic? you should always trim, then validate all input data before using it. if there are no validation errors, use the submitted data. if there are validation errors, display them when you re-display the add product form(s). the only product information you insert into the cart is the product id and the quantity. all the other product data exists in the featuredlist table and should not be duplicated in the cart. 

you have a lot of unnecessary code that isn't helping. by putting the form processing code on a different page from the add product form(s), you have more code, a bunch of redirects, and are producing a bad user experience. when you put these on the same page, there will be less code, you can eliminate all but one redirect (upon successful completion), and you can display any validation/user error messages when you re-display the add product form(s). the only redirect you should have in your post method form processing code is upon successful completion of the processing code and it should be to the exact same url of the current page. this will cause a get request for that page. if you want the user to be able to navigate to other pages, provide navigation links. if you want to display a one-time success message, store it in a session variable, then test, display, and clear the session variable at the appropriate location in the html document.

as to another reason why your code may not work, on one system, but not another, and be giving the symptom of going back to the cart.html page - what does using a phpinfo() statement in a php script show for the output_buffering setting? because you are redirecting all over the place (which hopefully you will remove, see the above paragraph), any debugging output from your script and any non-fatal php errors will get discarded if the ouput_buffering setting is ON (any non-zero value.) you should set this to OFF.

lastly, you should not attempt to SELECT data in order to decide if it already exists. you should set up the table with a unique composite index for the column(s) that identify the user id and product id combination, just attempt to insert the data, and detect if a duplicate index error occurred (see the above paragraph concerning catching an exception for a duplicate error.) 

wohhh... i think my mind get explode while absorbing the necessary things that you said.. thanks alot im not getting this information through hearing from the professor or else it would be repeated   thank you for this... i am gladly you made effort for checking my file.. thanks alot.. im just only a newbie here where youtube and google is my instructor.. thank you sir..

[mac_gyver]

most of the points will actually simplify the code e.g. putting the form processing and the form(s) on the same page will eliminate all those lines of code for the session variables. the only increase in code are for things that don't exist now that the code needs.

i thought of two more things that could cause the code/query(ies) to work on one server but not another and you might not get any indication from the current code/configuration as to what the problem is -

1. this has to do with the output_buffering setting (if it's on, turn it off) and using the procedural mysqli statements. the error response for the procedural mysqli statements is different from the OOP mysqli statements. things that would be a fatal runtime error, if using OOP, halting program execution, are just warnings, if using procedural statements, and the code will continue to run, which if output_buffering is on, will discard the php warning messages.
2. this has to do with the database server's strict mode setting and having no error handling for the execute() calls. if strict mode is set to ON, on the cheep/free hosting, out of range values will produce an error, but since there's currently no error handling for the execute() call, you don't know if this is happening or not. using exceptions for errors will solve this since it will give you error handling for all the database statements that can fail. if strict mode is off, on your development system, out of range values will instead be truncated to the nearest legal value for the data type, without producing an error.

i didn't state it previously, but switching to the much simpler, more consistent, and better designed PDO extension will also simplify the code.

Edited August 2, 2021 by mac_gyver