Cannot create mysql query for bad table structure. is it possible?!

[jarmanje]

Hello again!

I cannot figure out how to do this mysql query!  I realise the table structure is not great, But i'm working with an old system

Basically, I have two tables:

'selected'
productID, email, product1, product2, product3, product4, product5, product6, product7, product8, product9, product10

'Stocked'
stockID, code, size, price, colour, name


I want to select all values from table 'selected', And then each of the 'product1' 'product2' etc fields are Equal to a value of stockID - but i want to take the 'code' field value for that ID....

So it would look like this:

Product ID, email, product1, product1CODE, product2, product2CODE, product3, product3CODE etc etc

Hope you understand me?

Again, apologies for bad table structure!!! But this query will be run maybe 5 times a week! But saves a big/boring job of looking up the codes!!!!! (actually there are 30 fields )

[Ronald Wernecke]

this structure makes no sense at all
you could run this through a script, which is a crutch as well.

Or your selected structure needs to be rather a table, looking like this:

ID
productID
email
product

and then have entries looking like this:
ID (a running number for key)
productID (of the selected product)
email (whatever it does there)
productNr
------
ID (next number)
productID (the same ID, if it is the same selection)
email
productNr (next produkt Number)
------
etc..

This way there is no limit, and you can list them easy as you would expect from a database.

[jarmanje]

Hi Ronald, Thank you for your Reply.

Your DB structure is exactly how i'd also like to set it out.. it makes perfect sense to me. i also already limit the user to selecting 15 within flash... so no problems there.

However, I am just not able to write such a php file! And isn't it a little hectic on the mysql ?

For example, this is how i Envision it to be when the user saves 15 products to their selection.

It creates a new entry in the Selection table

order table:

orderID (auto int)
email
description

and also then create 15 entires into the Selected table:

selectedID
productID
codeID
orderID

This is very good, but a little too advanced PHP for me. I have never done such a 'looping' technique. Everytime I use php and mysql, it is saving one record at a time.

currently this is how it saves...

Code:

<?php
@require_once('database.php');
@require_once('function.php');

if(isset($_POST['varEmail'])) {
$result = saveSelection($_POST['varEmail'], $_POST['varMat0'], $_POST['varMat1'], $_POST['varMat2'], $_POST['varMat3'], $_POST['varMat4'], $_POST['varMat5'], $_POST['varMat6'], $_POST['varMat7'], $_POST['varMat8'], $_POST['varMat9'], $_POST['varMat10'], $_POST['varMat11'], $_POST['varMat12'], $_POST['varMat13'], $_POST['varMat14']);
echo "feedback=success";
}
?>

inside function.php:

Code:

function saveSelection($email, $mat0, $mat1, $mat2, $mat3, $mat4, $mat5, $mat6, $mat7, $mat8, $mat9, $mat10, $mat11, $mat12, $mat13, $mat14) {
$sql = "INSERT INTO `selections` (`email` , `mat1` , `mat2` , `mat3` , `mat4` , `mat5` , `mat6` , `mat7` , `mat8` , `mat9` , `mat10` , `mat11` , `mat12` , `mat13` , `mat14` , `mat15`) VALUES ('".$email."', '".$mat0."', '".$mat1."', '".$mat2."', '".$mat3."', '".$mat4."', '".$mat5."', '".$mat6."', '".$mat7."', '".$mat8."', '".$mat9."', '".$mat10."', '".$mat11."', '".$mat12."', '".$mat13."', '".$mat14."')";

$result = mysql_query($sql)
or die(mysql_error());
}

This just one operation, but the nice layout you discuss would actually do 16 saves. And first it would need to save a new orderID, and tell that orderID for the next 15 selections to save.

Do you get what i mean??? Probably not


Within flash, I pass the varibales in this way:

Code:

function saveSelection() {

var theVariables:URLVariables = new URLVariables();
theVariables.varEmail = MovieClip(parent).globalUserAccount;

for(var i:int=0; i < ThumbArray.length; i++)
{
    theVariables["varMat" + i] = ThumbArray[i][0];
}

for(var p=ThumbArray.length; p < 15; p++)
{
    theVariables["varMat" + p] = 0
}

var theRequest:URLRequest = new URLRequest();
theRequest.url = "saveSelection.php";
theRequest.method = URLRequestMethod.POST;
theRequest.data = theVariables;

var theLoader:URLLoader = new URLLoader();
theLoader.dataFormat = URLLoaderDataFormat.VARIABLES;
theLoader.addEventListener(Event.COMPLETE, loadCompleteHandler);
theLoader.addEventListener(IOErrorEvent.IO_ERROR, handleIOError);
theLoader.load(theRequest);

function handleIOError(event:IOErrorEvent):void {
event.target.removeEventListener(IOErrorEvent.IO_ERROR, handleIOError);
}

function loadCompleteHandler(event:Event):void {
MovieClip(root).mcCart.createOrder();
}/

}

[Ronald Wernecke]

you are right, you will have up to 15 inserts into selection table.

But this also mean, if you dont have 6 selections, you only save 6 records, and if it is 30 selections, you insert 30 records.

First of all, you should download the latest version (or the version matching your installation) of the php manual.

There you'll find, that you can receive the last insert id from mysql (I think it is mysql_insert_id) bot not sure - and have no access to the manual now.

If you can use JSON, or (if youre frontend is flash) work with amfphp, you can transfer the records as array.
Then you can walk it through with foreach loop.

[jarmanje]

I dont use amfphp, just standard php 5 and mysql - dont like amfphp

guess i'm going to have to lock myself in this weekend. What search terms would you use to best desrcibe the php technique? Loop saving?

[Ronald Wernecke]

depending on mysql version you can either just run one insert after the other (in a loop),
or secure this with a transaction (to make sure either all, or none worked).

Insert the master record first, to receive the reference for the following inserts.