PHP / MYSQL Multiply each column of each row by the selected value
I am trying to create a multiplication system to give consumers results for a product that I think they will like. I'm new to PHP and MYSQL, so if you can add a little explanation, that would be great! If not, then I'll find out!
Thus, each product gets a rating from 1-3.
___________________________________________
Name | Softness | Warmth
Point 1 | 2 | 1
Point 2 | 3 | 3
Point 3 | ... | ...
... | ... | ...
On the product page, he asks the consumer what they are looking for.
Softness [Menu 1-3]
Warmth [Menu 1-3]
I want the dropdown menu value to increase with the column values in the mysql database.
The client chooses softness: 1 and warmth: 2 Point 1: 2, 2 Point 2: 3, 6 Point 3: etc. Continues with all products
Then it will display the top 3 items with the highest values.
Question page:
How important is softness in your product?
<input id="1" type="radio" name="softness" value="-1.0">
<label for="1">Not very important</label>
<input id="2" type="radio" name="softness" value="0" checked>
<label for="2">Neutral</label>
<input id="3" type="radio" name="soft" value="1.0">
<label for="3">Very important</label>
How important is warmth in your product?
<input id="1" type="radio" name="warmth " value="-1.0">
<label for="1">Not very important</label>
<input id="2" type="radio" name="warmth " value="0" checked>
<label for="2">Neutral</label>
<input id="3" type="radio" name="warmth " value="1.0">
<label for="3">Very important</label>
This is all I got so far...
<?php
// Connect SQL
include"database/sqlcon.php";
// Select Database
$query = mysql_query(
"SELECT * FROM product LIMIT 3"
);
if(!$query) {
die(mysql_error());
}
// Get Form Values
$softness= $_POST['softness'];
$warmth= $_POST['warmth'];
while($row = mysql_fetch_array($query)) {
$productname = $row['product'];
$productsoft= $row['soft'];
$productwarm = $row['warm'];
}
$totalsoft = $productsoft * $softness;
$totalwarm = $productwarm * $warmth;
mysql_close();
?>
source to share
if this is the post and you are using mysql PDO
if(isset($_POST))
{
$items = $db->query("select * from items");
$row_items = $items->fetch();
$weight_values = array();
$x= 0;
do{
$weight_values[$x]['1'] = $_POST['Softness']*$row_items['softness'];
$weight_values[$x]['2'] = $_POST['Warmth']*$row_items['warmth'];
$x++;
}while($row_items = $items->fetch());
}
Then you can just extract the values from the array. Basically you are accessing all the information from your items table, then you will go through it 1 by 1 in a while loop. For each row, you will multiply the value in the database by the published value, and then you will put that value into an array.
Then you can access this array with
echo $weight_values[0][1]; //for item with id 1 softness value
echo $weight_values[0][2]; //for item with id 1 warmth value
If you are using mysql you should not be listing for the reason in the link
if(isset($_POST))
{
$query_items("select * from items");
$items = mysql_query($query_items,$items_db) or die(mysql_error());
$row_items = mysql_fetch_assoc($items);
$weight_values = array();
$x= 0;
do{
$weight_values[$x]['1'] = $_POST['Softness']*$row_items['softness'];
$weight_values[$x]['2'] = $_POST['Warmth']*$row_items['warmth'];
$x++;
}while($row_items = mysql_fetch_assoc($items));
}
source to share
Pure Mysql implementation, given that
- All entrances are disinfected and screened.
- All other principles / factors / Good practice followed.
IMHO looks like this:
select
*,
softness * '. $softness .' + warmth * '. $warmth .' as totalindexy
from items2
order by totalindexy DESC
LIMIT 3 -- Then it would display the top 3 items with the highest values
and this method has several advantages,
- Less data moves between
MySQL
andphp
- You don't need to process it in php after that, as it already gives you the three best options.
- If the table is huge like none of the rows, then this solution is better, as importing the entire table into
php
and sorting / filtering the top three can create memory limitation errors.
Update 1: - Regarding syntax error.
Here is a SQL Fiddle to prove / explain my solution working.
Common warning: -
Please start using MySQLi instead of MySQL ASAP as by not doing this, you must compromise the security of your application.
source to share