Hey guys,

 

so I find myself in an odd position.

 

I am a designer at heart, but obviously it doesn't hurt to know some development skills.

I am in the process of designing/developing a 'glue selector' for my company which can be found here: http://www.hilldesigns.co.uk/projects/glue-selector/

 

The app will be used to show the customer the best 5 glues for a specific set of rules, the scoring is pretty simple, and works on a 1 - 4 system:

 

1 = Poor

2 = Fair

3 = Good

4 = Very Good

 

So I got the app to a stage where it was working, and showed it to the directors, they were very impressed but asked for a few minor tweaks.

 

They understandably want to be able to update the records themselves, without coding knowledge, so I built them an admin section, which is where the problem lies.

 

<?php
// Create connection
include('../includes/db_info.php');
$connection = mysql_connect($host, $user, $pass) or die("Could not connect to Database");
mysql_select_db($db) or die("Could not select Database: " .$db);

$query = "SELECT * FROM substrates";
$runMysql = mysql_query($query);

// Variables
$reference = $_POST['reference'];
$opentime = $_POST['opentime'];
$colour = $_POST['colour'];
$url = $_POST['url'];
$url15 = "http://www.hilldesigns.co.uk"; 	// Temp value, just so it holds some data
$url43 = "http://www.hilldesigns.co.uk"; 	// Temp value, just so it holds some data
$urlct = "http://www.hilldesigns.co.uk"; 	// Temp value, just so it holds some data

// The start of the $insert string I am trying to create will always start like below
$insertStr = "INSERT INTO performance (reference, opentime, colour, url, url15, url43, urlct";

// Therefore it will also need to have these values
$insertStrEnd = ") VALUES ('$reference', '$opentime', '$colour', '$url', '$url15', '$url43', '$urlct'";
$i = 0;


// Set variables dynamically.
while($row = mysql_fetch_row($runMysql))	// Run a loop as many times as there are rows, each time do the following:
{
	$sub_name = $row[1]; 					// Set $sub_name = {database_value} e.g $sub_name = "PLASTIC";
	$sub_name = strtolower($sub_name); 		// Sets $sub_name to lower case
	$$sub_name = $_POST[$sub_name]; 		// This is a variable variable, hopefully it will output: $plastic = $_POST['plastic']; <= That is what I need it to equal, I think it may be a little off though.

	/*
	$val = $sub_name; // Assign Variable $val
	echo $val . "<br />"; // Uncomment for testing;
	*/

	// We need to add each database entry 
	// (or substrate) to the string that will
	// eventually be sent as a query.
	// There has got to be an easier way 
	// to do this?!
	$append = ", " . $row[1]; 				// Set $append to equal ", {database_vale}" -- e.g ", PLASTIC"
	$append = strtolower($append);			// Sets $append value to lower case -- e.g ", plastic"
	$insertStr .= $append;					// Append $append to $insertStr -- e.g Adding ", plastic"


	// And then we run the same routine
	// for the 'variable values'
	$variable = ", '$" . $row[1] . "'";		// Set $variable to equal ", '${database_vale}'" -- e.g ", '$PLASTIC'"
	$variable = strtolower($variable);		// Sets $variable value to lower case -- e.g ", '$plastic'"
	$insertStrEnd .= $variable;				// Append $variable to $insertStr -- e.g Adding ", '$plastic'"
};

$insertStrEnd .= ")";						// Close the $insertStrEnd with an appropriate ")"

// Query Database
$insert = $insertStr . $insertStrEnd;		// Create the string (and assign it to $insert) we have been aiming for the whole time by appending the beginning and end sections.
echo $insert;								// Echo out $insert, comment out when not testing.

/* $result = mysql_query($insert) or die("Whoops: " . mysql_error()); */ // Send query to database, comment out when testing
?>

 

I'm pretty sure my specific problem lies with the 'variable variable', as I dont see how it is passing in a value from the form. Any help is greatley appreciated

 

Much love.

Can't you just use an array?

 

$substrates = array();

while($row = mysql_fetch_row($runMysql)){
   $substrates[$row[1]] = $_POST[$row[1]];
};

foreach ($substrates as $substrate => $value) {
   $insertStr .= ', '.$substrate;
   $insertStrEnd .= ", '".$value."'";
}

//..

Actually, do you even need to do a foreach? probably not, you could probably get the business done in the while loop just by putting the strings together.

 

Also, the more I think about it, something like that is schematically wrong. The substrates/attributes shouldn't be pinned onto the product table, they should be in their own database table... eg.

 

product_substrates
------------------
product_id | substrate_id | value

Edited August 5, 201016 yr by Jock

Can't you just use an array?

 

$substrates = array();

while($row = mysql_fetch_row($runMysql)){
   $substrates[$row[1]] = $_POST[$row[1]];
};

foreach ($substrates as $substrate => $value) {
   $insertStr .= ', '.$substrate;
   $insertStrEnd .= ", '".$value."'";
}

//..

 

 

I could actually kiss you right now.

Scotland is a bit far for me, but I would drive the 8 hours just to kiss you.

 

Seriously dude, been struggling with this for a while.

I over complicating things. You are simply a hero.

 

I'll be back next time I fail. Look for the 'Jock-signal'!

 

x <-- will have to do for now.

Actually, do you even need to do a foreach? probably not, you could probably get the business done in the while loop just by putting the strings together.

 

Also, the more I think about it, something like that is schematically wrong. The substrates/attributes shouldn't be pinned onto the product table, they should be in their own database table... eg.

 

product_substrates
------------------
product_id | substrate_id | value

 

Jock, we were golden. I was ready to go about my daily failings.

Now you're telling me I'm failing at failing.

 

Bugger.

 

I will definitely read up on arrays, could never get the hang of them, they always seemed useless to me, but I've seen 2 examples you have shown (one was about ham and rotisseries?!) in the last few posts that have made me see the error of my ways.

First off, I trust you've been escaping your variables used in mysql statements

 

Another simplification might be to use form-array notation (<input name="substrate[plastic]"...) then just foreach over that key in the $_POST array. For security, you should also set what columns are actually allowed within the php script. So have an array containing the valid substrate names and check them with something like

if (in_array($currentName, $substrateList) == false) {
   continue;
} else {
   // add it to the sql statement after escaping the value
}

Lewwy, the least you could do is +1 Jock

First off, I trust you've been escaping your variables used in mysql statements

 

Another simplification might be to use form-array notation (<input name="substrate[plastic]"...) then just foreach over that key in the $_POST array. For security, you should also set what columns are actually allowed within the php script. So have an array containing the valid substrate names and check them with something like

if (in_array($currentName, $substrateList) == false) {
   continue;
} else {
   // add it to the sql statement after escaping the value
}

 

Appreciate the help.

It has become clear to me that I don't really know anything about PHP. I will sort out my security issues, thanks for pointing those out

 

Lewwy, the least you could do is +1 Jock

 

Well reminded, I have spread the +1 love.

eKiss accepted.