Page 1 of 1

Building an array into a query

Posted: Wed Jan 11, 2006 3:08 am
by rsmarsha
I'll explain the context first. :)

I have a product database containing all the product info.

A form passing variables through in the form $postName = category, $postValue = product id.

I then need to enter the description from products into a table containing the order info such as the descriptions and other info. This means i need to loop through the id's to get all the descriptions, which i am then putting into an array. This array the needs to form part of a query.

Code:

Code: Select all

//this builds the array containing the product descriptions
 foreach ($_POST as $postName => $postValue) { 
if (($postName>0 && $postName<31) && $postName!='quantity') {
$display = "SELECT * FROM categories,products WHERE categories.cat_id=products.cat_id AND categories.cat_id='$postName' AND prod_id='$postValue'";
$dq = mysql_query($display) or die("Query $display Failed".mysql_error());
$array=array(); 
$i=0; 
while ($type_row=mysql_fetch_object($dq)) 
{ 
$array[$i]=',\''.$type_row->desc.'\'';
$i++; 
} 
	}

		}

//query

$enterorder = "INSERT INTO orders (orderno,username,dateadded,status,archive,price,exvat,payment,cpu,memory,motherboard,usb,hdd,hdda,raid,cdrom,dvd,dvdw,gcard,gcarda,sound,modem,network,floppy,cardreader,pccase,psu,osreq,firewire,monitor,keyboard,mouse,speakers,printer,virus,office,tvcard,onsite,q,discount,total,totalexvat,deldet,refer,bat,ccase,pbar,l_model,config) VALUES ('','$mail','$datejoined','$type','','$_POST[price]','$_POST[exvat2]','Finance'
//array would go in this bit of the query
,'$_POST[quantity]','$_POST[discount]','$_POST[final]','$_POST[extotal]','$_POST[deldet]','$refer'$array2)";
The reason it's done like this is the order table was already there, originally using a text file for the info, i'm putting the products in the database and creating a new ordering process.

All the post variables such as the prod cats and id's, price and other bits are passed through from an earlier page. As the order table contains product descriptions and price i've had to use the above loop to create the array, missing out some of the posted variables.

Any advice is greatly appreciated. :)

Posted: Wed Jan 11, 2006 7:04 am
by BDKR
I wouldn't call your code legible. :wink:

Code: Select all

//this builds the array containing the product descriptions
foreach ($_POST as $postName => $postValue)
    {
    if (($postName>0 && $postName<31) && $postName!='quantity')
        {
        $display = "SELECT * FROM categories,products WHERE categories.cat_id=products.cat_id AND
        categories.cat_id='$postName' AND prod_id='$postValue'";
        $dq = mysql_query($display) or die("Query $display Failed".mysql_error());
        $array=array();
        $i=0;
        while ($type_row=mysql_fetch_object($dq))
            {
            $array[$i]=',\''.$type_row->desc.'\'';
            $i++;
            
            /* Put your insert query here! Also run that insert query here as well. */
            }
        }
    }
As for updating the query at each loop, php has a ton of string manipulation functions that can get you over the hump here.

Cheers

Posted: Wed Jan 11, 2006 9:44 am
by timvw
I'd probably use insert select

Posted: Wed Jan 11, 2006 11:36 am
by rsmarsha
I've used insert select before, but i need to insert the post variables as one query.

eg.

orders table

orderno prod1 prod 2 and so on.

:)

Sorry about the code, hehe didn't look like that before i'm sure. :)

Posted: Wed Jan 11, 2006 3:21 pm
by timvw
Meaby i'm not awake well, but the following would work too:

Code: Select all

$mysql['somepostedvariable'] = mysql_real_escape_string($_POST['something']);

INSERT INTO sometable 
SELECT somevalue, othervalue, $mysql['somepostedvariable'], yetanothervalue
FROM othertable

Posted: Thu Jan 12, 2006 2:59 am
by rsmarsha
I'd have to do that for each of the 30 variables though, wouldn't I?

First for each posted variable i have to pull out a description from the product table and then insert all those into one row of another table.