You are viewing our Forum Archives. To view or take place in current topics click here.
Can someone help PHP and SQL
Posted:
Can someone help PHP and SQLPosted:
Status: Offline
Joined: Sep 12, 201013Year Member
Posts: 458
Reputation Power: 23
Status: Offline
Joined: Sep 12, 201013Year Member
Posts: 458
Reputation Power: 23
Below is a form that people can fill out to add a product which is then added to a database and used elsewhere. My add for my add product is below:
It connects to the database but does not seem to insert the form input data to the database, can anyone spot why?
<?php
// Stage S7. Open the connection to the MySQL database
include '../private/dblinkopen.php';
// Stage M1. Set up start of $output_message for addition throughout script and output to user via the browser
$output_message = '<p id="output_message"><strong>Add Your Product...</strong>';
// Stage S1. Check to see if the Save Form has been submitted - save_submitted
if (isset($_POST['save_submitted'])) {
// Stage S2. Accept the data from the $_POST array and allocate array values to PHP variables
$select_id = "SELECT * FROM bsw_db";
$result_id = mysql_query($select_id);
while ($row = mysql_fetch_array($result_id))
{ $next_product_id = $row['next_product_id'];
}
$add_reference_no = $next_product_id;
$add_product_name = htmlentities(trim($_POST['product_name']),ENT_QUOTES,"ISO-8859-15");
$add_product_description = htmlentities(trim($_POST['product_description']),ENT_QUOTES,"ISO-8859-15");
$add_product_location = htmlentities(trim($_POST['product_location']),ENT_QUOTES,"ISO-8859-15");
$add_product_price = htmlentities(trim($_POST['product_price']),ENT_QUOTES,"ISO-8859-15");
$add_category_id = htmlentities(trim($_POST['category_id']),ENT_QUOTES,"ISO-8859-15");
$add_product_DATE_ADDED = htmlentities(trim($_POST['DATE_ADDED']),ENT_QUOTES,"ISO-8859-15");
// $_FILES: Uploaded files are held in the $_FILES array
// Filenames of uploaded files are amended to include $add_referennce_no if image filename is supplied
// Stage S3. Initialise the $errors array to hold error messages
$errors = array();
// Stage S4. Check that values have been supplied for mandatory data items
// 1. category_name
if (empty($add_product_name)) {
$errors[] = 'You must supply the <strong>Product Name</strong>';
}
if (empty($add_product_description)) {
$errors[] = 'You must supply the <strong>Product Description</strong>';
}
if (empty($add_product_price)) {
$errors[] = 'You must supply the <strong>Product Price</strong>';
}
if ($add_category_id == '0') {
$errors[] = 'You must supply the <strong>Product Category</strong>';
}
// Stage M2. Check to see that $errors array is NOT empty, then update $output_message for output to user via the browser
if (!empty($errors)) {
$output_message = $output_message.'<br /><span>ERROR(S):</span> - This Product has not been posted<br />';
foreach ($errors as $msg) {
$output_message = $output_message." - $msg<br />\n";
}
// Stage S5. Else, i.e. if $errors array is empty
} else {
// Stage S6. Set up $insert as MySQL INSERT statement
$insert = "
INSERT INTO bsw_products
( product_name,
product_description,
product_location,
product_price,
category_id,
DATE_ADDED )
VALUES
( '$add_product_name',
'$add_product_description',
'$add_product_location',
'$add_product_price',
'$add_category_id',
'$add_product_DATE_ADDED' ); ";
// Stage S8. Submit MySQL INSERT statement to MySQL database and assign results to $result PHP array
$result = mysql_query($insert);
// Stage S9. Update $output_message for output to user via the browser. Set $disabled_form_marker to 'disabled="disabled"'.
if (mysql_affected_rows() == 1) {
$output_message = $output_message.'<br />1 new record has been saved to the database';
$disabled_form_marker = 'disabled="disabled"';
} else {
$output_message = $output_message.'<br />Database Error: Record NOT Added In Database: '.mysql_error();
$disabled_form_marker = 'disabled="disabled"';
}
header( "Location: add-product.php");
// Stage S11. End of If statement for Save Form functionality - not (empty($errors))
} // END-OF-IF not (empty($errors))
// Stage S12. End of If statement for Save Form functionality - save_submitted
} // END-OF-IF (isset($_POST['save_submitted']))
// Stage M3. Add <p> to end of $output_message
$output_message = $output_message.'</p>';
?>
<!DOCTYPE html>
<html>
<?php include 'head_meta_info.php'; ?>
<body>
<?php include 'page_header.php'; ?>
<div id="content">
<div id="wide_column">
<h1>Adding a Product</h1>
<p>Fill in all the necessary details and click on the <strong>Save</strong> button.</p>
<?php
// Only show $output_message if Find or Save has been clicked
if (isset($_POST['save_submitted'])) {
echo $output_message;
}
?>
<form enctype="multipart/form-data" style="clear: none;" name="add-product" action="add-product.php" method="post" <?php echo $disabled_form_marker; ?>>
<table id="details">
<tr>
<td class="title">Product Name</td>
<td class="value">
<input type="text" name="product_name" size="60" maxlength="100" value="<?php if (isset($add_product_name)) echo $add_product_name; ?>" />
</td>
</tr>
<tr>
<td class="title">Product Description</td>
<td class="value">
<textarea name="product_description" rows="10" cols="80" /><?php if (isset($add_product_description)) echo $add_product_description; ?></textarea>
</td>
</tr>
<tr>
<td>Location (optional)</td>
<td class="value">
<textarea name="product_location" cols="80" /><?php if (isset($add_product_location)) echo $add_product_location; ?></textarea>
</td>
</tr>
<tr>
<td>Price</td>
<td class="value">
<textarea name="product_price" cols="80" /><?php if (isset($add_product_price)) echo $add_product_price; ?></textarea>
</td>
</tr>
<tr>
<td class="title">Product Category</td>
<td class="value">
<?php
$select_category = "select bswt.category_id,
bswt.category_name
from bsw_categories bswt
order by bswt.category_name asc";
$result_category = mysql_query($select_category);
echo '<select name="category_id">';
echo '<option value="0">Please select</option>';
while ($row = mysql_fetch_array($result_category))
{ $sub_category_id = $row['category_id'];
$sub_category_name = $row['category_name'];
if ($add_category_id == $sub_category_id) {
echo '<option value="'.$sub_category_id.'" selected="selected">'.$sub_category_name.'</option>';
} else {
echo '<option value="'.$sub_category_id.'">'.$sub_category_name.'</option>';
}
}
echo '</select>';
?>
</td>
</tr>
<tr>
<td class="title" colspan="2">
<input type="submit" value="Save">
<input type="hidden" name="save_submitted" value="TRUE">
</td>
</tr>
</table>
</form>
</div>
</div>
<?php include 'footer.php'; ?>
</body>
</html>
<?php
// Stage S10. Close the connection to the MySQL database
include '../private/dblinkclose.php';
?>
It connects to the database but does not seem to insert the form input data to the database, can anyone spot why?
#2. Posted:
Status: Offline
Joined: Jun 29, 201310Year Member
Posts: 211
Reputation Power: 13
Status: Offline
Joined: Jun 29, 201310Year Member
Posts: 211
Reputation Power: 13
The query looks incorrect. I may be wrong since the formatting is odd and there's no syntax highlighting and I'm lazy but there seems to be a ";" where there probably shouldn't be.
Also, you may as well scrap all of this and start using OOP code along with ditching mysql_* functions since they'll be gone in the future versions of PHP. I suggest PDO; to a lesser extent, mysqli.
Also, you may as well scrap all of this and start using OOP code along with ditching mysql_* functions since they'll be gone in the future versions of PHP. I suggest PDO; to a lesser extent, mysqli.
- 2useful
- 0not useful
#3. Posted:
Status: Offline
Joined: May 02, 201211Year Member
Posts: 1,129
Reputation Power: 34
Status: Offline
Joined: May 02, 201211Year Member
Posts: 1,129
Reputation Power: 34
1) Switch to MySQLI at least. Looks like you're new so whenever you're ready.. you can go ahead and convert this to OOP and maybe use an ORM like doctrine.
2) This code block:
Needs to be looked at. What the code is doing is assigning the LAST row to the variable $next_product_id. Thus you are overwriting each row. If you are only suppose to get ONE row then change up your SELECT query and add in a condition.
On a larger scale, if you have a table that contains 1+ mil rows (like TTG's Users table), then you will notice that your code will take a long time to run when in reality you will only need to get one ROW!
With that said, I assume that this is a bug. You need to look at your logic and redo this part because I am confused on why you are selection ALL the columns for that table.
3. Didn't test your code but try changing the insert values to this:
4. Learn how to debug your code. For query debugging, I normally just go to PHPMYADMIN and test out the query there. If it doesn't work, you will know why.
5. You do not need to close the connection for the DB.
2) This code block:
while ($row = mysql_fetch_array($result_id))
{ $next_product_id = $row['next_product_id'];
}
$add_reference_no = $next_product_id;
Needs to be looked at. What the code is doing is assigning the LAST row to the variable $next_product_id. Thus you are overwriting each row. If you are only suppose to get ONE row then change up your SELECT query and add in a condition.
On a larger scale, if you have a table that contains 1+ mil rows (like TTG's Users table), then you will notice that your code will take a long time to run when in reality you will only need to get one ROW!
With that said, I assume that this is a bug. You need to look at your logic and redo this part because I am confused on why you are selection ALL the columns for that table.
3. Didn't test your code but try changing the insert values to this:
VALUES
( '{$add_product_name}',
'{$add_product_description}',
'{$add_product_location}',
'{$add_product_price}',
'{$add_category_id}',
'{$add_product_DATE_ADDED}' );
4. Learn how to debug your code. For query debugging, I normally just go to PHPMYADMIN and test out the query there. If it doesn't work, you will know why.
5. You do not need to close the connection for the DB.
- 0useful
- 0not useful
#4. Posted:
Status: Offline
Joined: Jun 23, 201310Year Member
Posts: 2,308
Reputation Power: 228
Status: Offline
Joined: Jun 23, 201310Year Member
Posts: 2,308
Reputation Power: 228
I'd recommend using html entities on a few of those queries.
Also never define a string and then echo it; as it could provide vital information people need for gaining access or information from your database.
Also never define a string and then echo it; as it could provide vital information people need for gaining access or information from your database.
- 0useful
- 0not useful
You are viewing our Forum Archives. To view or take place in current topics click here.